搭建oracle主、备库（datagard）

root

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

同时赞一个

a13698822086

流氓不可怕，就怕流氓有文化。

孤独750

吃饭与ml是第一生产力。

k668

看尽天下A片，心中自然无码～

angela

长大了娶唐僧做老公，能玩就玩一玩，不能玩就把他吃掉。

linghaiyan

过来看看的

dream789

沒有激情的亲吻，哪來床上的翻滾？