Duplicate copies the database and creates a physical standby (pfile version)

Time:2020-9-27

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 the operation

Primary database

set SQLPROMPT Primary>

Standby database

set SQLPROMPT StandBy>

1. The primary side sets the archiving mode + forced log

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>

Open mandatory log

Primary>select force_logging from v$database;

				FOR
				---
				NO

					Primary>alter database force logging;

	Database altered.

2. Create related directory on standby side

In order to keep the same structure as the primary database, we need to establish the same directory in the standby database. First, query the relevant directory 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

[[email protected] ~]$ mkdir -p /usr/oracle/app/admin/orcl/adump
				[[email protected] ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace
					[[email protected] ~]$ mkdir -p /usr/oracle/app/oradata/orcl
						[[email protected] ~]$ mkdir -p /usr/oracle/app/flash_recovery_area/orcl
							[[email protected] ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/
	[[email protected] ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace

3. Create secondary instance key file

Method 1: directly copy the key file from the primary database

[[email protected] dbs]$ scp orapworcl 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbs
			[email protected]'s password: 
			orapworcl                                     100% 1536     1.5KB/s   00:00    
	[[email protected] 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

On the primary side, pfile is produced according to SPFILE

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 the 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 is the network service name of the primary server
	fal_server                 string            tns_standby

Copy generated initorcl.ora $Oracle to standby_ Home / DBS directory

[[email protected] dbs]$ pwd
				/usr/oracle/app/product/11.2.0/dbhome_1/dbs
					[[email protected] 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.
						[email protected]'s password: 
							initorcl.ora                                  100% 1291     1.3KB/s   00:00    
	[[email protected] dbs]$

Modify the initorcl.ora Document, which reads 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 connection to the primary can be opened 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 the 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 monitoring (configuredStatic 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 static monitoring registration is required after duplicate
					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 side listening

[[email protected] 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 side and standby side 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 )
															)
	)

***********************************************************************************************

SERVICE_ The value of name refers to the output of lsnrctl to ensure that the service can be connected with sqlplus or navicact_ NAME

Because of fal_ The server will use the service name. If the configuration is not correct, the archive log cannot be sent normally

***********************************************************************************************

Test both primary and standby (for role switching)

[[email protected] 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)
										[[email protected] 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

[[email protected] admin]$ rman target sys/[email protected]_primary auxiliary sys/[email protected]_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

The primary side switches the log and writes 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 consumer ා the current primary is not 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

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>

The primary side inserts a piece of data

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 the redo application; pause the redo application; open the database; query whether the data has been synchronized

Standby > alter database recover managed standby database disconnect from session; redo is applied on the standby side

			Database altered.

			Standby > alter database recover managed standby database cancel; ා cancel the application of redo

			Database altered.

			Standby > alter database open; ා open the database

			Database altered.

			StandBy>select open_mode from v$database;  
			#Standby is read-only

			OPEN_MODE
			----------------------------------------
			READ ONLY

	StandBy>

A moment 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

At present, there are three groups, one member in each group, and the size is 50m. We add four groups, one member in each group, and the size is 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 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.

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 primary and standby side logs_ archive_ dest_2

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';
									#It used to be 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 of standby side_ archive_ dest_2

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 side redo application

Standby > alter database recover managed standby database using current logfile disconnect from session; ා because it is changed to LGWR for delivery, real-time is required

verification:

First, insert a piece of data in 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

The standby database suspends the 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 is because the master database has not been switched to standby

Switch the primary to physical standby. After the switch, view the database role, open mode, and

Primary > alter database commit to switch 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 switch 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>

Common query

 

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. Query archive file path and creation 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 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: