###############################################################################
Creating Multitenant Oracle 12c Database manually via Database Creation Scripts
###############################################################################
Note: DBUA gives us option to generate database creation script so we can run them manualy to create database.
Below are the scripts DBUA generated. We are only required to run shell script that is <dbname>.sh.
[oracle@dbtrmrk 12.1.0]$ ll
total 17624
drwxr-xr-x 69 oracle oinstall 4096 Apr 30 05:39 12cR1
-rwxr-xr-x 1 oracle oinstall 815 Apr 30 08:42 cdbdemo.sh
-rwxr-xr-x 1 oracle oinstall 649 Apr 30 08:42 cdbdemo.sql
-rw-r----- 1 oracle oinstall 1601 Apr 30 08:42 cloneDBCreation.sql
-rw-r----- 1 oracle oinstall 789 Apr 30 08:42 CloneRmanRestore.sql
-rw-r----- 1 oracle oinstall 1947 Apr 30 08:42 initcdbdemoTempOMF.ora
-rw-r----- 1 oracle oinstall 2047 Apr 30 08:42 initcdbdemoTemp.ora
-rw-r----- 1 oracle oinstall 1979 Apr 30 08:42 init.ora
-rw-r----- 1 oracle oinstall 535 Apr 30 08:42 lockAccount.sql
-rw-r----- 1 oracle oinstall 79 Apr 30 08:42 PDBCreation.sql
-rw-r----- 1 oracle oinstall 785 Apr 30 08:42 plug_pdb1.sql
-rw-r----- 1 oracle oinstall 2141 Apr 30 08:42 plug_PDBSeed.sql
-rw-r----- 1 oracle oinstall 969 Apr 30 08:42 postDBCreation.sql
-rw-r----- 1 oracle oinstall 163 Apr 30 08:42 postPDBCreation_pdb1.sql
-rw-r----- 1 oracle oinstall 1132 Apr 30 08:42 postScripts.sql
-rw-r----- 1 oracle oinstall 87 Apr 30 08:42 rmanPDBCleanUpDatafiles.sql
-rw-r----- 1 oracle oinstall 290 Apr 30 08:42 rmanPDBRestoreDatafiles.sql
-rw-r----- 1 oracle oinstall 517 Apr 30 08:42 rmanRestoreDatafiles.sql
-rw-r----- 1 oracle oinstall 17973248 Apr 30 08:42 tempControl.ctl
================================
Main Shell Script
================================
Note: As you can notice, this script set the env variables and then call other .sql scripts in sequence
[oracle@dbtrmrk 12.1.0]$ more cdbdemo.sh
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/cdbdemo/adump
mkdir -p /u01/app/oracle/admin/cdbdemo/dpdump
mkdir -p /u01/app/oracle/admin/cdbdemo/pfile
mkdir -p /u01/app/oracle/audit
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/cdbdemo
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/fast_recovery_area/cdbdemo
mkdir -p /u03/app/oracle/12.1.0/12cR1/dbs
mkdir -p /u03/arch
mkdir -p /u03/oradata/cdbdemo
mkdir -p /u03/oradata/cdbdemo/pdbseed
umask ${OLD_UMASK}
PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
ORACLE_SID=cdbdemo; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: cdbdemo:/u03/app/oracle/12.1.0/12cR1:Y
/u03/app/oracle/12.1.0/12cR1/bin/sqlplus /nolog @/u03/app/oracle/12.1.0/cdbdemo.sql
[oracle@dbtrmrk 12.1.0]$ more /u03/app/oracle/12.1.0/cdbdemo.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 /u03/app/oracle/12.1.0/12cR1/bin/orapwd file=/u03/app/oracle/12.1.0/12cR1/dbs/orapwcdbdemo force=y format=12
@/u03/app/oracle/12.1.0/CloneRmanRestore.sql
@/u03/app/oracle/12.1.0/cloneDBCreation.sql
@/u03/app/oracle/12.1.0/plug_PDBSeed.sql
@/u03/app/oracle/12.1.0/postScripts.sql
@/u03/app/oracle/12.1.0/lockAccount.sql
@/u03/app/oracle/12.1.0/postDBCreation.sql
@/u03/app/oracle/12.1.0/PDBCreation.sql
@/u03/app/oracle/12.1.0/plug_pdb1.sql
@/u03/app/oracle/12.1.0/postPDBCreation_pdb1.sql
====================================
Main <dbname>.sql script
====================================
[oracle@dbtrmrk 12.1.0]$ more cdbdemo.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 /u03/app/oracle/12.1.0/12cR1/bin/orapwd file=/u03/app/oracle/12.1.0/12cR1/dbs/orapwcdbdemo force=y format=12
@/u03/app/oracle/12.1.0/CloneRmanRestore.sql
@/u03/app/oracle/12.1.0/cloneDBCreation.sql
@/u03/app/oracle/12.1.0/plug_PDBSeed.sql
@/u03/app/oracle/12.1.0/postScripts.sql
@/u03/app/oracle/12.1.0/lockAccount.sql
@/u03/app/oracle/12.1.0/postDBCreation.sql
@/u03/app/oracle/12.1.0/PDBCreation.sql
@/u03/app/oracle/12.1.0/plug_pdb1.sql
@/u03/app/oracle/12.1.0/postPDBCreation_pdb1.sql
[oracle@dbtrmrk 12.1.0]$ more /u03/app/oracle/12.1.0/cloneDBCreation.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u03/app/oracle/12.1.0/cloneDBCreation.log append
shutdown abort;
startup nomount pfile="/u03/app/oracle/12.1.0/init.ora";
Create controlfile reuse set database "cdbdemo"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
Datafile
'&&file0',
'&&file1',
'&&file2',
'&&file3'
LOGFILE GROUP 1 ('/u03/oradata/cdbdemo/redo01.log') SIZE 50M,
GROUP 2 ('/u03/oradata/cdbdemo/redo02.log') SIZE 50M,
GROUP 3 ('/u03/oradata/cdbdemo/redo03.log') SIZE 50M RESETLOGS;
exec dbms_backup_restore.zerodbid(0);
shutdown immediate;
startup nomount pfile="/u03/app/oracle/12.1.0/initcdbdemoTemp.ora";
Create controlfile reuse set database "cdbdemo"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
Datafile
'&&file0',
'&&file1',
'&&file2',
'&&file3'
LOGFILE GROUP 1 ('/u03/oradata/cdbdemo/redo01.log') SIZE 50M,
GROUP 2 ('/u03/oradata/cdbdemo/redo02.log') SIZE 50M,
GROUP 3 ('/u03/oradata/cdbdemo/redo03.log') SIZE 50M RESETLOGS;
alter system enable restricted session;
alter database "cdbdemo" open resetlogs;
exec dbms_service.delete_service('seeddata');
exec dbms_service.delete_service('seeddataXDB');
alter database rename global_name to "cdbdemo";
ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/cdbdemo/temp01.dbf' SIZE 61440K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
select tablespace_name from dba_tablespaces where tablespace_name='USERS';
alter user sys account unlock identified by "&&sysPassword";
alter user system account unlock identified by "&&systemPassword";
===============================================
Set ORACLE_HOME before you run .sh script
===============================================
Note: if we do not set the ORACLE_HOME, we will get the following error:
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
[oracle@dbtrmrk 12.1.0]$ export ORACLE_HOME=/u03/app/oracle/12.1.0/12cR1
[oracle@dbtrmrk 12.1.0]$ ./cdbdemo.sh
You should Add this entry in the /etc/oratab: cdbdemo:/u03/app/oracle/12.1.0/12cR1:Y
SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 30 09:59:18 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter new password for SYS:
Enter new password for SYSTEM:
Enter password for SYS:
Connected to an idle instance.
SQL> spool /u03/app/oracle/12.1.0/CloneRmanRestore.log append
SQL> startup mount pfile="/u03/app/oracle/12.1.0/initcdbdemoTempOMF.ora";
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 855639816 bytes
Database Buffers 385875968 bytes
Redo Buffers 8859648 bytes
Database mounted.
SQL> execute dbms_backup_restore.resetCfileSection(dbms_backup_restore.RTYP_DFILE_COPY);
PL/SQL procedure successfully completed.
SQL> execute dbms_backup_restore.resetCfileSection(13);
PL/SQL procedure successfully completed.
SQL> host /u03/app/oracle/12.1.0/12cR1/bin/rman @/u03/app/oracle/12.1.0/rmanRestoreDatafiles.sql &&sysPassword;
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Apr 30 09:59:58 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target *
2>
3> CATALOG START WITH '/u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates//Seed_Database.dfb' NOPROMPT ;
4>
5> RUN {
6>
7> set newname for datafile 3 to '/u03/oradata/cdbdemo/sysaux01.dbf' ;
8>
9> set newname for datafile 1 to '/u03/oradata/cdbdemo/system01.dbf' ;
10>
11> set newname for datafile 6 to '/u03/oradata/cdbdemo/users01.dbf' ;
12>
13> set newname for datafile 4 to '/u03/oradata/cdbdemo/undotbs01.dbf' ;
14>
15> restore datafile 3;
16>
17> restore datafile 1;
18>
19> restore datafile 6;
20>
21> restore datafile 4; }
22>
connected to target database: SEEDDATA (DBID=4144941807, not open)
Starting implicit crosscheck backup at 30-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK
Finished implicit crosscheck backup at 30-APR-14
Starting implicit crosscheck copy at 30-APR-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 30-APR-14
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates//Seed_Database.dfb
List of Files Unknown to the Database
=====================================
File Name: /u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates/Seed_Database.dfb
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates/Seed_Database.dfb
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 30-APR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u03/oradata/cdbdemo/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates/Seed_Database.dfb
channel ORA_DISK_1: piece handle=/u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates/Seed_Database.dfb tag=TAG20130524T133058
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 30-APR-14
Starting restore at 30-APR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u03/oradata/cdbdemo/system01.dbf
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates/Seed_Database.dfb
channel ORA_DISK_1: piece handle=/u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates/Seed_Database.dfb tag=TAG20130524T133058
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 30-APR-14
Starting restore at 30-APR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u03/oradata/cdbdemo/users01.dbf
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates/Seed_Database.dfb
channel ORA_DISK_1: piece handle=/u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates/Seed_Database.dfb tag=TAG20130524T133058
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 30-APR-14
Starting restore at 30-APR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u03/oradata/cdbdemo/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates/Seed_Database.dfb
channel ORA_DISK_1: piece handle=/u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates/Seed_Database.dfb tag=TAG20130524T133058
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 30-APR-14
Recovery Manager complete.
SQL> column file0 NEW_VALUE file0;
SQL> select NAME file0 FROM V$DATAFILE_COPY where file# = 1;
FILE0
--------------------------------------------------------------------------------
/u03/oradata/cdbdemo/system01.dbf
SQL> column file1 NEW_VALUE file1;
SQL> select NAME file1 FROM V$DATAFILE_COPY where file# = 3;
FILE1
--------------------------------------------------------------------------------
/u03/oradata/cdbdemo/sysaux01.dbf
SQL> column file2 NEW_VALUE file2;
SQL> select NAME file2 FROM V$DATAFILE_COPY where file# = 4;
FILE2
--------------------------------------------------------------------------------
/u03/oradata/cdbdemo/undotbs01.dbf
SQL> column file3 NEW_VALUE file3;
SQL> select NAME file3 FROM V$DATAFILE_COPY where file# = 6;
FILE3
--------------------------------------------------------------------------------
/u03/oradata/cdbdemo/users01.dbf
SQL> spool off
SQL> @/u03/app/oracle/12.1.0/cloneDBCreation.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u03/app/oracle/12.1.0/cloneDBCreation.log append
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount pfile="/u03/app/oracle/12.1.0/init.ora";
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 855639816 bytes
Database Buffers 385875968 bytes
Redo Buffers 8859648 bytes
SQL> Create controlfile reuse set database "cdbdemo"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 1024
7 Datafile
8 '&&file0',
9 '&&file1',
10 '&&file2',
11 '&&file3'
12 LOGFILE GROUP 1 ('/u03/oradata/cdbdemo/redo01.log') SIZE 50M,
13 GROUP 2 ('/u03/oradata/cdbdemo/redo02.log') SIZE 50M,
14 GROUP 3 ('/u03/oradata/cdbdemo/redo03.log') SIZE 50M RESETLOGS;
Control file created.
SQL> exec dbms_backup_restore.zerodbid(0);
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile="/u03/app/oracle/12.1.0/initcdbdemoTemp.ora";
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 855639816 bytes
Database Buffers 385875968 bytes
Redo Buffers 8859648 bytes
SQL> Create controlfile reuse set database "cdbdemo"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 1024
7 Datafile
8 '&&file0',
9 '&&file1',
10 '&&file2',
11 '&&file3'
12 LOGFILE GROUP 1 ('/u03/oradata/cdbdemo/redo01.log') SIZE 50M,
13 GROUP 2 ('/u03/oradata/cdbdemo/redo02.log') SIZE 50M,
14 GROUP 3 ('/u03/oradata/cdbdemo/redo03.log') SIZE 50M RESETLOGS;
Control file created.
SQL> alter system enable restricted session;
System altered.
SQL> alter database "cdbdemo" open resetlogs;
Database altered.
SQL> exec dbms_service.delete_service('seeddata');
PL/SQL procedure successfully completed.
SQL> exec dbms_service.delete_service('seeddataXDB');
PL/SQL procedure successfully completed.
SQL> alter database rename global_name to "cdbdemo";
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/cdbdemo/temp01.dbf' SIZE 61440K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
Tablespace altered.
SQL> select tablespace_name from dba_tablespaces where tablespace_name='USERS';
TABLESPACE_NAME
------------------------------
USERS
SQL> alter user sys account unlock identified by "&&sysPassword";
User altered.
SQL> alter user system account unlock identified by "&&systemPassword";
User altered.
SQL> @/u03/app/oracle/12.1.0/plug_PDBSeed.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u03/app/oracle/12.1.0/plugDatabase.log append
SQL> select 'database_running' from dual;
'DATABASE_RUNNIN
----------------
database_running
SQL> spool /u03/app/oracle/12.1.0/plugDatabase.log append
SQL> startup ;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u03/app/oracle/12.1.0/12cR1/dbs/initcdbdemo.ora'
SQL> host mkdir -p /u03/oradata/cdbdemo/pdbseed;
SQL> host /u03/app/oracle/12.1.0/12cR1/bin/rman @/u03/app/oracle/12.1.0/rmanPDBRestoreDatafiles.sql &&sysPassword;
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Apr 30 10:02:48 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target *
2>
3> run
4> {
5> set command id to 'PDB$SEED';
6> RESTORE FOREIGN DATAFILE 2 FORMAT '/u03/oradata/cdbdemo/pdbseed/system01.dbf',4 FORMAT '/u03/oradata/cdbdemo/pdbseed/sysaux01.dbf' FROM BACKUPSET '/u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates//pdbseed.dfb' ;
7> }
8> exit;
connected to target database: CDBDEMO (DBID=3503257845)
executing command: SET COMMAND ID
Starting restore at 30-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=244 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring foreign file 00002
channel ORA_DISK_1: restoring foreign file 00004
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates//pdbseed.dfb
channel ORA_DISK_1: restoring foreign file 2 to /u03/oradata/cdbdemo/pdbseed/system01.dbf
channel ORA_DISK_1: restoring foreign file 4 to /u03/oradata/cdbdemo/pdbseed/sysaux01.dbf
channel ORA_DISK_1: foreign piece handle=/u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates//pdbseed.dfb
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 30-APR-14
Recovery Manager complete.
SQL> column pdbfile0 NEW_VALUE pdbfile0;
SQL> select dc.name pdbfile0 from v$datafile_copy dc, v$rman_status rs where dc.name is not null and dc.status = 'A' and file# = 2 and rs.recid = dc.rman_status_recid and rs.stamp =dc.rman_status_stamp and rs.command_id = 'PDB$SEED';
PDBFILE0
--------------------------------------------------------------------------------
/u03/oradata/cdbdemo/pdbseed/system01.dbf
SQL> column pdbfile1 NEW_VALUE pdbfile1;
SQL> select dc.name pdbfile1 from v$datafile_copy dc, v$rman_status rs where dc.name is not null and dc.status = 'A' and file# = 4 and rs.recid = dc.rman_status_recid and rs.stamp =dc.rman_status_stamp and rs.command_id = 'PDB$SEED';
PDBFILE1
--------------------------------------------------------------------------------
/u03/oradata/cdbdemo/pdbseed/sysaux01.dbf
SQL> host /u03/app/oracle/12.1.0/12cR1/bin/rman @/u03/app/oracle/12.1.0/rmanPDBCleanUpDatafiles.sql &&sysPassword;
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Apr 30 10:03:38 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target *
2>
3> change datafilecopy '&&pdbfile0','&&pdbfile1' uncatalog
4> exit;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "exit": expecting one of: "device, ;"
RMAN-01007: at line 4 column 1 file: /u03/app/oracle/12.1.0/rmanPDBCleanUpDatafiles.sql
SQL> alter session set "_oracle_script"=TRUE;
Session altered.
SQL> create pluggable database PDB$SEED as clone using '/u03/app/oracle/12.1.0/12cR1/assistants/dbca/templates//pdbseed.xml' source_file_name_convert = ('/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/temp01.dbf','/u03/oradata/cdbdemo/pdbseed/pdbseed_temp01.dbf',
2 '/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/system01.dbf','&&pdbfile0',
3 '/ade/b/3593327372/oracle/oradata/seeddata/pdbseed/sysaux01.dbf','&&pdbfile1') NOCOPY;
Pluggable database created.
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> alter pluggable database PDB$SEED open restricted;
Pluggable database altered.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected to an idle instance.
SQL> startup restrict pfile="/u03/app/oracle/12.1.0/initcdbdemoTemp.ora";
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 855639816 bytes
Database Buffers 385875968 bytes
Redo Buffers 8859648 bytes
Database mounted.
Database opened.
SQL> select sid, program, serial#, username from v$session;
SID PROGRAM SERIAL#
---------- ------------------------------------------------ ----------
USERNAME
------------------------------
1 oracle@dbtrmrk.localdomain (VKTM) 1
2 oracle@dbtrmrk.localdomain (DIAG) 1
3 oracle@dbtrmrk.localdomain (LGWR) 1
SID PROGRAM SERIAL#
---------- ------------------------------------------------ ----------
USERNAME
------------------------------
4 oracle@dbtrmrk.localdomain (SMON) 1
5 oracle@dbtrmrk.localdomain (MMNL) 1
6 oracle@dbtrmrk.localdomain (TT00) 5
SID PROGRAM SERIAL#
---------- ------------------------------------------------ ----------
USERNAME
------------------------------
119 oracle@dbtrmrk.localdomain (GEN0) 1
120 oracle@dbtrmrk.localdomain (DBRM) 1
121 oracle@dbtrmrk.localdomain (CKPT) 1
SID PROGRAM SERIAL#
---------- ------------------------------------------------ ----------
USERNAME
------------------------------
122 oracle@dbtrmrk.localdomain (RECO) 1
123 oracle@dbtrmrk.localdomain (SMCO) 3
237 oracle@dbtrmrk.localdomain (PMON) 1
SID PROGRAM SERIAL#
---------- ------------------------------------------------ ----------
USERNAME
------------------------------
238 oracle@dbtrmrk.localdomain (MMAN) 1
239 oracle@dbtrmrk.localdomain (DIA0) 1
240 oracle@dbtrmrk.localdomain (LG00) 1
SID PROGRAM SERIAL#
---------- ------------------------------------------------ ----------
USERNAME
------------------------------
241 oracle@dbtrmrk.localdomain (LREG) 1
242 oracle@dbtrmrk.localdomain (W000) 1
355 sqlplus@dbtrmrk.localdomain (TNS V1-V3) 5
SYS
SID PROGRAM SERIAL#
---------- ------------------------------------------------ ----------
USERNAME
------------------------------
356 oracle@dbtrmrk.localdomain (PSP0) 1
357 oracle@dbtrmrk.localdomain (TMON) 5
358 oracle@dbtrmrk.localdomain (DBW0) 1
SID PROGRAM SERIAL#
---------- ------------------------------------------------ ----------
USERNAME
------------------------------
359 oracle@dbtrmrk.localdomain (LG01) 1
360 oracle@dbtrmrk.localdomain (MMON) 1
363 oracle@dbtrmrk.localdomain (CJQ0) 3
24 rows selected.
SQL> alter database character set INTERNAL_CONVERT WE8MSWIN1252;
Database altered.
SQL> alter database national character set INTERNAL_CONVERT AL16UTF16;
Database altered.
SQL> alter system disable restricted session;
System altered.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter session set "_oracle_script"=TRUE;
Session altered.
SQL> alter pluggable database PDB$SEED CLOSE IMMEDIATE;
Pluggable database altered.
SQL> alter pluggable database PDB$SEED OPEN;
Pluggable database altered.
SQL> @/u03/app/oracle/12.1.0/postScripts.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u03/app/oracle/12.1.0/postScripts.log append
SQL> UPDATE sys.USER$ set SPARE6=NULL;
121 rows updated.
SQL> host perl /u03/app/oracle/12.1.0/12cR1/rdbms/admin/catcon.pl -n 1 -l /u03/app/oracle/12.1.0 -b dbmssml /u03/app/oracle/12.1.0/12cR1/rdbms/admin/dbmssml.sql;
SQL> execute dbms_datapump_utl.replace_default_dir;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> host perl /u03/app/oracle/12.1.0/12cR1/rdbms/admin/catcon.pl -n 1 -l /u03/app/oracle/12.1.0 -b ordlib /u03/app/oracle/12.1.0/12cR1/ord/im/admin/ordlib.sql;
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> create or replace directory XMLDIR as '/u03/app/oracle/12.1.0/12cR1/rdbms/xml';
Directory created.
SQL> create or replace directory XSDDIR as '/u03/app/oracle/12.1.0/12cR1/rdbms/xml/schema';
Directory created.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> execute ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
PL/SQL procedure successfully completed.
SQL> execute dbms_qopatch.replace_logscrpt_dirs;
PL/SQL procedure successfully completed.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u03/app/oracle/12.1.0/postDBCreation.log append
SQL> grant sysdg to sysdg;
Grant succeeded.
SQL> grant sysbackup to sysbackup;
Grant succeeded.
SQL> grant syskm to syskm;
Grant succeeded.
SQL> @/u03/app/oracle/12.1.0/lockAccount.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u03/app/oracle/12.1.0/lockAccount.log append
SQL> BEGIN
2 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
3 'SYS','SYSTEM') )
4 LOOP
5 dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
6 execute immediate 'alter user ' ||
7 sys.dbms_assert.enquote_name(
8 sys.dbms_assert.schema_name(
9 item.USERNAME),false) || ' password expire account lock' ;
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> spool off
SQL> @/u03/app/oracle/12.1.0/postDBCreation.sql
SQL> SET VERIFY OFF
SQL> spool /u03/app/oracle/12.1.0/postDBCreation.log append
SQL> host perl /u03/app/oracle/12.1.0/12cR1/rdbms/admin/catcon.pl -n 1 -l /u03/app/oracle/12.1.0 -b catbundle -a 1 /u03/app/oracle/12.1.0/12cR1/rdbms/admin/catbundle.sql 1psu 1apply;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected to an idle instance.
SQL> startup mount pfile="/u03/app/oracle/12.1.0/init.ora";
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 855639816 bytes
Database Buffers 385875968 bytes
Redo Buffers 8859648 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> create spfile='/u03/app/oracle/12.1.0/12cR1/dbs/spfilecdbdemo.ora' FROM pfile='/u03/app/oracle/12.1.0/init.ora';
File created.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> host perl /u03/app/oracle/12.1.0/12cR1/rdbms/admin/catcon.pl -n 1 -l /u03/app/oracle/12.1.0 -b utlrp /u03/app/oracle/12.1.0/12cR1/rdbms/admin/utlrp.sql;
SQL> select comp_id, status from dba_registry;
COMP_ID STATUS
------------------------------ -----------
DV VALID
APEX VALID
OLS VALID
SDO VALID
ORDIM VALID
CONTEXT VALID
OWM VALID
XDB VALID
CATALOG VALID
CATPROC VALID
JAVAVM VALID
COMP_ID STATUS
------------------------------ -----------
XML VALID
CATJAVA VALID
APS VALID
XOQ VALID
RAC OPTION OFF
16 rows selected.
SQL> execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected to an idle instance.
SQL> startup ;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 855639816 bytes
Database Buffers 385875968 bytes
Redo Buffers 8859648 bytes
Database mounted.
Database opened.
SQL> spool off
SQL> @/u03/app/oracle/12.1.0/PDBCreation.sql
SQL> SET VERIFY OFF
SQL> set echo on
SQL> spool /u03/app/oracle/12.1.0/PDBCreation.log append
SQL> @/u03/app/oracle/12.1.0/plug_pdb1.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u03/app/oracle/12.1.0/plugDatabase.log append
SQL> select 'database_running' from dual;
'DATABASE_RUNNIN
----------------
database_running
SQL> spool /u03/app/oracle/12.1.0/plugDatabase.log append
SQL> startup ;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> host mkdir -p /u03/oradata/cdbdemo/pdb1;
SQL> CREATE PLUGGABLE DATABASE pdb1 ADMIN USER PDBADMIN IDENTIFIED BY "redhat" ROLES=(CONNECT) file_name_convert=('/u03/oradata/cdbdemo/pdbseed',
2 '/u03/oradata/cdbdemo/pdb1');
Pluggable database created.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> alter system register;
System altered.
SQL> alter session set container =pdb1;
Session altered.
SQL> CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/u03/oradata/cdbdemo/pdb1/pdb1_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TABLESPACE "USERS";
Database altered.
SQL> @/u03/app/oracle/12.1.0/postPDBCreation_pdb1.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter session set container=pdb1;
Session altered.
SQL> set echo on
SQL> spool /u03/app/oracle/12.1.0/postPDBCreation.log append
SQL> exit;
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
==========================================
Verification of Database Creation
==========================================
Note: We have created this Multitenant database with one pluggable database.
Every .sql script generated a .log file for us to view the operation.
[oracle@dbtrmrk 12.1.0]$ ps -ef | grep pmon
oracle 23063 1 0 10:08 ? 00:00:00 ora_pmon_cdbdemo
[oracle@dbtrmrk 12.1.0]$ ps -ef | grep tns
oracle 21937 1 0 08:42 ? 00:00:04 /u03/app/oracle/12.1.0/12cR1/bin/tnslsnr CDB_LISTENER -inherit
Note: As you can see we created cdbdemo database successfully, listener CDB_LISTENER is running from ORACLE_HOME.
Enjoy.......Abid Malik
