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 