-- Data Guard 安装

-- 环境 Oracle Linux 5.5

-- 主机
   -- DB_NAME:ORA11GR2
   -- DB_UNIQUE_NAME:DBONE

-- 备机
   -- DB_NAME:ORA11GR2
   -- DB_UNIQUE_NAME:DBTWO

-- 安装
   -- 配置静态监听

       LISTENER =
       (DESCRIPTION_LIST =
           (DESCRIPTION =
             (ADDRESS = (PROTOCOL = TCP)(HOST = BlackBox)(PORT = 1521))
             (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
           )
       )

       ADR_BASE_LISTENER = /u01/app/oracle

       SID_LIST_LISTENER =
           (SID_LIST =
           (SID_DESC =
               (GLOBAL_DBNAME = ORA11GR2)
               (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
                (SID_NAME = ORA11GR2)
            )
           )
   -- 配置连接字符串

       DBTWO =
       (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.13)(PORT = 1521))
          )
          (CONNECT_DATA =
            (SERVICE_NAME = dbtwo)
          )
       )


       DBONE =
          (DESCRIPTION =
            (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521))
            )
            (CONNECT_DATA =
              (SERVICE_NAME = dbone)
            )
          )

   -- 开启主库归档模式，开启强制归档，创建 4 组备用日志文件

       --开启归档
           --查看归档状态

               SYS@ORA11GR2> archive log list
               Database log mode                 No Archive Mode
               Automatic archival               Disabled
               Archive destination               /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
               Oldest online log sequence     2
               Current log sequence             4

           --关闭数据库

               SYS@ORA11GR2> shutdown immediate
               Database closed.
               Database dismounted.
               ORACLE instance shut down.

           --启动数据库到 mount 状态

               SYS@ORA11GR2> startup mount
               ORACLE instance started.

               Total System Global Area 835104768 bytes
               Fixed Size                      2257840 bytes
               Variable Size                  541068368 bytes
               Database Buffers            289406976 bytes
               Redo Buffers                    2371584 bytes
               Database mounted.

           --开启归档

               SYS@ORA11GR2> alter database archivelog;

               Database altered.

           --查看归档状态

               SYS@ORA11GR2> archive log list
               Database log mode                 Archive Mode
               Automatic archival               Enabled
               Archive destination               /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
               Oldest online log sequence     2
               Next log sequence to archive   4
               Current log sequence             4

       --开启强制归档

           --开启强制归档

               SYS@ORA11GR2> alter database force logging;

               Database altered.

           --查看 force 状态

               SYS@ORA11GR2> select force_logging from v$database;

               FOR
               ---
               YES

       --创建 4 组备用日志文件

           --打开数据库

               SYS@ORA11GR2> alter database open;

               Database altered.

           --查看日志文件

               SYS@ORA11GR2> select member from v$logfile;

               MEMBER
               ----------------------------------------------------------------------------------------------------
               /u01/app/oracle/oradata/ORA11GR2/redo03.log
               /u01/app/oracle/oradata/ORA11GR2/redo02.log
               /u01/app/oracle/oradata/ORA11GR2/redo01.log

           --查看日志文件大小

               SYS@ORA11GR2> select bytes from v$log;

                     BYTES
               ----------
                  52428800
                  52428800
                  52428800

       --创建备用日志文件

           SYS@ORA11GR2> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/ORA11GR2/sta_do04.log') size 50m;

               Database altered.

            SYS@ORA11GR2> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/ORA11GR2/sta_do05.log') size 50m;

               Database altered.

            SYS@ORA11GR2> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/ORA11GR2/sta_do06.log') size 50m;

               Database altered.

            SYS@ORA11GR2> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/ORA11GR2/sta_do07.log') size 50m;

               Database altered.

           --查看创建的备用日志文件

               SYS@ORA11GR2> select member from v$logfile;

               MEMBER
               ----------------------------------------------------------------------------------------------------
               /u01/app/oracle/oradata/ORA11GR2/redo03.log
               /u01/app/oracle/oradata/ORA11GR2/redo02.log
               /u01/app/oracle/oradata/ORA11GR2/redo01.log
               /u01/app/oracle/oradata/ORA11GR2/sta_do04.log
               /u01/app/oracle/oradata/ORA11GR2/sta_do05.log
                /u01/app/oracle/oradata/ORA11GR2/sta_do06.log
                /u01/app/oracle/oradata/ORA11GR2/sta_do07.log

    --创建修改主库 pfile 文件

        --创建 pfile 文件

            SYS@ORA11GR2> create pfile from spfile;

           File created.

        --修改 pfile 文件

            --修改前
                /*
                ORA11GR2.__db_cache_size=348127232
               ORA11GR2.__java_pool_size=4194304
               ORA11GR2.__large_pool_size=8388608
               ORA11GR2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
               ORA11GR2.__pga_aggregate_target=335544320
               ORA11GR2.__sga_target=503316480
               ORA11GR2.__shared_io_pool_size=0
               ORA11GR2.__shared_pool_size=130023424
               ORA11GR2.__streams_pool_size=4194304
               *.audit_file_dest='/u01/app/oracle/admin/ORA11GR2/adump'
               *.audit_trail='db'
               *.compatible='11.2.0.4.0'
               *.control_files='/u01/app/oracle/oradata/ORA11GR2/control01.ctl','/u01/app/oracle/oradata/ORA11GR2/control02.ctl'
               *.db_block_size=8192
               *.db_domain=''
               *.db_name='ORA11GR2'
               *.diagnostic_dest='/u01/app/oracle'
               *.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11GR2XDB)'
               *.memory_target=838860800
               *.open_cursors=300
               *.processes=150
               *.remote_login_passwordfile='EXCLUSIVE'
               *.undo_tablespace='UNDOTBS1
                */

            --修改后
                /*
                ORA11GR2.__db_cache_size=348127232
               ORA11GR2.__java_pool_size=4194304
               ORA11GR2.__large_pool_size=8388608
               ORA11GR2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
               ORA11GR2.__pga_aggregate_target=335544320
               ORA11GR2.__sga_target=503316480
               ORA11GR2.__shared_io_pool_size=0
               ORA11GR2.__shared_pool_size=130023424
               ORA11GR2.__streams_pool_size=4194304
               *.audit_file_dest='/u01/app/oracle/admin/ORA11GR2/adump'
               *.audit_trail='db'
               *.compatible='11.2.0.4.0'
               *.db_block_size=8192
               *.db_domain=''
               *.diagnostic_dest='/u01/app/oracle'
               *.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11GR2XDB)'
               *.memory_target=838860800
               *.open_cursors=300
               *.processes=150
               *.remote_login_passwordfile='EXCLUSIVE'
               *.undo_tablespace='UNDOTBS1'

               DB_NAME=ORA11GR2
               DB_UNIQUE_NAME=DBONE
               LOG_ARCHIVE_CONFIG='DG_CONFIG=(DBONE,DBTWO)'
               CONTROL_FILES='/u01/app/oracle/oradata/ORA11GR2/control01.ctl','/u01/app/oracle/oradata/ORA11GR2/control02.ctl'
               LOG_ARCHIVE_DEST_1=
               'LOCATION=/u01/app/arch1/DBONE/
                  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
                  DB_UNIQUE_NAME=DBONE'
               LOG_ARCHIVE_DEST_2=
               'SERVICE=DBTWO ASYNC
                  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                  DB_UNIQUE_NAME=DBTWO'
               LOG_ARCHIVE_DEST_STATE_1=ENABLE
               LOG_ARCHIVE_DEST_STATE_2=ENABLE
               REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
               LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
               LOG_ARCHIVE_MAX_PROCESSES=30


               FAL_SERVER=DBTWO
               DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORA11GR2','/u01/app/oracle/oradata/ORA11GR2'
               LOG_FILE_NAME_CONVERT=
               '/u01/app/oracle/oradata/ORA11GR2','/u01/app/oracle/oradata/ORA11GR2'
               STANDBY_FILE_MANAGEMENT=AUTO
                */

            -- 创建 LOG_ARCHIVE_DEST_1 的目录

                mkdir -p /u01/app/arch1/DBONE

            -- 关闭数据库

                SYS@ORA11GR2> shutdown immediate
               Database closed.
               Database dismounted.
               ORACLE instance shut down.

            -- 删除 spfile 文件

                [oracle@BlackBox dbs]$ rm spfileORA11GR2.ora
               [oracle@BlackBox dbs]$ ls
               hc_ORA11GR2.dat init.ora initORA11GR2.ora initORA11GR2.ora.bak lkORA11GR2 orapwORA11GR2

            -- 通过 pfile 启动数据库

                SYS@ORA11GR2> startup
               ORACLE instance started.

                Total System Global Area 835104768 bytes
               Fixed Size                      2257840 bytes
               Variable Size                  541068368 bytes
               Database Buffers            289406976 bytes
               Redo Buffers                    2371584 bytes
               Database mounted.
               Database opened.


            -- 通过 pfile 文件创建 spfile 文件

                SYS@ORA11GR2> create spfile from pfile;

               File created.

            -- 通过 scp 把 listener.ora tnsnames.ora initORA11GR2.ora orapwORA11GR2 分别传送的备库主机上

                scp /u01/app/oracle/product/11.2.0/dbhome_1/network/adminlistener.ora oracle@192.168.0.13:$ORACLE_HOME/network/admin/.
                scp /u01/app/oracle/product/11.2.0/dbhome_1/network/admintnsnames.ora oracle@192.168.0.13:$ORACLE_HOME/network/admin/.
                scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORA11GR2.ora oracle@192.168.0.13:$ORACLE_HOME/dbs/.
                scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORA11GR2 oracle@192.168.0.13:$ORACLE_HOME/dbs/.

        -- 修改备库的 pfile 文件

            /*
            *.audit_file_dest='/u01/app/oracle/admin/ORA11GR2/adump'
           *.audit_trail='db'
           *.compatible='11.2.0.4.0'
           *.db_block_size=8192
           *.db_domain=''
           *.diagnostic_dest='/u01/app/oracle'
           *.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11GR2XDB)'
           *.memory_target=838860800
           *.open_cursors=300
           *.processes=150
           *.remote_login_passwordfile='EXCLUSIVE'
           *.undo_tablespace='UNDOTBS1'

           DB_NAME=ORA11GR2
           DB_UNIQUE_NAME=DBTWO
           LOG_ARCHIVE_CONFIG='DG_CONFIG=(DBONE,DBTWO)'
           CONTROL_FILES='/u01/app/oracle/oradata/ORA11GR2/control01.ctl','/u01/app/oracle/oradata/ORA11GR2/control02.ctl'
           LOG_ARCHIVE_DEST_1=
           'LOCATION=/u01/app/arch1/DBTWO/
              VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
              DB_UNIQUE_NAME=DBTWO'
           LOG_ARCHIVE_DEST_2=
           'SERVICE=DBONE ASYNC
              VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
              DB_UNIQUE_NAME=DBONE'
           LOG_ARCHIVE_DEST_STATE_1=ENABLE
           LOG_ARCHIVE_DEST_STATE_2=ENABLE
           REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
           LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
           LOG_ARCHIVE_MAX_PROCESSES=30


           FAL_SERVER=DBONE
           DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORA11GR2','/u01/app/oracle/oradata/ORA11GR2'
           LOG_FILE_NAME_CONVERT=
           '/u01/app/oracle/oradata/ORA11GR2','/u01/app/oracle/oradata/ORA11GR2'
           STANDBY_FILE_MANAGEMENT=AUTO
            */

        -- 创建目录

            mkdir -p /u01/app/oracle/oradata/ORA11GR2/
            mkdir -p /u01/app/arch1/DBTWO/
            mkdir -p /u01/app/oracle/admin/ORA11GR2/adump

        -- 通过 pfile 文件创建 spfile 文件

            SQL> create spfile from pfile;

           File created.

        -- 启动数据库到 nomount 状态

            SQL> startup nomount
           ORACLE instance started.

           Total System Global Area 835104768 bytes
           Fixed Size                      2257840 bytes
           Variable Size                  541068368 bytes
           Database Buffers            289406976 bytes
           Redo Buffers                    2371584 bytes

        -- 使用 rman 从主库复制数据文件、日志文件、控制文件到备库

            -- 进入 rman

                [oracle@BlackBox admin]$ rman target sys/oracle@dbone auxiliary sys/oracle@dbtwo

               Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jul 3 19:22:40 2016

               Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

               connected to target database: ORA11GR2 (DBID=232806940)
               connected to auxiliary database: ORA11GR2 (not mounted)


            -- 复制数据

                RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

               Starting Duplicate Db at 03-JUL-16
               using target database control file instead of recovery catalog
               allocated channel: ORA_AUX_DISK_1
               channel ORA_AUX_DISK_1: SID=18 device type=DISK

               contents of Memory Script:
               {
                   backup as copy reuse
                   targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORA11GR2' auxiliary format
               '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORA11GR2'   ;
               }
               executing Memory Script

               Starting backup at 03-JUL-16
               allocated channel: ORA_DISK_1
               channel ORA_DISK_1: SID=100 device type=DISK
               Finished backup at 03-JUL-16

               contents of Memory Script:
               {
                   backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/ORA11GR2/control01.ctl';
                   restore clone controlfile to '/u01/app/oracle/oradata/ORA11GR2/control02.ctl' from
               '/u01/app/oracle/oradata/ORA11GR2/control01.ctl';

               。。。。。。。。。。。。。。。。。。。。。。。。


               Starting recover at 03-JUL-16
            using channel ORA_AUX_DISK_1

            starting media recovery

            archived log for thread 1 with sequence 10 is already on disk as file /u01/app/arch1/DBTWO/1_10_916235420.arc
            archived log for thread 1 with sequence 11 is already on disk as file /u01/app/arch1/DBTWO/1_11_916235420.arc
            archived log file name=/u01/app/arch1/DBTWO/1_10_916235420.arc thread=1 sequence=10
            archived log file name=/u01/app/arch1/DBTWO/1_11_916235420.arc thread=1 sequence=11
            media recovery complete, elapsed time: 00:00:00
            Finished recover at 03-JUL-16
            Finished Duplicate Db at 03-JUL-16

         RMAN>

-- 应用日志

   SQL> alter database recover managed standby database using current logfile disconnect from session;

       Database altered.

-- 查询数据库打开模式

   -- 查询主库打开模式

      SYS@ORA11GR2> select database_role,open_mode from v$database;

      DATABASE_ROLE      OPEN_MODE
      ---------------- --------------------
      PRIMARY               READ WRITE

   -- 查询备库打开模式

    SQL> select database_role,open_mode from v$database;

      DATABASE_ROLE      OPEN_MODE
      ---------------- --------------------
      PHYSICAL STANDBY MOUNTED

-- 取消日志应用

SQL> alter database recover managed standby database cancel;

    Database altered.

-- snapshot standby

   -- 开启闪回

    SQL> alter system set db_recovery_file_dest_size = 200m;

    System altered.

    SQL> alter system set db_recovery_file_dest = '/u01/app/oracle/FRA';

    System altered.

-- 变更数据库角色

SQL> alter database convert to snapshot standby;

    Database altered.

    SQL> select database_role, open_mode from v$database;

    DATABASE_ROLE      OPEN_MODE
    ---------------- --------------------
    SNAPSHOT STANDBY MOUNTED

--查看数据库状态

SQL> select database_role, open_mode from v$database;

    DATABASE_ROLE      OPEN_MODE
    ---------------- --------------------
    SNAPSHOT STANDBY READ WRITE

-- 恢复备用数据库为 Physical Standby 状态

   -- 关闭数据库，然后打开到 mount 状态

    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 835104768 bytes
    Fixed Size                      2257840 bytes
    Variable Size                  566234192 bytes
    Database Buffers            264241152 bytes
    Redo Buffers                    2371584 bytes
    Database mounted.

-- 查看数据库状态

      SQL> select database_role,open_mode from v$database;

       DATABASE_ROLE      OPEN_MODE
       ---------------- --------------------
       SNAPSHOT STANDBY MOUNTED

   -- 变更数据库状态到 physical standby

    SQL> alter database convert to physical standby;

    Database altered.

-- 这时数据库需要关闭并重启到 mount 状态

SQL> shutdown immediate
    ORA-01507: database not mounted


    ORACLE instance shut down.
    SQL> startup mount;
    ORACLE instance started.

    Total System Global Area 835104768 bytes
    Fixed Size                      2257840 bytes
    Variable Size                  566234192 bytes
    Database Buffers            264241152 bytes
    Redo Buffers                    2371584 bytes
    Database mounted.

-- 打开数据库应用日志

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

      Database altered.

-- Oracle 11g物理Active Data Guard实时查询（Real-time query）特性

   -- 备库

       -- 查看备库状态

          SQL> select database_role,open_mode from v$database;

          DATABASE_ROLE   OPEN_MODE
          ---------------- --------------------
          PHYSICAL STANDBY MOUNTED

       -- 关闭日志应用

           SQL> alter database recover managed standby database cancel;

           Database altered.

       -- 打开数据库到 open 状态

          SQL> alter database open;

          Database altered.

       -- 查看备库状态

          SQL> select database_role,open_mode from v$database;

          DATABASE_ROLE      OPEN_MODE
          ---------------- --------------------
          PHYSICAL STANDBY READ ONLY

       -- 开启归档应用

          SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

          Database altered.

       -- 查看备库状态

           SQL> select database_role,open_mode from v$database;

           DATABASE_ROLE      OPEN_MODE
           ---------------- --------------------
           PHYSICAL STANDBY READ ONLY WITH APPLY

-- 主库

    -- 创建表空间 ts_test, 创建用户 xxf, 授予权限, 创建表 test

      SYS@ORA11GR2> create tablespace ts_test datafile '/u01/app/oracle/oradata/ORA11GR2/ts_test01.dbf' size 10M;

      Tablespace created.

      SYS@ORA11GR2> create user xxf identified by oracle default tablespace ts_test;

      User created.

      SYS@ORA11GR2> grant connect,resource to xxf;

      Grant succeeded.

      SYS@ORA11GR2> conn xxf/oracle
      Connected.
      XXF@ORA11GR2> create table test as select * from all_objects;

      Table created.

-- 备库

   -- 查看同步情况

    SQL> conn xxf/oracle
      Connected.
      SQL> select count(*) from test;

        COUNT(*)
      ----------
           68316

-- SWITCHOVER

   -- 原备库

    SQL> select switchover_status from v$database;

    SWITCHOVER_STATUS
    --------------------
    NOT ALLOWED

-- 原主库

SYS@ORA11GR2> select switchover_status from v$database;

    SWITCHOVER_STATUS
    --------------------
    SESSIONS ACTIVE

    SYS@ORA11GR2> alter database commit to switchover to physical standby;    --切换原主库状态为物理备库
    ERROR:
    ORA-01034: ORACLE not available
    Process ID: 28605
    Session ID: 101 Serial number: 1459



    Database altered.

    SYS@ORA11GR2> conn / as sysdba
    Connected to an idle instance.
    SYS@ORA11GR2> startup mount;                                              --启动到 mount 状态
    ORACLE instance started.

    Total System Global Area 835104768 bytes
    Fixed Size                      2257840 bytes
    Variable Size                  679480400 bytes
    Database Buffers            150994944 bytes
    Redo Buffers                    2371584 bytes
    Database mounted.
    SYS@ORA11GR2> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

    SWITCHOVER_STATUS
    --------------------
    RECOVERY NEEDED

    SYS@ORA11GR2> select database_role,open_mode from v$database;

    DATABASE_ROLE      OPEN_MODE
    ---------------- --------------------
    PHYSICAL STANDBY MOUNTED

    SYS@ORA11GR2> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

    Database altered.

    SYS@ORA11GR2> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

    SWITCHOVER_STATUS
    --------------------
    NOT ALLOWED

   -- 主库

    SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

    SWITCHOVER_STATUS
    --------------------
    TO PRIMARY

    SQL> alter database commit to switchover to primary;

    Database altered.

    SQL> select switchover_status from v$database;

    SWITCHOVER_STATUS
    --------------------
    NOT ALLOWED

    SQL> select database_role,open_mode from v$database;

    DATABASE_ROLE      OPEN_MODE
    ---------------- --------------------
    PRIMARY               MOUNTED

    SQL> alter database open;

    Database altered.

    SQL> select database_role,open_mode from v$database;

    DATABASE_ROLE      OPEN_MODE
    ---------------- --------------------
    PRIMARY               READ WRITE

ITPub博客

Oracle Data Guard 基本配置

最新文章