I. Introduction
According to oracle note Oracle Data Guard ensures high availability, data
protection, and disaster recovery for enterprise data. Data Guard provides a
comprehensive set of services that create, maintain, manage, and monitor one or
more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data
Guard maintains these standby databases as transactionally consistent copies of
the production database. Then, if the production database becomes unavailable
because of a planned or an unplanned outage, Data Guard can switch any standby
database to the production role, minimizing the downtime associated with the
outage. Data Guard can be used with traditional backup, restoration, and cluster
techniques to provide a high level of data protection and data availability.
A standby database can be either a physical standby database or a logical
standby database:
-
Physical standby
database
Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, though Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.
A physical standby database can be used for business purposes other than disaster recovery on a limited basis.
-
Logical standby
database
Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database though SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database.
A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.
Complete article : (http://docs.oracle.com/cd/B19306_01/server.102/b14239/concepts.htm)
For my example, the primary database is ORALNDO (host=hots1), and the standby is NASHVILL (host=hots2).
The OS is linux x86_64 and the oracle version is 10.2.0.5.
II. Configuration
1. Ensure that ORLANDO is in archivelog mode, otherwise convert
it.
remote_login_passwordfile =exclusive.
Enable the logging mode
2. Create Standby Redo Logs (optional)
on primary db ORALNDO :
SQL> select group#, thread#, bytes, members from v$log;
GROUP# THREAD# BYTES MEMBERS
---------- ---------- ---------- ----------
1 1 52428800 2
2 1 52428800 2
3 1 52428800 2
create standby redo log : (# of online redo log file groups on primary + 1) * maximum # of threads
for oralndo : threads = 1 because no RAC
The number of standby redo logs required for the physical standby database in this example is (3 + 1) * 1 = 4 at 50MB each.
From the primary database, connect as SYS and run the following to create four standby redo log file groups:
SQL> alter database add standby logfile thread 1 group 4 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 size 50m;
Database altered.
sql > select group#, type, member from v$logfile order by group#, member;
....
## Standby Role Parameters ##
###########################################
4. Rman backup of primary db: orlando
RMAN > backup database plus archivelog;
5. Create a Standby Controlfile
RMAN> backup device type disk format '/u02/oracle/oradata/ORLANDO/standby_controlfile/%U' current controlfile for standby;
6. create dirtectories on the standby server (host2)
mkdir -p /u01/app/oracle/admin/NASHVILL/adump
mkdir -p /u01/app/oracle/admin/NASHVILL/udump
mkdir -p /u01/app/oracle/admin/NASHVILL/dpdump
mkdir -p /u01/app/oracle/admin/NASHVILL/bdump
mkdir -p /u01/app/oracle/admin/NASHVILL/pfile
mkdir -p /u01/app/oracle/admin/NASHVILL/scripts
mkdir -p /u02/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u03/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u04/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u02/oracle/oradata/NASHVILL/data_system
mkdir -p /u02/oracle/oradata/ORLANDO/data
mkdir -p /u02/oracle/oradata/NASHVILL/data
mkdir -p /u03/oracle/oradata/NASHVILL/data
mkdir -p /u04/oracle/oradata/NASHVILL/data
mkdir -p /u02/oracle/oradata/NASHVILL/redo
mkdir -p /u03/oracle/oradata/NASHVILL/redo
mkdir -p /u04/oracle/oradata/NASHVILL/redo
mkdir -p /u05/oracle/oradata/NASHVILL/archive
mkdir -p /u02/oracle/oradata/NASHVILL/standby_controlfile
mkdir -p /u02/oracle/oradata/ORLANDO/ ----> to copy standby_controlfile see 13.
7. copy orlando.pfile to standby
From primary host (host1) to stdb host (host2)
a. scp ORLANDO_init.ora oracle@host2:/u01/app/oracle/admin/NASHVILL/pfile
b. on the stdb host : mv ORLANDO_init.ora NASHVILL_init.ora
8.update NASHVILL_init.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
## Primary Role Parameters ##
DB_NAME=ORLANDO
INSTANCE_NAME=NASHVILL
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORLANDO,NASHVILL)'
compatible=10.2.0.5.0
control_files=("/u02/oracle/oradata/NASHVILL/controlfile/control01.ctl", "/u03/oracle/oradata/NASHVILL/controlfile/control02.ctl",
"/u04/oracle/oradata/NASHVILL/controlfile/control03.ctl")
DB_UNIQUE_NAME=NASHVILL
LOG_ARCHIVE_DEST_1=
'LOCATION=/u05/oracle/oradata/NASHVILL/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=NASHVILL'
LOG_ARCHIVE_DEST_2=
'SERVICE=ORLANDO VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORLANDO'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
SERVICE_NAMES=NASHVILL
## Standby Role Parameters ##
DB_FILE_NAME_CONVERT= '/ORLANDO/', '/NASHVILL/'
LOG_FILE_NAME_CONVERT= '/ORLANDO/', '/NASHVILL/'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER='ORLANDO','NASHVILL'
FAL_CLIENT='NASHVILL'
standby_file_management='auto'
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=1247805440
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# File Configuration
###########################################
db_recovery_file_dest=/u05/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
###########################################
# SGA Memory
###########################################
sga_target=1610612736
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/app/oracle/admin/NASHVILL/bdump
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Diagnostics and Statistics
###########################################
user_dump_dest=/u01/app/oracle/admin/NASHVILL/udump
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=NASHVILLXDB)"
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/NASHVILL/adump
###########################################
# Database Identification
###########################################
db_domain=""
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
###########################################
# Diagnostics and Statistics
###########################################
core_dump_dest=/u01/app/oracle/admin/NASHVILL/cdump
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
###########################################
# Cache and I/O
###########################################
db_file_multiblock_read_count=16
9. tnsnames
host2 :
NASHVILL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NASHVILL)
)
)
hots1 :
NASHVILL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NASHVILL) (UR=A) -- This will allow the primary database to connect remotely to a database in nomount or mount mode. It’s a feature introduced in oracle 10g.
)
)
10. listener (host2)
(SID_DESC =
(GLOBAL_DBNAME = NASHVILL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = NASHVILL)
Check with the SQL*Net configuration using the following commands on the Primary AND Standby
from host1 server, check the communication beetween the 2 databases : tnsping NASHVILL
11. create pwdfile on host2
orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapwNASHVILL password=xxxx
12. startup stanby on nomount
export ORACLE_SID=NASHVILL
sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/admin/NASHVILL/pfile/NASHVILL_init.ora';
13.copy backups
Using an OS remote copy utility, transfer the backup of the primary database, standby controlfile, and standby initialization parameter file to the standby host :
scp -r /u05/oracle/flash_recovery_area/ORLANDO oracle@xxxx:/u05/oracle/flash_recovery_area/
scp -r /u02/oracle/oradata/ORLANDO/standby_controlfile oracle@xxxx:/u02/oracle/oradata/ORLANDO/
15. primary db
alter system set log_archive_dest_state_2=enable scope=both;
16. standby
To use Real Time Apply, run the following alternate version of the alter database command to place the standby database in managed recovery mode:
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
17.test
primary : alter system archive log current;
standby : select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME ARC APP
---------- --------- --------- --- ---
131 11-APR-12 11-APR-12 YES YES
132 11-APR-12 12-APR-12 YES YES
133 12-APR-12 16-APR-12 YES YES
III. Role transition : Implement Role Switchover Operation
1. Initialisation
Primary and stantdby databases must support the role transition.
Primary :
Standby:
2.tnsnames
hots2 :
ORLANDO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORLANDO )
)
)
4. Redo apply within standby DB : NASHVILL
SQL> select client_process, process, sequence#, status
2 from v$managed_standby;
CLIENT_P PROCESS SEQUENCE# STATUS
-------- --------- ---------- ------------
ARCH ARCH 150 CLOSING
ARCH ARCH 151 CLOSING
N/A MRP0 152 WAIT_FOR_LOG
UNKNOWN RFS 0 IDLE
Enable the logging mode
SQL> ALTER DATABASE FORCE LOGGING;
2. Create Standby Redo Logs (optional)
on primary db ORALNDO :
SQL> select group#, thread#, bytes, members from v$log;
GROUP# THREAD# BYTES MEMBERS
---------- ---------- ---------- ----------
1 1 52428800 2
2 1 52428800 2
3 1 52428800 2
create standby redo log : (# of online redo log file groups on primary + 1) * maximum # of threads
for oralndo : threads = 1 because no RAC
The number of standby redo logs required for the physical standby database in this example is (3 + 1) * 1 = 4 at 50MB each.
From the primary database, connect as SYS and run the following to create four standby redo log file groups:
SQL> alter database add standby logfile thread 1 group 4 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 size 50m;
Database altered.
sql > select group#, type, member from v$logfile order by group#, member;
....
3. Init param for primary db : ORLANDO
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
## Primary Role Parameters ##
DB_UNIQUE_NAME=ORLANDO
SERVICE_NAMES=ORLANDO
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORLANDO,NASHVILL)'
compatible=10.2.0.5.0
control_files=("/u02/oracle/oradata/ORLANDO/controlfile/control01.ctl", "/u03/oracle/oradata/ORLANDO/controlfile/control02.ctl",
"/u04/oracle/oradata/ORLANDO/controlfile/control03.ctl")
LOG_ARCHIVE_DEST_1=
'LOCATION=/u05/oracle/oradata/ORLANDO/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ORLANDO'
LOG_ARCHIVE_DEST_2=
'SERVICE=NASHVILL
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=NASHVILL'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=DEFER
## Standby Role Parameters ##
DB_FILE_NAME_CONVERT= '/NASHVILL/', '/ORLANDO/'
LOG_FILE_NAME_CONVERT= '/NASHVILL/', '/ORLANDO/'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER='ORLANDO','NASHVILL'
FAL_CLIENT='ORLANDO'
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=1247805440
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# File Configuration
###########################################
db_recovery_file_dest=/u05/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
###########################################
# SGA Memory
###########################################
sga_target=1610612736
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/app/oracle/admin/ORLANDO/bdump
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Database Identification
###########################################
db_name=ORLANDO
###########################################
# Diagnostics and Statistics
###########################################
user_dump_dest=/u01/app/oracle/admin/ORLANDO/udump
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=ORLANDOXDB)"
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/ORLANDO/adump
###########################################
# Database Identification
###########################################
db_domain=""
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
###########################################
# Diagnostics and Statistics
###########################################
core_dump_dest=/u01/app/oracle/admin/ORLANDO/cdump
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
###########################################
# Cache and I/O
###########################################
db_file_multiblock_read_count=16
4. Rman backup of primary db: orlando
RMAN > backup database plus archivelog;
5. Create a Standby Controlfile
RMAN> backup device type disk format '/u02/oracle/oradata/ORLANDO/standby_controlfile/%U' current controlfile for standby;
6. create dirtectories on the standby server (host2)
mkdir -p /u01/app/oracle/admin/NASHVILL/adump
mkdir -p /u01/app/oracle/admin/NASHVILL/udump
mkdir -p /u01/app/oracle/admin/NASHVILL/dpdump
mkdir -p /u01/app/oracle/admin/NASHVILL/bdump
mkdir -p /u01/app/oracle/admin/NASHVILL/pfile
mkdir -p /u01/app/oracle/admin/NASHVILL/scripts
mkdir -p /u02/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u03/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u04/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u02/oracle/oradata/NASHVILL/data_system
mkdir -p /u02/oracle/oradata/ORLANDO/data
mkdir -p /u02/oracle/oradata/NASHVILL/data
mkdir -p /u03/oracle/oradata/NASHVILL/data
mkdir -p /u04/oracle/oradata/NASHVILL/data
mkdir -p /u02/oracle/oradata/NASHVILL/redo
mkdir -p /u03/oracle/oradata/NASHVILL/redo
mkdir -p /u04/oracle/oradata/NASHVILL/redo
mkdir -p /u05/oracle/oradata/NASHVILL/archive
mkdir -p /u02/oracle/oradata/NASHVILL/standby_controlfile
mkdir -p /u02/oracle/oradata/ORLANDO/ ----> to copy standby_controlfile see 13.
7. copy orlando.pfile to standby
From primary host (host1) to stdb host (host2)
a. scp ORLANDO_init.ora oracle@host2:/u01/app/oracle/admin/NASHVILL/pfile
b. on the stdb host : mv ORLANDO_init.ora NASHVILL_init.ora
8.update NASHVILL_init.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
## Primary Role Parameters ##
DB_NAME=ORLANDO
INSTANCE_NAME=NASHVILL
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORLANDO,NASHVILL)'
compatible=10.2.0.5.0
control_files=("/u02/oracle/oradata/NASHVILL/controlfile/control01.ctl", "/u03/oracle/oradata/NASHVILL/controlfile/control02.ctl",
"/u04/oracle/oradata/NASHVILL/controlfile/control03.ctl")
DB_UNIQUE_NAME=NASHVILL
LOG_ARCHIVE_DEST_1=
'LOCATION=/u05/oracle/oradata/NASHVILL/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=NASHVILL'
LOG_ARCHIVE_DEST_2=
'SERVICE=ORLANDO VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORLANDO'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
SERVICE_NAMES=NASHVILL
## Standby Role Parameters ##
DB_FILE_NAME_CONVERT= '/ORLANDO/', '/NASHVILL/'
LOG_FILE_NAME_CONVERT= '/ORLANDO/', '/NASHVILL/'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER='ORLANDO','NASHVILL'
FAL_CLIENT='NASHVILL'
standby_file_management='auto'
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=1247805440
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# File Configuration
###########################################
db_recovery_file_dest=/u05/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
###########################################
# SGA Memory
###########################################
sga_target=1610612736
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/app/oracle/admin/NASHVILL/bdump
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Diagnostics and Statistics
###########################################
user_dump_dest=/u01/app/oracle/admin/NASHVILL/udump
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=NASHVILLXDB)"
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/NASHVILL/adump
###########################################
# Database Identification
###########################################
db_domain=""
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
###########################################
# Diagnostics and Statistics
###########################################
core_dump_dest=/u01/app/oracle/admin/NASHVILL/cdump
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
###########################################
# Cache and I/O
###########################################
db_file_multiblock_read_count=16
9. tnsnames
host2 :
NASHVILL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NASHVILL)
)
)
hots1 :
NASHVILL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NASHVILL) (UR=A) -- This will allow the primary database to connect remotely to a database in nomount or mount mode. It’s a feature introduced in oracle 10g.
)
)
10. listener (host2)
(SID_DESC =
(GLOBAL_DBNAME = NASHVILL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = NASHVILL)
Check with the SQL*Net configuration using the following commands on the Primary AND Standby
from host1 server, check the communication beetween the 2 databases : tnsping NASHVILL
11. create pwdfile on host2
orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapwNASHVILL password=xxxx
12. startup stanby on nomount
export ORACLE_SID=NASHVILL
sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/admin/NASHVILL/pfile/NASHVILL_init.ora';
13.copy backups
Using an OS remote copy utility, transfer the backup of the primary database, standby controlfile, and standby initialization parameter file to the standby host :
scp -r /u05/oracle/flash_recovery_area/ORLANDO oracle@xxxx:/u05/oracle/flash_recovery_area/
scp -r /u02/oracle/oradata/ORLANDO/standby_controlfile oracle@xxxx:/u02/oracle/oradata/ORLANDO/
14.Create the Physical Standby Database
on stdby host (host2):
[oracle@host2 ~]$ export ORACLE_SID=NASHVILL
[oracle@host2 ~]$ rman target sys/xxxxl@ORLANDO auxiliary/
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 19 17:23:49 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORLANDO (DBID=4090161544)
connected to auxiliary database: ORLANDO (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate Db at 20-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 20-MAR-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u02/oracle/oradata/ORLANDO/standby_controlfile/12n697ah_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/oracle/oradata/ORLANDO/standby_controlfile/12n697ah_1_1 tag=TAG20120319T151009
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u02/oracle/oradata/NASHVILL/controlfile/control01.ctl
output filename=/u03/oracle/oradata/NASHVILL/controlfile/control02.ctl
output filename=/u04/oracle/oradata/NASHVILL/controlfile/control03.ctl
Finished restore at 20-MAR-12
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u02/oracle/oradata/NASHVILL/data_system/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u02/oracle/oradata/NASHVILL/data_system/system01.dbf";
set newname for datafile 2 to
"/u02/oracle/oradata/NASHVILL/data_system/undotbs01.dbf";
set newname for datafile 3 to
"/u02/oracle/oradata/NASHVILL/data_system/sysaux01.dbf";
set newname for datafile 4 to
"/u02/oracle/oradata/NASHVILL/data_system/users01.dbf";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /u02/oracle/oradata/NASHVILL/data_system/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-MAR-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oracle/oradata/NASHVILL/data_system/system01.dbf
restoring datafile 00002 to /u02/oracle/oradata/NASHVILL/data_system/undotbs01.dbf
restoring datafile 00003 to /u02/oracle/oradata/NASHVILL/data_system/sysaux01.dbf
restoring datafile 00004 to /u02/oracle/oradata/NASHVILL/data_system/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u05/oracle/flash_recovery_area/ORLANDO/backupset/2012_03_19/o1_mf_nnndf_TAG20120319T150920_7pgd00oh_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u05/oracle/flash_recovery_area/ORLANDO/backupset/2012_03_19/o1_mf_nnndf_TAG20120319T150920_7pgd00oh_.bkp tag=TAG20120319T150920
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 20-MAR-12
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=8 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=9 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/users01.dbf
Finished Duplicate Db at 20-MAR-12
exit;
15. primary db
alter system set log_archive_dest_state_2=enable scope=both;
16. standby
To use Real Time Apply, run the following alternate version of the alter database command to place the standby database in managed recovery mode:
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
17.test
primary : alter system archive log current;
SQL> select status, error from v$archive_dest where dest_id = 2; STATUS ERROR --------- ----------------------------------------------------------------- VALID
standby : select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME ARC APP
---------- --------- --------- --- ---
131 11-APR-12 11-APR-12 YES YES
132 11-APR-12 12-APR-12 YES YES
133 12-APR-12 16-APR-12 YES YES
1. Initialisation
Primary and stantdby databases must support the role transition.
Primary :
LOG_ARCHIVE_DEST_2=
'SERVICE=NASHVILL VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=NASHVILL'
FAL_SERVER='ORLANDO','NASHVILL'
FAL_CLIENT='ORLANDO'
Standby:
LOG_ARCHIVE_DEST_2=
'SERVICE=ORLANDO VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORLANDO'
DB_UNIQUE_NAME=NASHVILL'
DB_UNIQUE_NAME=NASHVILL'
FAL_SERVER='ORLANDO','NASHVILL'
FAL_CLIENT='NASHVILL'
2.tnsnames
hots2 :
ORLANDO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORLANDO )
)
)
3. Redo current with primary DB : ORLANDO
SQL> alter system switch logfile; System altered. SQL> select status, error from v$archive_dest where dest_id = 2; STATUS ERROR --------- --------------------------------------------------------- VALID
4. Redo apply within standby DB : NASHVILL
SQL> select client_process, process, sequence#, status
2 from v$managed_standby;
CLIENT_P PROCESS SEQUENCE# STATUS
-------- --------- ---------- ------------
ARCH ARCH 150 CLOSING
ARCH ARCH 151 CLOSING
N/A MRP0 152 WAIT_FOR_LOG
UNKNOWN RFS 0 IDLE
5. Perform the Switcher Operation
Primary DB:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
Status "Session active" indicates to apply "WITH SESSION SHUTDOWN" clause :
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> startup mount;
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
7. standby DB NASHVILL becomes primary :
Primary DB:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
Status "Session active" indicates to apply "WITH SESSION SHUTDOWN" clause :
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
6. Shtdown and restart the primary DB as a standby DB
SQL> shutdown immediate;SQL> startup mount;
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
7. standby DB NASHVILL becomes primary :
SQL> alter database commit to switchover to primary;SQL> shutdown immediate;SQL> startup ;
8.standby manages operations :
--START REDO APPLY SQL> alter database recover managed standby database disconnect; --START REDO APPLY USING REAL-TIME APPLY SQL> alter database recover managed standby database using current logfile disconnect;
Aucun commentaire:
Enregistrer un commentaire