DBCA

●CDB編集cp -pr HUB3C01 HUB3C01.orggrep 'HUB3C01_[^1]' `find HUB3C01`cd HUB3C01/scripts
cp -p HUB3C01_1.sql HUB3C01_1.sql.orgsed -i 's/orapwHUB3C01_/orapwHUB3C01/' HUB3C01_1.sqlsed -i '/JServer.sql/s/^/--/' HUB3C01_1.sqlsed -i '/context.sql/s/^/--/' HUB3C01_1.sqlsed -i '/cwmlite.sql/s/^/--/' HUB3C01_1.sqlsed -i '/spatial.sql/s/^/--/' HUB3C01_1.sqlsed -i '/labelSecurity.sql/s/^/--/' HUB3C01_1.sqlsed -i '/datavault.sql/s/^/--/' HUB3C01_1.sql
cp -p CreateDB.sql CreateDB.sql.orgsed -i \-e 's/SMALLFILE/BIGFILE/' \-e 's/TABLESPACE TEMP/TABLESPACE HUB3C01_TEMP01/' \-e 's/UNDOTBS1/HUB3C01_UNDO01/' \
CreateDB.sql
cp -p CreateDBFiles.sql CreateDBFiles.sql.orgsed -i \-e 's/USERS/HUB3C01_USER01/' \CreateDBFiles.sql
●HUB3P01編集cp -p plug_HUB3P01.sql plug_HUB3P01.sql.orgsed -i \-e 's/SMALLFILE/BIGFILE/' \-e 's/TABLESPACE TEMP TEMPFILE SIZE.*;/TABLESPACE HUB3P01_TEMP01 TEMPFILE SIZE 10240M;/' \-e 's/\"TEMP\"/\"HUB3P01_TEMP01\"/' \plug_HUB3P01.sqldiff plug_HUB3P01.sql.org plug_HUB3P01.sql
cp -p postPDBCreation_HUB3P01.sql postPDBCreation_HUB3P01.sql.orgsed -i \-e 's/\"USERS\"/\"HUB3P01_USER01\"/' \-e '/CREATE BIGFILE/s/DATAFILE *SIZE.*;/DATAFILE SIZE 10240M;/' \postPDBCreation_HUB3P01.sqldiff postPDBCreation_HUB3P01.sql.org postPDBCreation_HUB3P01.sql
●HUB3P02編集cp -p plug_HUB3P02.sql plug_HUB3P02.sql.orgsed -i \-e 's/SMALLFILE/BIGFILE/' \-e 's/TABLESPACE TEMP TEMPFILE SIZE.*;/TABLESPACE HUB3P02_TEMP01 TEMPFILE SIZE 10240M;/' \-e 's/\"TEMP\"/\"HUB3P02_TEMP01\"/' \plug_HUB3P02.sqldiff plug_HUB3P02.sql.org plug_HUB3P02.sql
cp -p postPDBCreation_HUB3P02.sql postPDBCreation_HUB3P02.sql.orgsed -i \-e 's/\"USERS\"/\"HUB3P02_USER01\"/' \-e '/CREATE BIGFILE/s/DATAFILE *SIZE.*;/DATAFILE SIZE 10240M;/' \postPDBCreation_HUB3P02.sqldiff postPDBCreation_HUB3P02.sql.org postPDBCreation_HUB3P02.sql
/hub/pp1/26ai/grid/bin/crsctl stop crssystemctl poweroff

