oracle 19c RAC部署ADG手顺

oracle 19c RAC部署ADG手顺

1、DG基础环境

ORACLE 主库RAC，scan-IP：111.111.111.155? SID:leodb? ?db_name=‘leodb‘? ? ?db_unique_name=leodb?主机名：rac19c-01、rac19c-02

ORACLE 备库? ? ? ? ? IP：111.111.111.150? SID:leodbadg? ?db_name=‘leodb‘? ? db_unique_name=leodbadg?主机名：19c-adg

主库归档目录物理路径：

SQL> archive log list;

Database log mode? ? ? ?Archive Mode

Automatic archival? ? ? ?Enabled

Archive destination? ? ? ?USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence? ? ?7

Next log sequence to archive? ?8

Current log sequence? ? ? ?8

+DATA/LEODB/FLASHBACK

主库DATAFILE物理路径

+DATA/LEODB/DATAFILE

主库REDO物理路径

+DATA/LEODB/ONLINELOG

主库字符集

SQL> select * from nls_database_parameters;

PARAMETER? ? ? ?VALUE

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

NLS_NCHAR_CHARACTERSET? ? ? ?AL16UTF16

NLS_CHARACTERSET? ? ? ?AL32UTF8

2、修改主库配置文件initLEODB.ora

这里现在数据库里修改相关的参数，与DG的参数就只与几个参数相关，大概就是日志，文件的位置的转换，GAP的处理，其实GAP已经会自动的处理，不过这里我们还是介绍配置FAL_SERVER,FAL_CLIENT参数。在修改完之后重新创建了pfile文件

先创建spfile，修改完后重新生成pfile

SQL> alter system set LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(leodb,leodbadg)‘;

SQL> alter system set LOG_ARCHIVE_DEST_1=‘LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=leodb‘;

SQL> alter system set LOG_ARCHIVE_DEST_2=‘SERVICE=leodbadg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=leodbadg‘;

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

SQL> alter system set FAL_SERVER=leodbadg;

SQL> alter system set FAL_CLIENT=leodb;

SQL> alter system set DB_FILE_NAME_CONVERT=‘/u01/app/oracle/oradata/leodbadg/‘,‘+DATA/LEODB/DATAFILE/‘ scope=spfile;

SQL> alter system set LOG_FILE_NAME_CONVERT=‘/u01/app/oracle/oradata/leodbadg/‘,‘+DATA/LEODB/ONLINELOG/‘ scope=spfile;

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

SQL> create pfile=‘/home/oracle/initLEODB.ora‘ from spfile;

DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数必须重启数据库生效

下面是备份的主库pfile文件

leodb1.__data_transfer_cache_size=0

leodb2.__data_transfer_cache_size=0

leodb1.__db_cache_size=398458880

leodb2.__db_cache_size=394264576

leodb1.__inmemory_ext_roarea=0

leodb2.__inmemory_ext_roarea=0

leodb1.__inmemory_ext_rwarea=0

leodb2.__inmemory_ext_rwarea=0

leodb1.__java_pool_size=0

leodb2.__java_pool_size=0

leodb1.__large_pool_size=4194304

leodb2.__large_pool_size=4194304

leodb1.__oracle_base=‘/u01/app/oracle‘#ORACLE_BASE set from environment

leodb2.__oracle_base=‘/u01/app/oracle‘#ORACLE_BASE set from environment

leodb1.__pga_aggregate_target=264241152

leodb2.__pga_aggregate_target=264241152

leodb1.__sga_target=792723456

leodb2.__sga_target=792723456

leodb1.__shared_io_pool_size=29360128

leodb2.__shared_io_pool_size=29360128

leodb1.__shared_pool_size=343932928

leodb2.__shared_pool_size=348127232

leodb1.__streams_pool_size=0

leodb2.__streams_pool_size=0

leodb1.__unified_pga_pool_size=0

leodb2.__unified_pga_pool_size=0

*.audit_file_dest=‘/u01/app/oracle/admin/leodb/adump‘

*.audit_trail=‘db‘

*.cluster_database=TRUE

*.compatible=‘19.0.0‘

