Oracle 19c rac+adg

Oracle 19c rac+adg

rac与备库环境:

PrimaryStandby
DB Version19.3.019.3.0
HOST IP192.168.189.211 rac1 192.168.189.212 rac2192.168.189.209  std
SCAN IP192.168.189.215    rac-scan
DB_NAMEorclstd
DB_UNIQUE_NAMEorclstd
Instance_Nameorclstd
ArchiveFile+data/u01/arch/
DB StorageASMFileSystem
ASM for DB files+DATA/ORCL/DATAFILE//u01/app/oracle/oradata/std/datafile/
ASM for LOG files+DATA/orcl/onlinelog/u01/app/oracle/oradata/orcl/onlinelog
ASM for TEMP files+DATA/orcl/tempfile//u01/app/oracle/oradata/std/tempfile/
ORACLE_HOME/u01/app/oracle/product/19.3.0/db_1//u01/app/oracle/product/19.3.0/db_1/
OSRed Hat Enterprise Linux Server 7.4CentOS 7.6

1、rac主库开启归档

[oracle@rac1:/home/oracle]$sqlplus / as sysdba
SQL> startup mount;
SQL> alter system set log_archive_dest_1='location=+arch' scope=spfile;

System altered.

SQL> alter database archivelog; 

Database altered.

SQL> alter database force logging;

Database altered.

开启数据库
SQL> alter database open;

2、修改主库适应dataguard环境参数
当主库已开启force logging之后再进行如下操作

alter database force logging;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,std)' scope=both sid='*';         
alter system set log_archive_dest_1='LOCATION=+arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=std LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std' scope=both sid='*';
alter system set fal_client='orcl' scope=both sid='*';    
alter system set FAL_SERVER='std' scope=both sid='*';  
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/datafile','+DATA/orcl/datafile','/u01/app/oracle/oradata/orcl/tempfile','+DATA/orcl/tempfile' scope=spfile sid='*';  
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/onlinelog','+DATA/orcl/onlinelog' scope=spfile sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
alter system set PARALLEL_EXECUTION_MESSAGE_SIZE=8192 scope=spfile;
重启数据库

3、主库增加srl日志组

SQL> select bytes/1024/1024 mb from v$log;

        MB
----------
       300
       300
       300
       300

主库增加standby日志组:

主库时2组logfile,slr需要至少多一组,每个thread多一组

alter database add standby logfile thread 1 group 5 '+data' size 200m;
alter database add standby logfile thread 1 group 6 '+data' size 200m;
alter database add standby logfile thread 1 group 7 '+data' size 200m;
alter database add standby logfile thread 2 group 8 '+data' size 200m;
alter database add standby logfile thread 2 group 9 '+data' size 200m;
alter database add standby logfile thread 2 group 10 '+data' size 200m;

4、主库备库增加tns别名

主库tnsnamrs:
orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )



std =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = std) 
    )
  )
  
  
  备库tnsnames:
  orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAILOVER = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )


std =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = std) 
    )
  )
  

5、拷贝主库密码文件到备库

  ASMCMD> pwd
+data/orcl/password

ASMCMD> cp pwdorcl.256.998919277 /tmp/orapwstd
copying +data/orcl/password/pwdorcl.256.998919277 -> /tmp/orapwstd

将/tmp目录的密码文件拷贝到备库dbs目录

对参数文件,密码文件重命名符合备库实例命名规范
[oracle@std:/u01/app/oracle/product/18.3.0/db_1/dbs]$mv orapworcl1 orapwstd     
[oracle@std:/u01/app/oracle/product/18.3.0/db_1/dbs]$mv orcl.ora initstd.ora

6、备库创建实例目录

mkdir -p /u01/arch
mkdir -p /u01/app/oracle/oradata/std
mkdir -p /u01/app/oracle/oradata/std/datafile/
mkdir -p /u01/app/oracle/oradata/std/tempfile/
mkdir -p /u01/app/oracle/admin/std/adump/
mkdir -p /u01/app/oracle/oradata/std/onlinelog/

7、修改备库实例pfile文件

[oracle@std:/u01/app/oracle/product/18.3.0/db_1/dbs]$vi initstd.ora

*.audit_file_dest='/u01/app/oracle/admin/std/adump'
*.audit_trail='NONE'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/std/control01.ctl','/u01/app/oracle/oradata/std/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='std'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=1g
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sga_target=6g
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(std,orcl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=std'
*.LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_format='%t_%s_%r.arc'
*.FAL_SERVER='orcl'
*.fal_client='std'
*.FAL_SERVER='orcl'
*.DB_FILE_NAME_CONVERT='+DATA/ORCL/DATAFILE/','/u01/app/oracle/oradata/std/datafile/','+DATA/orcl/tempfile/','/u01/app/oracle/oradata/std/tempfile/'
*.LOG_FILE_NAME_CONVERT='+DATA/ORCL/ONLINELOG/','/u01/app/oracle/oradata/std/onlinelog/'
*.standby_file_management=AUTO

8、增加备库静态监听

[oracle@std:/u01/app/oracle/product/18.3.0/db_1/network/admin]$vi listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/18.3.0/db_1)
      (SID_NAME = std)
    )
  )
  
  
  
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521))
    )
  )  
  lsnrctl start

9、启动备库

  export ORACLE_SID=std
SQL> startup nomount;

10、主库准备连接辅助实例

[oracle@rac1 ~]$ rman target sys/oracle@orcl auxiliary sys/oracle@std
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jul 22 09:45:32 2019
Version 19.3.0.0.0

Copyright © 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1539821669)
connected to auxiliary database: ORCL (not mounted)

11、开始备库创建

执行脚本
run
{ 
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate AUXILIARY channel c7 type disk;
allocate AUXILIARY channel c8 type disk;
allocate AUXILIARY channel c9 type disk;
allocate AUXILIARY channel c10 type disk;
allocate AUXILIARY channel c11 type disk;
allocate AUXILIARY channel c12 type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
release channel c11;
release channel c12;
}

输出日志:

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=1711 instance=orcl1 device type=DISK

allocated channel: c2
channel c2: SID=384 instance=orcl1 device type=DISK

allocated channel: c3
channel c3: SID=1710 instance=orcl2 device type=DISK

allocated channel: c4
channel c4: SID=1712 instance=orcl1 device type=DISK

allocated channel: c5
channel c5: SID=1903 instance=orcl1 device type=DISK

allocated channel: c6
channel c6: SID=1903 instance=orcl2 device type=DISK

allocated channel: c7
channel c7: SID=961 device type=DISK

allocated channel: c8
channel c8: SID=1151 device type=DISK

allocated channel: c9
channel c9: SID=1338 device type=DISK

allocated channel: c10
channel c10: SID=6 device type=DISK

allocated channel: c11
channel c11: SID=196 device type=DISK

allocated channel: c12
channel c12: SID=388 device type=DISK

Starting Duplicate Db at 2019/07/22 09:46:00
current log archived

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/19.3.0/db_1/dbs/orapwstd'   ;
}
executing Memory Script

Starting backup at 2019/07/22 09:46:07
Finished backup at 2019/07/22 09:46:09

contents of Memory Script:
{
   restore clone from service  'orcl' standby controlfile;
}
executing Memory Script

Starting restore at 2019/07/22 09:46:09

channel c7: starting datafile backup set restore
channel c7: using network backup set from service orcl
channel c7: restoring control file
channel c7: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/std/control01.ctl
output file name=/u01/app/oracle/oradata/std/control02.ctl
Finished restore at 2019/07/22 09:46:15

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/app/oracle/oradata/std/tempfile/temp.263.1012040963";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/std/datafile/system.260.1012040943";
   set newname for datafile  2 to 
 "/u01/app/oracle/oradata/std/datafile/sysaux.261.1012040955";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/std/datafile/undotbs1.262.1012040961";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/std/datafile/undotbs2.264.1012040981";
   set newname for datafile  5 to 
 "/u01/app/oracle/oradata/std/datafile/users.265.1012040983";
   set newname for datafile  6 to 
 "/u01/app/oracle/oradata/std/datafile/benchmarksql.269.1012466397";
   restore
   from  nonsparse   from service 
 'orcl'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/std/tempfile/temp.263.1012040963 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

Starting restore at 2019/07/22 09:46:21

channel c7: starting datafile backup set restore
channel c7: using network backup set from service orcl
channel c7: specifying datafile(s) to restore from backup set
channel c7: restoring datafile 00001 to /u01/app/oracle/oradata/std/datafile/system.260.1012040943
channel c8: starting datafile backup set restore
channel c8: using network backup set from service orcl
channel c8: specifying datafile(s) to restore from backup set
channel c8: restoring datafile 00002 to /u01/app/oracle/oradata/std/datafile/sysaux.261.1012040955
channel c9: starting datafile backup set restore
channel c9: using network backup set from service orcl
channel c9: specifying datafile(s) to restore from backup set
channel c9: restoring datafile 00003 to /u01/app/oracle/oradata/std/datafile/undotbs1.262.1012040961
channel c10: starting datafile backup set restore
channel c10: using network backup set from service orcl
channel c10: specifying datafile(s) to restore from backup set
channel c10: restoring datafile 00004 to /u01/app/oracle/oradata/std/datafile/undotbs2.264.1012040981
channel c11: starting datafile backup set restore
channel c11: using network backup set from service orcl
channel c11: specifying datafile(s) to restore from backup set
channel c11: restoring datafile 00005 to /u01/app/oracle/oradata/std/datafile/users.265.1012040983
channel c12: starting datafile backup set restore
channel c12: using network backup set from service orcl
channel c12: specifying datafile(s) to restore from backup set
channel c12: restoring datafile 00006 to /u01/app/oracle/oradata/std/datafile/benchmarksql.269.1012466397
channel c11: restore complete, elapsed time: 00:00:14
channel c7: restore complete, elapsed time: 00:00:18
channel c9: restore complete, elapsed time: 00:00:54
channel c10: restore complete, elapsed time: 00:00:53
channel c8: restore complete, elapsed time: 00:01:05
channel c12: restore complete, elapsed time: 00:01:15
Finished restore at 2019/07/22 09:47:48

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'orcl' 
           archivelog from scn  8065198;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 2019/07/22 09:47:55

