Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to understand the primary database standby. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Open physical standby in Read only/write mode

By opening the physical standby in read only or read write mode, you can transfer some queries, backups, and other operations to the standby database to share some of the primary pressure.

1)。 Standby database is in shutdown state

Just startup directly.

2)。 Standby database is in the state of redo application

First, cancel the redo application:

SQL > alter database recover managed standby database cancel; SQL > alter database open

3)。 Switching from open state to redo application does not require shutdown, you only need to enable redo application

SQL > alter database recover managed standby database disconnect from session

Since it cannot be applied when read-only is opened, although we can query it, the result of the query is out of sync with primary, which greatly reduces the possibility that the physical standby report service can share the pressure on the main database. For this, we have two solutions:

a. Use logic standby b. Use oracle 11G

2. Manage primary database events that affect standby

In some cases, some changes to the primary database are automatically propagated to the standby database through redo data, so no additional operations are required in the standby database, while in some cases, manual adjustments are required.

The following events are handled automatically by the redo transport service and redo applications without the intervention of dba:

ALTER DATABASE ENABLE | DISABLE THREAD statement

Modify tablespace state (for example, read-write to read-only, online to offline)

Create modify delete tablespace or data file (prerequisite: parameter STANDBY_FILE_MANAGEMENT is set to AUTO)

Manual intervention by dba is required for the following:

1. Add, modify, delete data files or tablespaces

Standby_file_management is set to manual

It is important to note, however, that if the data file is copied from another database, it must be copied to the standby database at the same time, regardless of the Standby_file_management parameter value, and be careful to modify the control file of the standby database.

two。 Rename data file

If the primary database renames one or more data files, the unmodified item is not automatically propagated to the standby database, regardless of whether the standby_file_management is auto or manual.

A. SQL > alter tablespace webtbs offline;-- primary database operation

b. Manual data file renaming (operating system)-- primary database operation

C. SQL > alter tablespace webtbs rename datafile

'webtbs01.dbf' to 'tbsweb01.dbf'

SQL > alter tablespace webtbs online

d. Pause redo application and shutdown-standby database operation

SQL > alter database recover managed standby database cancel

SQL > shutdown immediate

e. Rename the data file manually-- standby database operation

f. Restart standby and modify the data file path (data dictionary)-- standby database operation

SQL > startup mount

SQL > alter database rename file

'webtbs01.dbf' to 'tbsweb01.dbf'

g. Restart the redo application

SQL > alter database recover managed standby database disconnect from session

h. Switch logs-primary database operation

SQL > alter system switch logfile

3. Add or remove online redo logs

III. Recovery of standby, the primary database of open resetlogs

4. Monitoring primary/standby database

1. V$ view application related to recovery progress

a)。 Check the activity status of the process-- v$managed_standby

b)。 Confirm the progress of redo application-v$archive_dest_status

c)。 Check the path and creation information of the archive file-- v$archived_log

d)。 Query Archive History-v$log_history

e)。 Query v$archived_gap, the gap problem on the standby database, to display information about archived vacancies on the standby database. This view can be used to identify gaps in the current archive and hinder the recovery of the current recovery avatar.

1.1. View the activity status of the process

SQL > select process,status,thread#,sequence# from v$managed_standby order by 3

PROCESS STATUS THREAD# SEQUENCE#

--

RFS IDLE 0 0

RFS IDLE 0 0

RFS IDLE 0 0

RFS IDLE 0 0

ARCH CLOSING 1 13411

ARCH CLOSING 1 13412

RFS IDLE 1 13413

ARCH CLOSING 2 8849

ARCH CLOSING 2 4101

MRP0 APPLYING_LOG 2 8850

RFS IDLE 2 8850

Here, process is the process name, including ARCH, RFS, MRP0, etc. The corresponding English explanation is as follows:

Type of the process whose information is being reported:

RFS-Remote file server---- receiving process

MRP0-Detached recovery server process---- recovery process

MR (fg)-Foreground recovery session

ARCH-Archiver process

FGRD

LGWR

RFS (FAL)

RFS (NEXP)

LNS-Network server process

CLIENT_PROCESS corresponds to processes in Primary database, such as ARCH\ LGWR, etc.

SEQUENCE#: archival serial number

Current process status of STATUS

What is important is the status field, which indicates the current process status, which is explained in Chinese as follows:

ALLOCATED: preparing but not yet connecting to the main library

ATTACHED: connecting to the main library

CONNECTED: connected to the main library

IDLE: idle

ERROR: failed process that requires attention

RECEIVING: receiving archive log

OPENING: archive log processing

CLOSING: the archive log is finished and is in the process of ending

WRITING: the process is writing REDO data to an archive file

WAIT_FOR_LOG: waiting for new REDO archive data

WAIT_FOR_GAP: the portion of REDO data that has been interrupted and is waiting to be interrupted.

APPLYING_LOG: applying REDO archive data to standby database

1.2 View the progress of REDO application

SELECT DEST_NAME,ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#,DB_UNIQUE_NAME,STATUS FROM V$ARCHIVE_DEST_STATUS

-- WHERE STATUS='VALID'

DEST_NAME ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# DB_UNIQUE_NAME STATUS