*.control_files=‘+DATA/LEODB/CONTROLFILE/current.261.1056020927‘

*.db_block_size=8192

*.db_create_file_dest=‘+DATA‘

*.db_file_name_convert=‘/u01/app/oracle/oradata/leodbadg/‘,‘+DATA/LEODB/DATAFILE/‘

*.db_name=‘leodb‘

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest=‘+DATA‘

*.diagnostic_dest=‘/u01/app/oracle‘

*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=leodbXDB)‘

*.fal_client=‘LEODB‘

*.fal_server=‘LEODBADG‘

family:dw_helper.instance_mode=‘read-only‘

leodb1.instance_number=1

leodb2.instance_number=2

*.local_listener=‘-oraagent-dummy-‘

*.log_archive_config=‘DG_CONFIG=(leodb,leodbadg)‘

*.log_archive_dest_1=‘LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=leodb‘

*.log_archive_dest_2=‘SERVICE=leodbadg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=leodbadg‘

*.log_archive_dest_state_1=‘ENABLE‘

*.log_file_name_convert=‘/u01/app/oracle/oradata/leodbadg/‘,‘+DATA/LEODB/ONLINELOG/‘

*.nls_language=‘AMERICAN‘

*.nls_territory=‘AMERICA‘

*.open_cursors=300

*.pga_aggregate_target=252m

*.processes=300

*.remote_login_passwordfile=‘exclusive‘

*.sga_target=756m

*.standby_file_management=‘AUTO‘

leodb2.thread=2

leodb1.thread=1

leodb1.undo_tablespace=‘UNDOTBS1‘

leodb2.undo_tablespace=‘UNDOTBS2‘

3、备库服务器配置一般和主库存在差异，建议先在备库根据实际环境创建一个数据库（开启归档和闪回，db_name、字符集要与主库一致），然后保留pfile删库，修改备库的配置文件：initLEODBADG.ora

create pfile=‘/home/oracle/initLEODBADG.ora‘ from spfile;

下面是新增了dataguard部分的参数后的pfile文件

leodbadg.__data_transfer_cache_size=0

leodbadg.__db_cache_size=2013265920

leodbadg.__inmemory_ext_roarea=0

leodbadg.__inmemory_ext_rwarea=0

leodbadg.__java_pool_size=0

leodbadg.__large_pool_size=16777216

leodbadg.__oracle_base=‘/u01/app/oracle/‘#ORACLE_BASE set from environment

leodbadg.__pga_aggregate_target=922746880

leodbadg.__sga_target=2734686208

leodbadg.__shared_io_pool_size=134217728

leodbadg.__shared_pool_size=553648128

leodbadg.__streams_pool_size=0

leodbadg.__unified_pga_pool_size=0

*.audit_file_dest=‘/u01/app/oracle/admin/leodbadg/adump‘

*.audit_trail=‘db‘

*.compatible=‘19.0.0‘

*.control_files=‘/u01/app/oracle/oradata/leodbadg/control01.ctl‘,‘/u01/app/oracle/fast_recovery_area/leodbadg/control02.ctl‘

*.db_block_size=8192

*.db_name=‘leodb‘

*.db_recovery_file_dest=‘/u01/app/oracle/fast_recovery_area‘

*.db_recovery_file_dest_size=8256m

*.diagnostic_dest=‘/u01/app/oracle‘

*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=leodbadgXDB)‘

*.log_archive_format=‘%t_%s_%r.dbf‘

*.nls_language=‘AMERICAN‘

*.nls_territory=‘AMERICA‘

*.open_cursors=300

*.pga_aggregate_target=868m

*.processes=300

*.remote_login_passwordfile=‘EXCLUSIVE‘

*.sga_target=2604m

*.undo_tablespace=‘UNDOTBS1‘

#For data guard

*.db_unique_name=‘leodbadg‘

*.LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(leodb,leodbadg)‘

*.LOG_ARCHIVE_DEST_1=‘LOCATION=/u01/app/oracle/fast_recovery_area/leodbadg/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=leodbadg‘

*.LOG_ARCHIVE_DEST_2=‘SERVICE=leodb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=leodb‘

*.FAL_SERVER=leodb

