I have been doing it wrong all these time by creating a database using SQL when I could be creating a database using RMAN active database duplication from a base install.
The process was incredibly FAST.
A simple database was created with the options indentified below with Archive Mode, Force Logging, and Minimal Supplemental Logging.
CREATE DATABASE
CHARACTER SET AL32UTF8
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 8388672K
sysaux DATAFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 8388672K
LOGFILE GROUP 1 SIZE 100M,GROUP 2 SIZE 100M,GROUP 3 SIZE 100M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 8388672K
DEFAULT TABLESPACE users DATAFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 8388672K
UNDO TABLESPACE undotbs DATAFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 8388672K;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/catio.sql
@?/rdbms/admin/catoctk.sql
@?/rdbms/admin/userlock.sql
@?/rdbms/admin/utldtree.sql
db1 and db3 are aliases used to source Oracle environment. Please see previous post for more information.
oracle@dc1:db1:/u01/app/oracle/product/11.2.0.3/db_1/dbs
> ls
c.sql hc_db1.dat initdb1.ora initdb3.ora lkSAN_DB1 orapwdb1 orapwdb3 spfiledb1.ora
oracle@dc1:db1:/u01/app/oracle/product/11.2.0.3/db_1/dbs
> db3
The Oracle base remains unchanged with value /u01/app/oracle
IPC Resources for ORACLE_SID “db3” :
Shared Memory
ID KEY
No shared memory segments used
Semaphores:
ID KEY
No semaphore resources used
Oracle Instance not alive for sid “db3”
oracle@dc1:db3:/u01/app/oracle/product/11.2.0.3/db_1/dbs
> sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 19 23:39:51 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size 2232640 bytes
Variable Size 222301888 bytes
Database Buffers 574619648 bytes
Redo Buffers 2547712 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@dc1:db3:/u01/app/oracle/product/11.2.0.3/db_1/dbs
> db1
The Oracle base remains unchanged with value /u01/app/oracle
IPC Resources for ORACLE_SID “db1” :
Shared Memory:
ID KEY
1048579 0x00000000
1081348 0x00000000
1114117 0xc936a9a8
Semaphores:
ID KEY
1441794 0x9aae592c
Oracle Instance alive for sid “db1”
oracle@dc1:db1:/u01/app/oracle/product/11.2.0.3/db_1/dbs
> rman target /
Recovery Manager: Release 11.2.0.3.0 – Production on Sat Jan 19 23:40:13 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB1 (DBID=1398625995)
RMAN> connect auxiliary sys@db3;
auxiliary database Password:
connected to auxiliary database: DB3 (not mounted)
RMAN> duplicate database to db3 from active database nofilenamecheck;
Starting Duplicate Db at 19-JAN-2013 23:41:27
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
sql clone “alter system set control_files =
”/oracle/oradata/SAN_DB3/controlfile/o1_mf_8hpxcqvw_.ctl”, ”/oracle/oradata/flashrecovery/SAN_DB3/controlfile/o1_mf_8hpxcqw2_.ctl” comment=
”Set by RMAN” scope=spfile”;
sql clone “alter system set db_name =
”DB1” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format ‘/oracle/oradata/SAN_DB3/controlfile/o1_mf_8hpxcqw7_.ctl’;
restore clone controlfile to ‘/oracle/oradata/flashrecovery/SAN_DB3/controlfile/o1_mf_8hpxcqwc_.ctl’ from
‘/oracle/oradata/SAN_DB3/controlfile/o1_mf_8hpxcqw7_.ctl’;
sql clone “alter system set control_files =
”/oracle/oradata/SAN_DB3/controlfile/o1_mf_8hpxcqw7_.ctl”, ”/oracle/oradata/flashrecovery/SAN_DB3/controlfile/o1_mf_8hpxcqwc_.ctl” comment=
”Set by RMAN” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ”/oracle/oradata/SAN_DB3/controlfile/o1_mf_8hpxcqvw_.ctl”, ”/oracle/oradata/flashrecovery/SAN_DB3/controlfile/o1_mf_8hpxcqw2_.ctl” comment= ”Set by RMAN” scope=spfile
sql statement: alter system set db_name = ”DB1” comment= ”Modified by RMAN duplicate” scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 801701888 bytes
Fixed Size 2232640 bytes
Variable Size 222301888 bytes
Database Buffers 574619648 bytes
Redo Buffers 2547712 bytes
Starting backup at 19-JAN-2013 23:41:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_db1.f tag=TAG20130119T234137 RECID=5 STAMP=805160498
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 19-JAN-2013 23:41:38
Starting restore at 19-JAN-2013 23:41:38
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 19-JAN-2013 23:41:40
sql statement: alter system set control_files = ”/oracle/oradata/SAN_DB3/controlfile/o1_mf_8hpxcqw7_.ctl”, ”/oracle/oradata/flashrecovery/SAN_DB3/controlfile/o1_mf_8hpxcqwc_.ctl” comment= ”Set by RMAN” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 801701888 bytes
Fixed Size 2232640 bytes
Variable Size 222301888 bytes
Database Buffers 574619648 bytes
Redo Buffers 2547712 bytes
database mounted
contents of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 19-JAN-2013 23:41:55
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/oradata/SAN_DB1/datafile/o1_mf_system_8hbrm3bz_.dbf
output file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_system_0lnvrhi3_.dbf tag=TAG20130119T234155
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/oradata/SAN_DB1/datafile/o1_mf_sysaux_8hbrm8k6_.dbf
output file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_sysaux_0mnvrhis_.dbf tag=TAG20130119T234155
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oracle/oradata/SAN_DB1/datafile/o1_mf_undotbs_8hbrmdo9_.dbf
output file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_undotbs_0nnvrhjb_.dbf tag=TAG20130119T234155
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oracle/oradata/SAN_DB1/datafile/o1_mf_users_8hbrmj9w_.dbf
output file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_users_0onvrhjq_.dbf tag=TAG20130119T234155
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
Finished backup at 19-JAN-2013 23:43:06
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like “/oracle/oradata/flashrecovery/SAN_DB1/archivelog/2013_01_19/o1_mf_1_11_8hpxgt64_.arc” auxiliary format
“/oracle/oradata/flashrecovery/SAN_DB3/archivelog/2013_01_19/o1_mf_1_11_%u_.arc” ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 19-JAN-2013 23:43:06
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=11 RECID=5 STAMP=805160586
output file name=/oracle/oradata/flashrecovery/SAN_DB3/archivelog/2013_01_19/o1_mf_1_11_0pnvrhka_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 19-JAN-2013 23:43:07
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /oracle/oradata/flashrecovery/SAN_DB3/controlfile/o1_mf_8hpxcqw2_.ctl
File Name: /oracle/oradata/flashrecovery/SAN_DB3/archivelog/2013_01_19/o1_mf_1_11_0pnvrhka_.arc
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/oradata/flashrecovery/SAN_DB3/archivelog/2013_01_19/o1_mf_1_11_0pnvrhka_.arc
List of Files Which Where Not Cataloged
=======================================
File Name: /oracle/oradata/flashrecovery/SAN_DB3/controlfile/o1_mf_8hpxcqw2_.ctl
RMAN-07517: Reason: The file header is corrupted
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=805160587 file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_system_0lnvrhi3_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=805160587 file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_sysaux_0mnvrhis_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=805160587 file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_undotbs_0nnvrhjb_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=805160587 file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_users_0onvrhjq_.dbf
contents of Memory Script:
{
set until scn 217450;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 19-JAN-2013 23:43:07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file /oracle/oradata/flashrecovery/SAN_DB3/archivelog/2013_01_19/o1_mf_1_11_0pnvrhka_.arc
archived log file name=/oracle/oradata/flashrecovery/SAN_DB3/archivelog/2013_01_19/o1_mf_1_11_0pnvrhka_.arc thread=1 sequence=11
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-JAN-2013 23:43:09
Oracle instance started
Total System Global Area 801701888 bytes
Fixed Size 2232640 bytes
Variable Size 222301888 bytes
Database Buffers 574619648 bytes
Redo Buffers 2547712 bytes
contents of Memory Script:
{
sql clone “alter system set db_name =
”DB3” comment=
”Reset to original value by RMAN” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ”DB3” comment= ”Reset to original value by RMAN” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 801701888 bytes
Fixed Size 2232640 bytes
Variable Size 222301888 bytes
Database Buffers 574619648 bytes
Redo Buffers 2547712 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “DB3” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 100 M ,
GROUP 2 SIZE 100 M ,
GROUP 3 SIZE 100 M
DATAFILE
‘/oracle/oradata/SAN_DB3/datafile/o1_mf_system_0lnvrhi3_.dbf’
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy “/oracle/oradata/SAN_DB3/datafile/o1_mf_sysaux_0mnvrhis_.dbf”,
“/oracle/oradata/SAN_DB3/datafile/o1_mf_undotbs_0nnvrhjb_.dbf”,
“/oracle/oradata/SAN_DB3/datafile/o1_mf_users_0onvrhjq_.dbf”;
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/oradata/SAN_DB3/datafile/o1_mf_temp_%u_.tmp in control file
cataloged datafile copy
datafile copy file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_sysaux_0mnvrhis_.dbf RECID=1 STAMP=805160607
cataloged datafile copy
datafile copy file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_undotbs_0nnvrhjb_.dbf RECID=2 STAMP=805160607
cataloged datafile copy
datafile copy file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_users_0onvrhjq_.dbf RECID=3 STAMP=805160607
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=805160607 file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_sysaux_0mnvrhis_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=805160607 file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_undotbs_0nnvrhjb_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=805160607 file name=/oracle/oradata/SAN_DB3/datafile/o1_mf_users_0onvrhjq_.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data
Executing: alter database force logging
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 19-JAN-2013 23:43:50
RMAN> exit
Recovery Manager complete.
oracle@dc1:db1:/u01/app/oracle/product/11.2.0.3/db_1/dbs
> sqlplus ggs13/ggs13@db1
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 19 23:49:50 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn ggs13/ggs13@db3
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@dc1:db1:/u01/app/oracle/product/11.2.0.3/db_1/dbs
>
Now I have 2 databases on the same server configured for Golden Gate Bi-directional replication with Golden Gate users created.