channel c7: starting archived log restore to default destination
channel c7: using network backup set from service orcl
channel c7: restoring archived log
archived log thread=1 sequence=54
channel c8: starting archived log restore to default destination
channel c8: using network backup set from service orcl
channel c8: restoring archived log
archived log thread=1 sequence=55
channel c9: starting archived log restore to default destination
channel c9: using network backup set from service orcl
channel c9: restoring archived log
archived log thread=1 sequence=56
channel c10: starting archived log restore to default destination
channel c10: using network backup set from service orcl
channel c10: restoring archived log
archived log thread=2 sequence=49
channel c11: starting archived log restore to default destination
channel c11: using network backup set from service orcl
channel c11: restoring archived log
archived log thread=2 sequence=50
channel c7: restore complete, elapsed time: 00:00:02
channel c8: restore complete, elapsed time: 00:00:01
channel c9: restore complete, elapsed time: 00:00:01
channel c10: restore complete, elapsed time: 00:00:00
channel c11: restore complete, elapsed time: 00:00:01
Finished restore at 2019/07/22 09:47:59

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1014284902 file name=/u01/app/oracle/oradata/std/datafile/system.260.1012040943
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1014284902 file name=/u01/app/oracle/oradata/std/datafile/sysaux.261.1012040955
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1014284902 file name=/u01/app/oracle/oradata/std/datafile/undotbs1.262.1012040961
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1014284902 file name=/u01/app/oracle/oradata/std/datafile/undotbs2.264.1012040981
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1014284902 file name=/u01/app/oracle/oradata/std/datafile/users.265.1012040983
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=1014284902 file name=/u01/app/oracle/oradata/std/datafile/benchmarksql.269.1012466397

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

executing command: SET until clause

Starting recover at 2019/07/22 09:48:00

starting media recovery

archived log for thread 1 with sequence 55 is already on disk as file /u01/arch/1_55_1012040933.arc
archived log for thread 1 with sequence 56 is already on disk as file /u01/arch/1_56_1012040933.arc
archived log for thread 2 with sequence 49 is already on disk as file /u01/arch/2_49_1012040933.arc
archived log for thread 2 with sequence 50 is already on disk as file /u01/arch/2_50_1012040933.arc
archived log file name=/u01/arch/1_55_1012040933.arc thread=1 sequence=55
archived log file name=/u01/arch/2_49_1012040933.arc thread=2 sequence=49
archived log file name=/u01/arch/2_50_1012040933.arc thread=2 sequence=50
archived log file name=/u01/arch/1_56_1012040933.arc thread=1 sequence=56
media recovery complete, elapsed time: 00:00:01
Finished recover at 2019/07/22 09:48:05

contents of Memory Script:
{
   delete clone force archivelog all;
}
executing Memory Script

deleted archived log
archived log file name=/u01/arch/1_54_1012040933.arc RECID=1 STAMP=1014284900
Deleted 1 objects

deleted archived log
archived log file name=/u01/arch/1_55_1012040933.arc RECID=2 STAMP=1014284900
Deleted 1 objects

deleted archived log
archived log file name=/u01/arch/2_49_1012040933.arc RECID=4 STAMP=1014284901
Deleted 1 objects

deleted archived log
archived log file name=/u01/arch/1_56_1012040933.arc RECID=3 STAMP=1014284901
Deleted 1 objects

deleted archived log
archived log file name=/u01/arch/2_50_1012040933.arc RECID=5 STAMP=1014284902
Deleted 1 objects

Finished Duplicate Db at 2019/07/22 09:48:35

released channel: c1

released channel: c2

released channel: c3

released channel: c4

released channel: c5

released channel: c6

released channel: c7

released channel: c8

released channel: c9

released channel: c10

released channel: c11

released channel: c12

12、检查备库开启MRP

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY


SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> select open_mode from v$database;                          

OPEN_MODE
--------------------
READ ONLY WITH APPLY
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值