Processes and Duplicate replicates the database and creates a physical StandBy Similarly, you just don't need to restart the database.
Objective: To create standby without restarting the source database
1 Set the environment as follows:
Primary Database
| IP | 172.17.22.16 |
| SID | orcl |
Standby database
| IP | 172.17.22.17 |
| SID | orcl_standby |
Set prompts to distinguish the location of operations
primary database
set SQLPROMPT Primary>
standby database
set SQLPROMPT StandBy>
1. Set archiving mode on Primary side
Ensure that the primary database is running in Archive Mode
Primary>archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Current log sequence 7
Primary>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Primary>startup mount
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 1795164312 bytes
Database Buffers 1476395008 bytes
Redo Buffers 16568320 bytes
Database mounted.
Primary>alter database archivelog;
Database altered.
Primary>alter database open;
Database altered.
Primary>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
Primary>
2. Open mandatory log on Primary side
Primary>select force_logging from v$database;
FOR
---
NO
Primary>alter database force logging;
Database altered.
About FORCE LOGGING
You know that there are DDL statements that can avoid writing redo logs by specifying NOLOGGING clauses (for speed, sometimes valid). When a database is specified as FORCE LOGGING mode, the database records all operations except temporary table spaces or temporary rollback segments and ignores specified parameters such as NOLOGGING.If a statement such as NOLOGGING is executed while force logging is executed, force logging waits until all such statements are executed.FORCE LOGGING is saved as a fixed parameter in the control file, so it is not affected by operations such as restart (it can only be executed once), if you want to cancel, you can close the mandatory record through the alter database no force logging statement.
3. Create related directories on standby side
In order to save the same structure as the Primary library, we need to create the same directory in the Standby database by first querying the related directories of the existing Primary database
Primary>col name for a30
Primary>col value for a100
Primary>select name ,value from v$parameter where name in ('audit_file_dest','background_dump_dest','control_files','core_dump_dest','user_dump_dest') ORDER BY name ASC;
NAME VALUE
------------------------------ ---------------------------------------------------------------------------------------------
audit_file_dest /usr/oracle/app/admin/orcl/adump
background_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/trace
control_files /usr/oracle/app/oradata/orcl/control01.ctl, /usr/oracle/app/flash_recovery_area/orcl/control02.ctl
core_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/cdump
user_dump_dest /usr/oracle/app/diag/rdbms/orcl/orcl/trace
Create the following directory on the standby database server:
[oracle@oracledb ~]$ mkdir -p /usr/oracle/standby/app/admin/orcl_standby/adump [oracle@oracledb ~]$ mkdir -p /usr/oracle/standby/app/diag/rdbms/orcl_standby/orcl_standby/trace [oracle@oracledb ~]$ mkdir -p /usr/oracle/standby/app/oradata/orcl_standby [oracle@oracledb ~]$ mkdir -p /usr/oracle/standby/app/flash_recovery_area/orcl_standby [oracle@oracledb ~]$ mkdir -p /usr/oracle/standby/app/diag/rdbms/orcl_standby/orcl_standby/
3. Create auxiliary instance key file
Method 1: Copy the key file directly from the Primary database. (If ORACLE_SID is different, rename it)
[oracle@oracledb dbs]$ scp orapworcl 172.17.22.17:/usr/oracle/standby/app/product/11.2.0/dbhome_1/dbs oracle@172.17.22.17's password: orapworcl 100% 1536 1.5KB/s 00:00 [oracle@oracledb dbs]$
Rename
[oracle@oracledb dbs]$ mv orapworcl orapworcl_standby
Method 2: orapwd generation
orapwd FILE=/usr/oracle/standby/app/product/11.2.0/dbhome_1/dbs/orapworcl password=wangshengzhuang entries=30;
4. Modify the primary end initialization parameters.
The following parameters need to be configured and described below
1. Description of initialization parameters related to the primary library role: DB_NAME Note that keep all databases DB_NAME the same in the same DataGuard environment DB_UNIQUE_NAME Specify a unique name for each database to identify different databases in the same dataguard environment. LOG_ARCHIVE_CONFIG This parameter lists all db_UNIQUE_NAME (including primary and standby dbs) in the same Data Guard through the DG_CONFIG attribute, separated by commas. For example: LOG_ARCHIVE_CONFIG='DB_CONFIG=(orcl,orcl2)' LOG_ARCHIVE_DEST_n Generation path of archive file.This parameter is important, and the dataguard transfers the logs through the settings here. LOG_ARCHIVE_DEST_STATE_n The specified parameter value is ENABLE, indicating whether the corresponding LOG_ARCHIVE_DEST_n parameter is valid. REMOTE_LOGIN_PASSWORDFILE The recommended setting parameter value is EXCLUSIVE or SHARED, taking care to ensure that all db server sys passwords in the same Data Guard configuration are the same.If different log transfers fail.The database defaults to EXCLUSIVE and generally does not need to be modified. LOG_ARCHIVE_FORMAT Specify the archive file format.Normally no modification is required, just leave the default 2. The following parameters are related to the standby role. It is recommended that you also set them in the initialization parameters of the primary library so that the dataguard can function without modification after the primary and standby roles have been converted to each other. FAL_SERVER Specify the name of the connection service from the standby to the primary database, FAL_SERVER = the server on which the orcl2 log resides. FAL_CLIENT Specify the connection service name from the primary library to the standby library, FAL_CLIENT = orcl log receiving client. STANDBY_FILE_MANAGEMENT If the database files of the primary database are modified (e.g. new, renamed, etc.), the corresponding changes are made in the standby according to the settings of this parameter.Setting AUTO means automatic management.Setting MANUAL means manual management is required. For example: STANDBY_FILE_MANAGEMENT=AUTO
Generate pfiles from spfile s on the Primary side and back them up
Primary>create pfile from spfile; File created.
Query db_unique_name of Primary library (same default as db_name)
Primary>show parameter db_unique_name; NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_unique_name string orcl
Modify spfile parameter value
-------In order not to restart,Follow the above db_unique_name
--alter system set DB_UNIQUE_NAME=orcl scope=spfile
----There orcl and orcl_standby For primary and backup Libraries db_unique_name
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_standby)' scope=spfile
-----There service The name of the service that connects to the standby for the primary library, followed by tnsnames.ora File Configuration valid_for Parameter specifies that this archive log destination needs to be in the role of this database as the primary Library online_logfile Transfer to the repository. arch async NOAFFIRM Describes the synchronization method, which has three ways, maximum protection, maximum performance and maximum availability.Each method has different settings, as described below
alter system set LOG_ARCHIVE_DEST_2='SERVICE=tns_standby ARCH ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_standby'
----- Do not enable log shipping for now
alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER
------The following modifications are required as parameters for the backup role. For the convenience of switching between the primary and backup libraries in the future, it is recommended that the parameters related to the backup role be also configured in the main library.
alter system set FAL_SERVER=tns_standby
alter system set FAL_CLIENT=tns_primary
alter system set STANDBY_FILE_MANAGEMENT=AUTOalter system set DB_FILE_NAME_CONVERT='/usr/oracle/standby/app/oradata/orcl_standby/','/usr/oracle/app/oradata/orcl/' scope=spfile;alter system set LOG_FILE_NAME_CONVERT='/usr/oracle/standby/app/oradata/orcl_standby/','/usr/oracle/app/oradata/orcl/' scope=spfile;
Generate pfile on standby side
Regenerate the pfile from the spfile on the Primary side to start the standby database
Primary>create pfile from spfile; File created.
Copy the generated initorcl.ora to the $ORACLE_HOME/dbs directory of standby and rename it (because the instance names are different)
[oracle@oracledb dbs]$ pwd
/usr/oracle/app/product/11.2.0/dbhome_1/dbs
[oracle@oracledb dbs]$ scp initorcl.ora 172.17.22.17:/usr/oracle/standby/app/product/11.2.0/dbhome_1/dbs
The authenticity of host '172.17.22.17 (172.17.22.17)' can't be established.
RSA key fingerprint is 72:28:f5:f9:9c:f8:49:23:48:6d:9d:d4:0e:0c:89:71.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.17.22.17' (RSA) to the list of known hosts.
oracle@172.17.22.17's password:
initorcl.ora 100% 1291 1.3KB/s 00:00
[oracle@oracledb dbs]$
Rename
[oracle@oracledb dbs]$ mv initorcl.ora initorcl_standby.ora
Modify the initorcl_standby.ora file on the standbyd side as follows
------The instance name here is to be orcl Change to orcl_standbyorcl_standby.__db_cache_size=1476395008
orcl_standby.__java_pool_size=16777216
orcl_standby.__large_pool_size=16777216
orcl_standby.__oracle_base='/usr/oracle/app'#ORACLE_BASE set from environment
orcl_standby.__pga_aggregate_target=1325400064
orcl_standby.__sga_target=1979711488
orcl_standby.__shared_io_pool_size=0
orcl_standby.__shared_pool_size=436207616
orcl_standby.__streams_pool_size=0
*.audit_file_dest='/usr/oracle/standby/app/admin/orcl_standby/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/usr/oracle/standby/app/oradata/orcl_standby/control01.ctl','/usr/oracle/standby/app/flash_recovery_area/orcl_standby/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/usr/oracle/standby/app/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/usr/oracle/standby/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=3299868672
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.DB_UNIQUE_NAME=orcl_standby
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_standby)'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_primary ARCH ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' #arch represents archive log
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
------Path Conversion
*.DB_FILE_NAME_CONVERT= '/usr/oracle/app/oradata/orcl/','/usr/oracle/standby/app/oradata/orcl_standby/' *.LOG_FILE_NAME_CONVERT='/usr/oracle/app/oradata/orcl/','/usr/oracle/standby/app/oradata/orcl_standby/' *.FAL_SERVER=tns_primary *.FAL_CLIENT=tns_standby*.STANDBY_FILE_MANAGEMENT=AUTO
Create spfile s for Standby databases from copied pfiles
StandBy> create spfile from pfile; File created.
Start to nomount environment
SQL> startup nomount
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 1795164312 bytes
Database Buffers 1476395008 bytes
Redo Buffers 16568320 bytes
5. Configure monitoring services
Primary-side listening (should already exist, most of the time without reconfiguring)
# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /usr/oracle/app
StandBy side listening (with static listening service name GLOBAL_DBNAME = StandBy configured)
# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = StandBy)
(ORACLE_HOME =/usr/oracle/standby/app/product/11.2.0/dbhome_1)
(SID_NAME = orcl_standby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /usr/oracle/app
Start standby-side listening
[oracle@oracledb admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:15
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /usr/oracle/standby/app/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /usr/oracle/standby/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /usr/oracle/standby/app/diag/tnslsnr/oracledb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 23-DEC-2015 15:48:16
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /usr/oracle/standby/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /usr/oracle/standby/app/diag/tnslsnr/oracledb/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))
Services Summary...
Service "StandBy" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
6. Configure network service name and test interoperability
The Primary and StandBy ends are configured as follows:
tns_primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
tns_standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =StandBy )
)
)
***********************************************************************************************
The value of SERVICE_NAME refers to the output of lsnrctl to ensure that the SERVICE_NAME can be connected using the sqlplus or navicact tools
Because fal_server uses the service name, this archive log cannot be sent properly if it is not configured correctly
***********************************************************************************************
Testing on both primary and standby (for role switching)
[oracle@oracledb admin]$ tnsping tns_primary [oracle@oracledb admin]$ tnsping tns_standby
7,duplicate standby
rman connects two databases
[oracle@oracledb admin]$ rman target sys/wangshengzhuang@tns_primary auxiliary sys/wangshengzhuang@tns_standby
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 16:11:51 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1426832466)
connected to auxiliary database: ORCL (not mounted)
Start copying
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
Starting Duplicate Db at 23-DEC-15
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
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/23/2015 16:13:38
RMAN-05541: no archived logs found in target database
Switch down the log on the primary side and quit executes rman duplicate again
[oracle@oracledb ~]$ rman target sys/wangshengzhuang@tns_primary auxiliary sys/wangshengzhuang@tns_standby
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 30 16:56:13 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1426832466)
connected to auxiliary database: ORCL (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
Starting Duplicate Db at 30-DEC-15
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 '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format
'/usr/oracle/standby/app/product/11.2.0/dbhome_1/dbs/orapworcl_standby' ;
}
executing Memory Script
Starting backup at 30-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
Finished backup at 30-DEC-15
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/usr/oracle/standby/app/oradata/orcl_standby/control01.ctl';
restore clone controlfile to '/usr/oracle/standby/app/flash_recovery_area/orcl_standby/control02.ctl' from
'/usr/oracle/standby/app/oradata/orcl_standby/control01.ctl';
}
executing Memory Script
Starting backup at 30-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20151230T165618 RECID=4 STAMP=899830578
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-DEC-15
Starting restore at 30-DEC-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 30-DEC-15
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
"/usr/oracle/standby/app/oradata/orcl_standby/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/usr/oracle/standby/app/oradata/orcl_standby/system01.dbf";
set newname for datafile 2 to
"/usr/oracle/standby/app/oradata/orcl_standby/sysaux01.dbf";
set newname for datafile 3 to
"/usr/oracle/standby/app/oradata/orcl_standby/undotbs01.dbf";
set newname for datafile 4 to
"/usr/oracle/standby/app/oradata/orcl_standby/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/usr/oracle/standby/app/oradata/orcl_standby/system01.dbf" datafile
auxiliary format
"/usr/oracle/standby/app/oradata/orcl_standby/sysaux01.dbf" datafile
auxiliary format
"/usr/oracle/standby/app/oradata/orcl_standby/undotbs01.dbf" datafile
auxiliary format
"/usr/oracle/standby/app/oradata/orcl_standby/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /usr/oracle/standby/app/oradata/orcl_standby/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 30-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/usr/oracle/app/oradata/orcl/system01.dbf
output file name=/usr/oracle/standby/app/oradata/orcl_standby/system01.dbf tag=TAG20151230T165625
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/usr/oracle/app/oradata/orcl/sysaux01.dbf
output file name=/usr/oracle/standby/app/oradata/orcl_standby/sysaux01.dbf tag=TAG20151230T165625
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/usr/oracle/app/oradata/orcl/undotbs01.dbf
output file name=/usr/oracle/standby/app/oradata/orcl_standby/undotbs01.dbf tag=TAG20151230T165625
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=/usr/oracle/app/oradata/orcl/users01.dbf
output file name=/usr/oracle/standby/app/oradata/orcl_standby/users01.dbf tag=TAG20151230T165625
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-DEC-15
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_30/o1_mf_1_8_c8772wkh_.arc" auxiliary format
"/usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 30-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=8 RECID=2 STAMP=899830620
output file name=/usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_0lqq4kqs_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 30-DEC-15
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_0lqq4kqs_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_0lqq4kqs_.arc
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=899830621 file name=/usr/oracle/standby/app/oradata/orcl_standby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=899830621 file name=/usr/oracle/standby/app/oradata/orcl_standby/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=899830621 file name=/usr/oracle/standby/app/oradata/orcl_standby/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=899830621 file name=/usr/oracle/standby/app/oradata/orcl_standby/users01.dbf
contents of Memory Script:
{
set until scn 1027736;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 30-DEC-15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_0lqq4kqs_.arc
archived log file name=/usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_8_0lqq4kqs_.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-DEC-15
Finished Duplicate Db at 30-DEC-15
8. Enable log shipping
Primary>show parameter LOG_ARCHIVE_DEST_STATE_2
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_state_2 string DEFER
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
Primary>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
8. Verification results
Query primary database role
Primary>select database_role from v$database; DATABASE_ROLE -------------------------------- PRIMARY
Query standby database role
StandBy>select database_role from v$database;
DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY
StandBy>
Insert a piece of data at the primary end
Primary> INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('12', 'OPERATIONS', 'OPERATIONS');
row created.
Primary>commit;
Commit complete.
Primary>alter system switch logfile;
System altered.
standby side: start redo application, pause redo application to open database, query whether data has been synchronized
StandBy>alter database recover managed standby database disconnect from session;
Database altered.
StandBy>alter database recover managed standby database cancel;
Database altered.
StandBy>alter database open;
Database altered.
StandBy>select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ ONLY
StandBy>
When to witness miracles:
StandBy>select * from scott.dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
OPERATIONS OPERATIONS
9. Enable real-time application redo
Add redo log
First query the size and location of the current redo log
Primary>col group# for 9
Primary>col status for a10
Primary>col type for a10
Primary>col member for a50;
Primary>col is_rec for a10
Primary>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC
------ ---------- ---------- -------------------------------------------------- ------
ONLINE /usr/oracle/app/oradata/orcl/redo03.log NO
ONLINE /usr/oracle/app/oradata/orcl/redo02.log NO
ONLINE /usr/oracle/app/oradata/orcl/redo01.log NO
Primary>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ---------- ---------- ---------- ------ ---------- ------------- ------------ ------------ ------------
1 10 52428800 512 1 NO CURRENT 1029234 23-DEC-15 2.8147E+14
1 8 52428800 512 1 YES INACTIVE 1028340 23-DEC-15 1028421 23-DEC-15
1 9 52428800 512 1 YES INACTIVE 1028421 23-DEC-15 1029234 23-DEC-15
The file size of the standby redo log is the same as that of the primary database online redo log.
The standby redo log would ideally have more arrays than the main library online redo log.
There are currently three groups of primary libraries, one member for each group and a size of 50M. We have added four groups to the reserve, one member for each group and a size of 50M.
StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4('/usr/oracle/standby/app/oradata/orcl_standby/stbyredolog4a.log') SIZE 50 M;
Database altered.
StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/usr/oracle/standby/app/oradata/orcl_standby/stbyredolog5a.log') SIZE 50 M;
Database altered.
StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/usr/oracle/standby/app/oradata/orcl_standby/stbyredolog6a.log') SIZE 50 M;
Database altered.
StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/usr/oracle/standby/app/oradata/orcl_standby/stbyredolog7a.log') SIZE 50 M;
Database altered.
Query the current log of the repository
Standby>select group#, type, member from v$logfile;
GROUP# TYPE MEMBER
----------------------------------------------------------------------------------------------------------------------------- 3 ONLINE /usr/oracle/standby/app/oradata/orcl_standby/redo03.log
ONLINE /usr/oracle/standby/app/oradata/orcl_standby/redo02.log
ONLINE /usr/oracle/standby/app/oradata/orcl_standby/redo01.log
STANDBY /usr/oracle/standby/app/oradata/orcl_standby/stbyredolog4a.log
STANDBY /usr/oracle/standby/app/oradata/orcl_standby/stbyredolog5a.log
STANDBY /usr/oracle/standby/app/oradata/orcl_standby/stbyredolog6a.log
STANDBY /usr/oracle/standby/app/oradata/orcl_standby/stbyredolog7a.log
For switchover, we also added four sets of standby redo log s to the main library
Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4('/usr/oracle/app/oradata/orcl/stbyredolog4a.log') SIZE 50 M;
Database altered.
Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/usr/oracle/app/oradata/orcl/stbyredolog5a.log') SIZE 50 M;
Database altered.
Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/usr/oracle/app/oradata/orcl/stbyredolog6a.log') SIZE 50 M;
Database altered.
Primary>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/usr/oracle/app/oradata/orcl/stbyredolog7a.log') SIZE 50 M;
Database altered.
View the results:
Primary>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC
------ ---------- ---------- -------------------------------------------------- ------
ONLINE /usr/oracle/app/oradata/orcl/redo03.log NO
ONLINE /usr/oracle/app/oradata/orcl/redo02.log NO
ONLINE /usr/oracle/app/oradata/orcl/redo01.log NO
STANDBY /usr/oracle/app/oradata/orcl/stbyredolog4a.log NO
STANDBY /usr/oracle/app/oradata/orcl/stbyredolog5a.log NO
STANDBY /usr/oracle/app/oradata/orcl/stbyredolog6a.log NO
STANDBY /usr/oracle/app/oradata/orcl/stbyredolog7a.log NO
rows selected.
Change log_archive_dest_2 at the primary and standby ends,
Change log_archive_dest_2 on the Primary side
Primary>alter system set log_archive_dest_2='SERVICE=tns_standby LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_standby'; System altered.
Change log_archive_dest_2 on the standby side
Standby>alter system set log_archive_dest_2='SERVICE=tns_primary LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'; System altered.
Enable real-time StandBy-side redo applications
StandBy>alter database recover managed standby database using current logfile disconnect from session;
Verification:
First insert a piece of data at the Primay side:
Primary>select * from scott.dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
OPERATIONS OPERATIONS
Primary>INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('13', 'OPERATIONS', 'OPERATIONS');
row created.
Primary>commit;
Commit complete.
standby side view
StandBy>select * from scott.dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
OPERATIONS OPERATIONS
OPERATIONS OPERATIONS
rows selected.
10,switchover
Standby suspends redo application
StandBy>alter database recover managed standby database cancel; Database altered.
Query whether the primary library supports switchover operations
Primary> select switchover_status from v$database; SWITCHOVER_STATUS ---------------------------------------- TO STANDBY
Query whether the backup supports switchover operation
StandBy> select switchover_status from v$database; SWITCHOVER_STATUS ---------------------------------------- NOT ALLOWED
not allowed because the primary library has not been switched to standby
switchover, primary to physical standby, after switching to view database roles, open mode,
Primary>alter database commit to switchover to physical standby;
Database altered.
Primary>shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Primary>startup
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 1795164312 bytes
Database Buffers 1476395008 bytes
Redo Buffers 16568320 bytes
Database mounted.
Database opened.
Primary>select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ ONLY
Primary>select database_role from v$database;
DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY
Primary>
If error ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected, close the active connection
Primary>alter database commit to switchover to physical standby; alter database commit to switchover to physical standby * ERROR at line 1: ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
Switch physical standby to primary
SQL> select switchover_status from v$database; SWITCHOVER_STATUS ---------------------------------------- SWITCHOVER PENDING
If SWITCHOVER PENDING, the current standby database has not started the redo application. Re-execute the following command
alter database recover managed standby database using current logfile disconnect from session;
If session activity indicates that a user is currently connected to a StandBy database, it is recommended that you disconnect these connections or close the database first and start up in the mount state (the state above is when you connect to the primary library with sqlplus and start up to the mount state is normal).
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 1795164312 bytes
Database Buffers 1476395008 bytes
Redo Buffers 16568320 bytes
Database mounted.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
----------------------------------------
TO PRIMARY
switch
StandBy>select switchover_status from v$database;
SWITCHOVER_STATUS
----------------------------------------
TO PRIMARY
StandBy> alter database commit to switchover to primary;
Database altered.
StandBy>alter database open;
Database altered.
StandBy>select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ WRITE
StandBy>select database_role from v$database;
DATABASE_ROLE
--------------------------------
PRIMARY
StandBy>
The test is the same as above (insert delete), here is only a simple test
Now Primay Switch Log
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
SQL> alter system switch logfile;
System altered.
Query in current standby
SYS@orcl>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
15
Common Queries
1. Query the active state of the process
select process,client_process,sequence#,status from v$managed_standby;
2. Query redo application progress
select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from v$archive_dest_status where status ='VALID';
3. Query archive file path and create information
select name,creator,sequence#,applied,completion_time from v$archived_log;
4. Query archive history
select first_time,first_change#,next_change#,sequence# from v$log_history; select thread#,sequence#,applied from v$archived_log;
5. Refer to the basic information of the database
select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;
6. Query the activity status of redo applications and redo transport services
select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;
7. Check the application mode
StandBy>select recovery_mode from v$archive_dest_status where dest_id=2; RECOVERY_MODE ---------------------------------------------- IDLE
Value
idle
managed:
managed real_time_apply:
8. Execution on the main library is only to see if the log archive destination is available. If the remote archive directory is not available, error will display an error message
Primary>select dest_name,status,error from v$archive_dest;
DEST_NAME STATUS ERROR
------------------------- ---------- ----------------------------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 VALID
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE
LOG_ARCHIVE_DEST_11 INACTIVE
LOG_ARCHIVE_DEST_12 INACTIVE
LOG_ARCHIVE_DEST_13 INACTIVE
LOG_ARCHIVE_DEST_14 INACTIVE
LOG_ARCHIVE_DEST_15 INACTIVE
LOG_ARCHIVE_DEST_16 INACTIVE
LOG_ARCHIVE_DEST_17 INACTIVE
LOG_ARCHIVE_DEST_18 INACTIVE
LOG_ARCHIVE_DEST_19 INACTIVE
LOG_ARCHIVE_DEST_20 INACTIVE
LOG_ARCHIVE_DEST_21 INACTIVE
LOG_ARCHIVE_DEST_22 INACTIVE
LOG_ARCHIVE_DEST_23 INACTIVE
LOG_ARCHIVE_DEST_24 INACTIVE
LOG_ARCHIVE_DEST_25 INACTIVE
LOG_ARCHIVE_DEST_26 INACTIVE
LOG_ARCHIVE_DEST_27 INACTIVE
LOG_ARCHIVE_DEST_28 INACTIVE
LOG_ARCHIVE_DEST_29 INACTIVE
LOG_ARCHIVE_DEST_30 INACTIVE
LOG_ARCHIVE_DEST_31 INACTIVE
STANDBY_ARCHIVE_DEST VALID
rows selected.
9. Query the application of archive logs.Query results are different on the primary repository, where there are two records for each archive file.
View Code
10. Query dataguard status information
Standby>select message_num,message from v$dataguard_status;
MESSAGE_NUM MESSAGE
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
RFS[1]: Assigned to RFS process 5881
RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[2]: Assigned to RFS process 5892
RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[3]: Assigned to RFS process 5897
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 9101
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_25_c87mn8sw_.arc
Media Recovery Waiting for thread 1 sequence 26
RFS[4]: Assigned to RFS process 5906
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[5]: Assigned to RFS process 5911
RFS[5]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[6]: Assigned to RFS process 5918
RFS[6]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[7]: Assigned to RFS process 5925
RFS[7]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[8]: Assigned to RFS process 5932
RFS[8]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[9]: Assigned to RFS process 5938
RFS[9]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[10]: Assigned to RFS process 5945
RFS[10]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[11]: Assigned to RFS process 5952
RFS[11]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[12]: Assigned to RFS process 5959
RFS[12]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[13]: Assigned to RFS process 5970
RFS[13]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[14]: Assigned to RFS process 5976
RFS[14]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[15]: Assigned to RFS process 5984
RFS[15]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[16]: Assigned to RFS process 5991
RFS[16]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[17]: Assigned to RFS process 6006
RFS[17]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[18]: Assigned to RFS process 6018
RFS[18]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[19]: Assigned to RFS process 6024
RFS[19]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[20]: Assigned to RFS process 6035
RFS[20]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[21]: Assigned to RFS process 6047
RFS[21]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[22]: Assigned to RFS process 6059
RFS[22]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[23]: Assigned to RFS process 6070
RFS[23]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[24]: Assigned to RFS process 6076
RFS[24]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[25]: Assigned to RFS process 6082
RFS[25]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[26]: Assigned to RFS process 6090
RFS[26]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[27]: Assigned to RFS process 6097
RFS[27]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[28]: Assigned to RFS process 6104
RFS[28]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[29]: Assigned to RFS process 6110
RFS[29]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[30]: Assigned to RFS process 6116
RFS[30]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[31]: Assigned to RFS process 6123
RFS[31]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[32]: Assigned to RFS process 6133
RFS[32]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[33]: Assigned to RFS process 6144
RFS[33]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[34]: Assigned to RFS process 6161
RFS[34]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[35]: Assigned to RFS process 6167
RFS[35]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[36]: Assigned to RFS process 6174
RFS[36]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[37]: Assigned to RFS process 6181
RFS[37]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[38]: Assigned to RFS process 6188
RFS[38]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[39]: Assigned to RFS process 6201
RFS[39]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[40]: Assigned to RFS process 6207
RFS[40]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[41]: Assigned to RFS process 6214
RFS[41]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[42]: Assigned to RFS process 6221
RFS[42]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[43]: Assigned to RFS process 6232
RFS[43]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[44]: Assigned to RFS process 6238
RFS[44]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[45]: Assigned to RFS process 6244
RFS[45]: Identified database type as 'physical standby': Client is ARCH pid 9099
MRP0: Background Media Recovery cancelled with status 16037
MRP0: Background Media Recovery process shutdown
Managed Standby Recovery Canceled
RFS[46]: Assigned to RFS process 6253
RFS[46]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[47]: Assigned to RFS process 6263
RFS[47]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[48]: Assigned to RFS process 6270
RFS[48]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[49]: Assigned to RFS process 6276
RFS[49]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[50]: Assigned to RFS process 6284
RFS[50]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[51]: Assigned to RFS process 6295
RFS[51]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[52]: Assigned to RFS process 6301
RFS[52]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[53]: Assigned to RFS process 6312
RFS[53]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[54]: Assigned to RFS process 6320
RFS[54]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[55]: Assigned to RFS process 6325
RFS[55]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[56]: Assigned to RFS process 6332
RFS[56]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 10167
Primary database is in MAXIMUM PERFORMANCE mode
ARC0: Beginning to archive thread 1 sequence 26 (1039145-1040585)
RFS[57]: Assigned to RFS process 6334
RFS[57]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 10167
ARC0: Completed archiving thread 1 sequence 26 (0-0)
Primary database is in MAXIMUM PERFORMANCE mode
RFS[58]: Assigned to RFS process 6339
RFS[58]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[59]: Assigned to RFS process 6356
RFS[59]: Identified database type as 'physical standby': Client is ARCH pid 9099
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery starting Real Time Apply
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_26_c87po9r6_.arc
Media Recovery Waiting for thread 1 sequence 27 (in transit)
RFS[60]: Assigned to RFS process 6364
RFS[60]: Identified database type as 'physical standby': Client is ARCH pid 9099
MRP0: Background Media Recovery cancelled with status 16037
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery process shutdown
Managed Standby Recovery Canceled
RFS[61]: Assigned to RFS process 6373
RFS[61]: Identified database type as 'physical standby': Client is ARCH pid 9099
ARC1: Beginning to archive thread 1 sequence 27 (1040585-1040839)
ARC1: Completed archiving thread 1 sequence 27 (0-0)
RFS[62]: Assigned to RFS process 6378
RFS[62]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[63]: Assigned to RFS process 6382
RFS[63]: Identified database type as 'physical standby': Client is Foreground pid 9102
ARC3: Beginning to archive thread 1 sequence 28 (1040839-1040863)
ARC3: Completed archiving thread 1 sequence 28 (0-0)
RFS[64]: Assigned to RFS process 6386
RFS[64]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[65]: Assigned to RFS process 6393
RFS[65]: Identified database type as 'physical standby': Client is ARCH pid 9095
ARC0: Beginning to archive thread 1 sequence 29 (1040863-1040945)
ARC0: Completed archiving thread 1 sequence 29 (0-0)
RFS[66]: Assigned to RFS process 6395
RFS[66]: Identified database type as 'physical standby': Client is ARCH pid 9099
RFS[67]: Assigned to RFS process 6400
RFS[67]: Identified database type as 'physical standby': Client is Foreground pid 9102
ARC1: Beginning to archive thread 1 sequence 30 (1040945-1040959)
ARC1: Completed archiving thread 1 sequence 30 (0-0)
RFS[68]: Assigned to RFS process 6404
RFS[68]: Identified database type as 'physical standby': Client is Foreground pid 9102
RFS[69]: Assigned to RFS process 6406
RFS[69]: Identified database type as 'physical standby': Client is Foreground pid 9102
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery starting Real Time Apply
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_27_c87pwj5t_.arc
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_28_c87px4hz_.arc
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_29_c87pzfbv_.arc
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_30_c87pzr9s_.arc
Media Recovery Log /usr/oracle/standby/app/flash_recovery_area/ORCL_STANDBY/archivelog/2015_12_30/o1_mf_1_31_c87pzsk7_.arc
Resetting standby activation ID 1427590416 (0x55174d10)
MRP0: Media Recovery Complete: End-Of-REDO
MRP0: Background Media Recovery process shutdown
ALTER DATABASE SWITCHOVER TO PRIMARY
ARC0: Becoming the 'no SRL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1: Becoming the 'no SRL' ARCH
rows selected.
11. Check for log missing
Standby>select * from v$archive_gap; no rows selected
Start, stop and maintenance of the main reserve
Dataguard shutdown (shut down the main library before the standby library)
Dataguard Open (Ready Library then Main Library)
Failover Switching Method
(1) Judge that the master database does have a serious hardware failure or other reasons that cause the master database to fail to start.
(2) Check for archive redo log gaps on the physical standby database
Standby>SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; no rows selected
Eliminate archive redo log gaps if any
Archve redo logs that are not transferred to the physical standby database are transferred to the physical standby database from the primary database or other backup locations and registered in the controlfile of the physical standby database.
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE'archive redo log file name';
Repeat steps 2,3 until no records exist for the V$ARCHIVE_GAP view.
(3) See if the archive file is complete
The main library backup library executes the following statements to get the largest sequence number of files archived by each thread. If the maximum sequence number is different, the archive file corresponding to the extra sequence number must be copied to the standby server to be converted (it is also possible that the primary library can no longer be opened)
SQL> select distinct thread#, max(sequence#) over (partition by thread#) from v$archived_log;
THREAD# MAX(SEQUENCE#)OVER(PARTITIONBYTHREAD#)
---------- --------------------------------------
84
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
84
(4) Initiate failover operation on Physical Standby Database
SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
(5) Convert physical standby databases into primary roles
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
(6) Restart the new primary database
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;
(7) Make a full backup of the new primary database.