oracle dataguard.docx

Oracle DataGuard 常用维护命令参考手册2009-12-09 13:40:44|分类：Oracle|字号订阅Oracle DataGuard 常用维护命令参考手册Start Standby Databasestartup nomountalter database mount standby database;alter database recover managed standby database disconnect;Disable/Enable archive log destinationsalter system set log_archive_dest_state_2 = defer;alter system set log_archive_dest_state_2 = enable;To remove a delay from a standbyalter database recover managed standby database cancel;alter database recover managed standby database nodelay disconnect;Stop and Start of Logical standby applyalter database stop logical standby apply;alter database start logical standby apply;Physical Standby switchover:In Primary Database：ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;SHUTDOWN IMMEDIATE;STARTUP NOMOUNT;ALTER DATABASE MOUNT STANDBY DATABASE;In standby Database:ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;SHUTDOWN IMMEDIATE;STARTUP;In Primary Database:ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;If the primary Database is down,we can use fllowing step to active standby database:Alter DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;Alter DATABASE COMMIT TO SWITCHOVER TO PRIMARY;SHUTDOWN IMMEDIATE;STARTUP;Register missing archive log fileFind archive log gap by query:SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;register using:ALTER DATABASE REGISTER PHYSICAL LOGFILE filespec1;select protection_mode,protection_level from v$databaseselect database_role from v$databaseselectswitchover_statusfromv$databaseALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;1.设置主站点为FORCE LOGGING模式；Alter DATABASE FORCE LOGGING;2.设置归档模式，在整个DATAGUARD环境中，不允许出现非归档模式，因此以后导数据之类的工作不能改到非归档模式下做，否则dataguard需要重做。a)打开/关闭自动归档alter systemset log_archive_start=true scope=spfile;alter systemset log_archive_start=false scope=spfile;alter system archive log start;-打开自动归档alter system archive log stop;-关闭自动归档b)打开/关闭归档日志状态Shutdown immediateStartup mountalter database archivelog; -打开alter database noarchivelog; -关闭alter database open;3.做数据库的冷备份a)关闭数据库；Shutdown immediateb)拷贝数据文件等等；执行下面的查询，可以看到数据文件的路径，Select name from v$datafile;c)重新打开数据库；Startup4.创建standby控制文件alter database create standby controlfile as /orasoft/app/oradata/oracl/control01.ctl;5.创建pfile;create pfile from spfile;以上3，4，5步骤文件均拷贝至standby服务器上；6.在主节点/standby节点分别配制相关的tnsnames.oraTJJ650 =(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.72)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orACL) )TJJ570 =(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.85)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orACL) )7.standby_archive_dest，log_archive_dest_1，log_archive_dest_2参数standby_archive_dest指定来自一个主服务器的归档日志传送到standby服务器的到达位置。standby_archive_dest=/orasoft/app/archivelog_archive_dest_1指定本地 (指定 LOCATION) 归档日志的存放目标位置log_archive_dest_1= LOCATION=/orasoft/app/archivelog_archive_dest_2指定远程 (指定 SERVICE) 归档日志的存放目标位置，此地SERVICE的配置与tnsnames.ora中相一致log_archive_dest_2=SERVICE=TJJ570log_archive_dest_2=SERVICE=TJJ6508.启动备用数据库SQL STARTUP NOMOUNT;SQL Alter DATABASE MOUNT STANDBY DATABASE;SQL Alter DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;9.关闭备用数据库SQL Alter DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;SQL SHUTDOWN IMMEDIATE;10.只读方式打开备用数据库a)从关闭状态打开；SQL STARTUP NOMOUNT;SQL Alter DATABASE MOUNT STANDBY DATABASE;SQL Alter DATABASE OPEN READ ONLY;b)从正在恢复状态只读打开；SQL Alter DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;SQL Alter DATABASE OPEN READ ONLY;c)切换回到恢复状态；SQL Alter DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;11.主备数据库切换a)正常切换：（即主备服务器均可用的情况下，需维护主服务器的情况下）：主服务器：SQL alter database commit to switchover to physical standby;SQL shutdown immediateSQL startup nomountSQL alter database mount standby database;SQL Alter DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;备用服务器：SQL alter database commit to switchover to primary;SQL shutdown immediate;SQL startupb)非正常切换：（即主服务器当机的情况）：备用服务器：SQL Alter DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;SQL Alter DATABASE COMMIT TO SWITCHOVER TO PRIMARY;SQL SHUTDOWN IMMEDIATE;SQL STARTUP;12.验证及测试SQL Select SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG orDER BY SEQUENCE#;SQL Select SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG orDER BY SEQUENCE#;上述两个SQL是测试standby数据库的归档日志接受和恢复情况SQL Select TO_CHAR(TIMESTAMP,YYYY-MM-DD HH24:MI:SS), MESSAGE FROM V$DATAGUARD_STATUS order by TIMESTAMP;上述SQL查看最新STANDBY的状态信息select database_role from v$database;上述SQL查看当前数据库的角色状态；一、安装环境：Primary 数据库：sun01Standby 数据库：sun02Oracle_Sid:bnet1. 拷贝$ORACLE_BASE 到新机器上2. 让Primary数据库处于归档模式，对Primary数据库做一个Rman热备份startup mountalter database archivelog;archive log list;rman target sys/oraclebackup database format /.;3.创建Data Guard数据库控制文件alter database create standby controlfile as /.ctl;4.设置监听Primary：bnetguard =( (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = sun02)(PORT = 1522)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = bnet)Standby:listener.oraLISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = sun01)(PORT = 1522)tnsname.orabnet =( (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = sun02)(PORT = 1522)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = bnet)5. 设置主机参数alter database force logging;主库强制归档主机参数：fal_client=bnetguardfal_server=bnetstandby_file_management=autoremote_login_passwordfile=exclusive备机参数：db_file_name_convertlog_file_name_convertstandby_file_management=AUTOstandby_archive_dest=/export/home/oracle/oradata/bnet/arch6.创建standby 数据库orapwdsqlplus sys/oracle as sysdbastartup nomount pfile=alter database mount standby database;exitrman target sys/oracle nocatalogrestore database7.设置自动归档alter system set log_archive_dest_2=server=bnetguard reopen=2008.启动Data Guard自动恢复状态sqlplus sys/oracle as sysdbashutdown immediate;SQLstartup nomount;SQLalter database mount standby database;SQLalter database recover managed standby database disconnect from session;1.2备库启动到只读方式SQLshutdown immediate;SQLstartup nomount;SQLalter database mount standby database;SQLalter database open read only;1.3从管理恢复模式下到只读模式SQLrecover managed standby database cancel;SQLalter database open read only;这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的）如alter tablespace temp add tempfile /u02/oradata/BOSS/temp01.dbf size 100M;但注意DG模式下文件有两种管理方式（show parameter standby可以查），自动和手动管理，如果是自动管理，是没法做增加和重命名操作的。例如笔者昨天发的文章中有SQL alter database rename file /bak/datafile/namin_data.dbf to /usr/datafile/namin_data.dbf;alter database rename file /bak/datafile/namin_data.dbf to /usr/datafile/namin_data.dbf*ERROR at line 1:ORA-01511: error in renaming log/data filesORA-01275: Operation RENAME is not allowed if standby file management isautomatic.上ORACLE查了下，在DG模式下文件有两种管理方式（show parameter standby可以查），自动和手动管理，如果是自动管理，是没法重命名的， 需要改成手动模式。SQL show parameter standbyNAME TYPE VALUE- - -standby_archive_dest string ?/dbs/archstandby_file_management string AUTOSQL alter system set standby_file_management=MANUAL;System altered.SQL show parameter standbyNAME TYPE VALUE- - -standby_archive_dest string ?/dbs/archstandby_file_management string MANUALSQL alter database rename file /bak/datafile/namin_data.dbf to /usr/datafile/namin_data.dbf;Database altered.1.4从只读方式到管理恢复方式SQLrecover managed standby database disconnect from session;打开实时应用功能SQLALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;SQLALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;1.5查看Data Guard数据库运行在哪种模式下：在主数据库上：sqlselect DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL- - -PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE在备用数据库上：sqlselect DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL- - -PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE1.6在备机查看日志序列和被应用的状态Sqlselect sequence#,applied from v$archived_log where applied=YES;1.7查看Data Guard Standby 后台恢复进程是否正常在备机上：$ps -ef |grep mrp输出结果应该有mrp的后台进程1.8查看主机和备机归档目录Sqlarchive log list;1.9在备机查看ALERT文件，查看杯恢复的日志序列$tail -f $ORACLE_BASA/admin/standby /bdump/alert_standby.logMedia Recovery Log /archivelog/invest_arch_log/1_1042_650380049.dbfMedia Recovery Waiting for thread 1 sequence 1043 (in transit)2主备数据库之间失败切换2.1在备机查看日志序列和被应用的状态a)如果在备库有活动的未归档的日志，或者有从主数据库拷贝过来的联机日志，可以采用如下的办法注册并恢复SQLALTER DATABASE REGISTER LOGFILE /home/oracle/archlog/1_87.dbf;SQLrecover standby database;2.2在备用数据库执行最后的恢复操作Sqlalter database recover managed standby database finish;2.3执行失败切换操作Sqlalter database commit to switchover to primary;2.4 重新启动数据库，并成为新的主数据库Sqlshutdown immediate;SQL shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.Sqlstartup;ORACLE instance started.Total System Global Area 1610612736 bytesFixed Size 2073192 bytesVariable Size 385879448 bytesDatabase Buffers 1207959552 bytesRedo Buffers 14700544 bytesDatabase mounted.Database opened.SQL2.5 为新的主数库生成一个全备份 ：RMANconnect target /RMAN backup database format=/backup/%U_%s.bak;RMAN sql Alter System Archive Log Current;RMAN Backup filesperset 10 ArchiveLog all format=/backup/%U_%s.bak;3强行激活备库 为主库$sqlpus /nologSqlconn /as sysdbaSql alter system archive log current;Sqlrecover managed standby database cancel;Sqlalter database activate standby database;Sqlshutdown immediate;Sqlstartup;$lsnrctl stop$lsnrctl start创建physical standby-启动force loggingalter database force logging;-添加standby日志组alter database add standby logfile group 10 (/oracle/dbs/log1c.rdo,/oracle/dbs/log2c.rdo) size 500m;-添加standby日志组给特定的thread（for RAC）alter database add standby logfile thread 5 (/oracle/dbs/log1c.rdo,/oracle/dbs/log2c.rdo) size 500m;-删除standby日志组alter database drop standby logfile group 10;-验证standby日志的创建select group#,thread#,sequence#,archived,status from v$standby_log;-创建standby控制文件alter database create standby controlfile as /tmp/boston.ctl;-启动/关闭redo applyalter database recover managed standby database disconnect from session;alter database recover managed standby database cancel;-验证redo是否已传入standby databaseselect