1 the setting environment is as follows:
Primary database
| IP | 172.17.22.16 |
| SID | orcl |
Standby database
| IP | 172.17.22.17 |
| SID | orcl |
Set prompts to distinguish the location of operations
primary database
set SQLPROMPT Primary>
standby database
set SQLPROMPT StandBy>
1. Set Archive Mode + force log on Primary side
Make sure 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>
Enable force log
Primary>select force_logging from v$database;
FOR
---
NO
Primary>alter database force logging;
Database altered.
2. Create relevant directory on standby side
In order to save the same structure as the Primary database, we need to establish the same directory in the Standby database. First, query the relevant 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 same directory on the standby database server
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/admin/orcl/adump
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/oradata/orcl
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/flash_recovery_area/orcl
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace
3. Create secondary instance key file
Method 1: copy the key file directly from the Primary database
[oracle@oracledb dbs]$ scp orapworcl 172.17.22.17:/usr/oracle/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]$
Method 2: orapwd generation
orapwd FILE=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl password=wangshengzhuang entries=30;
4. Generate pfile on standby side
Produce pfile according to spfile on Primary side
Primary>create pfile from spfile; File created.
Modify the pfile on the Primary side as follows
orcl.__db_cache_size=1476395008
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/usr/oracle/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=1325400064
orcl.__sga_target=1979711488
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=436207616
orcl.__streams_pool_size=0
*.audit_file_dest='/usr/oracle/app/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/usr/oracle/app/oradata/orcl/control01.ctl','/usr/oracle/app/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/usr/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/usr/oracle/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=db_primary
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(db_primary,db_standby)'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_standby ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby' #arch means to synchronize archived logs
*.LOG_ARCHIVE_DEST_STATE_2=DEFER #Indicates that the archive destination is temporarily unavailable
*.FAL_SERVER=tns_standby
*.FAL_CLIENT=tns_primary
*.STANDBY_FILE_MANAGEMENT=AUTO
Primary side restart
Primary>create spfile from pfile;
File created.
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>show parameter fal
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
fal_client string tns_primary #TNS? Primary network service name of the primary server
fal_server string tns_standby
Copy the generated initorcl.ora to the $ORACLE_HOME/dbs directory of standby
[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/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]$
Modify the initorcl.ora file on the standbyd side as follows
orcl.__db_cache_size=1476395008
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/usr/oracle/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=1325400064
orcl.__sga_target=1979711488
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=436207616
orcl.__streams_pool_size=0
*.audit_file_dest='/usr/oracle/app/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/usr/oracle/app/oradata/orcl/control01.ctl','/usr/oracle/app/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/usr/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/usr/oracle/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=db_standby
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(db_primary,db_standby)'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_primary ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_primary' #ARCH is the archived log
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE #The standby connected to the primary can be enabled because the primary synchronization log to the standby is not enabled at this time
*.FAL_SERVER=tns_primary
*.FAL_CLIENT=tns_standby
*.STANDBY_FILE_MANAGEMENT=AUTO
Create spfile of Standby database through copied pfile
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 listening service
Primary monitoring
# 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 (configured with static listening service name GLOBAL_DBNAME = StandBy)
# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
#The red font is static monitoring, and the duplicate needs to use static monitoring registration
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = StandBy)
(ORACLE_HOME =/usr/oracle/app/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
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 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/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/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /usr/oracle/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/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /usr/oracle/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 end and StandBy end should be configured as follows:
tns_primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db_primary)
)
)
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 service name can be connected with sqlplus tool or navicact tool
Because the Falu server will use the service name, if the configuration is not correct, the archive log cannot be sent normally
***********************************************************************************************
Test on both primary and standby sides (for role switching)
[oracle@oracledb admin]$ tnsping tns_primary
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:44
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@oracledb admin]$ tnsping tns_standby
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:49
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
7,duplicate standby
rman connects two databases
[oracle@oracledb admin]$ rman target sys/primary_password@tns_primary auxiliary sys/standby_password@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 the logs on the primary side, rewrite and execute rman duplicate
View Code
8. Enable log shipping
Primary>show parameter LOG_ARCHIVE_DEST_STATE_2
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_state_2 string DEFER #The current master has not been enabled
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
Querying primary database roles
Primary>select database_role from v$database; DATABASE_ROLE -------------------------------- PRIMARY
Querying the 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 end: start redo application; pause redo application; open database; query whether data has been synchronized
StandBy>alter database recover managed standby database disconnect from session; #standby terminalapplication redo
Database altered.
StandBy>alter database recover managed standby database cancel; #Cancel applying redo
Database altered.
StandBy>alter database open; #Open database
Database altered.
StandBy>select open_mode from v$database; #standby is read-only
OPEN_MODE
----------------------------------------
READ ONLY
StandBy>
A moment of wonder
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
At present, there are three groups, one member in each group, with a size of 50M. We add four groups, one member in each group, with a size of 50M
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 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.
Similarly, do the same operation on the standby side
StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4('/usr/oracle/app/oradata/orcl/stbyredolog4a.log') SIZE 50 M;
Database altered.
StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/usr/oracle/app/oradata/orcl/stbyredolog5a.log') SIZE 50 M;
Database altered.
StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/usr/oracle/app/oradata/orcl/stbyredolog6a.log') SIZE 50 M;
Database altered.
StandBy>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/usr/oracle/app/oradata/orcl/stbyredolog7a.log') SIZE 50 M;
Database altered.
StandBy>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC
------ ---------- ---------- ---------------------------------------------------------------------------------------------------- ------
ONLINE /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_3_c7np1p97_.log YES
ONLINE /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_2_c7np1osm_.log YES
ONLINE /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_1_c7np1od8_.log YES
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
Change the log file dest on the primary and standby sides
Primary>show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
log_archive_dest_2 string SERVICE=tns_standby ARCH VALID
_FOR=(ONLINE_LOGFILES,PRIMARY_
ROLE) DB_UNIQUE_NAME=db_standb
y
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
Primary>alter system set log_archive_dest_2='SERVICE=tns_standby LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby';
#Previously, this was ARCH,
System altered.
Primary>show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
log_archive_dest_2 string SERVICE=tns_standby LGWR VALID
_FOR=(ONLINE_LOGFILES,PRIMARY_
ROLE) DB_UNIQUE_NAME=db_standb
y
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
Primary>
Change the log archive dest 2 on the standby side
StandBy>alter system set log_archive_dest_2='SERVICE=tns_primary LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_primary';
Enable real-time StandBy redo application
StandBy>alter database recover managed standby database using current logfile disconnect from session; #Because the change is passed by LGWR, real time is required
Verification:
First, insert a piece of data on the primary 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.
Stand by 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 database pauses redo application
StandBy>alter database recover managed standby database cancel; #Cancel redo application Database altered.
Query whether the main database supports switchover operation
Primary> select switchover_status from v$database; SWITCHOVER_STATUS ---------------------------------------- TO STANDBY
Query whether the standby database supports switchover operation
StandBy> select switchover_status from v$database; SWITCHOVER_STATUS ---------------------------------------- NOT ALLOWED
not allowed because the main database has not been switched to standby
Switch over, primary switch to physical standby, check the database role, open mode
Primary>alter database commit to switchover to physical standby; #Switch the current primary role 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>
Switch physical standby to primary
StandBy>select switchover_status from v$database;
SWITCHOVER_STATUS
----------------------------------------
TO PRIMARY
StandBy> alter database commit to switchover to primary; #standby 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>
Frequently used queries
1. Query the activity status of a 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. Querying the path and creation information of archive files
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. See basic information of 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 application and redo transport service
select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;
7. Check 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: