Do you ever wonder how to get all parameters for CREATE DATABASE Statement ?
I will be sharing some of the reverse engineering done to create a duplicate copy of the database.
Some of you may be thinking, “Why not just duplicate database or backup and restore?”
For the project I was working on, this was not feasible since Extended Data Types (12c NF) was enabled and there is no going back.
Restoring database from backup would result in too much data loss.
This leaves the only option is to create new database with max_string_size=standard, and perform full export/import.
From backup controlfile to trace:
SYS@DB1> alter database backup controlfile to trace as '/tmp/cf_@.sql' reuse resetlogs;
Database altered.
SYS@DB1>
From /tmp/cf_DB1.sql:
$ ll /tmp/cf_DB1.sql
-rw-r--r--. 1 oracle oinstall 2955 Oct 11 04:45 /tmp/cf_DB1.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DB1" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXDATAFILES 400
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata/DB1A/onlinelog/o1_mf_1_czl4h9sg_.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/oradata/DB1A/onlinelog/o1_mf_2_czl4h9yr_.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/oradata/DB1A/onlinelog/o1_mf_3_czl4hbdb_.log' SIZE 200M BLOCKSIZE 512
From DBA_REGISTRY:
DB1:(SYS@DB1):PRIMARY> select name,comp_id,comp_name,version,schema,status 2 from v$database,dba_registry 3 order by 2 4 ; NAME COMP_ID COMP_NAME VERSION SCHEMA STATUS ---------- ------------ ---------------------------------------- ---------- ---------- -------- DB1 CATALOG Oracle Database Catalog Views 12.1.0.2.0 SYS VALID DB1 CATJAVA Oracle Database Java Packages 12.1.0.2.0 SYS VALID DB1 CATPROC Oracle Database Packages and Types 12.1.0.2.0 SYS VALID DB1 JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 SYS VALID DB1 XDB Oracle XML Database 12.1.0.2.0 XDB VALID DB1 XML Oracle XDK 12.1.0.2.0 SYS VALID 6 rows selected. DB1:(SYS@DB1):PRIMARY>
From DATABASE_PROPERTIES:
DB1:(SYS@DB1):PRIMARY> select property_name,property_value from DATABASE_PROPERTIES; PROPERTY_NAME PROPERTY_VALUE ---------------------------------------- ---------------------------------------- DICT.BASE 2 DEFAULT_TEMP_TABLESPACE TEMP DEFAULT_PERMANENT_TABLESPACE USERS DEFAULT_EDITION ORA$BASE Flashback Timestamp TimeZone GMT TDE_MASTER_KEY_ID EXPORT_VIEWS_VERSION 8 DEFAULT_TBS_TYPE SMALLFILE GLOBAL_DB_NAME DB1 NLS_RDBMS_VERSION 12.1.0.2.0 NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_NCHAR_CONV_EXCP FALSE NLS_LENGTH_SEMANTICS BYTE NLS_COMP BINARY NLS_DUAL_CURRENCY $ NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_SORT BINARY NLS_DATE_LANGUAGE AMERICAN NLS_DATE_FORMAT DD-MON-RR NLS_CALENDAR GREGORIAN NLS_CHARACTERSET AL32UTF8 NLS_NUMERIC_CHARACTERS ., NLS_ISO_CURRENCY AMERICA NLS_CURRENCY $ NLS_TERRITORY AMERICA NLS_LANGUAGE AMERICAN DST_SECONDARY_TT_VERSION 0 DST_PRIMARY_TT_VERSION 18 DST_UPGRADE_STATE NONE MAX_STRING_SIZE STANDARD DBTIMEZONE US/Mountain WORKLOAD_CAPTURE_MODE WORKLOAD_REPLAY_MODE NO_USERID_VERIFIER_SALT 88C7FDB8D44CA60E05624A08A177722C 37 rows selected. DB1:(SYS@DB1):PRIMARY>
From V$DATABASE:
DB1:(SYS@DB1):PRIMARY> r
1 select log_mode,flashback_on,force_logging,created
2 from V$DATABASE
3*
LOG_MODE FLASHBACK_ON FORCE_LOGGING CREATED
------------ ------------------ --------------------------------------- -------------------
ARCHIVELOG NO NO 2016-10-08 08:34:02
DB1:(SYS@DB1):PRIMARY>
From: V$BLOCK_CHANGE_TRACKING:
DB1:(SYS@DB1):PRIMARY> r 1 select status, filename 2 from V$BLOCK_CHANGE_TRACKING 3* STATUS FILENAME ---------- -------------------- DISABLED DB1:(SYS@DB1):PRIMARY>
From DBA_SCHEDULER_GLOBAL_ATTRIBUTE:
DB1:(SYS@DB1):PRIMARY> r
1 select *
2 from DBA_SCHEDULER_GLOBAL_ATTRIBUTE
3 order by 1
4*
ATTRIBUTE_NAME VALUE
---------------------------------------- ----------------------------------------
CURRENT_OPEN_WINDOW
DEFAULT_TIMEZONE US/Mountain
EMAIL_SENDER
EMAIL_SERVER
EMAIL_SERVER_CREDENTIAL
EMAIL_SERVER_ENCRYPTION NONE
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT 0
LAST_OBSERVED_EVENT
LOG_HISTORY 30
MAX_JOB_SLAVE_PROCESSES
11 rows selected.
DB1:(SYS@DB1):PRIMARY>
The finished SQL: crdb.sql
spool crdbp.log set echo on timing on time on host echo $ORACLE_SID host sysresv create spfile from pfile; startup force nomount; CREATE DATABASE MAXINSTANCES 1 MAXLOGFILES 32 MAXLOGMEMBERS 4 MAXLOGHISTORY 292 MAXDATAFILES 400 ARCHIVELOG CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 SET TIME_ZONE='US/Mountain' USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle EXTENT MANAGEMENT LOCAL DATAFILE SIZE 513M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M SYSAUX DATAFILE SIZE 257M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M LOGFILE GROUP 1 SIZE 200M,GROUP 2 SIZE 200M,GROUP 3 SIZE 200M DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 8192M DEFAULT TABLESPACE users DATAFILE SIZE 129M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M UNDO TABLESPACE undotbs1 DATAFILE SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 8192M; spool off @?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql @?/javavm/install/initjvm.sql @?/xdk/admin/initxml.sql @?/xdk/admin/xmlja.sql @?/rdbms/admin/catjava.sql @?/rdbms/admin/catxdbj.sql @?/rdbms/admin/utlrp.sql exec dbms_scheduler.set_scheduler_attribute(attribute=>'default_timezone',value=>'US/Mountain'); -- alter system set nls_length_semantics=CHAR scope=both sid='*'; -- alter database flashback on; -- alter database FORCE LOGGING; -- alter database enable block change tracking; connect system/oracle @?/sqlplus/admin/pupbld.sql exit
Thinking Out Loud 
Leave a comment