Creating a Database Template from an Existing Database for the DBCA Utility

A database template is valid for a specific Oracle version and Operating System (OS). When you install and configure a new database, it must be configured according to Primavera® Portfolio Management standards.

1. Create an init.ora using any of the following methods:
   • If the database uses pfile (init.ora), copy init.ora from %ORACLE_BASE%\admin\<SID>\pfile to %ORACLE_HOME%\database\init<SID>.ora.

     For example, copy C:\oracle\product\11.2.0\admin\ORCL\pfile\init.ora to C:\oracle\product\11.2.0\db_1\database\initORCL.ora.

   • If the database uses an spfile:
     1. Use the create pfile from spfile command to automatically create the pfile in the default directory, (ORACLE_HOME\database):

        sqlplus “sys/<password> as sysdba”

        create pfile from spfile;

        shutdown immediate

     2. Rename the spfile.

        For example, ren C:\oracle\product\110.2.0\db_1\database\SPFILEORCL.ORA C:\oracle\product\110.2.0\db_1\database\SPFILEORCL.ORA.BAK

2. Start the database.

   sqlplus "sys / <password> as sysdba"

   startup

3. Resize undo tablespace to 1024M. For example:

   sqlplus “sys/<password> as sysdba”

   -- Find UNDO tablespace name

   select tablespace_name from dba_tablespaces where tablespace_name like 'UNDO%';

   -- Find UNDO tablespace datafile

   select file_name, bytes from dba_data_files where tablespace_name = 'UNDOTBS1';

   -- Resize the UNDO Tablespace datafile to 1G

   alter database datafile 'C:\APP\QALAB\ORADATA\ORCL\UNDOTBS01.DBF' resize 1024M;

4. Run the dbca utility to create a template of the database.

   dbca -silent -createCloneTemplate -sourceDB <SID> -sysDBAUserName sys -sysDBAPassword <SYS_PASSWD> -templateName <VERSION> -datafileJarLocation <LOCATION>

   Where:

   <SID>: Oracle SID of existing database installed in the previous step. For example, ORCL

   <SYS_PASSWD>: A password of sys user. For example, oracle.

   <VERSION>: A template name. For example, 11.210.2.

   <LOCATION>: A location where the compressed datafiles will be transferred. For example, “C:\Program Files\Oracle\Primavera Portfolio Management\Portfolios\misc\ocu”

5. Check for the errors in the log file, <ORACLE_HOME>\cfgtoollogs\dbca\silentN.log.

   dbca -silent -createCloneTemplate -sourceDB ORCL -sysDBAUserName sys -sysDBAPassword oracle -templateName 11.2.0.4 -datafileJarLocation

   “C:\Program Files\Oracle\Primavera Portfolio Management\Portfolios\misc\ocu”

   The following files will be created:

   • A template file: <ORACLE_HOME>\assistants\dbca\templates\11.2.0.4.DBC
   • A data file: C:\Program Files\Oracle\Primavera Portfolio Management\Portfolios\misc\sql\PS11.2.DFB
   • A control file: C:\Program Files\Oracle\Primavera Portfolio Management\Portfolios\misc\sql\PS11.2.CTL
6. Test the created database templates.
7. Delete the existing instance as follows:

   oradim –delete –sid <SID>

   Where:

   <SID>: Oracle SID of the existing database. For example, ORCL.

8. Create a new database using the previously created template as follows:

   dbca -silent -createDatabase -templateName <VERSION> -gdbname <SID> -sid <SID> -sysPassword <SYS_PASSWD> -systemPassword <SYSTEM PASSWD> -continueOnNonFatalErrors true -datafileJarLocation <LOCATION>

   Where:

   <VERSION>: A template name. For example, 11.2.0.4.

   <SID>: Oracle SID of a new database. For example, ORCL.

   <LOCATION>: A location of the compressed datafiles. For example, “C:\Program Files\Oracle\Primavera Portfolio Management\Portfolios\misc\ocu”.

9. Check for the errors in the log file, <ORACLE_HOME>\cfgtoollogs\dbca\<ORACLE_SID>\<ORACLE_SID>.log

   dbca -silent -createDatabase -templateName 11.2.0.4-gdbname ORCL -sysPassword oracle -systemPassword manager -sid ORCL -continueOnNonFatalErrors true -datafileJarLocation

   “C:\Program Files\Oracle\Primavera Portfolio Management\Portfolios\misc\ocu”

Legal Notices | Your Privacy Rights
Copyright © 1998, 2020

Last Published Tuesday, December 8, 2020