on primary database
SQL> alter database force logging; Database altered. SQL> alter database add standby logfile group 4 size 50M; Database altered. SQL> alter database add standby logfile group 5 size 50M; Database altered. SQL> alter database add standby logfile group 6 size 50M; Database altered. SQL> alter database add standby logfile group 7 size 50M; Database altered.
[oracle@prodServer ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-MAR-2013 16:22:58
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prodServer)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 04-MAR-2013 14:19:15
Uptime 0 days 2 hr. 3 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/prodServer/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prodServer)(PORT=1521)))
Services Summary...
Service "zhongwc" has 2 instance(s).
Instance "zhongwc", status UNKNOWN, has 1 handler(s) for this service...
Instance "zhongwc", status READY, has 1 handler(s) for this service...
Service "zhongwcXDB" has 1 instance(s).
Instance "zhongwc", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@prodServer ~]$
[oracle@prodServer ~]$
[oracle@prodServer ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ZHONGWC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodServer)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhongwc)
)
)
clonedb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cloneServer)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clonedb)
)
)
[oracle@prodServer ~]$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = zhongwc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = zhongwc)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodServer)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@prodServer ~]$ scp $ORACLE_HOME/dbs/orapwzhongwc oracle@cloneServer://u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwclonedb
The authenticity of host 'cloneserver (192.168.1.102)' can't be established.
RSA key fingerprint is dd:4f:e4:4e:54:62:ba:8b:aa:8b:72:d3:ca:41:ca:f2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'cloneserver' (RSA) to the list of known hosts.
oracle@cloneserver's password:
orapwzhongwc
on standby database
[oracle@cloneServer admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-MAR-2013 16:28:36
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/cloneServer/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cloneServer)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cloneServer)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 04-MAR-2013 16:28:36
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/cloneServer/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cloneServer)(PORT=1521)))
Services Summary...
Service "clonedb" has 1 instance(s).
Instance "clonedb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@cloneServer admin]$
[oracle@cloneServer admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = clonedb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = clonedb)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cloneServer)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@cloneServer admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ZHONGWC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodServer)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhongwc)
)
)
clonedb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cloneServer)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clonedb)
)
)
[oracle@cloneServer admin]$ sqlplus system/oracle@zhongwc
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 4 16:28:52 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>
[oracle@cloneServer ~]$ echo "db_name=zhongwc" > $ORACLE_HOME/dbs/initclonedb.ora [oracle@cloneServer ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 4 16:30:19 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 238034944 bytes Fixed Size 2227136 bytes Variable Size 180356160 bytes Database Buffers 50331648 bytes Redo Buffers 5120000 bytes
on primary database
[oracle@prodServer ~]$ rman target / auxiliary sys/oracle@clonedb
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Mar 4 17:11:35 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ZHONGWC (DBID=184072095)
connected to auxiliary database: ZHONGWC (not mounted)
RMAN> duplicate target database for standby nofilenamecheck
from active database
dorecover
spfile
set db_unique_name='clonedb'
set log_archive_dest_1='location=/u01/app/oracle/arch'
set log_archive_dest_2='service=zhongwc async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=zhongwc'
set standby_file_management='AUTO'
set fal_server='zhongwc'
set fal_client='clonedb'
set control_files='/u01/app/oracle/oradata/clonedb/control1.ctl','/u01/app/oracle/oradata/clonedb/control2.ctl'
set db_file_name_convert='zhongwc','clonedb'
set log_file_name_convert='zhongwc','clonedb'
set memory_target='0'
15> set sga_target='400M';
Starting Duplicate Db at 04-MAR-2013 17:12:00
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwzhongwc' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwclonedb' targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilezhongwc.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileclonedb.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileclonedb.ora''";
}
executing Memory Script
Starting backup at 04-MAR-2013 17:12:01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
Finished backup at 04-MAR-2013 17:12:03
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileclonedb.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''clonedb'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''location=/u01/app/oracle/arch'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=zhongwc async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=zhongwc'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''zhongwc'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''clonedb'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/clonedb/control1.ctl'', ''/u01/app/oracle/oradata/clonedb/control2.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''zhongwc'', ''clonedb'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''zhongwc'', ''clonedb'' comment=
'''' scope=spfile";
sql clone "alter system set memory_target =
0 comment=
'''' scope=spfile";
sql clone "alter system set sga_target =
400M comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''clonedb'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=/u01/app/oracle/arch'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=zhongwc async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=zhongwc'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''zhongwc'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''clonedb'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/clonedb/control1.ctl'', ''/u01/app/oracle/oradata/clonedb/control2.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''zhongwc'', ''clonedb'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''zhongwc'', ''clonedb'' comment= '''' scope=spfile
sql statement: alter system set memory_target = 0 comment= '''' scope=spfile
sql statement: alter system set sga_target = 400M comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 188747056 bytes
Database Buffers 222298112 bytes
Redo Buffers 4272128 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/clonedb/control1.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/clonedb/control2.ctl' from
'/u01/app/oracle/oradata/clonedb/control1.ctl';
}
executing Memory Script
Starting backup at 04-MAR-2013 17:12:07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_zhongwc.f tag=TAG20130304T171207 RECID=12 STAMP=809197928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-MAR-2013 17:12:09
Starting restore at 04-MAR-2013 17:12:09
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 04-MAR-2013 17:12:10
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/clonedb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/clonedb/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/clonedb/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/clonedb/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/clonedb/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/clonedb/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/clonedb/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/clonedb/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/clonedb/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/clonedb/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/clonedb/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/clonedb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 04-MAR-2013 17:12:15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/zhongwc/system01.dbf
output file name=/u01/app/oracle/oradata/clonedb/system01.dbf tag=TAG20130304T171215
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/zhongwc/sysaux01.dbf
output file name=/u01/app/oracle/oradata/clonedb/sysaux01.dbf tag=TAG20130304T171215
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/zhongwc/example01.dbf
output file name=/u01/app/oracle/oradata/clonedb/example01.dbf tag=TAG20130304T171215
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=/u01/app/oracle/oradata/zhongwc/undotbs01.dbf
output file name=/u01/app/oracle/oradata/clonedb/undotbs01.dbf tag=TAG20130304T171215
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/zhongwc/users01.dbf
output file name=/u01/app/oracle/oradata/clonedb/users01.dbf tag=TAG20130304T171215
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-MAR-2013 17:13:35
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/app/oracle/arch/1_9_809188066.dbf" auxiliary format
"/u01/app/oracle/arch/1_9_809188066.dbf" ;
catalog clone archivelog "/u01/app/oracle/arch/1_9_809188066.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 04-MAR-2013 17:13:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=9 RECID=5 STAMP=809198015
output file name=/u01/app/oracle/arch/1_9_809188066.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 04-MAR-2013 17:13:36
cataloged archived log
archived log file name=/u01/app/oracle/arch/1_9_809188066.dbf RECID=1 STAMP=809198009
datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=809198009 file name=/u01/app/oracle/oradata/clonedb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=809198009 file name=/u01/app/oracle/oradata/clonedb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=809198009 file name=/u01/app/oracle/oradata/clonedb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=809198009 file name=/u01/app/oracle/oradata/clonedb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=809198009 file name=/u01/app/oracle/oradata/clonedb/example01.dbf
contents of Memory Script:
{
set until scn 1084245;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 04-MAR-2013 17:13:36
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/arch/1_9_809188066.dbf
archived log file name=/u01/app/oracle/arch/1_9_809188066.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-MAR-2013 17:13:37
Finished Duplicate Db at 04-MAR-2013 17:13:45
SQL> alter system set log_archive_dest_2='service=clonedb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=clonedb'; System altered. SQL> select DATABASE_ROLE,PROTECTION_MODE from v$database; DATABASE_ROLE PROTECTION_MODE ---------------- -------------------- PRIMARY MAXIMUM PERFORMANCE
on standby database
SQL> select DATABASE_ROLE,PROTECTION_MODE,open_mode from v$database; DATABASE_ROLE PROTECTION_MODE OPEN_MODE ---------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select DATABASE_ROLE,PROTECTION_MODE,open_mode from v$database; DATABASE_ROLE PROTECTION_MODE OPEN_MODE ---------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY