位置：首页-资讯-数据库

oracle 11g dataguard维护注意事项

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

1.检查primary和standby节点归档日志是否一至

(1).primary节点的归档日志

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和standby节点这个数字一至就可以了

SQL>

(2).standby节点的归档日志

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.检查primary和stendby两个节点是否归档和开启闪回

SQL> select log_mode ,force_logging,flashback_on from v$database;

LOG_MODE FOR FLASHBACK_ON

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

ARCHIVELOG YES NO

SQL>

3.查看primary和stendby节点监听是否启动或正常状态

[oracle@dg1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2016 14:39:14

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. 10 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, has 1 handler(s) for this service...

The command completed successfully

[oracle@dg1 ~]$

4.检查primary和stendby节点归档目录是否一至(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.检查磁盘使用空间(有时由于磁盘空间不够,闪回恢复区不能写入文件)

[oracle@dg1 ~]$ 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

[oracle@dg1 ~]$

6.正确打开dataguard数据库(先启动primary节点数据库,后在standby节点开启日志传送模式)

(1).在primary节点打开数据库

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;

SQL>

(2).在standby节点

SQL> startup nomount

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

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

7.正确关闭数据库(先在syandby断开复制,然后在primary关闭数据库)

(1).standby节点

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节点

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

8.在syandby节点修改数据库为read only模式

(1).primary节点的数据库是open状态

(2).standby节点的数据库是日志传送状态

SQL> recover managed standby database cancel; --结束日志传送状态

Media recovery complete.

SQL> alter database open read only; --将数据库的状态修改为read only

Database altered.

SQL> select * from t;

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

7 rows selected.

SQL>

9.将standby节点的数据库由read only修改为日志传送状态

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.查看日志传送的信息

(1).primary节点当前的日志信息

SQL> select sequence#,status from v$log;

SEQUENCE# STATUS

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

127 ACTIVE

128 CURRENT

126 ACTIVE

SQL>

(2).standby节点正在传送的日志信息

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 1 128 182 1

RFS IDLE 0 0 0 0

MRP0 WAIT_FOR_LOG 1 128 0 0

9 rows selected.

SQL>

11.检查standby节点的备库是否与主库同步

(1).primary节点查看已经归档的日志

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 --表示在127以前的日志全部归档

11 rows selected.

SQL>

(2).standby节点的归档日志信息

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

1 127 1 127

11 rows selected.

SQL>

12.在standby节点查看dataguard的状态

SQL> select message from v$dataguard_status;

13.primary和standby正常切换状态

(1).在primary节点查看是否可以切换

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

SWITCHOVER_STATUS:TO STANDBY表示可以正常切换.如果SWITCHOVER_STATUS的值为SESSIONS ACTIVE,表示当前有会话处于ACTIVE状态

如果SWITCHOVER_STATUS的值为TO STANDBY 则:
SQL> alter database commit to switchover to physical standby;
如果SWITCHOVER_STATUS的值为SESSIONS ACTIVE 则:
SQL> alter database commit to switchover to physical standby with session shutdown;
成功运行这个命令后，主库被修改为备库

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