Oracle的flashdg,Oracle DG搭建2(冷备方式)

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

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

(3) 主库：关闭闪回

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

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

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

(7) 主库：停库冷备

(8) 主库：生成standby controlfil

e

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

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

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

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

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

(14)备库：启动备库

(15)验证是否搭建成功

(16)主备切换

(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) 主库：修改参数文件

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 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)主从切换

主库：

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 recovered

Archived 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;

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！