Oracle 11g Dataguard Maintenance Considerations

1. Check if the primary and standby node archive logs are one to

(1). Archive logs for primary nodes

sql> archive log list;

Database Log Mode Archive mode

Automatic Archival Enabled

Archive destination/u01/oradata/tong/archive

Oldest online log sequence 111

Next log sequence to archive 113

Current log sequence 113 --primary and Standby nodes the number is as soon as possible.

Sql>

(2). Archive logs for standby nodes

sql> archive log list;

Database Log Mode Archive mode

Automatic Archival Enabled

Archive destination/u01/oradata/tong/archive

Oldest online log sequence 112

Next log sequence to archive 0

Current log sequence 113

Sql>

2. Check whether the primary and Stendby two nodes are archived and enabled for flashback

Sql> Select Log_mode, force_logging,flashback_on from V$database;

Log_mode for flashback_on

------------ --- ------------------

ARCHIVELOG YES NO

Sql>

3. Check if the primary and Stendby nodes are booting or in a healthy state

[Email protected] ~]$ lsnrctl status

Lsnrctl for linux:version 11.2.0.1.0-production on 11-nov-2016 14:39:14

Copyright (c) 1991, Oracle. All rights reserved.

Connecting to (Description= (address= (protocol=tcp) (host=192.168.3.55) (port=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version Tnslsnr for Linux:version 11.2.0.1.0-production

Start Date 11-nov-2016 14:39:04

Uptime 0 days 0 hr. 0 min. sec

Trace level off

Security on:local OS Authentication

SNMP OFF

Listener Parameter File/u01/product/11.2.0.1/db_1/network/admin/listener.ora

Listener Log File/u01/diag/tnslsnr/dg1/listener/alert/log.xml

Listening Endpoints Summary ...

(Description= (address= (protocol=tcp) (host=192.168.3.55) (port=1521)))

Services Summary ...

Service "Tong" has 1 instance (s).

Instance "Tong", Status UNKNOWN, have 1 handler (s) for the This service ...

The command completed successfully

[Email protected] ~]$

4. Check whether the primary and Stendby node archive directories are one to (log_archive_dest_1)

Sql> Show Parameter Log_archive_dest_1

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Log_archive_dest_1 string location=/u01/oradata/tong/archive Valid_for (All_logfiles,all_roles)

Db_unique_name=tong

Log_archive_dest_10 string

Log_archive_dest_11 string

5. Check disk usage space (sometimes the flashback recovery area cannot write to the file due to insufficient disk space)

[Email protected] ~]$ df-th

Filesystem Type Size used Avail use% mounted on

/dev/sda3 ext3 17G 11G 4.4G 72%/

/DEV/SDA1 ext3 510M 28M 456M 6%/boot

Tmpfs tmpfs 1.1G 520M 523M 50%/DEV/SHM

[Email protected] ~]$

6. Open the Dataguard database correctly (start the primary node database first, then turn on the log shipping mode on the standby node)

(1). Open the database on the primary node

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> Startup Mount

ORACLE instance started.

Total System Global area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 532678728 bytes

Database buffers 293601280 bytes

Redo buffers 2433024 bytes

Database mounted.

sql> ALTER DATABASE Archivelog;

Database altered.

sql> ALTER DATABASE open;

Database altered.

Sql> SELECT * from dual;

D

-

X

Sql>

(2). On the standby node

sql> startup Mount;

ORACLE instance started.

Total System Global area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 490735688 bytes

Database buffers 335544320 bytes

Redo buffers 2433024 bytes

Database mounted.

sql> ALTER DATABASE recover managed standby database disconnect from session;

Database altered.

Sql>

7. Properly shut down the database (Syandby, and then close the database in primary)

(1). Standby node

sql> ALTER DATABASE recover managed standby database cancel;

Database altered.

sql> shutdown Immediate

Ora-01109:database not open

Database dismounted.

ORACLE instance shut down.

Sql>

(2). Primary node

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql>

8. Modify the database in the Syandby node to read only mode

(1). The database of the primary node is the open state

(2). The database of the standby node is log shipping status

