用 rman duplicate 进行创建 Standby 库
-------------------------1 启用 Force Logging -------------------------将 Primary 数据库置为 Force Logging 模式。通过下列语句，看状态： select database_role, force_logging from v$database; DATABASE_ROLE FOR ---------------- --- PRIMARY NO修改模式 alter database force logging;取消 Force logging 模式： alter database no force logging; -----------------------------2 创建 standby redo logs -----------------------------在最大保护和最高可用性模式，还有 real-time apply时，必须为 Standby 数据库配置 Standby Redo logs。有 2 点注意的地方：每个 standby redo log 文件的大小必须大于等于 Primary 库的 online redo 的大小。最好是一样大。standby redo log group 要比 primary 的 redo group 至少多一组。这里要注意 Thread，每个 Thread 都有对应的所有的组。一般情况下，单实例只有 1 个 thread，那么单实例到 standby 只需要比现在的组多一个就可以了。
如果是 RAC 到 DG，RAC 的每个节点都可以看成是一个 thread，这种情况下，standby 端的 redo log group 就是：（每线程的日志组数 + 1）* 最大线程数。
比如有 2 个线程，每个线程有 3 组，standby redo 就需要（3+1）* 2 = 8 组。线程查看命令： select group#, thread#, bytes, members from v$log; GROUP# THREAD# BYTES MEMBERS ---------- ---------- ---------- ---------- 1 1 52428800 1 2 1 52428800 1 3 1 52428800 1 select thread#, groups from v$thread; THREAD# GROUPS ---------- ---------- 1 3 set line 200 col member for a80 set pagesize 9999 select group#, status, type, member from v$logfile order by 1,4; GROUP# STATUS TYPE MEMBER ---------- ------- ------- -------------------------------------------------- 1 ONLINE /data1/app/oracle/oradata/moxi/redo01.log 2 ONLINE /data1/app/oracle/oradata/moxi/redo02.log 3 ONLINE /data1/app/oracle/oradata/moxi/redo03.log Standby Redo Log 对于单线程： alter database add standby logfile thread 1 group 4 ('/data1/app/oracle/oradata/moxi/st_1_4.log') size 50m; alter database add standby logfile thread 1 group 5 ('/data1/app/oracle/oradata/moxi/st_1_5.log') size 50m; alter database add standby logfile thread 1 group 6 ('/data1/app/oracle/oradata/moxi/st_1_6.log') size 50m; alter database add standby logfile thread 1 group 7 ('/data1/app/oracle/oradata/moxi/st_1_7.log') size 50m;查看 redo log： set line 200 col member for a80 set pagesize 9999 select group#, status, type, member from v$logfile order by 1,4;----------------------------3 设置主数据库初始化参数 ----------------------------首先查看下列参数： col name for a50 col value for a50 set line 150 set pagesize 9999 select name, value from v$parameter where name in ('db_name','db_unique_name','service_names','log_archive_config','log_archive_dest_1', 'log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2', 'standby_file_management','fal_client','fal_server','db_file_name_convert', 'log_file_name_convert','remote_login_passwordfile');修改如下： alter system set log_archive_config='dg_config=(mth,atlas)'; alter system set log_archive_dest_1='location=/data2/app/arch/valid_for=(all_logfiles,all_roles) db_unique_name=mth'; alter system set log_archive_dest_2='service=second valid_for=(online_logfile,primary_role) db_unique_name=atlas'; alter system set log_archive_dest_state_1=enable; alter system set log_archive_dest_state_2=defer; alter system set standby_file_management=auto; alter system set fal_client=first; alter system set fal_server=second; alter system set db_file_name_convert='/data1/app/oracle/oradata/moxi/','/data1/app/oracle/oradata/moxi/' scope=spfile; alter system set log_file_name_convert='/data1/app/oracle/oradata/moxi/','/data1/app/oracle/oradata/moxi/' scope=spfile;
------------------4 设置归档模式 ------------------ shutdown immediate; startup mount; alter database archivelog; alter database open; archive log list;-----------------------------------------------------5 Create a Control File for the Standby Database -----------------------------------------------------注意：如果用了 rman duplicate，那么步骤 5 创建 standby 控制文件，可以省略掉了。 ------------------------------------------------------------------------------查看 primary 数据库 control 文件信息： col name for a60 select name from v$controlfile;在 Primary 库上执行如下语句，为 Standby 数据库创建控制文件： shutdown immediate; startup mount; alter database create standby controlfile as '/data2/app/control01.ctl'; alter database open;
-------------------------------------------------------6 Create a Parameter File for the Standby Database -------------------------------------------------------在 Primary 上，创建 pfile 文件： create pfile from spfile;修改参数文件 pfile *.control_files='/data1/app/oracle/oradata/moxi/control01.ctl','/data1/app/oracle/flash_recovery_area/moxi/control02.ctl' *.db_name='moxi' *.db_unique_name='ATLAS' *.fal_client='SECOND' *.fal_server='FIRST' *.log_archive_config='dg_config=(mth,atlas)'
*.log_archive_dest_1='location=/data2/app/arch/valid_for=(all_logfiles,all_roles) db_unique_name=atlas' *.log_archive_dest_2='service=first valid_for=(online_logfile,primary_role) db_unique_name=mth' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.service_names='MOXI' *.standby_file_management='AUTO'备库用 rman duplicate 创建 standby 库是一定要在备库的 pfile 文件添加下面内容。 *.log_file_name_convert='/data1/app/oracle/oradata/moxi/','/data1/app/oracle/oradata/moxi/' *.db_file_name_convert='/data1/app/oracle/oradata/moxi/','/data1/app/oracle/oradata/moxi/' ---------------------------------------------------------------7 Copy Files from the Primary System to the Standby System ---------------------------------------------------------------将 rman 备份、密码文件、参数文件、控制文件都拷贝到备数据库中。在备库中运行如下命令： mkdir -p /data1/app/oracle/admin/moxi/adump mkdir -p /data1/app/oracle/oradata/moxi mkdir -p /data1/app/oracle/flash_recovery_area mkdir -p /data1/app/oracle/flash_recovery_area/moxi RMAN 备份 Primary 数据库，Standby 库用 duplicate 进行恢复，下面是在 Primary 主库操作：run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; backup as compressed backupset format '/data2/app/backup/db_%U' database plus archivelog; backup current controlfile for standby format='/data2/app/backup/control_%U'; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; }将 rman 备份全部拷贝到 standby 备库相同的目录下 $ cd /data1/app/backup $ scp -r * oracle@node2:/data1/app/backup在 Standby 数据库执行 rman 恢复 sqlplus / as sysdba startup nomount pfile=/data1/app/oracle/product/11.2.0/dbhome_1/dbs/initmoxi.ora开始 rman duplicate注意说明：rman 后面的 target sys 用户是主数据库的，后面 auxiliary 和 / 指的是 Standby 数据库 $ rman target sys/123@first auxiliary / duplicate target database for standby nofilenamecheck dorecover;创建 Standby spfile 文件 sqlplus / as sysdba create spfile from pfile='/data1/app/oracle/product/11.2.0/dbhome_1/dbs/initmoxi.ora'; ------------------------------------------------------8 Configure listeners for the primary and standby ------------------------------------------------------配置监听器 $ netmgr在主库和备库上分别执行：netmgr 命令配置 listener 和 tnsname.ora。 $ cat listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = moxi) (ORACLE_HOME = /data1/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = moxi) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.191)(PORT = 1521)) ) ADR_BASE_LISTENER = /data1/app/oracle $ cat tnsnames.ora FIRST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.191)(PORT = 1521)) ) (CONNECT_DATA = (SID = moxi) ) ) SECOND = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.192)(PORT = 1521)) ) (CONNECT_DATA = (SID = moxi) ) ) ----------------9 DG 主备库启动 ----------------
standby 监听：lsnrctl start第一次开启，因为第一次只有 pfile，所以要指定 pfile 开启 standby 备库，然后就可以在 standby 生成 spfile 了： startup pfile='/data1/app/oracle/product/11.2.0/dbhome_1/dbs/initmoxi.ora' nomount create spfile from pfile; alter database mount standby database;以后直接执行如下，启动备援资料库，语法如下：----------->“等同于 startup mount” SQL> connect sys/oracle as sysdba SQL> startup nomount SQL> alter database mount standby database下面查看 standby 备库的状态 select status from v$instance; set line 200 select open_mode, protection_mode, protection_level, database_role, switchover_status, force_logging from v$database;执行下面就可以 applied redo 日志，如果是物理恢复就是 MRP。 alter database recover managed standby database disconnect from session; alter database recover managed standby database disconnect;主库监听器：lsnrctl start主库： startup ----------------10 开始同步 ----------------下面是在 Primary 数据库上执行如下。 alter system set log_archive_dest_state_2=enable scope=both;在 Primary 库执行如下，进行日志的切换。 alter system switch logfile; select status,error from v$archive_dest where dest_id=2;在 standby 备库 执行如下： alter database open; alter database recover managed standby database using current logfile disconnect; SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY------------11 验证 ------------下面查看 standby 备库的状态 select status from v$instance; set line 200 select open_mode, protection_mode, protection_level, database_role, switchover_status, force_logging from v$database;下面是在 primary 主库执行如下： col dest_name for a20 col destination for a20 select dest_name, destination, status, error from v$archive_dest;上面 error 列没有显示，说明 redo 传输成功。下面在 primary 主库执行日志切换： SQL> alter system switch logfile;下面在 primary 和 standby 分别查看日志是否相等，如果相等说明传输成功： SQL> select max(sequence#) from v$archived_log;下面查看 redo 传送状态，在 standby 中执行： set line 200 col name for a50 select name, sequence#, applied, first_time, next_time from v$archived_log order by sequence#; ---------------------最大性能转换到最大可用 --------------------- shutdown immediate startup mount alter systemset log_archive_dest_2='service=second LGWR SYNC AFFIRM valid_for=(online_logfile,primary_role) db_unique_name=atlas'; alter database set standby database to maximize availability; alter database open; select open_mode, protection_mode from v$database; ------------主、备库切换 ------------ 1. 在主库 alter database commit to switchover to physical standby with session shutdown; shutdown immediate 2. 在备库 alter database commit to switchover to primary with session shutdown; shutdown immediate startup 3. 在主库 startup mount 4. 检查数据库角色 set line 200 select name, open_mode, protection_mode, protection_level, database_role, switchover_status, force_logging from v$database; ------------------- failover 非正常切换 ------------------- failover 切换一般是 Primary 数据库发生故障后的切换，这种情况是 Standby 数据库发挥其作用的情况。这种切换发生后，可能会造成数据的丢失。而且这个过程不是可逆的，Data Guard 环境会被破坏。由于 Primary 数据库已经无法启动，所以 Failover 切换所需的条件并不多，只要检查 standby 是否运行在最大保护模式下，如果是的话，需要将其置为最大性能模式，否则切换到 primary 角色也无法启动。 1. 查看是否有日志gap,没有应用的日志： sql> select unique thread#, max(sequence#) over(partition by thread#) last from v$archived_log; sql> select thread#, low_sequence#, high_sequence# from v$archive_gap; 如果有，则拷贝过来并且注册 sql> alter database register physical logfile ' 路径'; 重复查看直到没有应用的日志： 2. 然后停止应用归档： sql> alter database recover managed standby database cancel; 3. 下面将 standby 数据库切换为 priamry 数据库： sql> alter database recover managed standby database finish; 或 sql> alter database recover managed standby database finish force; sql> select database_role from v$database; database_role ---------------- physical standby sql> alter database commit to switchover to primary; sql> alter database open; 或者 shutdown immediate + startup 检查数据库是否已经切换成功： sql> select database_role from v$database; database_role ---------------- primary至此，Failover 切换完成。这个时候应该马上对新的 Primary 数据库进行备份。