Oracle DG搭建是怎样的

这期内容当中小编将会给大家带来有关Oracle DG搭建是怎样的，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

Oracle DG搭建(冷备方式)

(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#SBYDB00426Example 3-1 Primary Database: Primary Role Initialization ParametersExample 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 spfileboston.ora.bak

SQL> shutdown immediate

SQL> create spfile from pfile;

SQL> startup

SQL> show parameter log_archive_dest_2

(7)主库：停库冷备

SQL> shutdown immediatre

[oracle@chen admin]$ cd /u01/app/oracle/oradata/chicago

[oracle@chen PROD1]$ tar -zcvf /home/oracle/dg/chicago.tar.gz *

(8)主库：生成standby controlfile 

SQL> startup mount;

SQL> alter database create standby controlfile as /home/oracle/dg/standby_control01.ctl;

(9) 主库: 拷贝主库文件到备库冷备文件，监听文件，TNS文件，参数文件、密码文件

[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 PROD1]$ cp chicago.tar.gz /home/oracle/dg

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

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

(10) 备库：配置静态监听,配置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@edbjr2p2 admin]$ lsnrctl start

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

[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

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

[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

(13)备库：解压备份文件

[oracle@jch dg]$ cd /u01/app/oracle/oradata/boston/

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

(14)备库：启动备库

SQL> startup nomount;

[oracle@jch dg]$ export ORACLE_SID=boston

[oracle@jch dg]$ rman target / 

rman>restore controlfile from /home/oracle/control_standby.ctl;

SQL> alter database mount;

SQL> alter database open;

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

(15)验证是否搭建成功

主库：

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;

(16)主从切换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搭建是怎样的了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注行业资讯频道。