19c 单机ADG部署
前提：主库安装数据库，备库只安装oracle软件即可。
1.DG基础环境
oracle 主库ip：10.10.98.131 ORACLE_SID：orcl db_name='orcl' 主机名：sentry
oracle 主库ip：10.10.98.132 ORACLE_SID：orclst db_name='orcl' 主机名：sentryst
2.主库开启强制归档以及开启归档模，以及修改主库初始化参数
①开启强制归档
alter database force logging;
select force_logging from v$database;
②初始化参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCLST)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORCLST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLST' scope=both sid='*';
alter system set FAL_CLIENT='ORCL' scope=both sid='*';
alter system set FAL_SERVER='ORCLST' scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
alter system set DB_FILE_NAME_CONVERT='/u01/oradata/ORCLST','/u01/oradata/ORCL'scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='/u01/oradata/ORCLST','/u01/oradata/ORCL' scope=spfile sid='*';
③开启归档，开启归档需要重启数据库。
3.主库添加standby redo logfile(连接到CDB$ROOT中执行)
添加规则：创建standby日志组，个数是源日志个数+1再与实例数的积，size不能小于原来日志的大小
SQL> select thread#,group#,members,bytes,bytes/1024/1024 from v$log;
THREAD# GROUP# MEMBERS BYTES BYTES/1024/1024
---------- ---------- ---------- ---------- ---------------
1 1 1 209715200 200
1 2 1 209715200 200
1 3 1 209715200 200
alter database add standby logfile group 4 ('/u01/oradata/ORCL/redo04.log') size 200M;
alter database add standby logfile group 5 ('/u01/oradata/ORCL/redo05.log') size 200M;
alter database add standby logfile group 6 ('/u01/oradata/ORCL/redo06.log') size 200M;
alter database add standby logfile group 7 ('/u01/oradata/ORCL/redo07.log') size 200M;
SQL> select group#,status,type,member from v$logfile;
4.主库和备库监听配置以及TNS配置(主备库tns一样)，保证防火墙关闭
主库：listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.98.131)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = orcl)
)
)
主库:tnsname.ora
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.98.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCLST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.98.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclst)
)
)
备库：listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.98.132)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orclst)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = orclst)
)
)
备库:tnsname.ora
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.98.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCLST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.98.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclst)
)
)
注意：配置完毕再主备库互相访问数据库查看连接情况。
5.主库创建pfile以及cpoy创建好的pfile和口令文件到备库对应位置
主库：
create pfile=init202009024.ora from spfile;
cd $ORACLE_HOME/dbs
scp init202009024.ora oracle@10.10.98.132:/u01/app/oracle/product/19.0.0/dbhome_1/dbs
scp orapworcl oracle@10.10.98.132:/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapworclst
查看主库生成的init202009024.ora
----------------------------------------------------------------
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=1224736768
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=0
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle/'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=436207616
orcl.__sga_target=1728053248
orcl.__shared_io_pool_size=83886080
orcl.__shared_pool_size=385875968
orcl.__streams_pool_size=0
orcl.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/oradata/ORCL/control01.ctl','/u01/oradata/ORCL/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/oradata/ORCLST','/u01/oradata/ORCL'
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.fal_client='ORCL'
*.fal_server='ORCLST'
*.log_archive_config='DG_CONFIG=(ORCL,ORCLST)'
*.log_archive_dest_1='LOCATION=/u01/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'
*.log_archive_dest_2='SERVICE=ORCLST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLST'
*.log_file_name_convert='/u01/oradata/ORCLST','/u01/oradata/ORCL'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=410m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1638m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
----------------------------------------------------------------
6.备库修改init202009024.ora文件
mkdir /u01/app/oracle/admin/orclst/adump
-----------------------------------------------------------------
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=1224736768
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=0
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle/'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=436207616
orcl.__sga_target=1728053248
orcl.__shared_io_pool_size=83886080
orcl.__shared_pool_size=385875968
orcl.__streams_pool_size=0
orcl.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orclst/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/oradata/ORCLST/control01.ctl','/u01/oradata/ORCLST/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/oradata/ORCL','/u01/oradata/ORCLST'
*.db_name='orcl'
*.db_unique_name='orclst'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclstXDB)'
*.enable_pluggable_database=true
*.fal_client='ORCLST'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(ORCL,ORCLST)'
*.log_archive_dest_1='LOCATION=/u01/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLST'
*.log_archive_dest_2='SERVICE=ORCL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
*.log_file_name_convert='/u01/oradata/ORCL','/u01/oradata/ORCLST'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=410m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1638m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
----------------------------------------------------------------------------------
7.备库用init202009024.ora启动到nomount
startup pfile=init202009024.ora nomount;
8.duplicate开始，在备库通过rman进行复制备库
rman target sys/oracle@ORCL auxiliary sys/oracle@ORCLST
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 from active database nofilenamecheck;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
或者：duplicate target database for standby from active database nofilenamecheck dorecover;
9.查看备库数据库状态
select open_mode from v$database;
OPEN_MODE
-------------
MOUNTED
把备库启动到open only下：
alter database open read only;
在备库启动数据库到恢复管理模式，并开始准备从主库接受日志的传输：
alter database recover managed standby database using current logfile disconnect from session;
10.主库检查LNS进程：
select process,status from v$managed_standby;
如果没有LNS进程，则需要检查DG环境。
11.检查主备库角色和其他信息
备库：
select database_role,protection_mode,open_mode from v$database;
主库：
select database_role,open_mode from gv$database;
12.在备库查看data guard为那种日志接受方式：
select process,client_process,sequence#,status from v$managed_standby;
13.检查主备库sequence#
select max(sequence#) from v$archived_log;
select sequence#,name,standby_dest,applied,deleted from v$archived_log where archived='YES' order by sequence# desc;
14.检查日志队列是否正常传输
select sequence#,applied,first_time,next_time from v$archived_log order by sequence#;
检查两边的日志同步情况：
select sequence# from v$archived_log where applied='YES';
注意：备库需要重新创建spfile文件，否则关闭数据库以后重启会找不到参数文件而报错。语句：create spfile from pfile='$ORACLE_HOME/dbs/init202009024.ora';
************************************************************************************************************************
******************* 方法二，利用主库rman的备份集做ADG ********************************
******************* 如下 ********************************
************************************************************************************************************************
衔接上述方法的第7步骤，7之前都是一样的操作。
1.登录主库用rman做全备
rman target /
RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql‘ alter system archive log current‘;
backup database format ‘/home/oracle/rman/full_db_%U‘;
sql‘ alter system archive log current‘;
backup archivelog all format ‘/home/oracle/rman/archlog_%U‘;
backup current controlfile for standby format ‘/home/oracle/rman/controlfile_%U‘;;
release channel c1;
release channel c2;
}
2.将以上备份出来的备份集传到备库
scp /home/oracle/rman_bak/* oracle@10.10.98.132:/home/oracle/rman_bak/
3.备库用init202009024.ora启动到nomount
startup pfile=init202009024.ora nomount;
4.备库restore控制文件
rman target /
restore standby controlfile from ‘/home/oracle/rman_bak/controlfile_0ew9gd_1_1‘;
5.备库做控制文件恢复成功以后打开数据库到mount状态
alter database mount;
加载备份集
RMAN> catalog start with '/home/oracle/backup'; ----此目录为你备端存放备份集的目录
6.备库开始恢复数据库
restore database;
7.恢复成功以后打开备库数据导open
alter database open;
8.查看备库数据库状态
SQL> select name,db_unique_name,open_mode from v$database;
OPEN_MODE
-------- -----
READ ONLY
9.备库执行日志实时利用。
alter database recover managed standby database using current logfile disconnect;
ADG搭建完毕，查看状态测试同步情况即可！！
在rac到rac部署adg遇到的问题：
restore恢复完成以后有些日志显示:
+DATA/MUST_RENAME_THIS_LOGFILE_2.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_2.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_1.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_1.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_3.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_3.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_4.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_4.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_5.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_5.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_6.232423.43523
+DATA/MUST_RENAME_THIS_LOGFILE_6.232423.43523
则通过重新rename
alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_2.232423.43523' to '+DATA/redo2_01.log';
alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_2.232423.43523' to '+DATA/redo2_02.log';
alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_1.232423.43523' to '+DATA/redo1_01.log';
alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_1.232423.43523' to '+DATA/redo1_02.log';
以此类推。。。。修改完成启动alter database open;
在改完名字以后，在open到只读库时候会报错：
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/MUST_RENAME_THIS_DATAFILE_1.23423.123112'
可以检查archive_dest是否配置的正确，如果正确的话。可以是太旧最近的日志没有追上来，可以先打开应用日志，等一段时间在open到只读库就可以了