[root@hub3sudk1a scripts]# cat CreateDB.sql.orgSET VERIFY OFFconnect "SYS"/"&&sysPassword" as SYSDBAset echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/CreateDB.log appendstartup nomount pfile="/hub/pp1/oracle/admin/HUB3C01/scripts/init.ora";CREATE DATABASE "HUB3C01"MAXINSTANCES 32MAXLOGHISTORY 1MAXLOGFILES 192MAXLOGMEMBERS 3MAXDATAFILES 1024DATAFILE SIZE 700M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITEDEXTENT MANAGEMENT LOCALSYSAUX DATAFILE SIZE 550M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITEDSMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITEDSMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITEDCHARACTER SET JA16SJISTILDENATIONAL CHARACTER SET AL16UTF16SET DEFAULT BIGFILE TABLESPACELOGFILE GROUP 1 SIZE 200M,GROUP 2 SIZE 200M,GROUP 3 SIZE 200MUSER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"enable pluggable database LOCAL UNDO ON;set linesize 2048;column ctl_files NEW_VALUE ctl_files;select concat('control_files=''', concat(replace(value, ', ', ''','''), '''')) ctl_files from v$parameter where name ='control_files';host echo &ctl_files >>/hub/pp1/oracle/admin/HUB3C01/scripts/init.ora;spool off[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]# cat HUB3C01_1.sql.orgset verify offACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDEACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDEACCEPT dbsnmpPassword CHAR PROMPT 'Enter new password for DBSNMP: ' HIDEACCEPT pdbAdminPassword CHAR PROMPT 'Enter new password for PDBADMIN: ' HIDEhost /hub/pp1/oracle/product/26ai/db/bin/srvctl add database -d HUB3C01 -pwfile +DATADG_1/HUB3C01/orapwHUB3C01_ -o /hub/pp1/oracle/product/26ai/db -n HUB3C01 -a "DATADG_1,ARCDG_1,ARCDG_2"host /hub/pp1/oracle/product/26ai/db/bin/srvctl add instance -d HUB3C01 -i HUB3C01_1 -n hub3sudk1ahost /hub/pp1/oracle/product/26ai/db/bin/srvctl disable database -d HUB3C01host /hub/pp1/oracle/product/26ai/db/bin/orapwd file=+DATADG_1/HUB3C01/orapwHUB3C01_ force=y format=12 dbuniquename=HUB3C01@/hub/pp1/oracle/admin/HUB3C01/scripts/CreateDB.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/CreateDBFiles.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/CreateDBCatalog.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/JServer.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/context.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/cwmlite.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/spatial.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/labelSecurity.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/datavault.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/CreateClustDBViews.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/lockAccount.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/postDBCreation.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/PDBCreation.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/plug_HUB3P01.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/postPDBCreation_HUB3P01.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/plug_HUB3P02.sql@/hub/pp1/oracle/admin/HUB3C01/scripts/postPDBCreation_HUB3P02.sql[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]# cat CreateDBFiles.sql.orgSET VERIFY OFFconnect "SYS"/"&&sysPassword" as SYSDBAset echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/CreateDBFiles.log appendCREATE BIGFILE TABLESPACE "USERS" LOGGING DATAFILE SIZE 7M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;ALTER DATABASE DEFAULT TABLESPACE "USERS";spool off[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]# cat postDBCreation.sqlSET VERIFY OFFconnect "SYS"/"&&sysPassword" as SYSDBAalter user DBSNMP identified by "&&dbsnmpPassword" account unlock;spool /hub/pp1/oracle/admin/HUB3C01/scripts/postDBCreation.log appendhost /hub/pp1/oracle/product/26ai/db/OPatch/datapatch -skip_upgrade_check -db HUB3C01_1;shutdown immediate;connect "SYS"/"&&sysPassword" as SYSDBAstartup mount pfile="/hub/pp1/oracle/admin/HUB3C01/scripts/init.ora";alter database archivelog;alter database open;host echo cluster_database=true >>/hub/pp1/oracle/admin/HUB3C01/scripts/init.ora;connect "SYS"/"&&sysPassword" as SYSDBAset echo oncreate spfile='+DATADG_1' FROM pfile='/hub/pp1/oracle/admin/HUB3C01/scripts/init.ora';connect "SYS"/"&&sysPassword" as SYSDBAhost /hub/pp1/oracle/product/26ai/db/perl/bin/perl /hub/pp1/oracle/product/26ai/db/rdbms/admin/catcon.pl -n 1 -l /hub/pp1/oracle/admin/HUB3C01/scripts -v -b utlrp -U "SYS"/"&&sysPassword" /hub/pp1/oracle/product/26ai/db/rdbms/admin/utlrp.sql;select comp_id, status from dba_registry;shutdown immediate;host /hub/pp1/oracle/product/26ai/db/bin/srvctl enable database -d HUB3C01;host /hub/pp1/oracle/product/26ai/db/bin/srvctl start database -d HUB3C01;connect "SYS"/"&&sysPassword" as SYSDBAspool offselect instance from v$thread where instance like 'UNNAMED_INSTANCE%';[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]# cat plug_HUB3P01.sql.orgSET VERIFY OFFconnect "SYS"/"&&sysPassword" as SYSDBAset echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/plugDatabase.log appendspool /hub/pp1/oracle/admin/HUB3C01/scripts/plugDatabase.log appendCREATE PLUGGABLE DATABASE "HUB3P01" ADMIN USER PDBADMIN IDENTIFIED BY "&&pdbadminPassword" ROLES=(CONNECT) PARALLEL file_name_convert=NONE STORAGE INHERIT;select name from v$containers where upper(name) = 'HUB3P01';alter pluggable database "HUB3P01" open instances=all;alter system register;ALTER SESSION SET CONTAINER = "HUB3P01";select con_id from v$pdbs where con_id > 1 and upper(name)=upper('HUB3P01') ;SELECT bigfile FROM sys.cdb_tablespaces WHERE tablespace_name='TEMP' AND CON_ID=0;CREATE SMALLFILE TEMPORARY TABLESPACE TEMP_NON_ENC TEMPFILE SIZE 20480K AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP_NON_ENC";alter session set container=cdb$root;ALTER PLUGGABLE DATABASE "HUB3P01" CLOSE IMMEDIATE instances=all;alter pluggable database "HUB3P01" open instances=all;alter system register;ALTER SESSION SET CONTAINER = "HUB3P01";drop tablespace TEMP including contents and datafiles;CREATE SMALLFILE TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20480K AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";alter session set container=cdb$root;ALTER PLUGGABLE DATABASE "HUB3P01" CLOSE IMMEDIATE instances=all;alter pluggable database "HUB3P01" open instances=all;alter system register;ALTER SESSION SET CONTAINER = "HUB3P01";drop tablespace TEMP_NON_ENC including contents and datafiles;[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]# cat postPDBCreation_HUB3P01.sql.orgSET VERIFY OFFconnect "SYS"/"&&sysPassword" as SYSDBAalter session set container="HUB3P01";set echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/postPDBCreation.log appendCREATE BIGFILE TABLESPACE "USERS" LOGGING DATAFILE SIZE 7M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;ALTER DATABASE DEFAULT TABLESPACE "USERS";host /hub/pp1/oracle/product/26ai/db/OPatch/datapatch -skip_upgrade_check -db HUB3C01_1 -pdbs HUB3P01;connect "SYS"/"&&sysPassword" as SYSDBAselect property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';connect "SYS"/"&&sysPassword" as SYSDBAalter session set container="HUB3P01";set echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/postPDBCreation.log appendconnect "SYS"/"&&sysPassword" as SYSDBAalter session set container="HUB3P01";set echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/postPDBCreation.log appendselect TABLESPACE_NAME from cdb_tablespaces a,dba_pdbs b where a.con_id=b.con_id and UPPER(b.pdb_name)=UPPER('HUB3P01');connect "SYS"/"&&sysPassword" as SYSDBAalter session set container="HUB3P01";set echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/postPDBCreation.log appendSelect count(*) from dba_registry where comp_id = 'DV' and status='VALID';show con_name;alter session set container="HUB3P01";select count(a.username) from cdb_users a, v$pdbs b where a.con_id=b.con_id and a.username=upper('PDBADMIN') and upper(b.name)=upper('HUB3P01');show con_name;alter session set container="CDB$ROOT";alter session set container=CDB$ROOT;[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]# cat CreateClustDBViews.sqlSET VERIFY OFFconnect "SYS"/"&&sysPassword" as SYSDBAset echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/CreateClustDBViews.log appendhost /hub/pp1/oracle/product/26ai/db/perl/bin/perl /hub/pp1/oracle/product/26ai/db/rdbms/admin/catcon.pl -n 1 -l /hub/pp1/oracle/admin/HUB3C01/scripts -v -b catclust -U "SYS"/"&&sysPassword" /hub/pp1/oracle/product/26ai/db/rdbms/admin/catclust.sql;host /hub/pp1/oracle/product/26ai/db/perl/bin/perl /hub/pp1/oracle/product/26ai/db/rdbms/admin/catcon.pl -n 1 -l /hub/pp1/oracle/admin/HUB3C01/scripts -v -b catfinal -U "SYS"/"&&sysPassword" /hub/pp1/oracle/product/26ai/db/rdbms/admin/catfinal.sql;spool offconnect "SYS"/"&&sysPassword" as SYSDBAset echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/postDBCreation.log append[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]# cat CreateDBCatalog.sqlSET VERIFY OFFconnect "SYS"/"&&sysPassword" as SYSDBAset echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/CreateDBCatalog.log appendalter session set "_oracle_script"=true;alter pluggable database pdb$seed close;alter pluggable database pdb$seed open;host /hub/pp1/oracle/product/26ai/db/perl/bin/perl /hub/pp1/oracle/product/26ai/db/rdbms/admin/catctl.pl -u "SYS"/"&&sysPassword" -n 4 -icatpcat -c 'CDB$ROOT PDB$SEED' -a -d /hub/pp1/oracle/product/26ai/db/rdbms/admin -l /hub/pp1/oracle/admin/HUB3C01/scripts rdbms/admin/catpcat.sql;host /hub/pp1/oracle/product/26ai/db/perl/bin/perl /hub/pp1/oracle/product/26ai/db/rdbms/admin/catcon.pl -n 1 -l /hub/pp1/oracle/admin/HUB3C01/scripts -v -b owminst -U "SYS"/"&&sysPassword" /hub/pp1/oracle/product/26ai/db/rdbms/admin/owminst.plb;host /hub/pp1/oracle/product/26ai/db/perl/bin/perl /hub/pp1/oracle/product/26ai/db/rdbms/admin/catcon.pl -n 1 -l /hub/pp1/oracle/admin/HUB3C01/scripts -v -b pupbld -u SYSTEM/&&systemPassword -U "SYS"/"&&sysPassword" /hub/pp1/oracle/product/26ai/db/sqlplus/admin/pupbld.sql;host /hub/pp1/oracle/product/26ai/db/perl/bin/perl /hub/pp1/oracle/product/26ai/db/rdbms/admin/catcon.pl -n 1 -l /hub/pp1/oracle/admin/HUB3C01/scripts -v -b pupdel -u SYS/&&sysPassword -U "SYS"/"&&sysPassword" /hub/pp1/oracle/product/26ai/db/sqlplus/admin/pupdel.sql;connect "SYS"/"&&sysPassword" as SYSDBAset echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/sqlPlusHelp.log appendhost /hub/pp1/oracle/product/26ai/db/perl/bin/perl /hub/pp1/oracle/product/26ai/db/rdbms/admin/catcon.pl -n 1 -l /hub/pp1/oracle/admin/HUB3C01/scripts -v -b hlpbld -u SYS/&&sysPassword -U "SYS"/"&&sysPassword" -a 1 /hub/pp1/oracle/product/26ai/db/sqlplus/admin/help/hlpbld.sql;spool offspool off[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]# cat PDBCreation.sqlSET VERIFY OFFset echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/PDBCreation.log append[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]# cat lockAccount.sqlSET VERIFY OFFconnect "SYS"/"&&sysPassword" as SYSDBAset echo onspool /hub/pp1/oracle/admin/HUB3C01/scripts/lockAccount.log appendalter session set "_oracle_script"=true;alter pluggable database pdb$seed close;alter pluggable database pdb$seed open;BEGIN FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ('SYS','SYSTEM','SYSRAC','XS$NULL') ) LOOPIF item.AUTHENTICATION_TYPE='PASSWORD' THEN 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' ; ELSE dbms_output.put_line('Locking: ' || item.USERNAME); execute immediate 'alter user ' || sys.dbms_assert.enquote_name( sys.dbms_assert.schema_name( item.USERNAME),false) || ' account lock' ; END IF; END LOOP;END;/alter session set container=pdb$seed;BEGIN FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ('SYS','SYSTEM','SYSRAC','XS$NULL') ) LOOPIF item.AUTHENTICATION_TYPE='PASSWORD' THEN 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' ; ELSE dbms_output.put_line('Locking: ' || item.USERNAME); execute immediate 'alter user ' || sys.dbms_assert.enquote_name( sys.dbms_assert.schema_name( item.USERNAME),false) || ' account lock' ; END IF; END LOOP;END;/alter session set container=cdb$root;spool off[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]# cat mkDir.sqlSET VERIFY OFFconnect / as SYSDBA;alter diskgroup DATADG_1 add directory '+DATADG_1/HUB3C01';exit;[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#[root@hub3sudk1a scripts]#

Page updated

Google Sites

Report abuse