Oracle 19c adg全库迁移数据
导读：使用adg将生产数据复制至预生产环境，使用failover将主备库完成分开成2个互不相关的数据库。adg复制数据这个过程类似于使用mysql ab复制功能，将a库数据复制给b库即可。
# 环境背景 primary环境：3节点rac standby环境：单实例本地存储
使用adg迁移数据的基本思路与步骤：
##################################################################################### # adg搭建过程： 1.primary开启force logging模式 2.primary配置tnsnames.ora 3.primary与standby库创建Standby Redo Log 4.primary配置adg参数 5.standby创建密码文件 6.standby创建所需目录与权限 7.standby创建临时实例与配置静态监听 8.standby库rman的auxiliary技术恢复primary库的数据文件，参数文件，standby controlfile等 9.standby启动日志应用 10.standby检查adg状态 # failover拆分adg,使原primary和standby成为2个互不相关的数据库 11.使用failover拆分adg 12.清除primary和standby的adg参数 13.standby重启open成为独立的primary库 14.oracle 19c dataguard 管理命令汇总 #####################################################################################
1.primary开启force logging模式
##################################################################################### # Enable Archiving and Enable force logging in rac --rac srvctl stop databaas -dsrvctl start database -d -o mount SQL> alter system set log_archive_dest_1='LOCATION=+ARCH'; SQL> alter database archivelog; # enable database force_logging mode SQL> alter database force logging; srvctl stop database -d srvctl start database -d SQL> archive log list; SQL> select DBID, INST_ID, NAME, OPEN_MODE,force_logging from gv$database; --Single instance SQL> shutdown immediate; SQL> startup mount; SQL> alter system set log_archive_dest_1='LOCATION=/ARCH'; SQL> alter database archivelog; SQL> alter database force logging; SQL> alter database open; SQL> archive log list; SQL> select force_logging from v$database; ##################################################################################### # enable database force_logging mode as follows： SQL> select DBID, INST_ID, NAME, OPEN_MODE,force_logging from gv$database; DBID INST_ID NAME OPEN_MODE FORCE_LOGGING ---------- ---------- ------------------ ---------------------------------------- ----------------------------------- 1090140655 3 RACDB READ WRITE YES 1090140655 1 RACDB READ WRITE YES 1090140655 2 RACDB READ WRITE YES #####################################################################################
2.primary配置tnsnames.ora
2.1 所有节点的tnsnames.ora均添加以下配置，并使用tnsping racdbadg验证联通性 RACDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.107)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) ) ) racdbdg = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.204)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = racdbdg) ) ) 2.2 tnsping检查联通性,所有节点均验证 tnsping racdb tnsping racdbdg
3.primary与standby库创建Standby Redo Log
#####################################################################################
# primary库创建srl文件，standby库恢复时会自动创建
# 参考：Usage, Benefits and Limitations of Standby Redo Logs (SRL) (Doc ID 219344.1)
--oracle 10g Real-Time Apply we directly apply Redo Data from Standby RedoLogs
--Note that starting with Oracle 11g ARCH Log Transport Method is deprecated.
# SRL要求：
SRL文件大小:保持与primary库redo logfile大小一致.
SRL文件数目:(maximum number of logfiles for each thread + 1) *maximum number of threads
--检查集群实例数
show parameter cluster_database_instances
# SRL创建:
--thread1
alter database add standby logfile thread 1 group 20 ('+DATA') size 200M;
......
--thread2
alter database add standby logfile thread 2 group 23 ('+DATA') size 200M;
......
--thread3
alter database add standby logfile thread 3 group 26 ('+DATA') size 200M;
......
# SRL检查:
select t.group#,t.status,t.type,t.member,d.thread#,d.sequence#,d.bytes/1024/1024 m from v$logfile t,v$standby_log d where t.group#=d.group# order by 1,5;
######################################################################################
# 操作记录：
# 创建srl：
col member for a50
set line 200
--检查集群实例数
SQL> show parameter cluster_database_instances
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
cluster_database_instances integer 3
SQL> select t.group#,t.status,t.type,t.member,d.thread#,d.bytes/1024/1024 m from v$logfile t,v$log d where t.group#=d.group# order by 1,5;
GROUP# STATUS TYPE MEMBER THREAD# M
---------- -------------- -------------- -------------------------------------------------- ---------- ----------
1 ONLINE +DATA/RACDB/ONLINELOG/group_1.263.1092523891 1 200
2 ONLINE +DATA/RACDB/ONLINELOG/group_2.264.1092523891 1 200
3 ONLINE +DATA/RACDB/ONLINELOG/group_3.268.1092525365 2 200
4 ONLINE +DATA/RACDB/ONLINELOG/group_4.269.1092525365 2 200
5 ONLINE +DATA/RACDB/ONLINELOG/group_5.270.1092525367 3 200
6 ONLINE +DATA/RACDB/ONLINELOG/group_6.271.1092525367 3 200
# SRL创建:
--thread1
alter database add standby logfile thread 1 group 20 ('+DATA') size 200M;
alter database add standby logfile thread 1 group 21 ('+DATA') size 200M;
alter database add standby logfile thread 1 group 22 ('+DATA') size 200M;
--thread2
alter database add standby logfile thread 2 group 23 ('+DATA') size 200M;
alter database add standby logfile thread 2 group 24 ('+DATA') size 200M;
alter database add standby logfile thread 2 group 25 ('+DATA') size 200M;
--thread3
alter database add standby logfile thread 3 group 26 ('+DATA') size 200M;
alter database add standby logfile thread 3 group 27 ('+DATA') size 200M;
alter database add standby logfile thread 3 group 28 ('+DATA') size 200M;
# SRL检查:
SQL> select t.group#,t.status,t.type,t.member,d.thread#,d.sequence#,d.bytes/1024/1024 m from v$logfile t,v$standby_log d where t.group#=d.group# order by 1,5;
GROUP# STATUS TYPE MEMBER THREAD# M
---------- -------------- -------------- -------------------------------------------------- ---------- ----------
20 STANDBY +DATA/RACDB/ONLINELOG/group_20.275.1118890319 1 200
21 STANDBY +DATA/RACDB/ONLINELOG/group_21.276.1118890335 1 200
22 STANDBY +DATA/RACDB/ONLINELOG/group_22.283.1118894803 1 200
23 STANDBY +DATA/RACDB/ONLINELOG/group_23.282.1118894859 2 200
24 STANDBY +DATA/RACDB/ONLINELOG/group_24.281.1118894859 2 200
25 STANDBY +DATA/RACDB/ONLINELOG/group_25.280.1118894859 2 200
26 STANDBY +DATA/RACDB/ONLINELOG/group_26.279.1118894873 3 200
27 STANDBY +DATA/RACDB/ONLINELOG/group_27.278.1118894875 3 200
28 STANDBY +DATA/RACDB/ONLINELOG/group_28.277.1118894875 3 200
4.primary配置adg参数
##################################################################################### ##与角色无关的参数(primary库均需配置的参数) --db_name和db_unique_name无需更改 db_name=racdb db_unique_name=racdb --tunning arch processes,19c default is 4 log_archive_max_processes=4 --log_archive_config is list of db_unique_name parameter log_archive_config='dg_CONFIG=(racdb,racdbadg)'; ##primary角色参数(若不考虑切换，仅primary库配置即可) --LOCATION=local archivelog directory；ALL_LOGFILES=redo log files or archivelog files is valid;ALL_ROLES=primary or the standby role is valid log_archive_dest_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb' --service=net_service_name;LGWR=redo archival process ;ASYNC=network transtmisson mode; NOAFFIRM=disk write option ; online redo log files is valid;PRIMARY_ROLE=primary role is valid log_archive_dest_2='SERVICE=racdbadg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdbadg' --log archive destination is valid,default is enable;归档路径是否开启自动传输，enable参数时归档路径可用于后续归档自动传输；defer参数修改enable之前归档路径为不可用 LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE ##################################################################################### # primary库在线执行命令如下： --关闭日志传输，待standby库数据恢复完成后开启日志传输 alter system set log_archive_dest_state_2=defer; alter system set log_archive_config='dg_CONFIG=(racdb,racdbdg)' ; alter system set log_archive_dest_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb' ; alter system set log_archive_dest_2='SERVICE=racdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdbdg' ; alter system set standby_file_management='AUTO';
5.standby创建密码文件
# 将primary库的密码文件copy至standby库，并修改相应的oracle_sid即可.密码文件，默认使用sys为数据同步用户。也可创建新用户 ##################################################################################### --primary srvctl config database -d racdb su - grid asmcmd cp +DATA/RACDB/PASSWORD/pwdracdb.274.1113430875 /tmp/ scp /tmp/pwdracdb.274.1113430875 oracle@192.168.56.204:/u01/app/oracle/product/19.0.0/db_1/dbs/orapwracdbdg --standby chmod u+x orapwracdbdg ls -l orapwracdbdg ##################################################################################### # 操作记录： --rac环境查看密码文件位置 [oracle@rac2:/home/oracle]$srvctl config database -d racdb # 3个节点的密码文件以共享的方式存储在asm磁盘组中，所以copy一个即可 ...... Password file: +DATA/RACDB/PASSWORD/pwdracdb.274.1113430875 ...... --从asm磁盘组中复制密码至本地，并copy至standby su - grid [grid@rac1:/home/grid]$asmcmd cp +DATA/RACDB/PASSWORD/pwdracdb.274.1113430875 /tmp/ copying +DATA/RACDB/PASSWORD/pwdracdb.274.1113430875 -> /tmp//pwdracdb.274.1113430875 [grid@rac1:/home/grid]$scp /tmp/pwdracdb.274.1113430875 oracle@192.168.56.204:/u01/app/oracle/product/19.0.0/db_1/dbs/orapwracdbdg oracle@192.168.56.204's password: pwdracdb.274.1113430875 100% 2048 847.8KB/s 00:00 chmod u+x orapwracdbdg [root@rac_dg dbs]# ls -l orapwracdbdg -rwxr----- 1 oracle oinstall 2048 11月 6 21:33 orapwracdbdg
6.standby创建所需目录与权限
##################################################################################### su - root mkdir -p /home/oracle/data/racdb/datafile mkdir -p /home/oracle/data/adump mkdir -p /home/oracle/data/arch mkdir -p /home/oracle/data/racdb/onlinelog chown -R oracle:oinstall /home/oracle/data chmod -R 775 /home/oracle/data #####################################################################################
7.standby创建临时实例与配置静态监听
7.1 开启临时实例racdb su - oracle echo db_name=racdb > /u01/app/oracle/product/19.0.0/db_1/dbs/initracdbdg.ora ORACLE_SID=racdbdg sqlplus / as sysdba startup nomount pfile='/u01/app/oracle/product/19.0.0/db_1/dbs/initracdbdg.ora'; 7.2 standby库配置静态监听；使nomount状态可使用rman进行链接 cat /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=rac_dg)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) # SID_LIST_# List of services the listener knows about and can connect # clients to. There is no default. See the Net8 Administrator's # Guide for more information. # SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=racdbdg) (SID_NAME=racdbdg) (ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1) ) ) 7.3 lsnrctl配置重新加载，静态监听状态查看 lsnrctl stop lsnrctl start lsnrctl status sqlplus / as sysdba alter system register; lsnrctl status # 配置静态监听成功日志输出如下： ...... Listener Parameter File /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/rac_dg/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac_dg)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "racdb" has 1 instance(s). Instance "racdbdg", status BLOCKED, has 1 handler(s) for this service... Service "racdbdg" has 1 instance(s). Instance "racdbdg", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully 7.4 静态监听的登录验证（nomount状态） [oracle@rac_dg:/u01/app/oracle/product/19.0.0/db_1/dbs]$sqlplus sys/oracle@192.168.56.204:/racdbdg as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 6 21:56:52 2022 Version 19.9.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 SQL> 7.5 standby配置tnsnames.ora,以备fal使用 cat /u01/app/oracle/product/19.0.0/db_1/network/admin/tnsnames.ora RACDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.107)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) ) ) racdbdg = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.204)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = racdbdg) ) )
8.standby库rman的auxiliary技术恢复primary库的数据文件，参数文件，standby controlfile等
# standby库配置adg参数
#####################################################################################
##与角色无关的参数
db_name=racdb --（不变）
db_unique_name=racdbdg
log_archive_max_processes=4
log_archive_config='dg_CONFIG=(racdb,racdbdg)
##standby角色参数(若不考虑切换，仅standby库配置即可)
--The fal_server is init.ora parameters are used for the gap resolution
fal_server='racdb'
--failover log
log_archive_dest_1='LOCATION=/home/oracle/data/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdbdg'
log_archive_dest_2='SERVICE=racdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
--all the modification of datafiles on the primary database will be reflected on the standby site
DB_FILE_NAME_CONVERT='+DATA','+DATA_dg'
LOG_FILE_NAME_CONVERT='+DATA','+DATA_dg'
standby_file_management='AUTO'
#####################################################################################
# 在线命令如下：
rman target sys/oracle@racdb auxiliary sys/oracle@racdbdg
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate AUXILIARY channel c3 type disk;
allocate AUXILIARY channel c4 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert'racdb','racdbdg'
set db_name='racdb'
set db_unique_name='racdbdg'
set db_file_name_convert='+data/','/home/oracle/data/'
set log_file_name_convert='+data/','/home/oracle/data/'
set control_files='home/oracle/data/stadnby.ctl'
set fal_server='racdb'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(racdb,racdbdg)'
set memory_target='500m'
set cluster_database='false'
set diagnostic_dest='/home/oracle/data/adump/'
set log_archive_dest_1='LOCATION=/home/oracle/data/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdbdg'
set log_archive_dest_2='SERVICE=racdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
nofilenamecheck;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
--rman的auxiliary恢复完成，并且standby已开启至mount状态
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
-------------------------------- ------------------------
racdbdg MOUNTED
9.standby启动adg
##################################################################################### --primary库未传输日志，primary库开启传输日志 alter system set log_archive_dest_state_2=enable; --standby库执行 select database_role,protection_mode,open_mode from v$database; alter database recover managed standby database cancel; alter database open; --start active redo apply alter database recover managed standby database using current logfile disconnect; select database_role,protection_mode,open_mode from v$database; --close redo apply alter database recover managed standby database cancel; ##################################################################################### # 操作记录： --oracle 19c enable adg SQL> set line 200 SQL> select database_role,protection_mode,open_mode from v$database; DATABASE_ROLE PROTECTION_MODE OPEN_MODE -------------------------------- ---------------------------------------- ---------------------------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE MOUNTED SQL> alter database recover managed standby database cancel; alter database recover managed standby database cancel * ERROR at line 1: ORA-16136: Managed Standby Recovery not active SQL> alter database open; Database altered. SQL> alter database recover managed standby database using current logfile disconnect; Database altered. SQL> select database_role,protection_mode,open_mode from v$database; DATABASE_ROLE PROTECTION_MODE OPEN_MODE -------------------------------- ---------------------------------------- ---------------------------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY
10.standby检查adg状态
##################################################################################### --检查adg库状态 select database_role,protection_mode,open_mode,switchover_status from v$database; --接收归档日志传输序列与应用情况 select sequence#,first_time,next_time,applied from v$archived_log order by sequence#; --检查日志传输与日志应用进程状态，若是没有mgr进行，则优先检查是否有gap select role,thread#,sequence#,action from v$dataguard_process; --检查是否有gap select thread#,low_sequence#,high_sequence# from v$archive_gap; --检查lag情况；可判断adg是否有延迟 col name for a23 col value for a13 col time_computed for a20 col datum_time for a20 select name,value,time_computed,datum_time from v$dataguard_stats; ##################################################################################### # 操作记录: --检查日志传输与日志应用进程状态 select role,thread#,sequence#,action from v$dataguard_process; ROLE THREAD# SEQUENCE# ACTION ------------------------------------------------ ---------- ---------- ------------------------ log writer 0 0 IDLE redo transport monitor 0 0 IDLE gap manager 0 0 IDLE redo transport timer 0 0 IDLE archive local 0 0 IDLE archive redo 0 0 IDLE archive redo 0 0 IDLE archive redo 0 0 IDLE RFS ping 1 151 IDLE RFS async 1 151 IDLE RFS archive 0 0 IDLE managed recovery 1 151 APPLYING_LOG 12 rows selected. # managed recovery 'applying_log' shows redo is applied --检查接收归档日志传输与应用状态 SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME NEXT_TIME APPLIED ---------- ----------------------- ----------------------- ------------------ 150 07-NOV-2022 00:58:48 07-NOV-2022 02:39:56 YES --检查lag情况；可判断adg是否有延迟 col name for a23 col value for a13 col time_computed for a20 col datum_time for a20 select name,value,time_computed,datum_time from v$dataguard_stats; NAME VALUE TIME_COMPUTED DATUM_TIME ----------------------- ------------- -------------------- -------------------- transport lag +00 00:00:00 11/07/2022 02:53:50 11/07/2022 02:53:48 apply lag +00 00:00:00 11/07/2022 02:53:50 11/07/2022 02:53:48 apply finish time 11/07/2022 02:53:50 estimated startup time 53 11/07/2022 02:53:50 # DATUM_TIME估算standby库接收的时间 # TIME_COMPUTED估算standby库完成的时间 # transport lagstandby库transport of redo传输滞后的指标 # apply lagstandby库applying redo滞后的指标
11.使用failover拆分adg
##################################################################################### --未执行failover前查看adg状态 col database_role for a30 col PROTECTION_MODE for a30 col open_mode for a30 col switchover_status for a30 set line 200 select database_role,protection_mode,open_mode,switchover_status from v$database; --确保adg没有gap，若有gap解决后在failover select thread#,low_sequence#,high_sequence# from v$archive_gap; --执行failover select database_role from v$database; recover managed standby database cancel; alter database recover managed standby database finish; select name,open_mode,database_role from v$database; alter database activate standby database; select database_role from v$database; alter database open; ##################################################################################### # 操作记录： --未执行failover前查看adg状态 col database_role for a30 col PROTECTION_MODE for a30 col open_mode for a30 col switchover_status for a30 set line 200 select database_role,protection_mode,open_mode,switchover_status from v$database; DATABASE_ROLE PROTECTION_MODE OPEN_MODE SWITCHOVER_STATUS ------------------------------ ------------------------------ ------------------------------ ------------------------------ PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY NOT ALLOWED --确保adg没有gap，若有gap解决后在failover SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap; no rows selected SQL> select database_role from v$database; DATABASE_ROLE ------------------------------ PHYSICAL STANDBY SQL> recover managed standby database cancel; Media recovery complete. SQL> alter database recover managed standby database finish; # alert 报错 hang：NET (PID:80835): Begin: SRL archival # 解决办法：备库配置log_archive_dest_1和log_archive_dest_2所需参数均可 SQL> alter database recover managed standby database finish; Database altered. select name,open_mode,database_role from v$database; NAME OPEN_MODE ------------------ ---------------------------------------- DATABASE_ROLE -------------------------------- RACDB READ ONLY PHYSICAL STANDBY SQL> alter database activate standby database; Database altered. SQL> select database_role from v$database; DATABASE_ROLE -------------------------------- PRIMARY SQL> alter database open; Database altered
12.清除primary和standby的adg参数
##################################################################################### primary: alter system set log_archive_config='' ; alter system set log_archive_dest_1='LOCATION=+ARCH' ; alter system set log_archive_dest_2='' ; alter system set standby_file_management='MANUAL'; standby: alter system set log_archive_config='' ; alter system set log_archive_dest_1='LOCATION=/home/oracle/data/racdb/arch' ; alter system set log_archive_dest_2='' ; alter system set standby_file_management='MANUAL'; alter system set fal_server=''; alter system reset DB_FILE_NAME_CONVERT scope=spfile; alter system reset LOG_FILE_NAME_CONVERT scope=spfile; #####################################################################################
13.standby重启open成为独立的primary库
shutdown immediate startup 13.1 解决临时文件 # 添加临时文件，因rman恢复时不会恢复临时文件，故需新创建 ##################################################################################### select TABLESPACE_NAME,FILE_NAME from dba_temp_files; alter database tempfile '/home/oracle/data/racdb/tempfile/temp.265.1092523901' drop; create tablespace temp datafile '/home/oracle/data/racdb/datafile/temp01.dbf' size 100m autoextend on; alter tablespace temp add tempfile '/home/oracle/data/racdb/datafile/temp01.dbf' size 100m autoextend on; select TABLESPACE_NAME,FILE_NAME from dba_temp_files; ##################################################################################### # 操作记录： SQL> select TABLESPACE_NAME,FILE_NAME from dba_temp_files; select TABLESPACE_NAME,FILE_NAME from dba_temp_files * ERROR at line 1: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: '/home/oracle/data/racdb/tempfile/temp.265.1092523901' SQL> alter database tempfile '/home/oracle/data/racdb/tempfile/temp.265.1092523901' drop; Database altered. SQL> create tablespace temp datafile '/home/oracle/data/racdb/datafile/temp01.dbf' size 100m autoextend on; create tablespace temp datafile '/home/oracle/data/racdb/datafile/temp01.dbf' size 100m autoextend on * ERROR at line 1: ORA-01543: tablespace 'TEMP' already exists SQL> alter tablespace temp add tempfile '/home/oracle/data/racdb/datafile/temp01.dbf' size 100m autoextend on; Tablespace altered. SQL> select TABLESPACE_NAME,FILE_NAME from dba_temp_files; TABLESPACE_NAME ------------------------------------------------------------ FILE_NAME -------------------------------------------------------------------------------- TEMP /home/oracle/data/racdb/datafile/temp01.dbf 13.2 增加service_names # 若预生产环境默认连接的service_names是racdb，这里我们增加一个叫racdb的service_names SQL> show parameter name NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ cdb_cluster_name string cell_offloadgroup_name string db_file_name_convert string db_name string racdb db_unique_name string racdbdg global_names boolean FALSE instance_name string racdbdg lock_name_space string log_file_name_convert string pdb_file_name_convert string processor_group_name string NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ service_names string racdbdg SQL> alter system set service_names=racdbdg,racdb; System altered. SQL> show parameter name NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ cdb_cluster_name string cell_offloadgroup_name string db_file_name_convert string db_name string racdb db_unique_name string racdbdg global_names boolean FALSE instance_name string racdbdg lock_name_space string log_file_name_convert string pdb_file_name_convert string processor_group_name string NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ service_names string RACDBDG, RACDB
14.oracle 19c dataguard 管理命令汇总
14.1 查看adg类型与保护模式 SQL> select database_role,protection_mode,open_mode from v$database; DATABASE_ROLE PROTECTION_MODE OPEN_MODE -------------------------------- ---------------------------------------- ---------------------------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY 14.2 检查standby库日志传输与日志应用的延迟 set line 200 col name for a23 col value for a13 col time_computed for a20 col datum_time for a20 select name,value,time_computed,datum_time from v$dataguard_stats; NAME VALUE TIME_COMPUTED DATUM_TIME ----------------------- ------------- -------------------- -------------------- transport lag +00 00:00:00 10/27/2022 05:47:57 10/27/2022 05:47:55 apply lag +00 00:00:00 10/27/2022 05:47:57 10/27/2022 05:47:55 apply finish time 10/27/2022 05:47:57 estimated startup time 51 10/27/2022 05:47:57 # DATUM_TIME估算standby库接收的时间；包含standby库上次接收此数据的时间戳 # TIME_COMPUTED估算standby库完成的时间；包含应用滞后度量时获取的时间戳 # transport lagstandby库transport of redo传输滞后的指标 # apply lagstandby库applying redo滞后的指标 14.3 检查日志传输与日志应用进程状态 select role,thread#,sequence#,action from v$dataguard_process; ROLE THREAD# SEQUENCE# ACTION ------------------------------------------------ ---------- ---------- ------------------------ log writer 0 0 IDLE redo transport monitor 0 0 IDLE gap manager 0 0 IDLE redo transport timer 0 0 IDLE archive local 0 0 IDLE archive redo 0 0 IDLE archive redo 0 0 IDLE archive redo 0 0 IDLE RFS ping 1 119 IDLE RFS async 1 119 IDLE managed recovery 1 119 APPLYING_LOG # managed recovery 'applying_log' shows redo is applied 14.4 检查接收归档日志传输与应用状态 select sequence#,first_time,next_time,applied from v$archived_log order by sequence#; 14.5 oracle 19c adg备库配置归档日志自动删除策略 rman target / configure archivelog deletion policy to applied on all standby;