oracle备库复制主库,搭建oracle主、备库（datagard）

DG的搭建

主库115 备库116

1.必须开启归档模式

QL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/archive

Oldest online log sequence     0

Next log sequence to archive   0

Current log sequence           32

2主库必须开启强制写日志

SQL> alter database force logging;SQL> select force_logging from v$database;

FOR

---

YES

3.修改主备数据库的参数文件及传输文件

SQL> create pfile from spfile;

[oracle@jokesql2 archive]$ vim /oracle/app/oracle/product/10.2.0/db_1/dbs/initTEST.ora

添加如下内容

DB_UNIQUE_NAME=TEST

LOG_ARCHIVE_CONFIG='DG_CONFIG=(trans115,trans116)'

LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/archive  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST'

LOG_ARCHIVE_DEST_2='SERVICE=trans116 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'

FAL_SERVER=trans115

FAL_CLIENT=trans116

STANDBY_FILE_MANAGEMENT=AUTO[oracle@jokesql2 archive]$ vim /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

TRANS115 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = TEST)

)

)

TRANS116 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = TEST)

)

)

两边的sys用户密码必须相同，开启lsnrctl，并测试互相连通

[oracle@jokesql2 ~]$ lsnrctl start

[oracle@jokesql2 ~]$sqlplus sys/123@trans116 as sysdba

[oracle@jokesql ~]$sqlplus sys/123@trans115 as sysdba

4.创建归档日志存放目录

[oracle@sq123 ~]$ mkdir /home/oracle/archive

5.修改备库的参数文件，把主库的参数文件拷贝到备库

修改3行

LOG_ARCHIVE_DEST_2='SERVICE=trans115 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'

FAL_SERVER=trans116

FAL_CLIENT=trans115

6.迁移主库数据库文件到备库，以下RMAN操作全部在主库执行

115上

创建备份目录

[oracle@jokesql2 ~]$ mkdir /home/oracle/db_bak1

RMAN> backup full database format='/home/oracle/db_bak1/%U' include current controlfile for standby;

Starting backup at 18-DEC-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=136 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/oracle/app/oradata/TEST/system01.dbf

input datafile fno=00003 name=/oracle/app/oradata/TEST/sysaux01.dbf

input datafile fno=00002 name=/oracle/app/oradata/TEST/undotbs01.dbf

input datafile fno=00005 name=/oracle/app/oradata/TEST/tbs_recover.dbf

input datafile fno=00006 name=/oracle/app/oradata/TEST/ts01.dbf

input datafile fno=00007 name=/oracle/app/oradata/TEST/ts02.dbf

input datafile fno=00008 name=/oracle/app/oradata/TEST/ts03.dbf

input datafile fno=00009 name=/oracle/app/oradata/TEST/ts04.dbf

input datafile fno=00010 name=/home/oracle/bbb.dbf

input datafile fno=00004 name=/oracle/app/oradata/TEST/users01.dbf

channel ORA_DISK_1: starting piece 1 at 18-DEC-12

channel ORA_DISK_1: finished piece 1 at 18-DEC-12

piece handle=/home/oracle/db_bak1/0mnt73tc tag=TAG20121218T231347 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including standby control file in backupset

channel ORA_DISK_1: starting piece 1 at 18-DEC-12

channel ORA_DISK_1: finished piece 1 at 18-DEC-12

piece handle=/home/oracle/db_bak1/0nnt740v tag=TAG20121218T231347 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 18-DEC-12

传输备份片文件到备库的相同备份目录下

oracle@192.168.18.116's password:

0fnt63ss                                              100%  539MB   7.5MB/s   01:12

0gnt63v7                                              100% 6944KB   6.8MB/s   00:01

RMAN> connect auxiliary sys/123@trans116

connected to auxiliary database: TEST (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

Starting Duplicate Db at 18-DEC-12

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=155 devtype=DISK

contents of Memory Script:

{

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 18-DEC-12

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/db_bak1/0pnt747m

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/home/oracle/db_bak1/0pnt747m tag=TAG20121218T231753

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05

output filename=/oracle/app/oradata/TEST/control01.ctl

output filename=/oracle/app/oradata/TEST/control02.ctl

output filename=/oracle/app/oradata/TEST/control03.ctl

Finished restore at 18-DEC-12

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

contents of Memory Script:

{

set newname for tempfile  1 to

"/oracle/app/oradata/TEST/temp01.dbf";

switch clone tempfile all;

set newname for datafile  1 to

"/oracle/app/oradata/TEST/system01.dbf";

set newname for datafile  2 to

"/oracle/app/oradata/TEST/undotbs01.dbf";

set newname for datafile  3 to

"/oracle/app/oradata/TEST/sysaux01.dbf";

set newname for datafile  4 to

"/oracle/app/oradata/TEST/users01.dbf";

set newname for datafile  5 to

"/oracle/app/oradata/TEST/tbs_recover.dbf";

set newname for datafile  6 to

"/oracle/app/oradata/TEST/ts01.dbf";

set newname for datafile  7 to

"/oracle/app/oradata/TEST/ts02.dbf";

set newname for datafile  8 to

"/oracle/app/oradata/TEST/ts03.dbf";

set newname for datafile  9 to

"/oracle/app/oradata/TEST/ts04.dbf";

set newname for datafile  10 to

"/home/oracle/bbb.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /oracle/app/oradata/TEST/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18-DEC-12

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=155 devtype=DISK

skipping datafile 6; already restored to file /oracle/app/oradata/TEST/ts01.dbf

skipping datafile 7; already restored to file /oracle/app/oradata/TEST/ts02.dbf

skipping datafile 8; already restored to file /oracle/app/oradata/TEST/ts03.dbf

skipping datafile 9; already restored to file /oracle/app/oradata/TEST/ts04.dbf

skipping datafile 10; already restored to file /home/oracle/bbb.dbf

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /oracle/app/oradata/TEST/system01.dbf

restoring datafile 00002 to /oracle/app/oradata/TEST/undotbs01.dbf

restoring datafile 00003 to /oracle/app/oradata/TEST/sysaux01.dbf

restoring datafile 00004 to /oracle/app/oradata/TEST/users01.dbf

restoring datafile 00005 to /oracle/app/oradata/TEST/tbs_recover.dbf

channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/db_bak1/0ont7451

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/home/oracle/db_bak1/0ont7451 tag=TAG20121218T231753

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55

Finished restore at 18-DEC-12

7.开启DG，并查看进程

115

SQL> select process from v$managed_standby;

PROCESS

---------

ARCH

ARCH

SQL> select error from v$archive_dest;

ERROR

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

10 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> select process from v$managed_standby;

PROCESS

---------

ARCH

ARCH

LNS

(已经有了进程，lns传输进程)

116上

SQL> alter database mount;

SQL>  select process from v$managed_standby;

PROCESS

---------

ARCH

ARCH

RFS

RFS (已经有了进程，rfs接收进程)

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

Database altered.

SQL> select process from v$managed_standby;

PROCESS

---------

ARCH

ARCH

RFS

RFS

MRP0

8.验证

主库115上(主库只有切换日志时，备库会同步，但主库日志变化 备库会记录到归档日志中)

SQL> create table t7 as select * from dba_objects;

Table created.

SQL> select count(*) from t7;

COUNT(*)

----------

49785

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

备库116上(只有把恢复日志进程取消，才可以打开数据库)

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select count(*) from t7;

COUNT(*)

----------

49785