We finally started creating some Databases once we made sure Marvin stopped whining about why the automatic doors of the spaceship had to be so cheerful.
You can read more about creating DBs on DBCA: Software + DB Creation, about Marvin and the automatic doors.. lets just leave there for now..
Sometimes however, what you want is to personalize a new DB you need to create or even clone a DB you already have in your cluster.
That is what we are going to explore today, some personalization and cloning using DBCA templates.
Create a template from a running Database
Sometimes you want to create a new Database keeping the same configuration you have in a DB you have already in the system, but that doesn't mean you want to close its content.
For that, we can use the DBCA argument "createTemplateFromDB"
To make sure we are not using a "default" Database, lets modify the Database and add some data (this will helpful later for another part of the guide) into our already existing database "db112"
## Parameters changed ## NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ processes integer 150 db_recovery_file_dest_size big integer 4407M SQL> alter system set processes = 200 scope=spfile; System altered. SQL> alter system set db_recovery_file_dest_size=10G scope=BOTH SID='*'; System altered. (After DB Restarted): NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ processes integer 200 db_recovery_file_dest_size big integer 10G ## Create Schema and Table TEST## sys@db1121>SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES; TABLESPACE_NAME STATUS CONTENTS ----------------- -------- ------------- SYSTEM ONLINE PERMANENT SYSAUX ONLINE PERMANENT UNDOTBS1 ONLINE UNDO TEMP ONLINE TEMPORARY USERS ONLINE PERMANENT UNDOTBS2 ONLINE UNDO TEST ONLINE PERMANENT 7 rows selected. sys@db1121> sys@db1121> CREATE USER TEST IDENTIFIED BY Welcome1 DEFAULT TABLESPACE TEST TEMPORARY TABLESPACE TEMP QUOTA 200M on TEST; 2 3 4 5 User created. sys@db1121> GRANT CONNECT TO TEST; Grant succeeded. sys@db1121> grant create session, create procedure,create table to TEST; Grant succeeded. sys@db1121> connect TEST Enter password: Connected. sys@db1121> create table TEST ( ID VARCHAR2(4 BYTE) NOT NULL primary key, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary Number(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE) ) / 2 3 4 5 6 7 8 9 10 11 12 Table created. sys@db1121> BEGIN FOR v_LoopCounter IN 1..50 LOOP INSERT INTO TEST (id) VALUES (v_LoopCounter); END LOOP; END; / 2 3 4 5 6 7 PL/SQL procedure successfully completed. sys@db1121> commit; Commit complete. sys@db1121> select count(*) from test; COUNT(*) ---------- 50 sys@db1121> Lets create the Template from the Database "db112":
## Command Example ## dbca -silent \ -createTemplateFromDB \ -sourceSID db112 \ -templateName db112_template \ -sysDBAUserName SYS \ -sysDBAPassword Welcome1 ## Output ## [oracle@rac1-node1 templates]$ time dbca -silent -createTemplateFromDB \ > -sourceSID db112 \ > -templateName db112_template \ > -sysDBAUserName SYS \ > -sysDBAPassword Welcome1 Creating a template from the database 10% complete 20% complete 30% complete 40% complete 50% complete 60% complete 70% complete 80% complete 90% complete 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/silent.log_2019-01-06_07-14-58-PM" for further details. real 0m34.074s user 0m9.702s sys 0m0.589s [oracle@rac1-node1 templates]$ You can find our new Template below:
[oracle@rac1-node1 dbhome_1]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1 [root@rac1-node1 ~]# locate db112_template /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/db112_template.dbt [oracle@rac1-node1 admin]$ ls -lrth /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/ total 610M -rw-r--r-- 1 oracle oinstall 12K May 1 2013 New_Database.dbt -rw-r--r-- 1 oracle oinstall 5.0K Aug 24 2013 Data_Warehouse.dbc -rw-r--r-- 1 oracle oinstall 4.9K Aug 24 2013 General_Purpose.dbc -rwxr-xr-x 1 oracle oinstall 1.5M Aug 27 2013 example.dmp -rwxr-xr-x 1 oracle oinstall 21M Aug 27 2013 example01.dfb -rwxr-xr-x 1 oracle oinstall 9.3M Aug 27 2013 Seed_Database.ctl -rwxr-xr-x 1 oracle oinstall 263M Aug 27 2013 Seed_Database.dfb -rw-r----- 1 oracle oinstall 17K Jan 6 19:15 db112_template.dbt -rw-r----- 1 oracle oinstall 466 Jan 6 20:11 LOG_cat_owb.TXT -rw-r----- 1 oracle oinstall 297M Jan 6 20:20 db112_clone.dfb -rw-r----- 1 oracle oinstall 18M Jan 6 20:20 db112_clone.ctl -rw-r----- 1 oracle oinstall 6.0K Jan 6 20:21 db112_clone.dbc [oracle@rac1-node1 admin]$ Lets now create a new Database using that new template and confirm is created with "db112" configuration. The new Database will be called "test112" (I know.. the names are super originals, right?)
## Command Example ## dbca \ -silent \ -createDatabase \ -templateName db112_template.dbt \ -sid test112 \ -gdbName test112 \ -SysPassword Welcome1 \ -SystemPassword Welcome1 \ -emConfiguration NONE \ -storageType ASM \ -diskGroupName DATA1 \ -recoveryGroupName RECO \ -nodelist rac1-node1,rac1-node2 \ -initParams db_unique_name=test112 ## Output ## [oracle@rac1-node1 templates]$ time dbca -silent \ > -createDatabase \ > -templateName db112_template.dbt \ > -sid test112 \ > -gdbName test112 \ > -SysPassword Welcome1 \ > -SystemPassword Welcome1 \ > -emConfiguration NONE \ > -storageType ASM \ > -diskGroupName DATA1 \ > -recoveryGroupName RECO \ > -nodelist rac1-node1,rac1-node2 \ > -initParams db_unique_name=test112 Creating and starting Oracle instance 1% complete 3% complete Creating database files 4% complete 6% complete Creating data dictionary views 7% complete 8% complete 9% complete 10% complete 11% complete 12% complete 13% complete 15% complete 16% complete 17% complete 18% complete Adding Oracle JVM 23% complete 28% complete 33% complete 35% complete Adding Oracle Text 36% complete 38% complete Adding Oracle XML DB 40% complete 41% complete 42% complete 45% complete Adding Oracle Multimedia 46% complete 47% complete 56% complete Adding Oracle OLAP 57% complete 58% complete 59% complete Adding Oracle Spatial 60% complete 61% complete 62% complete 66% complete Adding Enterprise Manager Repository 68% complete 70% complete Adding Oracle Application Express 73% complete 76% complete Adding Oracle Warehouse Builder 80% complete 83% complete Creating cluster database views 84% complete 90% complete Completing Database Creation 91% complete 92% complete 95% complete 98% complete 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/test112/test1120.log" for further details. real 55m50.522s user 1m37.506s sys 0m8.181s [oracle@rac1-node1 templates]$ Lets check the Database created and how "processes" / "db_recovery_file_dest_size" parameters are the same we did set in "db112"
[oracle@rac1-node1 ~]$ srvctl status database -d test112 -v Instance test1121 is running on node rac1-node1. Instance status: Open,HOME=/u01/app/oracle/product/11.2.0/dbhome_1. Instance test1122 is running on node rac1-node2. Instance status: Open,HOME=/u01/app/oracle/product/11.2.0/dbhome_1. [oracle@rac1-node1 ~]$ [oracle@rac1-node1 dbs]$ srvctl config database -d test112 Database unique name: test112 Database name: test112 Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1 Oracle user: oracle Spfile: +DATA1/test112/spfiletest112.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: test112 Database instances: test1121,test1122 Disk Groups: DATA1,RECO Mount point paths: Services: Type: RAC Database is administrator managed [oracle@rac1-node1 dbs]$ DB_NAME INSTANCE_NAME HOST_NAME STARTUP DATABASE_ROLE OPEN_MODE STATUS --------- ---------------- ----------------------------------- ----------------------------- ---------------- -------------------- ------------ TEST112 test1121 rac1-node1.raclab.local 06-JAN-2019 16:31:45 PRIMARY READ WRITE OPEN TEST112 test1122 rac1-node2.raclab.local 06-JAN-2019 16:31:45 PRIMARY READ WRITE OPEN NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ processes integer 200 db_recovery_file_dest_size big integer 10G Ok, lets be more accurate and find the real differences here..
Lets export the spfile and compare them side by side:
SQL> create pfile='/tmp/test112.ora' from spfile; [[.....]] SQL> create pfile='/tmp/db112.ora' from spfile; The Below table is the result of comparing both files, so what is not there, is already the same on both systems:
[oracle@rac1-node1 ~]$ diff /tmp/db112.ora /tmp/test112.ora *.control_files='+DATA1/db112/controlfile/current.261.994939157','+DATA1/db112/controlfile/current.260.994939157' *.db_name='db112' *.db_recovery_file_dest='+DATA1' =============================================== *.control_files='+DATA1/test112/controlfile/current.292.996853187','+RECO/test112/controlfile/current.321.996853187' *.db_name='test112' *.db_recovery_file_dest='+RECO' *.db_unique_name='test112' Notice how the only real difference are the Diskgroups for the Controlfiles and Recovery Files Destination and db_unique_name, which were all specified during the creation of the Database test112.
So, even if we use a template, we can still personalize parameters using the different DBCA attributes.
Let's talk a bit more about personalization in the following example.
Create your Personal Template
When we create a DB using DBCA, we use template with the most general configurations items setup in order for the Database to start.
In case we want to create a Database and the default configuration is not enough for us, we can edit or create one of these templates (they are just xml files) and execute DBCA with it.
See below the Default templates for 11.2:
[oracle@rac1-node1 dbhome_1]$ cd $ORACLE_HOME/assistants/dbca/templates [oracle@rac1-node1 templates]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates [oracle@rac1-node1 templates]$ ls -lrth total 295M -rw-r--r-- 1 oracle oinstall 12K May 1 2013 New_Database.dbt -rw-r--r-- 1 oracle oinstall 5.0K Aug 24 2013 Data_Warehouse.dbc -rw-r--r-- 1 oracle oinstall 4.9K Aug 24 2013 General_Purpose.dbc -rwxr-xr-x 1 oracle oinstall 1.5M Aug 27 2013 example.dmp -rwxr-xr-x 1 oracle oinstall 21M Aug 27 2013 example01.dfb -rwxr-xr-x 1 oracle oinstall 9.3M Aug 27 2013 Seed_Database.ctl -rwxr-xr-x 1 oracle oinstall 263M Aug 27 2013 Seed_Database.dfb [oracle@rac1-node1 templates]$ Lets create a new version of the default Template "New_Database.dbt" called "P42_Database.dbt" and modify (on this case we will set them to false) some of the components DBCA install when you create a DB like Spatial, OMS, JServer...:
P42_Database.dbt
<DatabaseTemplate name="P42 Database" description="" version="11.2.0.0.0"> <CommonAttributes> <option name="OMS" value="false"/> <option name="JSERVER" value="false"/> <option name="SPATIAL" value="false"/> <option name="IMEDIA" value="false"/> <option name="ORACLE_TEXT" value="false"> <tablespace id="SYSAUX"/> </option> <option name="XDB_PROTOCOLS" value="true"> <tablespace id="SYSAUX"/> </option> <option name="CWMLITE" value="true"> <tablespace id="SYSAUX"/> </option> <option name="EM_REPOSITORY" value="false"> <tablespace id="SYSAUX"/> </option> <option name="SAMPLE_SCHEMA" value="false"/> <option name="APEX" value="false"/> <option name="OWB" value="false"/> <option name="DV" value="false"/> </CommonAttributes> <Variables/> <CustomScripts Execute="false"/> <InitParamAttributes> <InitParams> <initParam name="db_block_size" value="8" unit="KB"/> <initParam name="memory_target" value="250" unit="MB"/> New_Database.dbt
<DatabaseTemplate name="New Database" description="" version="11.2.0.0.0"> <CommonAttributes> <option name="OMS" value="true"/> <option name="JSERVER" value="true"/> <option name="SPATIAL" value="true"/> <option name="IMEDIA" value="true"/> <option name="ORACLE_TEXT" value="true"> <tablespace id="SYSAUX"/> </option> <option name="XDB_PROTOCOLS" value="true"> <tablespace id="SYSAUX"/> </option> <option name="CWMLITE" value="true"> <tablespace id="SYSAUX"/> </option> <option name="EM_REPOSITORY" value="true"> <tablespace id="SYSAUX"/> </option> <option name="SAMPLE_SCHEMA" value="false"/> <option name="APEX" value="true"/> <option name="OWB" value="true"/> <option name="DV" value="false"/> </CommonAttributes> <Variables/> <CustomScripts Execute="false"/> <InitParamAttributes> <InitParams> <initParam name="db_block_size" value="8" unit="KB"/> <initParam name="memory_target" value="250" unit="MB"/> Now that we have our new Template without those components we can take it for a spin and we will see how none of those components are installed in the system:
[oracle@rac1-node1 templates]$ time dbca -silent \ > -createDatabase \ > -templateName P42_Database.dbt \ > -sid test \ > -gdbName test \ > -SysPassword Welcome1 \ > -SystemPassword Welcome1 \ > -emConfiguration NONE \ > -storageType ASM \ > -diskGroupName DATA1 \ > -recoveryGroupName RECO \ > -nodelist rac1-node1,rac1-node2 \ > -initParams db_unique_name=test Creating and starting Oracle instance 1% complete 2% complete 3% complete 7% complete Creating database files 8% complete 9% complete 15% complete Creating data dictionary views 17% complete 20% complete 22% complete 23% complete 24% complete 25% complete 26% complete 27% complete 28% complete 29% complete 34% complete 36% complete 38% complete 40% complete Adding Oracle XML DB 43% complete 44% complete 45% complete 46% complete 47% complete 48% complete 56% complete Adding Oracle OLAP 57% complete 58% complete 59% complete 60% complete 61% complete 63% complete Creating cluster database views 65% complete 79% complete Completing Database Creation 81% complete 83% complete 90% complete 97% complete 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/test/test2.log" for further details. real 19m7.625s user 0m32.105s sys 0m3.921s [oracle@rac1-node1 templates]$ Just in case you are not sure or you weren't paying any attention, lets check DB components:
sys@test1>select comp_id, version, status from dba_registry; COMP_ID VERSION STATUS ------------------------------ ------------------------------ ----------- AMD 11.2.0.4.0 VALID XDB 11.2.0.4.0 VALID OWM 11.2.0.4.0 VALID CATALOG 11.2.0.4.0 VALID CATPROC 11.2.0.4.0 VALID APS 11.2.0.4.0 VALID XOQ 11.2.0.4.0 VALID RAC 11.2.0.4.0 VALID 8 rows selected. I chose to modify some components to make the DB "lighter".. but of course you can just change the template and create the DB with all your specific requirements, like a different db_block_size, RedoLogSize.. etc...
If you don't see a parameter in your template and still want to add it, we can just add them to the section called "MiscParams" as we have below and make sure the new DB is created with those parameters set correctly.
On this case "characterSet" "nationalCharacterSet" were added to that section to make sure the DB was created with that parameter set
## DBCA Template ## [.....] <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/> <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/> <initParam name="db_recovery_file_dest" value="{ORACLE_BASE}/fast_recovery_area"/> <initParam name="db_recovery_file_dest_size" value="" unit="MB"/> </InitParams> <MiscParams> <databaseType>MULTIPURPOSE</databaseType> <maxUserConn>20</maxUserConn> <percentageMemTOSGA>40</percentageMemTOSGA> <customSGA>false</customSGA> <archiveLogMode>false</archiveLogMode> <initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName> <characterSet>AL32UTF8</characterSet> <nationalCharacterSet>UTF8</nationalCharacterSet> </MiscParams> <SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile> </InitParamAttributes> ## DB created ## PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------------------------ ---------------------------------------- ---------------------------------------- NLS_CHARACTERSET AL32UTF8 Character set NLS_NCHAR_CHARACTERSET UTF8 NCHAR Character set Clone a Database using DBCA
Sometimes you have that small DB and you want to actually clone it.. not only to create a similar DB as we did in the other examples, but you want to clone everything including the data on it
For this process, and if you have enough space in the system.. you can use "createCloneTemplate" option
DB restart
Be careful when you do this process. The source DB will be restarted
as mounted in order to create the Datafiles backup:
## DB Alert ## Sun Jan 06 13:55:38 2019 Shutting down instance (immediate) Shutting down instance: further logons disabled Sun Jan 06 13:55:38 2019 Stopping background process CJQ0 [.......] ALTER DATABASE MOUNT This instance was first to mount Sun Jan 06 13:56:05 2019 ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.21)(PORT=1521))' SCOPE=MEMORY SID='db1121'; ALTER SYSTEM SET remote_listener='rac1-scan:1521' SCOPE=MEMORY SID='db1121'; ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='db1121'; NOTE: Loaded library: System SUCCESS: diskgroup DATA1 was mounted NOTE: dependency between database db112 and diskgroup resource ora.DATA1.dg is established Successful mount of redo thread 1, with mount id 985562741 Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE) Lost write protection disabled Completed: ALTER DATABASE MOUNT Ok, lets create a clone template of the Database "db112" which has a Tablespace called TEST as additional to the "default" ones:
Create Clone Template ## Command ## dbca \ -silent \ -createCloneTemplate \ -sourceSID db112 \ -templateName db112_clone.dbc \ -sysDBAUserName SYS \ -sysDBAPassword Welcome1 ## Output ## [oracle@rac1-node1 dbs]$ dbca -silent -createCloneTemplate \ > -sourceSID db112 \ > -templateName db112_clone.dbc \ > -sysDBAUserName SYS \ > -sysDBAPassword Welcome1 Gathering information from the source database 4% complete 8% complete 13% complete 17% complete 22% complete Backup datafiles 28% complete 88% complete Creating template file 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/silent.log_2019-01-06_01-55-25-PM" for further details. [oracle@rac1-node1 dbs]$ Lets see the content of the new Template (db112_clone.dbc) and the Backup (db112_clone.dfb):
## Files Created ## [oracle@rac1-node1 templates]$ ls -lrth total 1.2G [...] -rw-r----- 1 oracle oinstall 299M Jan 6 13:58 db112_clone.dfb -rw-r----- 1 oracle oinstall 18M Jan 6 13:58 db112_clone.ctl -rw-r----- 1 oracle oinstall 6.0K Jan 6 13:58 db112_clone.dbc [oracle@rac1-node1 templates]$ ## Tablespace information from the Template ## [oracle@rac1-node1 templates]$ grep -i Tablespace db112_clone.dbc <Name id="1" Tablespace="SYSTEM" Contents="PERMANENT" Size="750" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system.256.994939031</Name> <Name id="2" Tablespace="SYSAUX" Contents="PERMANENT" Size="720" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux.257.994939031</Name> <Name id="3" Tablespace="UNDOTBS1" Contents="UNDO" Size="80" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs1.258.994939031</Name> <Name id="4" Tablespace="USERS" Contents="PERMANENT" Size="5" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users.259.994939031</Name> <Name id="5" Tablespace="UNDOTBS2" Contents="UNDO" Size="175" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs2.267.994939287</Name> <Name id="6" Tablespace="TEST" Contents="PERMANENT" Size="100" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/test.282.996846429</Name> <Name id="7" Tablespace="TEST" Contents="PERMANENT" Size="1024" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/test.281.996846441</Name> <Name id="1" Tablespace="TEMP" Contents="TEMPORARY" Size="32">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp.266.994939173</Name> [oracle@rac1-node1 templates]$ To use this template, we just need to make sure we specified the correct name (db112_clone.dbc) and we will have our new Database as a full clone of the source Database
## Command ## dbca \ -silent \ -createDatabase \ -templateName db112_clone.dbc \ -gdbName clone112 \ -sid clone112 \ -SysPassword Welcome1 \ -SystemPassword Welcome1 \ -emConfiguration NONE \ -storageType ASM \ -nodelist rac1-node1,rac1-node2 \ -initParams db_unique_name=clone112 ## Output ## [oracle@rac1-node1 ~]$ dbca -silent \ > -createDatabase \ > -templateName db112_clone.dbc \ > -gdbName clone112 \ > -sid clone112 \ > -SysPassword Welcome1 \ > -SystemPassword Welcome1 \ > -emConfiguration NONE \ > -storageType ASM \ > -nodelist rac1-node1,rac1-node2 \ > -initParams db_unique_name=clone112 Copying database files 1% complete 3% complete 9% complete 15% complete 21% complete 27% complete 30% complete Creating and starting Oracle instance 32% complete 36% complete 40% complete 44% complete 45% complete 48% complete 50% complete Creating cluster database views 52% complete 70% complete Completing Database Creation 73% complete 76% complete 85% complete 94% complete 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/clone112/clone112.log" for further details. [oracle@rac1-node1 ~]$ 
Top comments (0)