Thinking Out Loud

October 11, 2016

OTN Appreciation Day : Create Database Using SQL | Thinking Out Loud Blog

Filed under: 11g,12c,oracle — mdinh @ 12:30 pm

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
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: