DG_ORACLE11G_WIN

最新推荐文章于 2019-08-01 14:28:26 发布

hello985

最新推荐文章于 2019-08-01 14:28:26 发布

阅读量548

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/hello985/article/details/12942827

版权

oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

--DB_NAME=SOURCE DG中DB_NAME必须一样
--db_unique_name PRIMARY STANDBY
--tnsnames 最好和db_unique_name一致
--TNSNAME SOURCE TARGET
--PRIMARY CONFIG
alter database force logging;
alter system set db_unique_name='PRIMARY' scope=spfile;
alter system set service_names='PRIMARY,source'
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'
--RESTART DB
alter system set LOG_ARCHIVE_DEST_1='LOCATION=D:\app\Administrator\fast_recovery_area\source\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=TARGET LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
alter system set FAL_SERVER=STANDBY;
alter system set FAL_CLIENT=PRIMARY;
alter system set DB_FILE_NAME_CONVERT='D:\app\Administrator\oradata\target','D:\app\Administrator\oradata\source' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='D:\app\Administrator\oradata\target','D:\app\Administrator\oradata\source' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('D:\app\Administrator\oradata\source\redo11.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('D:\app\Administrator\oradata\source\redo12.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('D:\app\Administrator\oradata\source\redo13.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('D:\app\Administrator\oradata\source\redo14.log') size 50M;

Standby Parameter
*.DB_NAME=SOURCE
*.db_unique_name=STANDBY
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=D:\app\Administrator\fast_recovery_area\target\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
*.LOG_ARCHIVE_DEST_2='SERVICE=SOURCE LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.FAL_SERVER=PRIMARY
*.DB_FILE_NAME_CONVERT='D:\app\Administrator\oradata\source','D:\app\Administrator\oradata\target'
*.LOG_FILE_NAME_CONVERT='D:\app\Administrator\oradata\source','D:\app\Administrator\oradata\target'
*.STANDBY_FILE_MANAGEMENT=AUTO

利用以上参数 startup nomount pfile=''

--STANDBY CREATE
mkdir D:\app\Administrator\fast_recovery_area\target\ARCHIVELOG
mkdir D:\app\Administrator\oradata\target
mkdir D:\app\Administrator\admin\target\adump
mkdir D:\app\Administrator\admin\target\dpdump
mkdir D:\app\Administrator\admin\target\pfile

1 复制pwdorcl 文件
2 window oradim -new -sid orcl
服务设置自动启动
建立主库备份，并将备份复制到standby
run {
backup database format 'd:\rman\%d_%u_%s'
plus archivelog format 'd:\rman\%d_%u_%s';
backup current controlfile for standby format 'd:\rman\ctlbak_%s';
}
配置listener,静态监听
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
     (SID_DESC =
      (GLOBAL_NAME = orcl)
      (ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
      (SID_NAME = orcl)
    )

)

--备份主库并复制到备库
--启动实例服务，set ORACLE_SID=TARGET
--主库执行 rman target sys/password@primary auxiliary sys/password@standby

duplicate target database for standby dorecover nofilenamecheck;

若出现归档无法恢复的情况，一般是备份时无后期生成的归档（备份过久）导致
复制STandby后，
RMAN TARGET /
CATALOG START WITH '' --归档位置
RECOVER DATABASE

--启动主备flashback on
主 shutdown immediate / startup mount / alter database flashback on;
备 shutdown immediate / startup mount /
   alter database recover managed standby database cancel
   alter system set db_recovery_file_dest_size='10G'
   alter system set db_recovery_file_dest=' D:\app\Administrator\fast_recovery_area'
   alter database open

--11g Active Dataguard
      1. 备库上操作
      1) 查看备库当前状态 mount
      SQL> select open_mode,database_role,db_unique_name from v$database;
      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
      -------------------- ---------------- ------------------------------
      MOUNTED              PHYSICAL STANDBY DG

      2) 取消备库的自动恢复

      SQL> alter database recover managed standby database cancel;
      数据库已更改。

      3) OPEN备库为只读模式（Dataguard只能启动到readonly模式）

      SQL> alter database open;
      数据库已更改。

      SQL> select open_mode from v$database;

      OPEN_MODE
      --------------------
      READ ONLY

      4）打开实时应用状态模式

      SQL> alter database recover managed standby database using current logfile disconnect;
      数据库已更改。

      SQL> select open_mode,database_role,db_unique_name from v$database;
      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
      -------------------- ---------------- ------------------------------
      READ ONLY WITH APPLY PHYSICAL STANDBY DG

