Oracle DG如何搭建

这篇文章主要为大家展示了“Oracle DG如何搭建”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“Oracle DG如何搭建”这篇文章吧。

Oracle DG搭建(duplicate方式)

(1) 主库：开启归档模式(2) 主库：开启强制写日志功能(3) 主库：关闭闪回(4) 主库：配置静态监听,配置tnsnames 文件(5) 主库：增加standby logfile 文件(6) 主库：修改参数文件(7) 主库: 拷贝主库文件到备库(8) 备库：配置静态监听,配置tnsnames.ora文件(9) 备库：修改参数文件和口令文件(10) 备库：根据据参数文件创建相应的目录(11) 备库：startup nomount(12)主库: 通过rman duplicate 方式进行备库恢复(13)验证是否搭建成功(14)主从切换

(1) 主库：开启归档模式

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> alter system set db_recovery_file_dest_size=10G;  

SQL> alter system set db_recovery_file_dest=/home/oracle/flash;

(2) 主库：开启强制写日志功能

SQL> select force_logging from v$database;

SQL> alter database force logging;

(3) 主库：关闭闪回

SQL> select flashback_on from v$database;

SQL> alter database flashback off;

(4) 主库：配置静态监听,配置tnsnames 文件

[oracle@chen ~]$ cd $ORACLE_HOME/network/admin

[oracle@chen admin]$ vi listener.ora

SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (GLOBAL_DBNAME=chicago.us.oracle.com)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME=chicago)

     )

   )

LISTENER =

  (DESCRIPTION_LIST =

(DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = chen.example.com)(PORT = 1521))

    )

  )

[oracle@chen admin]$ vi tnsnames.ora

chicago =

  (DESCRIPTION =

    (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = chen.example.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = chicago.us.oracle.com)

    )

  )

boston =

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = jch.example.com)(PORT = 1521))

    (CONNECT_DATA =

(SERVER = DEDICATED)

      (SERVICE_NAME = boston.us.oracle.com)

    )

  )

[oracle@chen admin]$ lsnrctl stop

[oracle@chen admin]$ lsnrctl start

(5) 主库：增加standby logfile 文件

SQL> select member from v$logfile;

SQL> select bytes/1024/1024 from v$log;

SQL> alter database add standby logfile group 4 /u01/app/oracle/oradata/chicago/standby_redo04.log size 50M;

SQL> alter database add standby logfile group 5 /u01/app/oracle/oradata/chicago/standby_redo05.log size 50M;

SQL> alter database add standby logfile group 6 /u01/app/oracle/oradata/chicago/standby_redo06.log size 50M;

SQL> alter database add standby logfile group 7 /u01/app/oracle/oradata/chicago/standby_redo07.log size 50M;

(6) 主库：修改参数文件

https://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#SBYDB00426

Example 3-1 Primary Database: Primary Role Initialization Parameters

Example 3-2 Primary Database: Standby Role Initialization Parameters

SQL> create pfile from spfile;

[oracle@chen admin]$ cd $ORACLE_HOME/dbs

[oracle@chen dbs]$ vi initchicago.ora

*.DB_NAME=chicago 

*.DB_UNIQUE_NAME=chicago

*.LOG_ARCHIVE_CONFIG=DG_CONFIG=(chicago,boston)

*.LOG_ARCHIVE_DEST_1=LOCATION=/home/oracle/flash VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago

*.LOG_ARCHIVE_DEST_2=SERVICE=boston ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

*.FAL_SERVER=boston

*.DB_FILE_NAME_CONVERT=boston,chicago

*.LOG_FILE_NAME_CONVERT=boston,chicago

*.STANDBY_FILE_MANAGEMENT=AUTO

[oracle@chen dbs]$ cp spfilechicago.ora spfilechicago.ora.bak

SQL> shutdown immediate

SQL> create spfile from pfile;

SQL> startup

SQL> show parameter log_archive_dest_2

(7) 主库: 拷贝主库文件到备库

监听文件，TNS文件，参数文件、密码文件到备库并改名

[oracle@chen dbs]$ mkdir /home/oracle/dg

[oracle@chen dbs]$ cp initchicago.ora /home/oracle/dg/