LOG_ARCHIVE_DEST_1 0 0 0 cuuo VALID

LOG_ARCHIVE_DEST_2 0 0 0 cuug VALID

LOG_ARCHIVE_DEST_3 0 0 0 NONE INACTIVE

LOG_ARCHIVE_DEST_4 0 0 0 NONE INACTIVE

LOG_ARCHIVE_DEST_5 0 0 0 NONE INACTIVE

LOG_ARCHIVE_DEST_6 0 0 0 NONE INACTIVE

LOG_ARCHIVE_DEST_7 0 0 0 NONE INACTIVE

LOG_ARCHIVE_DEST_8 0 0 0 NONE INACTIVE

LOG_ARCHIVE_DEST_9 0 0 0 NONE INACTIVE

LOG_ARCHIVE_DEST_10 0 0 0 NONE INACTIVE

STANDBY_ARCHIVE_DEST 0 0 0 NONE VALID

11 rows selected.

1.3 View the path and creation information of the archive file

15:24:30 > SELECT NAME,CREATOR,THREAD#,SEQUENCE#,APPLIED,ARCHIVED,COMPLETION_TIME FROM V$ARCHIVED_LOG

NAME CREATOR THREAD# SEQUENCE# APP ARC COMPLETIO

/ u01/app/oracle/oradata/cuuo/arch2_91_787689201.dbf ARCH 1 91 YES YES 04-JUL-12

/ u01/app/oracle/oradata/cuuo/arch2_92_787689201.dbf LGWR 1 92 YES YES 04-JUL-12

/ u01/app/oracle/oradata/cuuo/arch2_93_787689201.dbf LGWR 1 93 YES YES 04-JUL-12

/ u01/app/oracle/oradata/cuuo/arch2_94_787689201.dbf LGWR 1 94 YES YES 04-JUL-12

1.4 View Archive History

SELECT FIRST_TIME,FIRST_CHANGE#,NEXT_CHANGE#,SEQUENCE# FROM V$LOG_HISTORY

1.5 View log files not received by the physical STANDBY database

-- obtained from the primary database

Select local.thread#,local.sequence# from

(select thread#,sequence# from v$archived_log where dest_id=1) local

Where local.sequence# not in

(select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#)

2 Monitoring log application service

2.1 query basic information of current data (V$DATABASE) database role, protection mode, protection level

SELECT DATABASE_ROLE,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS FROM V$DATABASE

Query the quick launch information after failover:

SELECT FS_FAILOVER_STATUS,FS_FAILOVER_CURRENT_TARGET,FS_FAILOVER_THRESHOLD,FS_FAILOVER_OBSERVER_PRESENT FROM V$DATABASE

2.2 query the activity status of REDO applications and REDO transport services

SELECT PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS FROM V$MANAGED_STANDBY

2.3 View REDO application mode (physical STANDBY database)

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2

RECOVERY_MODE

MANAGED

-- if a real-time application is enabled, the status shown here should be MANAGED REAL TIME APPLY

2.4 DATAGUARD event Monitoring

2.4.1 ALERT LOG

2.4.2 query V$DATAGUARD_STATUS view

16:03:17 > SELECT SEVERITY,DEST_ID,MESSAGE_NUM,ERROR_CODE,CALLOUT,MESSAGE FROM V$DATAGUARD_STATUS

SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL MESSAGE

-

Informational 0 1 0 NO ARC0: Archival started

Informational 0 2 0 NO ARC1: Archival started

Informational 0 30 NO ARC0: Becoming the'no FAL' ARCH

Informational 0 40 NO ARC0: Becoming the'no SRL' ARCH

Informational 0 5 0 NO ARC1: Becoming the heartbeat ARCH

Control 0 6 0 YES Media Recovery Start: Managed Standby Recovery

Informational 0 7 0 NO Managed Standby Recovery not using Real Time Apply

3. V $view applications related to log applications

a)。 Query the basic information of current data-- v$database

b)。 Check the application mode-- v$archive_dest_status

c)。 Data guard event-v$dataguard_status

Fifth, adjust the application frequency of physical standby log

To adjust the application frequency, to put it bluntly, it is to adjust the IO reading capacity.

5.1 set RECOVER parallelism

Redo log needs to be read during media recovery or REDO application. Serial recovery is the default.

You can add a PARALLEL clause to RECOVER to specify the degree of parallelism.

RECOVER STANDBY DATABASE PARALLEL 2

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 2 DISCONNECT FROM SESSION

5.2 accelerate the frequency of REDO applications

Modifying DB_BLOCK_CHECKING=FALSE can improve the application efficiency by 2 times, and the setting of FALSE is only suitable for physical STANDBY database, not suitable for primary database.

5.5Setting parallel_execution_message_size

If parallel recovery is turned on, appropriately increasing the parallel_execution_message_size size can also improve performance, but there are things to be aware of

Increasing this parameter consumes more memory.

5.5 optimize disk Imax O

The biggest performance bottleneck during recovery is the read and write of DISK_ASYNCH_IO O. In some cases, setting Imax to TRUE can reduce the number of parallel reads and speed up the overall recovery time.

The above is the editor for you to share how to understand the primary database standby, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.