*.FAL_CLIENT=leodbadg

*.DB_FILE_NAME_CONVERT=‘+DATA/LEODB/DATAFILE/‘,‘/u01/app/oracle/oradata/leodbadg/‘

*.LOG_FILE_NAME_CONVERT=‘+DATA/LEODB/ONLINELOG/‘,‘/u01/app/oracle/oradata/leodbadg/‘

*.STANDBY_FILE_MANAGEMENT=AUTO

创建相关目录

mkdir -p /u01/app/oracle/admin/leodbadg/adump

mkdir -p /u01/app/oracle/oradata/leodbadg/

mkdir -p /u01/app/oracle/fast_recovery_area/leodbadg/archivelog

注意：

1、Linux端配置的pfile文件中，所有windows的路径都要用大写，因为在duplication过程中，windows端都是按照大写路径来传输的！

如果用小写或者大小写混合，则无法识别路径，会有问题！

2、在duplication过程中，虽然是在主库操作，但是datafile和logfile的路径转换却认的是备库的pfile文件中的转换路径！

db_file_name_convert参数

因为rac是用的asm的，standby用的文件系统，目录不一样，需要转换。

所以了解了一下log_file_name_convert、db_file_name_convert参数。

1> 参数的作用：

db_file_name_convert 主数据库和备用数据库的数据文件转换目录对映（如果两数据库的目录结构不一样），

如果有多个对映，逐一指明对映关系。

2> 该参数应该配置在主还是备？

备库，这个参数不能动态配置，需要重启实例，如果在主库配置需要重启主库，对于不能停的数据库是不允许的。

3> 该参数的写法：

db_file_name_convert=‘+RACDATA/racdb/datafile/‘,‘/oradata/standby/‘

6、? 修改主库的listener.ora文件（grid用户执行） ------如果要主备切换演练，则要做。如果不做切换后的新备库无法从新主库接收数据。

1节点添加如下内容：

SID_LIST_LISTENER =

? (SID_LIST =

? ? (SID_DESC =?

? ? ? (GLOBAL_DBNAME = leodb)?

? ? ? (ORACLE_HOME = /u01/app/19.3.0/grid)?

? ? ? (SID_NAME = leodb1)?

? ? )

? )

??

2节点添加如下内容：

SID_LIST_LISTENER =

? (SID_LIST =

? ? (SID_DESC =?

? ? ? (GLOBAL_DBNAME = leodb)?

? ? ? (ORACLE_HOME = /u01/app/19.3.0/grid)?

? ? ? (SID_NAME = leodb2)?

? ? )

? )

重启监听

srvctl stop listener -n rac19c-01

srvctl stop listener -n rac19c-02

srvctl start listener -n rac19c-01

srvctl start listener -n rac19c-02

7、? 修改主库的tnsnames.ora文件

增加DG备库连接串（RAC环境所有节点都要配置，oracle用户进入ORACLE_HOME/network/admin）

LEODBADG =

? (DESCRIPTION =

? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 19c-adg)(PORT = 1521))

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = leodbadg)

? ? )

? )

8、? 修改备库的listener.ora文件

# listener.ora Network Configuration File: /u01/app/oracle//product/19.3.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

? (DESCRIPTION_LIST =

? ? (DESCRIPTION =

? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 19c-adg)(PORT = 1521))

? ? )

? )

SID_LIST_LISTENER =

? (SID_LIST =

? ? (SID_DESC =

? ? ? (GLOBAL_DBNAME = leodbadg)

? ? ? (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)

? ? ? (SID_NAME = leodbadg)

? ? )

? )

--SID_LIST_LISTENER部分为新增内容，GLOBAL_DBNAME不能少，少的了话后面主库rman连接过来会报错，本次就发生了这个问题，补上这一行就好了。

--重启监听

9、? 修改备库的tnsnames.ora文件

LEODBADG =

? (DESCRIPTION =

? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 19c-adg)(PORT = 1521))

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = leodbadg)

? ? )

? )

LISTENER_IPDBADG =

? (ADDRESS = (PROTOCOL = TCP)(HOST = 19c-adg)(PORT = 1521))

LEODB =

(DESCRIPTION =

? ? (ADDRESS = (PROTOCOL = TCP)(HOST = rac19c-scan)(PORT = 1521))

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = leodb)

? ? )

? )

??

LEODB1 =

(DESCRIPTION =

? ? (ADDRESS = (PROTOCOL = TCP)(HOST = rac19c-01)(PORT = 1521))

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = leodb)

? ? ? (INSTANCE_NAME = leodb1)

? ? )

? )

LEODB2 =

(DESCRIPTION =

? ? (ADDRESS = (PROTOCOL = TCP)(HOST = rac19c-02)(PORT = 1521))

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = leodb)

? ? ? (INSTANCE_NAME = leodb2)

? ? )

? )

??

orapwd file=orapwLEODBADG password=oracle（为防止密码问题导致无法访问，最好直接把主库的orapw文件拷贝过来然后更名orapwSID就可以了）

12c rac密码文件默认存放在asm上，不再是$ORACLE_HOME/dbs目录下

ASMCMD> pwcopy +DATA/LEODB/PASSWORD/pwdleodb.256.1056020773 /home/grid/orapwLEODBADG

copying +DATA/LEODB/PASSWORD/pwdleodb.256.1056020773 -> /home/grid/orapwLEODBADG

然后拷贝到备机$ORACLE_HOME/dbs目录下

主库和备库都启动监听：lsnrctl start

10、设置服务器为归档模式

alter database archivelog

设置主数据库为日志强制写状态

alter database force logging;

查看状态日志强制写状态为YES

select log_mode,force_logging from v$database;

LOG_MODE? ? ?FORCE_LOGGING

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

ARCHIVELOG? ?YES

11、查看主库数据库的日志组个数与大小，因为我们创建standby日志组的个数是每个节点日志组个数+1再与thread的积，size不能小于原日志文件的大小。

SQL> select group#,THREAD#,bytes/1024/1024 from v$log;

? ? GROUP#? ? THREAD# BYTES/1024/1024

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

?1? ? 1? 200

?2? ? 1? 200

?3? ? 2? 200

?4? ? 2? 200

SQL> select member from v$logfile;

MEMBER

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

+DATA/LEODB/ONLINELOG/group_2.263.1056020931

+DATA/LEODB/ONLINELOG/group_1.262.1056020931

+DATA/LEODB/ONLINELOG/group_3.266.1056021723

+DATA/LEODB/ONLINELOG/group_4.267.1056021725

12、创建standby日志组，位置与原日志组相同的路径。创建完成后查询是否成功? 创建6个standby 日志组

SQL> alter database add standby logfile ‘+DATA/LEODB/ONLINELOG/standby01.log‘ size 200m;

alter database add standby logfile ‘+DATA/LEODB/ONLINELOG/standby01.log‘ size 200m;

alter database add standby logfile ‘+DATA/LEODB/ONLINELOG/standby02.log‘ size 200m;

alter database add standby logfile ‘+DATA/LEODB/ONLINELOG/standby03.log‘ size 200m;

alter database add standby logfile ‘+DATA/LEODB/ONLINELOG/standby04.log‘ size 200m;

alter database add standby logfile ‘+DATA/LEODB/ONLINELOG/standby05.log‘ size 200m;

alter database add standby logfile ‘+DATA/LEODB/ONLINELOG/standby06.log‘ size 200m;

col member for a60

select group#,status,type,member from v$logfile;

13、使用之前创建的initLEODBADG.ora文件启动备库到nomount状态

SELECT ROUND(SUM(BYTES)/1024/1024/1024,2)||‘GB‘ FROM DBA_DATA_FILES;

源环境datafile大小：1.87GB?

**************************************************报错信息**************************************************

[oracle@rac19c-01 admin]$ rman target sys/oracle auxiliary sys/oracle@LEODBADG

Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 20 23:22:37 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.? All rights reserved.

connected to target database: LEODB (DBID=2839349055)

connected to auxiliary database (not started)

RMAN> duplicate target database for standby nofilenamecheck from active database;

Starting Duplicate Db at 2021-05-20.23:22:51

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 05/20/2021 23:22:51

RMAN-05501: aborting duplication of target database

RMAN-06403: could not obtain a fully authorized session

RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Additional information: 4376

Additional information: -724899413

RMAN> exit

本次报错多方查资料都无法找到原因，有的资料显示是在连接的时候数据库又挂掉了，可是我看到的库的状态是对的，且ora_pmon进程也在。

因为leodbadg实例是只写了pfile文件就来startup nomount了，于是通过dbca创建一个orcl实例来验证，验证后发现orcl实例在nomount状态下可以被连接

[oracle@rac19c-01 admin]$ rman target sys/oracle auxiliary sys/oracle@orcl

Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 21 00:14:13 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.? All rights reserved.

connected to target database: LEODB (DBID=2839349055)

connected to auxiliary database: ORCL (not mounted)

RMAN> exit

那么问题又回到leodbadg实例来了，对比两个实例的pfile文件，发现相差一行：

*.local_listener=‘LISTENER_ORCL‘

于是leodbadg添加：

*.local_listener=‘LISTENER_LEODBADG‘

重启leodbadg到nomount状态，测试：

[oracle@rac19c-01 admin]$ rman target sys/oracle auxiliary sys/oracle@leodbadg

Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 21 00:21:30 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.? All rights reserved.

connected to target database: LEODB (DBID=2839349055)

connected to auxiliary database: LEODB (not mounted)

RMAN> exit?

主库连接成功，但是在以往10gR2、11gR2、12.2.0.1版本都没有在备库添加这一行啊。。。

留了个心眼，没有马上传输表空间，先把orcl实例关闭，再次在主库连接，神奇的一幕发生了：

[oracle@rac19c-01 admin]$ rman target sys/oracle auxiliary sys/oracle@leodbadg

Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 21 00:27:19 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.? All rights reserved.

connected to target database: LEODB (DBID=2839349055)

connected to auxiliary database (not started)

RMAN> exit

连接失败了，not started状态。。。

一万头草泥马飘过。。。。

马上启动orcl实例，但是leodbadg仍然无法连接。

在备库查看leodbadg实例状态，神奇的一幕又发生了：

[oracle@19c-adg dbs]$ ps -ef |grep ora_pmon

oracle? ? 25501? ? ? 1? 0 May20 ?? ? ? ? 00:00:00 ora_pmon_leodbadg

oracle? ? 28882? ? ? 1? 0 00:05 ?? ? ? ? 00:00:00 ora_pmon_orcl

oracle? ? 29733? 29552? 0 00:18 pts/2? ? 00:00:00 grep --color=auto ora_pmon

[oracle@19c-adg dbs]$ echo $ORACLE_SID

leodbadg