[oracle@chen dbs]$ cp orapwchicago /home/oracle/dg/

[oracle@chen admin]$ cp listener.ora /home/oracle/dg/

[oracle@chen admin]$ cp tnsnames.ora /home/oracle/dg/

[oracle@chen ~]$ tar -zcvf dg.tar.gz dg/

[oracle@chen ~]$ scp dg.tar.gz jch:/home/oracle

(8) 备库：配置静态监听,配置tnsnames.ora文件

[oracle@jch ~]$ tar -zxvf dg.tar.gz

[oracle@jch ~]$ cd $ORACLE_HOME/network/admin

[oracle@jch admin]$ mv listener.ora listener.ora.bak

[oracle@jch admin]$ mv tnsnames.ora tnsnames.ora.bak

[oracle@jch admin]$ cp /home/oracle/dg/listener.ora .

[oracle@jch admin]$ cp /home/oracle/dg/tnsnames.ora .

[oracle@jch admin]$ vi listener.ora

SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

(GLOBAL_DBNAME=boston.us.oracle.com)

      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME=boston)

    )

  )

LISTENER =

(DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = jch.example.com)(PORT = 1521))

    )

  )

[oracle@jch admin]$ lsnrctl start

(9) 备库：修改参数文件和口令文件

[oracle@jch dbs]$ cp /home/oracle/dg/initchicago.ora .

[oracle@jch dbs]$ cp /home/oracle/dg/orapwchicago .

[oracle@jch dbs]$ mv orapwchicago orapwboston

[oracle@jch dbs]$ mv initchicago.ora initboston.ora

[oracle@jch dbs]$ vi initboston.ora

:%s/chicago/AAAA/g 

:%s/boston/chicago/g

:%s/AAAA/boston/g

*.DB_NAME=chicago

*.DB_UNIQUE_NAME=boston

*.LOG_ARCHIVE_CONFIG=DG_CONFIG=(boston,chicago)

*.LOG_ARCHIVE_DEST_1=LOCATION=/home/oracle/flash VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston

*.LOG_ARCHIVE_DEST_2=SERVICE=chicago ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

*.FAL_SERVER=chicago

*.DB_FILE_NAME_CONVERT=chicago,boston

*.LOG_FILE_NAME_CONVERT=chicago,boston

*.STANDBY_FILE_MANAGEMENT=AUTO

(10) 备库：根据据参数文件创建相应的目录

[oracle@jch ~]$ mkdir flash

[oracle@jch ~]$ cd /u01/app/oracle/admin/

[oracle@jch admin]$ mkdir boston/adump -p

[oracle@jch ~]$ mkdir /u01/app/oracle/oradata/boston

(11) 备库：startup nomount

[oracle@jch ~]$ export ORACLE_SID=boston

[oracle@jch ~]$ sqlplus / as sysdba

SQL> create spfile from pfile;

SQL> startup nomount

(12)主库: 通过rman duplicate 方式进行备库恢复

[oracle@chen ~]$ export ORACLE_SID=chicago

[oracle@chen ~]$ rman target / auxiliary sys/oracle@boston

RMAN> duplicate target database for standby from active database;

(13)验证是否搭建成功

主库：

SQL> archive log list;

SQL> alter system switch logfile;

备库： 

SQL> archive log list;

SQL> select process, pid, status, client_process from v$managed_standby;

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

备库：sync 数据

SQL> alter database open;

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

-----SQL> recover managed standby database cancel;

主库： 

SQL> create table test1 as select level as id from dual connect by level<=3;

备库： 

SQL> select * from test1;

(14)主从切换https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB00625

主库： 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

SQL> SHUTDOWN ABORT;

SQL> STARTUP MOUNT;

备库： 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

SQL> ALTER DATABASE OPEN;

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

当主库故障时，通过上面的方式主从切换回报错如下：Database not available for switchover  End-Of-REDO archived log file has not been recoveredArchived log files detected beyond End-Of-REDO  Incomplete recovery SCN:0:1038219 archive SCN:0:1037990解决方案：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

---ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

SQL> ALTER DATABASE OPEN;

以上是“Oracle DG如何搭建”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！