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

In this issue, the editor will bring you about how to carry out the daily management of Dataguard. 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.

Daily management of dataguard

1.fail over

2.switchover

3. Change the protection mode

4. Common commands and views

Fail over:

1. If you do fail over in dataguard, then the original standby will become primary by executing the following command

A

First check the v$archive_gap, then manually copy the archive_log that is, and then execute the following command:

Sql > alter database register physical standby 'logfile_patch'

B

Sql > alter database recover managed standby database cancel

C

Sql > alter database recover managed standby database finish

Or

Sql > alter database recover managed standby database finish force

D

Sql > alter dattabase commit to switchover to primary

There are two situations where an old primary database needs to be converted to standby:

a. It is relatively simple to open a database with the flashback function, and now execute this command on the new primary database

Select standby_became_primary_scn from v$database

Then execute on the database of the new standby

Flashback database to scn nnnnnn

Alter database convert to physical standby

Alter datbase recover managed standby database using current logfile disconnect from session

Do a log switch on the primary database to see if the new standby database log is normal.

Select applied from v$archived_log

b. If flashback is not enabled, recreate the dataguard

Switchover:

In switchover, it is best to make sure that no new session comes into the two libraries, so that switchover can be done quickly and exceptions can be avoided.

Several points that should be paid attention to in switchover:

1. There should be standby logfile in the primary library.

two。 Check v$archive_gap to check whether the logs have been archived to the standby library

Actual operation:

1. View switchover_status on primary

SQL > select database_role,switchover_status from v$database

DATABASE_ROLE SWITCHOVER_STATUS

--

PRIMARY TO STANDBY

Or

SQL > select database_role,switchover_status from v$database

DATABASE_ROLE SWITCHOVER_STATUS

--

PRIMARY SESSIONS ACTIVE

Status description:

TO STANDBY: no session connection, you can switchover directly

SESSIONS ACTIVE: there is also a session connection, so you cannot switchiver directly.

Switchover can be done directly if SWITCHOVER_STATUS is to standby, and the following steps can be performed if session active:

(1) check whether standby does not receive the latest log, and if not, manually switch in primary.

SQL > alter system switch logfile

The system has changed.

Then restore in standby

SQL > alter database recover managed standby database cancel

The database has changed.

(2) check whether there is an active sql session

SQL > SELECT SID, PROCESS, PROGRAM FROM V$SESSION

2 WHERE TYPE = 'USER'

3 AND SID (SELECT DISTINCT SID FROM V$MYSTAT)

No rows selected

If there is, then session kill

two. If all the above steps have been performed and the switchover_status of primary is still session active, add WITH SESSION SHUTDOWN after the normal switchover statement

(1) primary for switchover

SQL > alter database commit to switchover to physical standby WITH SESSION SHUTDOWN;-without active session and without WITH SESSION SHUTDOWN option, I also tested switchover successfully

The database has changed.

(2) restart the original primary database to mount

SQL > select open_mode from v$database

Select open_mode from v$database

*

An error occurred on line 1:

ORA-01507: no database mounted

SQL > shutdown immediate

ORA-01507: no database mounted

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 176163716 bytes

Database Buffers 427819008 bytes

Redo Buffers 7135232 bytes

The database is loaded.

SQL >

View the role of the switchover of the original primary

SQL > select database_role,switchover_status from v$database

DATABASE_ROLE SWITCHOVER_STATUS

--

PHYSICAL STANDBY TO PRIMARY

SQL >

(3) check the switchover_status of the original standby

SQL > select database_role,switchover_status from v$database

DATABASE_ROLE SWITCHOVER_STATUS

--

PHYSICAL STANDBY TO PRIMARY

(4) put it into recovery mode

SQL > alter database recover managed standby database using current logfile disconnect from session

The database has changed.

SQL >

three. Switch the original standby to primary

If the SWITCHOVER_STATUS of the above standby is to primary, then switch normally, otherwise check whether there is an active sql session, and if so, kill will be dropped.

Check the SWITCHOVER_STATUS again, and if it is still not to primary, add with session shutdown after the switch statement.

(1) switch standby to primary (standby needs to be placed in the mount state of receiving logs)

SQL > alter database commit to switchover to primary with session shutdown

The database has changed.

SQL > select open_mode from v$database

OPEN_MODE

-

MOUNTED

SQL > shutdown immediate

ORA-01109: the database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 234883972 bytes

Database Buffers 369098752 bytes

Redo Buffers 7135232 bytes

The database is loaded.

The database is already open.

SQL >

View the roles after viewing the original standby library switchover

SQL > select database_role from v$database

DATABASE_ROLE

-

PRIMARY

SQL >

Finally, we need to check the alert.log contents of the two libraries to see if they are normal.

Dataguard protection mode:

When changing the protected mode of dataguard, be sure to execute it on the primary library; the protected mode changed in primary will be applied to the standby library.

When changing the protection mode, you should also pay attention to modifying the log_archive_dest_n

SQL > alter system set log_archive_dest_2='SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg'

Actual operation:

On the primary library:

SQL > select open_mode,protection_mode,protection_level from v$database

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL > alter database set standby database to maximize protection

Alter database set standby database to maximize protection

*

An error occurred on line 1:

ORA-01126: the database must be loaded to this instance and not opened in any instance

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 239078276 bytes

Database Buffers 364904448 bytes

Redo Buffers 7135232 bytes

The database is loaded.

SQL > alter database set standby database to maximize protection

The database has changed.

SQL > alter database open

The database has changed.

SQL >

On the standby library:

Normally, when the primary changes the protection mode, the standby changes as well.

SQL > select open_mode, protection_mode,protection_level from v$database

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

MOUNTED MAXIMUM PROTECTION MAXIMUM PROTECTION

Be careful

When the protection mode changes the order:

Maximize protection-- > maximize availability-> maximize performance

When lowering the protection level of dataguard in the above order, the primary library is not required to be in the mount state

Primary can directly execute the protected mode change command in the open state.

Protection mode of primary:

SQL > select open_mode, protection_mode,protection_level from v$database

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

MOUNTED MAXIMUM PROTECTION MAXIMUM PROTECTION

Perform a protection mode change:

SQL > alter database set standby database to maximize protection

The database has changed.

SQL > alter database set standby database to maximize availability

The database has changed.

Before viewing the protection mode of primary:

SQL > select open_mode,protection_mode,protection_level from v$database

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL >

When the protection mode changes the order:

Maximize protection alter database set standby database to maximize protection

Alter database set standby database to maximize protection

*

An error occurred on line 1:

ORA-01126: the database must be loaded to this instance and not opened in any instance

Steps:

1. Primary library first shutdown immediate

2. Startup mount

3. Alter database set standby database to maximize protection

4. Alter database open

Dataguard common commands and views

View current protection mode

SQL > select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database

View log delivery methods

SQL > select dest_name,archiver from v$archive_dest

First stop the automatic recovery state of standby

SQL > alter database recover managed standby database finish

Add standby logfile

SQL > alter database add standby logfile group 4 ('/ oracle/product/10.2.0/db_1/oradata/sjh20g/redo04.log') size 50m

Change the protection mode

Alter database set standby database to maximize protection

Alter database set standby database to maximize availability

Alter database set standby database to maximize performancen

Change the mode of transmission

SQL > alter system set log_archive_dest_2='SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg'

Solution: open the flashback of the master / slave library:

Boot to mount

SQL > select FLASHBACK_ON from v$database

SQL > alter database flashback on

Cancel the automatic recovery model:

Alter database recover managed standby database cancel

Alter database recover managed standby database finish

Alter database recover managed standby database finish force

Switchover to primary

Alter dattabase commit to switchover to primary

Alter database commit to switchover to primary with session shutdown

Switchover to standby

Alter database commit to switchover to physical standby WITH SESSION SHUTDOWN

Flashback database to scn nnnnnn

Alter database convert to physical standby

Alter datbase recover managed standby database using current logfile disconnect from session

Alter database register physical standby 'logfile_patch'

Common views:

V$managed_standby

V$archive_dest

V$archive_dest_status

V$archive_gap

V$archiveg_log

V$dataguard_status

V$database

V$log_history

V$log

V$logfile

Note:

1. If you execute alter database clear unarchived logfile or alter database open resetlogs in the main library, the dataguard will be rebuilt.

2. Before working in the continuous recovery model, you need to ensure that all the previous archive logs have been applied to the standby database. Because in the case of a continuous recovery model, oracle does not apply the previous

Archive the logs, and only the subsequent archive logs will be applied.

3. When the archive log gap appears, you need to find out the corresponding archive log, and then copy these archive logs to the standby_archive_dest and the backup node.

Under the log_archive_dest directory. It should be noted that copy is also required under the log_archive_dest directory. And then ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE

4. New tables, tablespaces and datafile can all be applied to the standby database through logs, but a new temporary tablespace cannot be applied to the standby database with rename datafile.

5. You should look at the alert file of the standby library in real time so that you can clearly know the updates of the master and standby. This is also an important way to troubleshoot mistakes, remember!

Failover and switchover

Failover: offline the primary database and online the standby database. This operation is caused by system and software failures. Even if the redo log is applied on the standby database, the number may occur.

According to the missing phenomenon, unless the standby database is running in guaranteed protection mode. The instance must be restarted when the original primary database is reused. Other standby databases are also

You need to restart the instance.

Switchover: deliberately offline the primary database and online another standby database, which can switch to the standby database without synchronous operation. For example, Switchover can be used.

Complete the smooth upgrade of the system. Even if redo logs are not applied to the standby database, there is no data loss. The database does not need to restart the instance. This makes the main database almost immediately

Restore its functionality on the standby database, so it can be regularly maintained without interruption of operations. The difference between Failover and Switchover is that when Failover occurs, standby

After the database is switched to the primary database, it loses all the capabilities of the standby database, that is, it cannot return to the standby mode; while Switchover can, the standby database can be switched.

The primary database can also be switched from the primary database to the standby database.

The above is the editor for you to share how to carry out the daily management of Dataguard, 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.