1、调整强制归档：

SQL> select force_logging from v$database;

FOR

---

NO

2 RAC开启归档

SQL> alter system set log_archive_dest_1='LOCATION=+DATA' scope=spfile sid='RACDB1';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=+DATA' scope=spfile sid='RACDB2';

System altered.

[root@rac1 ~]# /u01/gridhome/11g/bin/srvctl stop database -d RACDB

SQL> conn / as sysdba

Connected to an idle instance.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 634679296 bytes

Fixed Size 2255912 bytes

Variable Size 490734552 bytes

Database Buffers 138412032 bytes

Redo Buffers 3276800 bytes

Database mounted.

SQL> alter database archivelog

2 ;

Database altered.

SQL> alter database open;

Database altered.

SQL>

[root@rac1 ~]# /u01/gridhome/11g/bin/srvctl stop database -d RACDB

[root@rac1 ~]# /u01/gridhome/11g/bin/srvctl start database -d RACDB

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +DATA

Oldest online log sequence 5

Next log sequence to archive 6

Current log sequence 6

SQL> alter system switch logfile;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL>

[grid@rac1 ~]$ asmcmd

ASMCMD> ls

thread_1_seq_6.269.888078345

thread_2_seq_2.268.888078005

thread_2_seq_3.270.888078429

ASMCMD> ls

thread_1_seq_6.269.888078345

thread_1_seq_7.272.888078581

thread_2_seq_2.268.888078005

thread_2_seq_3.270.888078429

thread_2_seq_4.271.888078581

3 配置主备库的监听，修改tnsnames.ora 和 listener.ora 文件

[oracle@rac1 admin]$ vi tnsnames.ora

RACDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = racscan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = RACDB)

)

RACDB1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.71)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = RACDB)

)

RACDB2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.72)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = RACDB)

)

RACDB_DG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.66 )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = RACDB)

)

用net manager 工具，在备库创建一个监听。也可以手动的在listener.ora 文件里添加如下内容：

[oracle@racdg admin]$ vi listener.ora

# listener.ora Network Configuration File: /u01/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

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

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = RACDB)

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

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = racdg)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

ADR_BASE_LISTENER = /u01/oracle

注意：SID_LIST_LISTENER 配置的是静态注册，如果没有该参数，而且Data Guard 启动顺序又不正确，那么在主库可能会报 PING[ARC1]: Heartbeat failed to connect to standby 'RACDB_DG'. Error is 12514. 错误，导致归档无法完成。

4 在主库上创建备库的密码文件和控制文件，并将文件传到备库的相关位置

[oracle@rac1 ~]$ cd /u01/oracle/11g/dbs/

[oracle@rac1 dbs]$ scp orapwRACDB1 192.168.56.66:/u01/oracle/product/11.2.0/db_1/dbs

备库修改口令文件名称

[oracle@racdg ~]$ cd $ORACLE_HOME/dbs

[oracle@racdg dbs]$ mv orapwRACDB1 orapwRACDB

5 调整主库初始化参数：

alter system set log_archive_config='DG_CONFIG=(RACDB,RACDB_DG)'; --db_unique_name

alter system set log_archive_dest_1='location=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RACDB';

alter system set log_archive_dest_2='SERVICE=RACDB_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACDB';

#ASYNC为异步同步 SYNC同步

alter system set standby_file_management=auto scope=both;

alter system set fal_server=RACDB_DG scope=both;--备库链接字符串

alter system set fal_client=RACDB scope=both;--主库链接字符串

下面两个参数只能修改spfile，然后重启数据库：

alter system set db_file_name_convert='+data/racdb/DATAFILE/','/u01/oracle/oradata/RACDB/' scope=spfile;

alter system set log_file_name_convert='+data/racdb/ONLINELOG','/u01/oracle/oradata/RACDB/' scope=spfile;

启动和关闭数据库：

[root@rac1 ~]# /u01/gridhome/11g/bin/srvctl stop instance -d RACDB -i RACDB1,RACDB2

[root@rac1 ~]# /u01/gridhome/11g/bin/srvctl start instance -d RACDB -i RACDB1,RACDB2

6 备份主数据

create pfile='/u01/oracle/rman_bak/init$ORACLE_SID.ora' from spfile;

rman target /

run {

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup incremental level 0 format '/u01/oracle/rman_bak/inr0_%U.bak' tag 'full_bak_for_standby' database plus archivelog;

release channel c1;

release channel c2;

}

backup format '/u01/oracle/rman_bak/control01.ctl' current controlfile for standby;

--拷贝备份到standby节点

scp -rp /u01/oracle/rman_bak/ 192.168.56.72:/u01/oracle/

7 修改参数文件：

修改前：

RACDB2.__db_cache_size=197132288

RACDB1.__db_cache_size=201326592

RACDB2.__java_pool_size=4194304

RACDB1.__java_pool_size=4194304

RACDB2.__large_pool_size=8388608

RACDB1.__large_pool_size=8388608

RACDB1.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment

RACDB2.__pga_aggregate_target=260046848

RACDB1.__pga_aggregate_target=255852544

RACDB2.__sga_target=377487360

RACDB1.__sga_target=381681664

RACDB2.__shared_io_pool_size=0

RACDB1.__shared_io_pool_size=0

RACDB2.__shared_pool_size=159383552

RACDB1.__shared_pool_size=159383552

RACDB2.__streams_pool_size=0

RACDB1.__streams_pool_size=0

*.audit_file_dest='/u01/oracle/admin/RACDB/adump'

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.4.0'

*.control_files='+DATA/racdb/controlfile/current.260.887983445'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_file_name_convert='+data/racdb/DATAFILE','/u01/oracle/oradata/RACDB/'

*.db_name='RACDB'

*.diagnostic_dest='/u01/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACDBXDB)'

*.fal_client='RACDB'

*.fal_server='RACDB_DG'

RACDB2.instance_number=2

RACDB1.instance_number=1

*.log_archive_config='DG_CONFIG=(RACDB,RACDB_DG)'

RACDB1.log_archive_dest_1='LOCATION=+DATA'

RACDB2.log_archive_dest_1='LOCATION=+DATA'

*.log_archive_dest_1='location=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RACDB'

*.log_archive_dest_2='SERVICE=RACDB_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACDB'

*.log_file_name_convert='+data/racdb/ONLINELOG','/u01/oracle/oradata/RACDB/'

*.memory_target=635437056

*.open_cursors=300

*.processes=150

*.remote_listener='racscan:1521'

*.remote_login_passwordfile='exclusive'

*.standby_file_management='AUTO'

RACDB2.thread=2

RACDB1.thread=1

RACDB2.undo_tablespace='UNDOTBS2'

RACDB1.undo_tablespace='UNDOTBS1'

修改后：

*.__db_cache_size=201326592

*.__java_pool_size=4194304

*.__large_pool_size=8388608

*.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment

*.__pga_aggregate_target=255852544

*.__sga_target=381681664

*.__shared_io_pool_size=0

*.__shared_pool_size=159383552

*.__streams_pool_size=0

*.audit_file_dest='/u01/oracle/admin/RACDB/adump'

*.audit_trail='db'

#*.cluster_database=true

*.compatible='11.2.0.4.0'

*.control_files='/u01/oracle/oradata/RACDB/control01.ctl','/u01/oracle/oradata/RACDB/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='/u01/oracle/oradata/RACDB/'

*.db_domain=''

*.db_file_name_convert='+data/racdb/DATAFILE','/u01/oracle/oradata/RACDB/'

*.db_name='RACDB'

*.diagnostic_dest='/u01/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACDBXDB)'

*.fal_client='RACDB_DG'

*.fal_server='RACDB'

#RACDB2.instance_number=2

#RACDB1.instance_number=1

