Thinking Out Loud

January 20, 2013

Doing it wrong

Filed under: 11g,oracle — mdinh @ 5:34 am

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.

About these ads

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 302 other followers

%d bloggers like this: