Oracle Study Notes: Create physical standby

Basic concepts:

1. Protection Mode: maximum protection, maximum performance, and maximum availability. The default value is the maximum performance.

2. physical and logical backup machines:

3. Online, archived, and standby redo logs

4. Log transmission, log application, and role switching services

5. DG is a feature of the Enterprise Edition. You can operate and deploy DG through sqlplus, dbmgrl, and OEM.

 

Software and Hardware requirements:

1. Same as the hardware platform

2. Same as OS

3. Same as Oracle Enterprise Edition

 

Required settings for primary:

1. The database must be in archivelog mode.

2. The database must be in force logging mode.

3. Each member in the DG must have the pwdsid. ora file with the same password.

4. For the maximum protection and maximum available mode, you must set the standby database to create a standby redo log file of the same size as the primary database. Recommended: perform similar settings on primary

5. initialize the parameter primary:

Db_name = dbname -- all members in the DG must be the same

Db_unique_name = pridbuniquename -- Unique name of each member in DG

Log_archive_format = arc_sid _ % S _ % T _ % R -- redo the format of archived logs. The best format in DG is the same.

Log_archive_config = 'dg _ Config = (priservicename, stdservicename) '-- all DG members must be set.

Standby_file_management = auto -- standby corresponds to the processing of primary data files: Automatic Processing

Log_archive_dest_n = 'location = pathname' -- a local archive path is required.

Log_archive_dest_m = 'service = stdservicename db_unique_name = stddbuniquename [lgwr] [async] '-- there must be an active standby target

-- The following is what works when primary is converted to standby.

Fal_server = stdservicename -- when the role is converted, standby becomes primary

Fal_client = priservicename -- when the role is converted, the primary becomes standby

Db_file_name_convert = 'stdfmt', 'primarymt' -- naming rules for data files accepted by standby from primary

Log_file_name_convert = 'stdfmt', 'primarymt' -- the naming rules for log files accepted by standby from primary

 

 

6. initialize the standby parameter:

Db_name = dbname -- all members in the DG must be the same

Db_unique_name = stddbuniquename -- Unique name of each member in DG

Log_archive_format = arc_sid _ % S _ % T _ % R -- redo the format of archived logs. The best format in DG is the same.

Log_archive_config = 'dg _ Config = (priservicename, stdservicename) '-- all DG members must be set.

Standby_file_management = auto -- standby corresponds to the processing of primary data files: automatic processing. Set valid on standby

Log_archive_dest_n = 'location = pathname' -- a local archive path is required.

Log_archive_dest_m = 'service = priservicename db_unique_name = pridbuniquename [lgwr] [async] '-- there must be an active standby target

-- The following is what works when standby is converted to primary.

Fal_server = priservicename -- when the role is converted, standby becomes primary

Fal_client = stdservicename -- when the role is converted, the primary becomes standby

Db_file_name_convert = 'primarymt', 'stdfmt' -- the naming rules for data files accepted by standby from primary.Set valid on standby

Log_file_name_convert = 'primarymt', 'stdfmt' -- the naming rules of the log files accepted by standby from primary.Set valid on standby

 

 

