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