[oracle@19c-adg dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 21 00:19:10 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.? All rights reserved.

Connected to an idle instance.

SQL> exit

ora_pmon进程还在，但是连接库却是宕机了。。。

于是强杀进程。

再次将leodbadg实例 startup nomount后，主库连接成功，不甘心！于是删除备库pfile中的*.local_listener=‘LISTENER_LEODBADG‘，重启备库到nomount状态。

主库再次连接成功。。。为什么。。。

算了不想了，执行操作吧：

[oracle@rac19c-01 admin]$ rman target sys/oracle auxiliary sys/oracle@leodbadg

Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 21 00:27:57 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.? All rights reserved.

connected to target database: LEODB (DBID=2839349055)

connected to auxiliary database: LEODB (not mounted)

RMAN> duplicate target database for standby nofilenamecheck from active database;

Starting Duplicate Db at 2021-05-21.00:31:23

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=22 device type=DISK

contents of Memory Script:

{

? ?backup as copy reuse

? ?passwordfile auxiliary format? ‘/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwleodbadg‘? ?;

}

executing Memory Script

Starting backup at 2021-05-21.00:31:24

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=308 instance=leodb1 device type=DISK

Finished backup at 2021-05-21.00:31:28

contents of Memory Script:

{

? ?backup as copy current controlfile for standby auxiliary format? ‘/u01/app/oracle/oradata/leodbadg/control01.ctl

? ?restore clone primary controlfile to? ‘/u01/app/oracle/fast_recovery_area/leodbadg/control02.ctl‘ from?

?‘/u01/app/oracle/oradata/leodbadg/control01.ctl‘;

}

executing Memory Script

Starting backup at 2021-05-21.00:31:28

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/oradata/leodbadg/control01.ctl tag=TAG20210521T003128

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 2021-05-21.00:31:30

Starting restore at 2021-05-21.00:31:30

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 2021-05-21.00:31:32

contents of Memory Script:

{

? ?sql clone ‘alter database mount standby database‘;

}

executing Memory Script

sql statement: alter database mount standby database

RMAN-05158: WARNING: auxiliary (tempfile) file name +DATA/LEODB/TEMPFILE/temp.264.1056020941 conflicts with a file?

RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:

{

? ?set newname for tempfile? 1 to?

?"+DATA";

? ?switch clone tempfile all;

? ?set newname for datafile? 1 to?

?"/u01/app/oracle/oradata/leodbadg/system.257.1056020793";

? ?set newname for datafile? 3 to?

?"/u01/app/oracle/oradata/leodbadg/sysaux.258.1056020839";

? ?set newname for datafile? 4 to?

?"/u01/app/oracle/oradata/leodbadg/undotbs1.259.1056020863";

? ?set newname for datafile? 5 to?

?"/u01/app/oracle/oradata/leodbadg/undotbs2.265.1056021603";

? ?set newname for datafile? 7 to?

?"/u01/app/oracle/oradata/leodbadg/users.260.1056020865";

? ?backup as copy reuse

? ?datafile? 1 auxiliary format?

?"/u01/app/oracle/oradata/leodbadg/system.257.1056020793"? ?datafile?

?3 auxiliary format?

?"/u01/app/oracle/oradata/leodbadg/sysaux.258.1056020839"? ?datafile?

?4 auxiliary format?

?"/u01/app/oracle/oradata/leodbadg/undotbs1.259.1056020863"? ?datafile?

?5 auxiliary format?

?"/u01/app/oracle/oradata/leodbadg/undotbs2.265.1056021603"? ?datafile?

?7 auxiliary format?

?"/u01/app/oracle/oradata/leodbadg/users.260.1056020865"? ?;

? ?sql ‘alter system archive log current‘;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA 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 2021-05-21.00:31:39

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/LEODB/DATAFILE/system.257.1056020793

output file name=/u01/app/oracle/oradata/leodbadg/system.257.1056020793 tag=TAG20210521T003139

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=+DATA/LEODB/DATAFILE/sysaux.258.1056020839

output file name=/u01/app/oracle/oradata/leodbadg/sysaux.258.1056020839 tag=TAG20210521T003139

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/LEODB/DATAFILE/undotbs1.259.1056020863

output file name=/u01/app/oracle/oradata/leodbadg/undotbs1.259.1056020863 tag=TAG20210521T003139

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=+DATA/LEODB/DATAFILE/undotbs2.265.1056021603

output file name=/u01/app/oracle/oradata/leodbadg/undotbs2.265.1056021603 tag=TAG20210521T003139

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=+DATA/LEODB/DATAFILE/users.260.1056020865

output file name=/u01/app/oracle/oradata/leodbadg/users.260.1056020865 tag=TAG20210521T003139

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 2021-05-21.00:32:20

sql statement: alter system archive log current

contents of Memory Script:

{

? ?switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=1073089946 file name=/u01/app/oracle/oradata/leodbadg/system.257.1056020793

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=1073089946 file name=/u01/app/oracle/oradata/leodbadg/sysaux.258.1056020839

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=1073089946 file name=/u01/app/oracle/oradata/leodbadg/undotbs1.259.1056020863

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=1073089946 file name=/u01/app/oracle/oradata/leodbadg/undotbs2.265.1056021603

datafile 7 switched to datafile copy

input datafile copy RECID=5 STAMP=1073089946 file name=/u01/app/oracle/oradata/leodbadg/users.260.1056020865

Finished Duplicate Db at 2021-05-21.00:32:46

**************************************************报错信息**************************************************

duplicate开始

在主库上通过rman进行复制备库(注意在这一步之前必须退出备库的所有连接,否则会报错)

rman target sys/oracle auxiliary sys/oracle@LEODBADG

configure device type disk parallelism 10;

rman>duplicate target database for standby nofilenamecheck from active database;

---也可使用下面语句（主库开3个通道，备库开3个通道）：

run

{?

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate AUXILIARY channel c4 type disk;

allocate AUXILIARY channel c5 type disk;

allocate AUXILIARY channel c6 type disk;

duplicate target database for standby nofilenamecheck from active database;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

release channel c5;

release channel c6;

}

备库查询

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

把备库启动到open only下面。并recover

SQL> alter database open read only;

为了方便实时查询，恢复自动恢复状态。

在备库上启动数据库到恢复管理模式，并开始准备从主库接受归档日志的传输。

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

13．主库执行：

select process,status from v$managed_standby;

查看进程，看有没有LNS进程

SQL> select process,status from v$managed_standby;

PROCESS?? ?STATUS

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

DGRD? ?ALLOCATED

ARCH? ?CLOSING

DGRD? ?ALLOCATED

ARCH? ?CLOSING

ARCH? ?CLOSING

ARCH? ?CLOSING

LNS? ?OPENING

DGRD? ?ALLOCATED

LNS? ?WRITING

9 rows selected.

验证standby能否接收日志传输

在备库端查看其角色是否已经是physical standby

select DATABASE_ROLE,open_mode from v$database;

DATABASE_ROLE?OPEN_MODE

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

PHYSICAL STANDBY READ ONLY WITH APPLY

在主库查看其角色

select DATABASE_ROLE,open_mode from gv$database;

DATABASE_ROLE? ? OPEN_MODE

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

PRIMARY? ? ? ? ? READ WRITE

PRIMARY? ? ? ? ? READ WRITE

在备库查看data guard为哪种日志接受方式

select process,client_process,sequence#,status from v$managed_standby;

检查当前备库的模式

select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUM PERFORMANCE

主备库检查当前最大sequence

select max(sequence#) from v$archived_log;

在备库查看日志的队列情况

select thread#,sequence#,creator,applied,first_time,next_time from v$archived_log order by sequence#;

? ?THREAD#? SEQUENCE# CREATOR APPLIED? ?FIRST_TIM NEXT_TIME

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

? ? ? ? ?1? ? ? ? ?28 ARCH? ? YES? ? ? ?21-MAY-21 21-MAY-21

? ? ? ? ?1? ? ? ? ?29 ARCH? ? YES? ? ? ?21-MAY-21 21-MAY-21

? ? ? ? ?2? ? ? ? ?44 ARCH? ? YES? ? ? ?21-MAY-21 21-MAY-21

?

其中applied字段应该为YES，如果为NO就使用偶redo没有些过来，需要关注了。

在主库进行强制归档

ALTER SYSTEM ARCHIVE LOG CURRENT;

alter system switch logfile;

检查下两边的日志同步情况

select thread#,sequence#,creator,applied,first_time,next_time from v$archived_log where applied=‘YES‘ order by sequence#;

这里注意，在备库创建之前的redo归档是不会写过来的。

查看DG是否正常工作

select dest_id,error,status from v$archive_dest where dest_id=2;

SQL> select dest_id,error,status from v$archive_dest where dest_id=2;

? ?DEST_ID ERROR? ? ?STATUS

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

?2? ? ?VALID

error值为空则正常

主库操作

--创建用户

create user dgtest identified by oracle;

grant dba to dgtest;

--创建表

create table dgtest (

? ? ? ?id number(9) not null primary key,

? ? ? ?classname varchar2(40) not null

? ? ? ?);

? ? ? ?

insert into dgtest values(28,‘class one‘);

commit;

刚才在sys用户下也创建了这个表，可以一起查看

为区分，分别插入两条数据:

sys用户：

insert into dgtest values(27,‘sys one‘);

dgtest用户：

insert into dgtest values(29,‘detest one‘);

在备库执行查询：

select * from sys.dgtest;

select * from dgtest.dgtest;

drop tablespace dfeip including contents and datafiles;

drop user dgtest cascade

原文：https://blog.51cto.com/u_1570955/2807033