• Log_archive_dest _ [1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10] =
  { Null_string | Location = path_name | service = SERVICE_NAME} -- path_name can be set to use_db_recovery_file_dest.
  [{ Mandatory | Optional }] -- UseMandatoryYes Force the RTS service process to switch logs only after the target standby archive log is successfully transferred
  [
  Reopen [= seconds] -- specifies the minimum number of seconds before
  The archiver processes (arcn) or the log writer process (lgwr) shold
  Try again to access a previusly failed destination. The default is 300
  Seconds.
  [Delay [= minutes] -- cannot set the delay attribute for a logical standby database destination, default is 30 minutes
  [Noregister] -- indicates that the Dest is not part of the DG.
  [Template = TEMPLATE]
  [Alternate = destination] -- format: log_archive_dest_n, mainly to solve the negative impact of mandatory
  [Dependency = destination] -- format: log_archive_dest_n, which enforces the priority of each target standby.
  [Max_failure = count] -- works with reopen to set the number of Primary attempts
  [ Arch | Lgwr] -- log transmission is usually performed using the arch process in the maximum performance mode. In other cases, the log is lgwr.
  [Sync | Async
  ] -- Network I/O is to be done synchronously or asynchronously when
  Archival is saved med using the log writer process (lgwr) and
  Network Server (lnsn) processes.
  [Affrem | Noaffrem ] -- Synchronous or asynchronous disk I/O
  [Net_timeout = seconds] -- the default value is 180 seconds.
  [Valid_for = (redo_log_type, database_role)] -- defines the constraints of log transmission.
  [{ Db_unique_name }] -- Speci distinct es a unique name for the database at this destination.
  [
  Verify] -- indicates if an archiver (arcn) process shocould scan and
  Verify the correctness of the contents of a completed archived redo log
  File, either local or remote, after successfully completing
  Archival operation. By default, archived redo log files are not
  Verified.
  }
• If you set mandatory, you must set alternate and reopen and max_failure.
• If you want to define the Dest sequence of each target, you need to set the dependency.
• Affrem is used to ensure that redo logs are written on each DEST to achieve lossless protection, and GWR sync is configured at the same time.

 

Procedure:

2. Section 3.2.1 create a backup copy of the primary database datafiles primary
4. Section 3.2.2 create a control file for the standby database primary -- an important step: it is used to generate control files for the standby database. By using alter database create standby controlfile as 'fullpathfilename ', it will generate a record to the RMAN metabase and be used by RMAN!
6. Section 3.2.3 prepare an initialization parameter file for the standby database primary
8. Section 3.2.4 copy files from the primary system to the standby system primary
10. Section 3.2.5 set up the environment to support the standby database standby
12. Section 3.2.6 start the physical standby database standby
14. Section 3.2.7 verify the physical standby database is refreshing properly standby

 

Basic management:

2. Set protection mode: on primary, the Mount status is alter database set standby database to maximize {Performance| Avialability | protection}
4. Add a standby logfile group and a member: Alter database add standby logfile [member] filespec ,...
6. Delete the standby logfile group and members: Alter database drop standby logfile [member] filespec ,...
8. Activate the standby recover management session and apply the redo log according to the initialization parameters: Alter database recover managed standby database [disconnect from session]
10. Activate standby's recover management session and apply redo logs immediately: Alter database recover managed standby database nodelay [disconnect from session]
12. Activate standby's real-time recover management session: Alter database recover managed standby database using current logfile [disconnect from session]
14. Activate the standby recover management session and restore it to a SCN: Alter database recover managed standby database until change N [disconnect from session]
16. Cancel the standby recover management session: Alter database recover managed standby database cancel [immediate | [Wait | Nowait]
18. Terminate the standy role function, end the recover management session, and convert it to primary: Alter database recover managed standby database.Finish[Force] [Wait | Nowait]
20. Convert physical to logical Standby: Alter database recover to logical standby {dbname} -- Note: You need to execute the dbms_lstdby.build process on the primary.
22. Activate standby as primary: Alter database active [physical | logical] standby database [finish apply -- can only be used for logical standby database]
24. Start logical redo log application: Alter database start logical standby apply
26. End the logical redo log application: Alter database {stop | abort} logical standby apply
28. Change primary away from DG to phyisical Standby: Alter database convert to physical standby

 

Common Errors and handling:

1. RMAN prompts that the required file cannot be found when duplicate target database for standby: Check whether the primary database is: Alter database create standby controlfile as filespec
2. When RMAN creates duplicate target database for standby, the following error occurs: Check whether the path related to the standby database is valid.
3. Redo file transmission failed: Check whether the primary and standby log_archive_config = 'dg _ Config = (pridbuniquename, pridbuniquename) ', db_unique_name, log_archive_dest_n, and quota parameters are correctly set. Especially log_archive_dest_n's arch | lgwr and other settings!
4. Standby cannot run in real-time mode. This mode requires that the standby database must have a standby redo log file.
5. Standby service unavailable: set static listening and restart listening