oracle dataguard 搭建

原创 于 2023-09-17 10:15:04 发布 · 252 阅读 · 0 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
标签
#oracle #数据库

一、环境


         主机名                       ip                        实例名  唯一名
主库  oracle_dg1.test.com  218.168.129.64  orcl      orclpri
备库  oracle_dg2.test.com  218.168.129.65  orcl      orclstd

[root@oracle_dg1 ~]# uname -r
2.6.32-754.el6.x86_64

二、主库操作


2.1 确认主库是归档模式


select log_mode from v$database;

/*
改为归档模式：
1.正常停库
  shutdown immediate
2.启动到mount
  startup mount
3.转换为归档
  alter database archivelog;
4.打开数据库
  alter database open;
*/

2.2 修改参数文件


alter system set db_unique_name='orclpri' scope=spfile;
alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;
alter system set log_archive_config='dg_config=(orclpri,orclstd)';
alter system set fal_server=orclstd;
alter system set fal_client=orclpri;
alter system set log_archive_dest_2='service=orclstd lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orclstd';
alter system set log_archive_dest_state_2=ENABLE;
alter system set standby_file_management=AUTO;

如果主备库目录不一致，则需要修改：
db_file_name_convert
log_file_name_convert

shutdown immediate;
startup mount;
alter database force logging;
alter database archivelog;
alter database open;
create pfile from spfile;

2.3 配置网络字符串


[oracle@oracle_dg1 ~]$ cat /u01/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLPRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_dg1.test.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orclpri)
    )
  )

ORCLSTD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_dg2.test.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orclstd)
      (UR=A)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = scan_01.test.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

tnsping orclpri
tnsping orclstd

2.4 修改hosts


[root@oracle_dg1 ~]# cat /etc/hosts
218.168.129.64 oracle_dg1.test.com oracle_dg1
218.168.129.65 oracle_dg2.test.com oracle_dg2
218.168.129.66 scan_01.test.com scan_01

2.5 为备库创建pfile


SQL> create pfile from spfile;

--拷贝到备库
scp $ORACLE_HOME/dbs/initorcl.ora oracle_dg2:$ORACLE_HOME/dbs/


三.备库操作


3.1 修改hosts


[root@oracle_dg2 ~]# cat /etc/hosts
218.168.129.64 oracle_dg1.test.com oracle_dg1
218.168.129.65 oracle_dg2.test.com oracle_dg2
218.168.129.66 scan_01.test.com scan_01

3.2 修改参数文件


cd $ORACLE_HOME/dbs
vi initprod.ora           --修改以下内容
*.db_unique_name='orclstd'
log_archive_config='dg_config=(orclpri,orclstd)'
fal_server=orclpri
fal_client=orclstd
log_archive_dest_2='service=orclpri lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orclpri'


3.3 配置网络字符串


[oracle@oracle_dg2 ~]$ cat /u01/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLPRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_dg1.test.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orclpri)
    )
  )

ORCLSTD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_dg2.test.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orclstd)
      (UR=A)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = scan_01.test.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

tnsping orclpri
tnsping orclstd

2.4 启动备库到nomount状态，使用 rman duplicate 创建备用数据库


sqlplus / as syorclstda 
create spfile from pfile;
startup nomount;

rman target sys/oracle@orclpri auxiliary sys/oracle@orclstd nocatalog
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

Starting Duplicate Db at 2023-09-14 22:47:52
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format 
 '/u01/oracle/product/11.2.0/dbhome_1/dbs/orapworcl'   ;
}
executing Memory Script

Starting backup at 2023-09-14 22:47:52
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=198 device type=DISK
Finished backup at 2023-09-14 22:47:53

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/oracle/oradata/orcl/control01.ctl';
   restore clone controlfile to  '/u01/oracle/fast_recovery_area/orcl/control02.ctl' from 
 '/u01/oracle/oradata/orcl/control01.ctl';
}
executing Memory Script

Starting backup at 2023-09-14 22:47:53
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20230914T224726 RECID=1 STAMP=1147560447
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2023-09-14 22:47:54

Starting restore at 2023-09-14 22:47:54
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2023-09-14 22:47:55

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/oracle/oradata/orcl/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/oracle/oradata/orcl/system01.dbf";
   set newname for datafile  2 to 
 "/u01/oracle/oradata/orcl/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/oracle/oradata/orcl/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/oracle/oradata/orcl/users01.dbf";
   set newname for datafile  5 to 
 "/u01/oracle/oradata/orcl/uds.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/oracle/oradata/orcl/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/oracle/oradata/orcl/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/oracle/oradata/orcl/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/oracle/oradata/orcl/users01.dbf"   datafile 
 5 auxiliary format 
 "/u01/oracle/oradata/orcl/uds.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oracle/oradata/orcl/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

Starting backup at 2023-09-14 22:48:04
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/oracle/oradata/orcl/sysaux01.dbf
output file name=/u01/oracle/oradata/orcl/sysaux01.dbf tag=TAG20230914T224737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/oracle/oradata/orcl/system01.dbf
output file name=/u01/oracle/oradata/orcl/system01.dbf tag=TAG20230914T224737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/oracle/oradata/orcl/uds.dbf
output file name=/u01/oracle/oradata/orcl/uds.dbf tag=TAG20230914T224737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/oracle/oradata/orcl/undotbs01.dbf tag=TAG20230914T224737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oracle/oradata/orcl/users01.dbf
output file name=/u01/oracle/oradata/orcl/users01.dbf tag=TAG20230914T224737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2023-09-14 22:49:07

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/arch/1_224_1028042316.dbf" auxiliary format 
 "/arch/1_224_1028042316.dbf"   archivelog like 
 "/arch/1_225_1028042316.dbf" auxiliary format 
 "/arch/1_225_1028042316.dbf"   ;
   catalog clone archivelog  "/arch/1_224_1028042316.dbf";
   catalog clone archivelog  "/arch/1_225_1028042316.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 2023-09-14 22:49:07
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=224 RECID=217 STAMP=1147560460
output file name=/arch/1_224_1028042316.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=225 RECID=218 STAMP=1147560520
output file name=/arch/1_225_1028042316.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2023-09-14 22:49:09

cataloged archived log
archived log file name=/arch/1_224_1028042316.dbf RECID=1 STAMP=1147560549

cataloged archived log
archived log file name=/arch/1_225_1028042316.dbf RECID=2 STAMP=1147560549

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1147560549 file name=/u01/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1147560549 file name=/u01/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1147560549 file name=/u01/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1147560549 file name=/u01/oracle/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1147560549 file name=/u01/oracle/oradata/orcl/uds.dbf

contents of Memory Script:
{
   set until scn  11642419;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2023-09-14 22:49:09
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 224 is already on disk as file /arch/1_224_1028042316.dbf
archived log for thread 1 with sequence 225 is already on disk as file /arch/1_225_1028042316.dbf
archived log file name=/arch/1_224_1028042316.dbf thread=1 sequence=224
archived log file name=/arch/1_225_1028042316.dbf thread=1 sequence=225
media recovery complete, elapsed time: 00:00:00
Finished recover at 2023-09-14 22:49:10
Finished Duplicate Db at 2023-09-14 22:49:14

--数据库自动启动到mount状态
SQL> select name,db_unique_name,database_role,protection_mode,open_mode,force_logging,switchover_status,flashback_on from v$database;
NAME      DB_UNIQUE_NAME                 DATABASE_ROLE                    PROTECTION_MODE                          OPEN_MODE                                FORCE_ SWITCHOVER_STATUS            FLASHBACK_ON
--------- ------------------------------ -------------------------------- ---------------------------------------- ---------------------------------------- ------ ---------------------------------------- ------------------------------------
ORCL      orclstd                        PHYSICAL STANDBY                 MAXIMUM PERFORMANCE                      MOUNTED                                  YES    NOT ALLOWED 

--开启闪回
SQL> alter database flashback on;   
SQL> alter database recover managed standby database disconnect from session; 

2.5 为两数据库分别创建standby日志文件


主库
alter database add standby logfile group 5 '/u01/oracle/oradata/orcl/standby_group_05.log' size 500M;
alter database add standby logfile group 6 '/u01/oracle/oradata/orcl/standby_group_06.log' size 500M;
alter database add standby logfile group 7 '/u01/oracle/oradata/orcl/standby_group_07.log' size 500M;
alter database add standby logfile group 8 '/u01/oracle/oradata/orcl/standby_group_08.log' size 500M;

备库
alter database recover managed standby database cancel;
alter database add standby logfile group 5 '/u01/oracle/oradata/orcl/standby_group_05.log' size 500M;
alter database add standby logfile group 6 '/u01/oracle/oradata/orcl/standby_group_06.log' size 500M;
alter database add standby logfile group 7 '/u01/oracle/oradata/orcl/standby_group_07.log' size 500M;
alter database add standby logfile group 8 '/u01/oracle/oradata/orcl/standby_group_08.log' size 500M;
--启动实时应用
alter database recover managed standby database using current logfile disconnect from session;

SQL> select name,db_unique_name,database_role,protection_mode,open_mode,force_logging,switchover_status,flashback_on from v$database;
NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE      OPEN_MODE            FOR SWITCHOVER_STATUS    FLASHBACK_ON
--------- ------------------------------ ---------------- -------------------- -------------------- --- -------------------- ------------------
ORCL      orclstd                        PHYSICAL STANDBY MAXIMUM PERFORMANCE  READ ONLY WITH APPLY YES NOT ALLOWED          YES


 

韩量超标
关注
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值