*.log_archive_config='DG_CONFIG=(RACDB_DG,RACDB)'

#RACDB1.log_archive_dest_1='LOCATION=+DATA'

#RACDB2.log_archive_dest_1='LOCATION=+DATA'

*.log_archive_dest_1='location=/u01/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RACDB'

*.log_archive_dest_2='SERVICE=RACDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACDB'

*.log_file_name_convert='+data/racdb/ONLINELOG','/u01/oracle/oradata/RACDB/'

*.memory_target=635437056

*.open_cursors=300

*.processes=150

#*.remote_listener='racscan:1521'

*.remote_login_passwordfile='exclusive'

*.standby_file_management='AUTO'

#RACDB2.thread=2

#RACDB1.thread=1

#RACDB2.undo_tablespace='UNDOTBS2'

*.undo_tablespace='UNDOTBS1'

8 恢复备库：

启动到nomount

SQL> startup pfile='/u01/oracle/rman_bak/initRACDB.ora' nomount;

ORACLE instance started.

Total System Global Area 634679296 bytes

Fixed Size 2255912 bytes

Variable Size 427819992 bytes

Database Buffers 201326592 bytes

Redo Buffers 3276800 bytes

SQL>

释放控制文件

RMAN> restore controlfile from '/u01/oracle/rman_bak/control01.ctl';

Starting restore at 25-AUG-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/oracle/oradata/RACDB/control01.ctl

output file name=/u01/oracle/oradata/RACDB/control02.ctl

Finished restore at 25-AUG-15

[oracle@racdg dbs]$ rman target sys/oracle@RACDB auxiliary /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 25 12:21:04 2015

connected to target database: RACDB (DBID=885600213)

connected to auxiliary database: RACDB (not mounted)

RMAN> catalog start with '/u01/oracle/rman_bak';

RMAN> duplicate target database for standby nofilenamecheck;

9 启动备库到dg模式

SQL> select name,database_role from gv$database;

NAME DATABASE_ROLE

--------- --------------------------------

RACDB PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

col name for a50

select SEQUENCE#,first_time,next_time,APPLIED, THREAD# from v$archived_log order by SEQUENCE#,THREAD#;

10 主备库添加standby 日志组

主库

alter database add standby logfile thread 1 group 5 '+DATA' size 50m;

alter database add standby logfile thread 1 group 6 '+DATA' size 50m;

alter database add standby logfile thread 2 group 7 '+DATA' size 50m;

alter database add standby logfile thread 2 group 8 '+DATA' size 50m;

备库

alter database add standby logfile thread 1 group 5 '/u01/oracle/oradata/RACDB/redo05.log' size 50m;

alter database add standby logfile thread 1 group 6 '/u01/oracle/oradata/RACDB/redo06.log' size 50m;

alter database add standby logfile thread 2 group 7 '/u01/oracle/oradata/RACDB/redo07.log' size 50m;

alter database add standby logfile thread 2 group 8 '/u01/oracle/oradata/RACDB/redo08.log' size 50m;

11 #检查主备状态

主库:alter system switch logfile;alter system archive log current;

备库:

col name for a50

select SEQUENCE#,first_time,next_time,APPLIED, THREAD# from v$archived_log order by SEQUENCE#,THREAD#;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

12 切换到adg

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY

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

Database altered.

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 RACDB

13检察主备库状态

主库

SQL> conn scott/tiger

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

SQL> create table t as select * from emp;

Table created.

SQL> insert into t select * from t;

14 rows created.

SQL> commit;

Commit complete.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

T

备库：

SQL> select TABLE_NAME from dba_tables where owner='SCOTT';

TABLE_NAME

------------------------------

DEPT

EMP

SALGRADE

T

BONUS

SQL> conn scott/tiger

Connected.

SQL> select count(*) from t;

COUNT(*)

----------

28

SQL>

--主备库

rman target /

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

ITPub博客

DG RAC - 单点搭建

最新文章