Scope:
This Blog explains about how to duplicate database from physical standby database.
If this situation, we need the database in open mode.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Database : 11.2.0.4.4
Platform : Linux 6
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Similar Oracle MOS Doc can be referred:
Performing RMAN duplicate from standby to create a new clone (Doc ID 1665784.1)
In case we have the active DG and we have tried start the duplicate in 2 possible cases as mentioned below where as below detailed steps, explains with case 2.
a. This will allow the duplicate to another database (either as
standby or normal DB).
2. Stop the log shipping and keep the database recovery running
with standby Database in OPEN READ ONLY APPLY mode:
a. We need to apply below patch (14263190) for your DB version on
database home (in our case duplicate database home and standby database
home are same)
Note: This patch is required on the destination database home.
Note: This patch is required on the destination database home.
b. Stop the redo log ship from primary : alter system set log_archive_dest_state_2=defer scope=spfile sid=’*’; >> At primary database.
c. Connect with net service with standby database as target and
CLONEDB name as auxiliary database.
d. Run the duplicate command. Duplicate target database to
<DB_NAME> from active database;
e. Start the redo log ship from primary : alter system set
log_archive_dest_state_2=enable scope=spfile sid=’*’; >> At
primary database.
1. Check the Standby database:
SQL> set lines 200 pages 200
SQL> SELECT thread#, process, pid, status, client_process, client_pid, sequence#, block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, process;
THREAD# PROCESS PID STATUS CLIENT_P CLIENT_PID SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- --------- ---------- ------------ -------- ---------------------------
0 ARCH 48094 CONNECTED ARCH 48094 0 0 0 0
0 RFS 56847 IDLE ARCH 67674 0 0 0 0
0 RFS 54373 IDLE UNKNOWN 18875 0 0 0 0
0 RFS 54365 IDLE UNKNOWN 67676 0 0 0 0
0 RFS 54355 IDLE UNKNOWN 18881 0 0 0 0
0 RFS 54918 IDLE UNKNOWN 67670 0 0 0 0
0 RFS 56315 IDLE ARCH 18879 0 0 0 0
1 MRP0 53701 APPLYING_LOG N/A N/A 804 408760 17 17
1 RFS 54409 IDLE LGWR 15664 804 408769 0 0
2 ARCH 48098 CLOSING ARCH 48098 373 2048 0 0
2 ARCH 48096 CLOSING ARCH 48096 374 1837056 0 0
2 RFS 54368 IDLE LGWR 62208 375 306471 0 0
13 rows selected.
SQL> select name, open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
NEERDB MOUNTED ARCHIVELOG
2. Open the database in read only mode with Archive applying:
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select name, open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
NEERDB READ ONLY WITH APPLY ARCHIVELOG
3. Prepare a pfile from spfile from Source database and make changes according to new Database name.
initDGNEER1.ora
*._b_tree_bitmap_plans=FALSE
*._fast_full_scan_enabled=FALSE
*._like_with_bind_as_equality=TRUE
*._optimizer_autostats_job=FALSE
*._sort_elimination_cost_ratio=5
*._system_trig_enabled=TRUE
*._trace_files_public=TRUE
*.aq_tm_processes=1
*.cluster_database=FALSE
*.compatible='11.2.0.4.0'
*.control_files='+DATA','+REDO'
*.cursor_sharing='EXACT'
*.db_block_checking='FALSE'
*.db_block_checksum='TRUE'
*.db_block_size=8192
*.db_cache_size=37580963840
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+RECO'
*.db_create_online_log_dest_2='+REDO'
*.db_domain=''
*.db_files=512
*.db_name='DGNEER'
*.DB_UNIQUE_NAME='DGNEER'
*.db_securefile='PERMITTED'
*.db_writer_processes=2
*.diagnostic_dest='/u01/app/oracle'
*.dml_locks=10000
dgneer.instance_number=1
*.job_queue_processes=10
*.large_pool_size=1073741824
*.log_archive_dest='+RECO'
*.log_buffer=50003968
*.log_checkpoint_interval=100000
*.log_checkpoint_timeout=1200
*.log_checkpoints_to_alert=TRUE
*.max_dump_file_size='20480'
*.nls_comp='binary'
*.nls_date_format='DD-MON-RR'
*.nls_language='american'
*.nls_length_semantics='BYTE'
*.nls_numeric_characters='.,'
*.nls_sort='binary'
*.nls_territory='america'
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.olap_page_pool_size=4194304
*.open_cursors=600
*.open_links=4
*.open_links_per_instance=8
*.optimizer_features_enable='11.2.0.3'
*.optimizer_secure_view_merging=FALSE
*.parallel_max_servers=8
*.parallel_min_servers=0
*.pga_aggregate_target=26843545600
*.processes=600
*.recyclebin='OFF'
*.remote_listener='SCANNEER:1521'
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan='DEFAULT_MAINTENANCE_PLAN'
*.sec_case_sensitive_logon=FALSE
*.sec_max_failed_login_attempts=8888
*.session_cached_cursors=500
*.sessions=924
*.sga_max_size=53376M
*.sga_target=53376M
*.shared_pool_reserved_size=1073741824
*.shared_pool_size=15000M
*.streams_pool_size=524288000
*.undo_management='AUTO'
*.undo_retention=172800
dgneer.undo_tablespace='UNDOTS1'
4. Configure the static listener at target database server:
cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
ADR_BASE_LISTENER_LOCAL = /u01/app/oracle
LISTENER_LOCAL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = neeraj.dba.com)(PORT = 1528))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1528))
)
)
SID_LIST_LISTENER_LOCAL = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = DGNEER) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1) (SID_NAME = DGNEER1)))
5. Start the static listener:
[oracle@neeraj.dba.com admin]$ lsnrctl status listener_local
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-JUN-2014 16:02:49
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=neeraj.dba.com)(PORT=1528)))
STATUS of the LISTENER
------------------------
Alias listener_local
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 29-JUN-2014 15:52:22
Uptime 0 days 0 hr. 10 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/neeraj.dba.com/listener_local/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=neeraj.dba.com)(PORT=1528)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1528)))
Services Summary...
Service "DGNEER" has 1 instance(s).
Instance "DGNEER1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
6. Check connection with source and target database:
7. Startup target database in nomount state:
Startup database in nomount with pfile and the create spfile after that shutdown the database and start database using spfile
[oracle@neeraj.dba.com dbs]$
[oracle@neeraj.dba.com dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 30 08:50:14 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_name string DGNEER
SQL> show parameter uniq
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_unique_name string DGNEER
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------
spfile string
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- -------------
control_file_record_keep_time integer 7
control_files string +DATA, +REDO
8. Connection test with RMAN:
[oracle@neeraj.dba.com dbs]$ rman target sys@NEERDBDG auxiliary sys@DGNEER1
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 30 08:52:23 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: NEERDB (DBID=3461377734)
auxiliary database Password:
connected to auxiliary database: DGNEER (not mounted)
RMAN>
9. At Primary database disable the archive log ship:
SQL> alter system set LOG_ARCHIVE_DEST_state_2=defer scope=both sid='*';
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------
log_archive_dest_state_2 string DEFER
With RMAN connection run the database duplicate command:
$ rman target sys@NEERDBDG auxiliary sys@DGNEER1
RMAN> duplicate target database to DGNEER from active database;
Once the Duplicate command is completed enable the archive log ship from Primary database:
SQL> alter system set LOG_ARCHIVE_DEST_state_2=enable scope=both sid='*';
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_state_2
NAME TYPE VALUE
------------------------------------ ----------- -----------
log_archive_dest_state_2 string ENABLE
Check the cloned database status:
[oracle@neeraj.dba.com dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 30 12:39:41 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select name, open_mode, log_mode , database_role from v$database;
NAME OPEN_MODE LOG_MODE DATABASE_ROLE
--------- -------------------- ------------ ----------------
DGNEER READ WRITE ARCHIVELOG PRIMARY
SQL> !date
Thu Jun 30 12:39:46 IDT 2014
SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
Session altered.
SQL> select startup_time from v$instance;
STARTUP_TIME
-------------------
30/06/2014 12:34:06
SQL>
Some Known Issues and expected errors:
1. RMAN-06136: ORACLE error from auxiliary database: ORA-01194: file 1 needs more recovery to be consistent
2. Segmentation Fault During Duplicate From Active Database
No comments:
Post a Comment