oracle 19c RAC部署ADG手顺
hostnamectl set-hostname 19c-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
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