--管理常用命令总结
    实施应用模式
    startup mount 模式下执行
     alter database recover managed standby database disconnect from session;
    取消应用模式
     alter database recover managed standby database cancel;
    完成应用模式，不可逆(破坏了主从结构，若w/r open后，用了finish就得重建dg。)
     alter database recover managed standby database finish;

     11g Active DG
     startup
     alter database recover managed standby database using current logfile disconnect from session;

     角色切换
       主库切换为备库
           alter database commit to switchover to physical standby;
           alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
           shutdown immediate
           startup nomount;
           alter database mount standby database;
           alter database recover managed standby database disconnect from session;
       从库切换为主库
       alter database commit to switchover to primary;
       shutdown immediate;
       startup
       alter system switch logfile;

       FailOver
         将没有同步的归档，以及redo 复制到备库
         然后手工注册
         alter database register logfile '路径'
         recover standby database until cancel;
         最大保护注意：需要将standby切换至最大可用后才可以激活
          ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
          ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
          alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER;
          alter database activate standby database;
          alter database open

       保护模式切换
        alter database set standby database to maximize protection;
        alter database set standby database to maximize availability;
        alter database set standby database to maximize performance;

监控视图
      查询进程处理状态-主备都可执行
      select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
      查询已经归档和已经应用的log-主备都可执行
      select archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;
      查询备库已经归档的redo
      select registrar,creator,thread#,sequence#,first_change#,next_change# from v$archived_log;
      查询备库已经应用的redo
      select thread#,sequence#,first_change#,next_change# from v$log_history;
      查询丢失的gap
       select * from v$archive_gap;
DG相关视图
      当前实例的所有归档日志目的文
      select * from v$archive_dest;
      验证standby redo log文件组是否存在
      select * from v$standby_log;
      该动态性能视图显示出在主库上产生了多少重做日志数据,但是还没有被备库所应用
      select * from V$DATAGUARD_STATS

注意：
-log_file_name_convert log_file_name_convert='/usr/oracle','/oracle/usr','/opt/oracle','/oracle/opt';
                                                primary        standby        primary       standby

--静态注册就是实例启动时读取listener.ora文件的配置，将实例和服务注册到监听程序。无论何时启动一个数据库，默认地都有
两条信息注册到监听器中：数据库服务器对应的实例和服务。
　　静态注册时，listener.ora中的GLOBAL_DBNAME向外提供服务名，listener.ora中的SID_NAME提供注册的实例名。
　　采取静态注册方法时，listener.ora中的内容如下：
　　SID_LIST_LISTENER =
　　(SID_LIST =
　　(SID_DESC =
　　(SID_NAME = PLSExtProc)
　　(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
　　(PROGRAM = extproc)
　　)
　　(SID_DESC =
　　(GLOBAL_DBNAME =orcl)
　　(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
　　(SID_NAME =orcl)
　　)
　　(SID_DESC =
　　(GLOBAL_DBNAME =orcl1)
　　(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
　　(SID_NAME =orcl)
　　)
　　)
　　该文件表明数据库是单实例的，实例名为orcl，向外提供了两个服务：orcl和orcl1

可以使用命令lsnrctl status来查看某服务是静态注册还是动态注册。
　　实例状态为UNKNOWN值时表明此服务是静态注册的设置。这时监听器用来表明它不知道关于该实例的任何信息，只有当客户发出连
接请求时，它才检查该实例是否存在。
　　动态注册的数据库通过状态信息中的状态READY或状态BLOCKED（对于一个备用数据库）来指明。不管关闭何时数据库，动态注册
的数据库都会动态地从监听器注销，而与之相关的信息将从状态列表中消失。这样，不管数据库是在运行还是已经关闭，监听器总是
知道它的状态。该信息将被用于连接请求的回退（fallback）和负载平衡。

--保护级别为RESYNCHRONIZATION问题
根据最大可用模式的最低要求，将log_archive_dest_2调整为lgwr sync affirm。

SQL> alter system set log_archive_dest_2='service=oxdb_standby lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=oxdg';

--db-broker 配置
主备库启动broker
alter system set dg_broker_start='ture';
主备库启动 flashback on
alter database flashback on;
--选取主机配置broker
dgmgrl sys/password@db
create configuration 'sourcebk' as primary database   is 'primary' connect identifier is 'source';
                                                         db_unquiue_name
add database 'standby' as connect identifier is target maintained as physical;
             db_unquiue_name
--更改dg 应用模式最大可用
-- enable configuration;
--enable fast_start failover;