sql> Recover managed standby database cancel; --End log shipping status

Media recovery complete.

Sql> ALTER DATABASE open read only; --Modify the state of the database to read only

Database altered.

Sql> select * from T;

A

-------------------

1

2

3

4

6

7

8

7 rows selected.

Sql>

9. Modify the standby node's database from Read only to log shipping status

Sql> select status from V$instance;

STATUS

------------

OPEN

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> Startup Mount

ORACLE instance started.

Total System Global area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 490735688 bytes

Database buffers 335544320 bytes

Redo buffers 2433024 bytes

Database mounted.

sql> ALTER DATABASE recover managed standby database disconnect from session;

Database altered.

Sql> select status from V$instance;

STATUS

------------

Mounted

Sql>

10. View the log Shipping information

(1). primary node Current log information

Sql> select Sequence#,status from V$log;

sequence# STATUS

---------- ----------------

127 ACTIVE

Current

126 ACTIVE

Sql>

(2). The log information that the standby node is transmitting

Sql> SELECT PROCESS, STATUS, thread#, sequence#, block#, BLOCKS from V$managed_standby;

PROCESS STATUS thread# sequence# block# BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

ARCH CLOSING 1 127 1 6

ARCH CLOSING 1 125 1 271

ARCH CONNECTED 0 0 0 0

ARCH CLOSING 1 126 1 43

RFS IDLE 0 0 0 0

RFS IDLE 0 0 0 0

RFS IDLE 1 128 182 1

RFS IDLE 0 0 0 0

MRP0 Wait_for_log 1 128 0 0

9 rows selected.

Sql>

11. Check if the standby node's standby is synchronized with the master library

(1). Primary node View archived logs

Sql> SELECT archived_thread#, archived_seq#, applied_thread#, applied_seq# from V$archive_dest_status;

archived_thread# archived_seq# applied_thread# applied_seq#

---------------- ------------- --------------- ------------

1 127 0 0

1 127 1 124

1 127 0 0

1 127 0 0

1 127 0 0

1 127 0 0

1 127 0 0

1 127 0 0

1 127 0 0

1 127 0 0

1 127 0 0 --Indicates that all logs before 127 are archived

One by one rows selected.

Sql>

(2). Archive log information for the standby node

Sql> SELECT archived_thread#, archived_seq#, applied_thread#, applied_seq# from V$archive_dest_status;

archived_thread# archived_seq# applied_thread# applied_seq#

---------------- ------------- --------------- ------------

1 127 0 0

0 0 0 0

0 0 0 0

0 0 0 0

0 0 0 0

0 0 0 0

0 0 0 0

0 0 0 0

0 0 0 0

0 0 0 0

1 127 1 127

One by one rows selected.

Sql>

12. View the status of Dataguard on the standby node

Sql> Select Message from V$dataguard_status;

13.primary and standby normal switching status

(1). On the primary node to see if you can switch

Sql> select Switchover_status from V$database;

Switchover_status

--------------------

To STANDBY

Switchover_status:to standby indicates that the switch can be normal. If the value of Switchover_status is sessions active, the current session is in the active state

If the value of Switchover_status is to STANDBY:
Sql> ALTER DATABASE commit to switchover to physical standby;
If the value of Switchover_status is sessions ACTIVE:
Sql> ALTER DATABASE commit to switchover-physical standby with session shutdown;
After the command is successfully run, the main library is modified to be a standby library

Sql> ALTER DATABASE commit to switchover to physical standby;

Database altered.

sql> shutdown Immediate

Ora-01507:database not mounted

ORACLE instance shut down.

Sql> Startup Mount

ORACLE instance started.

Total System Global area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 532678728 bytes

Database buffers 293601280 bytes

Redo buffers 2433024 bytes

Database mounted.

Sql> select Switchover_status from V$database;

Switchover_status

--------------------

To PRIMARY

Sql>

(2). Standby node

This article is from the "Days Together" blog, please be sure to keep this source http://tongcheng.blog.51cto.com/6214144/1871815

Oracle 11g Dataguard Maintenance Considerations