oracle 11g dataguard维护注意事项
|
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 Copyright (c) 1991,2009,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. 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; D - X 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; A ------------------- 1 2 3 4 6 7 8 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 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 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 --表示在127以前的日志全部归档 11 rows selected. SQL> (2).standby节点的归档日志信息 SQL> SELECT ARCHIVED_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 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; 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节点 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |