1. Database 생성
A) 작업 스크립트 준비
oracle 유저로 생성
mkdir -p $ORACLE_BASE/admin/[DB_NAME]/scripts cd $ORACLE_BASE/admin/[DB_NAME]/scripts
[oracle@racdb1 ~]$ mkdir -p $ORACLE_BASE/admin/racdb/scripts [oracle@racdb1 ~]$ cd $ORACLE_BASE/admin/racdb/scripts
스크립트 생성 디렉토리를 $ORACLE_BASE 경로 아래에 생성합니다.
작업할 스크립트를 모두 이곳에 저장합니다.
oracle 유저로 vi racDB.sh 생성
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p [ORACLE_BASE]/admin/[DB_NAME]/adump
mkdir -p [ORACLE_BASE]/admin/[DB_NAME]/dpdump
mkdir -p [ORACLE_BASE]/admin/[DB_NAME]/hdump
mkdir -p [ORACLE_BASE]/admin/[DB_NAME]/pfile
mkdir -p [ORACLE_BASE]/cfgtoollogs/dbca/[DB_NAME]
ssh oracle@[HOST_NAME2] "mkdir -p [ORACLE_BASE]/admin/[DB_NAME]/adump"
ssh oracle@[HOST_NAME2] "mkdir -p [ORACLE_BASE]/admin/[DB_NAME]/dpdump"
ssh oracle@[HOST_NAME2] "mkdir -p [ORACLE_BASE]/admin/[DB_NAME]/hdump"
ssh oracle@[HOST_NAME2] "mkdir -p [ORACLE_BASE]/admin/[DB_NAME]/pfile"
ssh oracle@[HOST_NAME2] "mkdir -p [ORACLE_BASE]/cfgtoollogs/dbca/[DB_NAME]"
umask ${OLD_UMASK}
ORACLE_SID=[ORACLE_SID1]; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: [ORACLE_SID]:[DB_HOME]:N
[DB_HOME]/bin/sqlplus /nolog @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateRACDB.sql
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/racdb/adump
mkdir -p /u01/app/oracle/admin/racdb/dpdump
mkdir -p /u01/app/oracle/admin/racdb/hdump
mkdir -p /u01/app/oracle/admin/racdb/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/racdb
ssh oracle@rac2 "mkdir -p /u01/app/oracle/admin/racdb/adump"
ssh oracle@rac2 "mkdir -p /u01/app/oracle/admin/racdb/dpdump"
ssh oracle@rac2 "mkdir -p /u01/app/oracle/admin/racdb/hdump"
ssh oracle@rac2 "mkdir -p /u01/app/oracle/admin/racdb/pfile"
ssh oracle@rac2 "mkdir -p /u01/app/oracle/cfgtoollogs/dbca/racdb":
umask ${OLD_UMASK}
ORACLE_SID=racdb1; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: racdb:/u01/app/oracle/product/11.2.0/db_1:N
/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus /nolog @/u01/app/oracle/admin/racdb/scripts/CreateRACDB.sql
데이터베이스 생성을 위해 필요한 디렉토리를 만들고 전체 작업을 수행할 메인 스크립트를 생성합니다.
괄호 [] 안에 표시된 변수 부분은 실제 설치할 환경에 해당되는 내용으로 수정하여 저장하도록 합니다.
[ORACLE_BASE] : 오라클 소프트웨어 설치 베이스 경로
[DB_NAME] : 데이터베이스 명
[HOST_NAME2] : 2번 노드의 서버 명
[ORACLE_SID1] : 1번 인스턴스 명
[ORACLE_SID] : 각 노드의 인스턴스 명
[DB_HOME] : 데이터베이스 소프트웨어 설치 경로
oracle 유저로 vi CreateRACDB.sql 생성
set verify off ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE host [DB_HOME]/bin/orapwd file=[DB_HOME]/dbs/orapw[ORACLE_SID1] force=y host ssh oracle@[HOST_NAME2] "[DB_HOME]/bin/orapwd file=[DB_HOME]/dbs/orapw[ORACLE_SID2] force=y" host [GRID_HOME]/bin/setasmgidwrap o=[DB_HOME]/bin/oracle host [DB_HOME]/bin/srvctl add database -d [DB_NAME] -o [DB_HOME] -p /dev/raw/raw20 -n [DB_NAME] host [DB_HOME]/bin/srvctl add instance -d [DB_NAME] -i [ORACLE_SID1] -n [HOST_NAME1] host [DB_HOME]/bin/srvctl add instance -d [DB_NAME] -i [ORACLE_SID2] -n [HOST_NAME2] host [DB_HOME]/bin/srvctl disable database -d [DB_NAME] @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateDB.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateDBFiles.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateDBCatalog.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateJVM.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateClustDBViews.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateOracleText.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateXDB.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateORDSchema.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateMultiMedia.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateOLAP.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateSpatial.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateEMRepos.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateAPEX.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateOWB.sql host echo "SPFILE='/dev/raw/raw20'" > [DB_HOME]/dbs/init[ORACLE_SID1].ora @[ORACLE_BASE]/admin/[DB_NAME]/scripts/lockAccount.sql @[ORACLE_BASE]/admin/[DB_NAME]/scripts/postDBCreation.sql
set verify off ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE host /u01/app/oracle/product/11.2.0/db_1/bin/orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdb1 force=y host ssh oracle@rac2 "/u01/app/oracle/product/11.2.0/db_1/bin/orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdb2 force=y" host /u01/app/11.2.0/grid/bin/setasmgidwrap o=/u01/app/oracle/product/11.2.0/db_1/bin/oracle host /u01/app/oracle/product/11.2.0/db_1/bin/srvctl add database -d racdb -o /u01/app/oracle/product/11.2.0/db_1 -p /dev/raw/raw20 -n racdb host /u01/app/oracle/product/11.2.0/db_1/bin/srvctl add instance -d racdb -i racdb1 -n rac1 host /u01/app/oracle/product/11.2.0/db_1/bin/srvctl add instance -d racdb -i racdb2 -n rac2 host /u01/app/oracle/product/11.2.0/db_1/bin/srvctl disable database -d racdb @/u01/app/oracle/admin/racdb/scripts/CreateDB.sql @/u01/app/oracle/admin/racdb/scripts/CreateDBFiles.sql @/u01/app/oracle/admin/racdb/scripts/CreateDBCatalog.sql @/u01/app/oracle/admin/racdb/scripts/CreateJVM.sql @/u01/app/oracle/admin/racdb/scripts/CreateClustDBViews.sql @/u01/app/oracle/admin/racdb/scripts/CreateOracleText.sql @/u01/app/oracle/admin/racdb/scripts/CreateXDB.sql --@/u01/app/oracle/admin/racdb/scripts/CreateORDSchema.sql --@/u01/app/oracle/admin/racdb/scripts/CreateMultiMedia.sql --@/u01/app/oracle/admin/racdb/scripts/CreateOLAP.sql --@/u01/app/oracle/admin/racdb/scripts/CreateSpatial.sql --@/u01/app/oracle/admin/racdb/scripts/CreateEMRepos.sql --@/u01/app/oracle/admin/racdb/scripts/CreateAPEX.sql --@/u01/app/oracle/admin/racdb/scripts/CreateOWB.sql host echo "SPFILE='/dev/raw/raw20'" > /u01/app/oracle/product/11.2.0/db_1/dbs/initracdb1.ora @/u01/app/oracle/admin/racdb/scripts/lockAccount.sql @/u01/app/oracle/admin/racdb/scripts/postDBCreation.sql
서비스에 등록하고 각각의 SQL 스크립트를 수행할 스크립트를 생성합니다.
괄호 [] 안에 표시된 변수 부분은 실제 설치할 환경에 해당되는 내용으로 수정하여 저장하도록 합니다.
[DB_HOME]
[ORACLE_SID1]
[GRID_HOME]
[DB_NAME]
[ORACLE_SID1]
[HOST_NAME1]
[ORACLE_SID2]
[HOST_NAME2]
[ORACLE_BASE]
oracle 유저로 vi CreateDB.sql 생성
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateDB.log append
startup nomount pfile="[ORACLE_BASE]/admin/[DB_NAME]/scripts/init.ora";
CREATE DATABASE "[DB_NAME]"
MAXINSTANCES 32
MAXLOGHISTORY 1
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '/dev/raw/raw6' SIZE 699M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/dev/raw/raw7' SIZE 699M AUTOEXTEND OFF
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/dev/raw/raw9' SIZE 699M AUTOEXTEND OFF
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/dev/raw/raw10' SIZE 699M AUTOEXTEND OFF
CHARACTER SET KO16MSWIN949
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/dev/raw/raw14') SIZE 199M,
GROUP 2 ('/dev/raw/raw15') SIZE 199M,
GROUP 3 ('/dev/raw/raw16') SIZE 199M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
spool off
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/racdb/scripts/CreateDB.log append
startup nomount pfile="/u01/app/oracle/admin/racdb/scripts/init.ora";
CREATE DATABASE "racdb"
MAXINSTANCES 32
MAXLOGHISTORY 1
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '/dev/raw/raw6' SIZE 699M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/dev/raw/raw7' SIZE 699M AUTOEXTEND OFF
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/dev/raw/raw9' SIZE 699M AUTOEXTEND OFF
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/dev/raw/raw10' SIZE 699M AUTOEXTEND OFF
CHARACTER SET KO16MSWIN949
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/dev/raw/raw14') SIZE 199M,
GROUP 2 ('/dev/raw/raw15') SIZE 199M,
GROUP 3 ('/dev/raw/raw16') SIZE 199M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
spool off
oracle 유저로 vi CreateDBFiles.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateDBFiles.log append CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/dev/raw/raw11' SIZE 699M AUTOEXTEND OFF; CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/dev/raw/raw8' SIZE 699M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; ALTER DATABASE DEFAULT TABLESPACE "USERS"; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateDBFiles.log append CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/dev/raw/raw11' SIZE 699M AUTOEXTEND OFF; CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/dev/raw/raw8' SIZE 699M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; ALTER DATABASE DEFAULT TABLESPACE "USERS"; spool off
oracle 유저로 vi CreateDBCatalog.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateDBCatalog.log append @[DB_HOME]/rdbms/admin/catalog.sql; @[DB_HOME]/rdbms/admin/catblock.sql; @[DB_HOME]/rdbms/admin/catproc.sql; @[DB_HOME]/rdbms/admin/catoctk.sql; @[DB_HOME]/rdbms/admin/owminst.plb; connect "SYSTEM"/"&&systemPassword" set echo on @[DB_HOME]/sqlplus/admin/pupbld.sql; @[DB_HOME]/sqlplus/admin/help/hlpbld.sql helpus.sql; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateDBCatalog.log append @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catalog.sql; @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catblock.sql; @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catproc.sql; @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catoctk.sql; @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/owminst.plb; connect "SYSTEM"/"&&systemPassword" set echo on @/u01/app/oracle/product/11.2.0/db_1/sqlplus/admin/pupbld.sql; @/u01/app/oracle/product/11.2.0/db_1/sqlplus/admin/help/hlpbld.sql helpus.sql; spool off
oracle 유저로 vi CreateJVM.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateJVM.log append @[DB_HOME]/javavm/install/initjvm.sql; @[DB_HOME]/xdk/admin/initxml.sql; @[DB_HOME]/xdk/admin/xmlja.sql; @[DB_HOME]/rdbms/admin/catjava.sql; @[DB_HOME]/rdbms/admin/catxdbj.sql; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateJVM.log append @/u01/app/oracle/product/11.2.0/db_1/javavm/install/initjvm.sql; @/u01/app/oracle/product/11.2.0/db_1/xdk/admin/initxml.sql; @/u01/app/oracle/product/11.2.0/db_1/xdk/admin/xmlja.sql; @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catjava.sql; @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catxdbj.sql; spool off
oracle 유저로 vi CreateClustDBViews.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateClustDBViews.log append @[DB_HOME]/rdbms/admin/catclust.sql; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateClustDBViews.log append @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catclust.sql; spool off
oracle 유저로 vi CreateOracleText.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateOracleText.log append @[DB_HOME]/ctx/admin/catctx [PASSWORD] SYSAUX TEMP NOLOCK; connect "CTXSYS"/"[PASSWORD]" @[DB_HOME]/ctx/admin/defaults/dr0defin.sql "KOREAN"; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateOracleText.log append @/u01/app/oracle/product/11.2.0/db_1/ctx/admin/catctx change_on_install SYSAUX TEMP NOLOCK; connect "CTXSYS"/"change_on_install" @/u01/app/oracle/product/11.2.0/db_1/ctx/admin/defaults/dr0defin.sql "KOREAN"; spool off
oracle 유저로 vi CreateXDB.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateXDB.log append @[DB_HOME]/rdbms/admin/catqm.sql [PASSWORD] SYSAUX TEMP YES; connect "SYS"/"&&sysPassword" as SYSDBA @[DB_HOME]/rdbms/admin/catxdbj.sql; @[DB_HOME]/rdbms/admin/catrul.sql; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateXDB.log append @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP YES; connect "SYS"/"&&sysPassword" as SYSDBA @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catxdbj.sql; @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catrul.sql; spool off
oracle 유저로 vi CreateORDSchema.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateORDSchema.log append @[DB_HOME]/ord/admin/ordinst.sql SYSAUX SYSAUX; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateORDSchema.log append @/u01/app/oracle/product/11.2.0/db_1/ord/admin/ordinst.sql SYSAUX SYSAUX; spool off
oracle 유저로 vi CreateMultiMedia.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateMultiMedia.log append @[DB_HOME]/ord/im/admin/iminst.sql; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateMultiMedia.log append @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/iminst.sql; spool off
oracle 유저로 vi CreateOLAP.sql 생성
SET VERIFY OFF set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateOLAP.log append connect "SYS"/"&&sysPassword" as SYSDBA @[DB_HOME]/olap/admin/olap.sql SYSAUX TEMP; spool off
SET VERIFY OFF set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateOLAP.log append connect "SYS"/"&&sysPassword" as SYSDBA @/u01/app/oracle/product/11.2.0/db_1/olap/admin/olap.sql SYSAUX TEMP; spool off
oracle 유저로 vi CreateSpatial.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateSpatial.log append @[DB_HOME]/md/admin/mdinst.sql; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateSpatial.log append @/u01/app/oracle/product/11.2.0/db_1/md/admin/mdinst.sql; spool off
oracle 유저로 vi CreateEMRepos.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo off spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateEMRepos.log append @[DB_HOME]/sysman/admin/emdrep/sql/emreposcre [DB_HOME] SYSMAN &&sysPassword TEMP ON; WHENEVER SQLERROR CONTINUE; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo off spool /u01/app/oracle/admin/racdb/scripts/CreateEMRepos.log append @/u01/app/oracle/product/11.2.0/db_1/sysman/admin/emdrep/sql/emreposcre /u01/app/oracle/product/11.2.0/db_1 SYSMAN &&sysPassword TEMP ON; WHENEVER SQLERROR CONTINUE; spool off
oracle 유저로 vi CreateAPEX.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateAPEX.log append @[DB_HOME]/apex/catapx.sql [PASSWORD] SYSAUX SYSAUX TEMP /i/ NONE; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateAPEX.log append @/u01/app/oracle/product/11.2.0/db_1/apex/catapx.sql change_on_install SYSAUX SYSAUX TEMP /i/ NONE; spool off
oracle 유저로 vi CreateOWB.sql 생성
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/CreateOWB.log append @[DB_HOME]/owb/UnifiedRepos/cat_owb.sql SYSAUX TEMP; spool off
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/racdb/scripts/CreateOWB.log append @/u01/app/oracle/product/11.2.0/db_1/owb/UnifiedRepos/cat_owb.sql SYSAUX TEMP; spool off
oracle 유저로 vi lockAccount.sql 생성
SET VERIFY OFF
set echo on
spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/lockAccount.log append
BEGIN
FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ('SYS','SYSTEM') )
LOOP
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' password expire account lock' ;
END LOOP;
END;
/
spool off
SET VERIFY OFF
set echo on
spool /u01/app/oracle/admin/racdb/scripts/lockAccount.log append
BEGIN
FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ('SYS','SYSTEM') )
LOOP
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' password expire account lock' ;
END LOOP;
END;
/
spool off
oracle 유저로 vi postDBCreation.sql 생성
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool [ORACLE_BASE]/admin/[DB_NAME]/scripts/postDBCreation.log append
execute DBMS_AUTO_TASK_ADMIN.disable();
@[DB_HOME]/rdbms/admin/catbundle.sql psu apply;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
select group# from v$log where group# =3;
select group# from v$log where group# =4;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ('/dev/raw/raw17') SIZE 199M,
GROUP 5 ('/dev/raw/raw18') SIZE 199M,
GROUP 6 ('/dev/raw/raw19') SIZE 199M;
ALTER DATABASE ENABLE PUBLIC THREAD 2;
host echo cluster_database=true >>[ORACLE_BASE]/admin/[DB_NAME]/scripts/init.ora;
host echo remote_listener=[SCAN_NAME]:[LISTENER_PORT]>>[ORACLE_BASE]/admin/[DB_NAME]/scripts/init.ora;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='/dev/raw/raw20' FROM pfile='[ORACLE_BASE]/admin/[DB_NAME]/scripts/init.ora';
shutdown immediate;
host [DB_HOME]/bin/srvctl enable database -d [DB_NAME];
host [DB_HOME]/bin/srvctl start database -d [DB_NAME];
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
@[DB_HOME]/rdbms/admin/utlrp.sql;
spool off
exit;
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/racdb/scripts/postDBCreation.log append
execute DBMS_AUTO_TASK_ADMIN.disable();
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catbundle.sql psu apply;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
select group# from v$log where group# =3;
select group# from v$log where group# =4;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ('/dev/raw/raw17') SIZE 199M,
GROUP 5 ('/dev/raw/raw18') SIZE 199M,
GROUP 6 ('/dev/raw/raw19') SIZE 199M;
ALTER DATABASE ENABLE PUBLIC THREAD 2;
host echo cluster_database=true >>/u01/app/oracle/admin/racdb/scripts/init.ora;
host echo remote_listener=rac-scan:1521>>/u01/app/oracle/admin/racdb/scripts/init.ora;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='/dev/raw/raw20' FROM pfile='/u01/app/oracle/admin/racdb/scripts/init.ora';
shutdown immediate;
host /u01/app/oracle/product/11.2.0/db_1/bin/srvctl enable database -d racdb;
host /u01/app/oracle/product/11.2.0/db_1/bin/srvctl start database -d racdb;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlrp.sql;
spool off
exit;
oracle 유저로 vi init.ora 생성
db_block_size=8192
#cluster_database=true
#remote_listener=[SCAN_NAME]:[LISTENER_PORT]
open_cursors=300
db_domain=""
db_name="[DB_NAME]"
control_files=('/dev/raw/raw12','/dev/raw/raw13')
compatible=11.2.0.0.0
diagnostic_dest=[ORACLE_BASE]
memory_target=834666496
processes=150
audit_file_dest="[ORACLE_BASE]/admin/[DB_NAME]/adump"
audit_trail=db
remote_login_passwordfile=exclusive
[ORACLE_SID1].instance_number=1
[ORACLE_SID2].instance_number=2
[ORACLE_SID1].thread=1
[ORACLE_SID2].thread=2
[ORACLE_SID1].undo_tablespace=UNDOTBS1
[ORACLE_SID2].undo_tablespace=UNDOTBS2
db_recovery_file_dest_size=3G
db_recovery_file_dest='/FRA'
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# Cluster Database
###########################################
#cluster_database=true
#remote_listener=rac-scan:1521
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name="racdb"
###########################################
# File Configuration
###########################################
control_files=('/dev/raw/raw12','/dev/raw/raw13')
###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=/u01/app/oracle
sga_target=3g
pga_aggregate_target=1g
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Security and Auditing
###########################################
audit_file_dest="/u01/app/oracle/admin/racdb/adump"
audit_trail=db
remote_login_passwordfile=exclusive
racdb1.instance_number=1
racdb2.instance_number=2
racdb1.thread=1
racdb2.thread=2
racdb1.undo_tablespace=UNDOTBS1
racdb2.undo_tablespace=UNDOTBS2
oracle 유저로 수행
chmod 774 racDB.sh ls -ltra | grep racDB.sh
[racdb1:/u01/app/oracle/admin/racdb/scripts]> ls -ltra | grep racDB.sh -rwxr-x---. 1 oracle oinstall 891 4월 14 10:39 racDB.sh [racdb1:/u01/app/oracle/admin/racdb/scripts]> chmod 774 racDB.sh [racdb1:/u01/app/oracle/admin/racdb/scripts]> ls -ltra | grep racDB.sh -rwxrwxr--. 1 oracle oinstall 891 4월 14 10:39 racDB.sh
root 유저로 1번 노드에서 수행
chmod 770 [ORACLE_BASE]/cfgtoollogs
[root@rac1 oracle]# chmod 770 /u01/app/oracle/cfgtoollogs
B) 데이터베이스 생성
oracle 유저에서 수행
cd $ORACLE_BASE/admin/[DB_NAME]/scripts sh racDB.sh
[oracle@racdb1 ~]$ cd $ORACLE_BASE/admin/racdb/scripts
[racdb1:/u01/app/oracle/admin/racdb/scripts]> sh racDB.sh
You should Add this entry in the /etc/oratab: racdb:/u01/app/oracle/product/11.2.0/db_1:N
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 14 15:26:19 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter new password for SYS:
Enter new password for SYSTEM:
Enter password for SYS:
Enter password for SYS: oracle
Unable to find error file.
Connected to an idle instance.
SQL> spool /u01/app/oracle/admin/racdb/scripts/CreateDB.log append
SQL> startup nomount pfile="/u01/app/oracle/admin/racdb/scripts/init.ora";
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 654314896 bytes
Database Buffers 2533359616 bytes
Redo Buffers 16904192 bytes
SQL> CREATE DATABASE "racdb"
2 MAXINSTANCES 32
3 MAXLOGHISTORY 1
4 MAXLOGFILES 192
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 1024
7 DATAFILE '/dev/raw/raw6' SIZE 1000M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL
8 SYSAUX DATAFILE '/dev/raw/raw7' SIZE 1000M AUTOEXTEND OFF
9 SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/dev/raw/raw9' SIZE 699M AUTOEXTEND OFF
10 SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/dev/raw/raw10' SIZE 1000M AUTOEXTEND OFF
11 CHARACTER SET KO16MSWIN949
12 NATIONAL CHARACTER SET AL16UTF16
13 LOGFILE GROUP 1 ('/dev/raw/raw14') SIZE 199M,
14 GROUP 2 ('/dev/raw/raw15') SIZE 199M,
15 GROUP 3 ('/dev/raw/raw16') SIZE 199M
16 USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
Database created.
.... << 생략 >> ....
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> EXECUTE dbms_registry_sys.validate_components;
PL/SQL procedure successfully completed.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL> spool off
not spooling currently
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
root 유저로 모든 노드의 vi /etc/oratab 에 추가
[ORACLE_SID]:[DB_HOME]:N
racdb1:/u01/app/oracle/product/11.2.0/db_1:N
racdb2:/u01/app/oracle/product/11.2.0/db_1:N
{}
