OracleDG常用命令集合（搭建、维护、测试、激活）

mahan · 发表于 5 天前

DG部署要素
归档配置
#确认是否开启归档
archive log list;
#是否开启强制日志
SELECT force_logging FROM v$database;

#开启归档并配置强制记录日志
shutdown immediate;
Startup mount;
alter database archivelog;
Alter database force logging;
Alter database open;

#切换归档
alter system switch logfile;
#查看归档情况
select group#,member from v$logfile order by group#;
监听配置
listener.ora

#如果多个静态监听，加SID_DESC即可
cat /u01/app/oracle/product/11.2.0/network/admin/listener.ora
E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
-------------------------------------------------------------------------------------------------------------------
SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
   (GLOBAL_DBNAME = orcl)
   (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
   (SID_NAME = orcl)
)
  )

LISTENER=
(DESCRIPTION_LIST =
(DESCRIPTION =
   (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
   )
)
  )
tnsname.ora

#添加如下项，主备都要
cat /u01/app/oracle/product/11.2.0/network/admin/listener.ora
E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
-------------------------------------------------------------------------------------------------------------------
zky =
  (DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.3)(PORT = 1521))
)
(CONNECT_DATA =
   (SERVICE_NAME = zky)
)
  )

zkydg =
  (DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.4)(PORT = 1521))
)
(CONNECT_DATA =
   (SERVICE_NAME = zkydg)
)
  )

重启监听
lsnrctl stop
lsnrctl start
sqlnet.ora

可选配置，如果有出现连不上库可以修改
cat /u01/app/oracle/product/11.2.0/network/admin/sqlnet.ora
E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
-------------------------------------------------------------------------------------------------------------------
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
adr_base = /u01/app/oracle

密码文件

记得不同环境目录不一样win(database) linnux(dbs)
-------------------------------------------------------------------------------------------------------------------
orapwd file=e:\app\Administrator\product\11.1.0\dbhome_1\database\PWDzky.ora password=oracle entries=10

日志文件
--查看日志情况
set linesize 200 pagesize 200
set
col MEMBER format a100
select * from v$log;
select * from v$standby_log;
select * from v$logfile order by GROUP#,MEMBER;

--主库处理日志用
alter database add standby logfile group 11 ('/u01/app/oracle/oradata/zky/stand01.log') size 2G;
alter database add standby logfile group 12 ('/u01/app/oracle/oradata/zky/stand01.log') size 2G;
alter database add standby logfile group 13 ('/u01/app/oracle/oradata/zky/stand01.log') size 2G;

--备库修改日志用
alter database recover managed standby database cancel;
alter system set standby_file_management='MANUAL' scope=both;
alter database add logfile group 1 ('/u01/app/oracle/oradata/zky/redo01.log') size 2G reuse;
alter database add logfile group 2 ('/u01/app/oracle/oradata/zky/redo02.log') size 2G reuse;
alter database add logfile group 3 ('/u01/app/oracle/oradata/zky/redo03.log') size 2G reuse;
alter database clear logfile group 5;
alter database drop logfile group 5;
alter database add standby logfile group 11 ('/u01/app/oracle/oradata/zky/stand01.log') size 2G;
alter database add standby logfile group 12 ('/u01/app/oracle/oradata/zky/stand01.log') size 2G;
alter database add standby logfile group 13 ('/u01/app/oracle/oradata/zky/stand01.log') size 2G;
alter system set standby_file_management='AUTO' scope=both;
alter database recover managed standby database using current logfile disconnect from session;
DG参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(zky,zkydg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=E:/u01/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=zky' scope=both;
alter system set LOG_ARCHIVE_DEST_2= 'service=zkydg lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=zkydg' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set FAL_SERVER='orcl_s' scope=both;
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/zky/','/u01/app/oracle/oradata/zky/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/u01/archive/','/u01/archive/' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
alter system set FAL_SERVER='zhankdg' scope=both;
create pfile='/u01/zky.txt' from spfile;
备库目录确认
mkdir D:\app\Administrator\flash_recovery_area
mkdir D:\app\Administrator\flash_recovery_area\zky
mkdir D:\app\Administrator\oradata
mkdir D:\app\Administrator\oradata\zky
Duplicate拉库

#手动处理
rman target sys/oracle@zky auxiliary sys/oracle@zkydg
duplicate target database for standby from active database nofilenamecheck;

#后台挂的跑
vi /home/oracle/dg.sh
-----------------------------------------------------------------------------------------------
#!/bin/sh
source /home/oracle/.bash_profile
$ORACLE_HOME/bin/rman log=/home/oracle/adg_center_`date +%Y-%m-%d`.log append <<EOF
connect target sys/manager@zky;
connect auxiliary sys/manager@zkydg;
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate auxiliary channel c5 device type disk;
allocate auxiliary channel c6 device type disk;
allocate auxiliary channel c7 device type disk;
allocate auxiliary channel c8 device 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;
release channel c7;
release channel c8;
}
EOF
chmod u+x /home/oracle/dg.sh
nohup /home/oracle/dg.sh &

常用运维操作
重启备库
alter database recover managed standby database cancel;
shutdown immediate
startup nomount;
alter database mount standby database;
alter database open read only;

alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database disconnect from session;
主备切换
Data Guard 角色转换
├── Switchover (计划内切换)
└── Failover (故障转移)
         ├── 使用 RECOVER MANAGED STANDBY DATABASE FINISH [FORCE] (推荐方式)
         └── 使用 ACTIVATE STANDBY DATABASE (传统但官方认可的方式)
两种Failover的区别对比

登录/注册后可看大图

2140569db5e6210e51.png (34.97 KB, 下载次数: 3)

下载附件

5 天前上传

DG切换步骤（switchover）
------------------------------------------------------------------------------------------------------------------------
--主库切换备库，关闭数据库
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database disconnect from session;

--备库关闭应用，切换为主库，关闭数据库
alter database recover managed standby database cancel;
alter database commit to switchover to primary with session shutdown;
alter database open;

shutdown immediate
startup
DG切换步骤（finish）
------------------------------------------------------------------------------------------------------------------------
--备库直接操作
alter database recover managed standby database cancel;
alter database recover managed standby database finish force;
alter database commit to switchover to primary with session shutdown;
startup

强制激活备库
------------------------------------------------------------------------------------------------------------------------
shutdown immediate
startup mount;
alter database activate standby database;
alter database open;
快照备库
------------------------------------------------------------------------------------------------------------------------
--转换为快照备库
show parameter db_recovery
alter system set db_recovery_file_dest='/u01/oracle/flash_recovery_area' scope=both;
alter system set db_recovery_file_dest_size='80G' scope=both;
select flashback_on from v$database;
select open_mode from v$database;
alter database recover managed standby database cancel;
create restore point beforetest guarantee flashback database;
shutdown immediate;
startup mount;
alter database convert to snapshot standby;
alter database open;
--再次检查状态，现在应该变为：snapshot standby, read write
select open_mode from v$database;
--转换回物理备库
shutdown immediate;
startup mount;
alter database convert to physical standby;
shutdown immediate;
startup;
drop restore point beforetest;
alter database recover managed standby database using current logfile disconnect from session;

--10G通过flashback实现快照库功能（10g没有snapshot standby的概念，但是有flashback功能，所以通过该功能实现快照库）
------------------------------------------------------------------------------------------------------------------------
show parameter db_recovery
alter system set db_recovery_file_dest='/u01/oracle/flash_recovery_area' scope=both;
alter system set db_recovery_file_dest_size='80G' scope=both;
--取消Standby DB的同步，创建可保障的还原点。
alter database recover managed standby database cancel;
create restore point beforetest guarantee flashback database;
alter database activate standby database;
alter database open;
--将激活的Standby 库闪回并重新转换为Standby DB。
startup mount force
flashback database to restore point beforetest;
alter database convert to physical standby ;
--删除之前创建的还原点并重新开启同步
startup mount force
drop restore point beforetest;
alter database recover managed standby database disconnect from session;

常用查询
--查看数据库状态
select open_mode from v$database;
--查看延时情况
col VALUE format a20
select NAME,VALUE from v$dataguard_stats where NAME='apply lag';
--查看切换状态
select switchover_status from v$database;
--查看是否同步yes or no
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
--调用生效
alter system set log_archive_dest_state_2=defer;
alter system set log_archive_dest_state_2=enable;
--查看配置文件是否报错
select dest_id,dest_name,error from v$archive_dest WHERE DEST_ID<=2 ;
--查看主库情况
select database_role,open_mode,switchover_status from v$database;
--备库查看gap
select * from v$archive_gap;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
set linesize 250
col VALUE format a20
col DESTINATION format a50
select group#,thread#,sequence#,archived,status from v$standby_log;
SELECT group#,bytes/1024/1024 as size_mb,ROUND((bytes - used)/1024/1024,2) as free_mb,ROUND((1 - (bytes - used)/bytes) * 100, 2) as free_percent FROM v$standby_log;

主库检查
--查看状态和scn号还有时间情况
select to_char(current_scn),sysdate,open_mode,database_role,switchover_status from v$database;
--查询dg相关配置
SELECT name,value,display_value,isdefault FROM v$parameter WHERE name IN ('log_archive_config','db_unique_name','fal_server','fal_client','standby_file_management','log_archive_dest_2');
--查看远程归档设置是否正常
select * from v$archive_dest WHERE status != 'INACTIVE';
--查看远程归档状态是否正常
select * from v$archive_dest_status WHERE status != 'INACTIVE';
-- 检查归档传输情况
SELECT thread#, sequence#, applied, completion_time FROM v$archived_log WHERE dest_id = 2 and applied = 'NO' ORDER BY completion_time DESC;
备库检查
--检查备库进程情况
SELECT process, status, thread#, sequence#, block#, blocks FROM v$managed_standby;
--检查Standby Redo Logs (SRL) 空间情况
SELECT group#,bytes/1024/1024 as size_mb,ROUND((bytes - used)/1024/1024,2) as free_mb,ROUND((1 - (bytes - used)/bytes) * 100, 2) as free_percent FROM v$standby_log;
--检查归档延时
SELECT * FROM v$dataguard_stats;

select * from v$standby_event_histogram;
常用部署方案
单实例新增DG节点-duplicate
#主库设置
--alter system set db_unique_name=zlemr scope=spfile; 这里我用的原库名，有些人可能都是建议主备都改对应的名字
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(zky,zkydg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=E:/u01/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=zky' scope=both;
alter system set LOG_ARCHIVE_DEST_2= 'service=zkydg lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=zkydg' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set FAL_SERVER='orcl_s' scope=both;
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/zky/','/u01/app/oracle/oradata/zky/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/u01/archive/','/u01/archive/' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
alter system set FAL_SERVER='zhankdg' scope=both;
create pfile='/u01/zky.txt' from spfile;

#备库调整
*.db_unique_name='zkydg'
*.log_archive_config='DG_CONFIG=(zky,zkydg)'
*.log_archive_dest_1='LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=orcldg'
*.log_archive_dest_2='service=orcl lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=orcl'
*.fal_server='zky'
*.db_file_name_convert='/u01/app/oracle/oradata/zky/','/u01/app/oracle/oradata/zky/'
*.log_file_name_convert='/u01/archive/','/u01/archive/'
create spfile from pfile='/u01/zky.txt';
shutdown immediate
startup nomount

#拉备库(备库启动到mount)
rman target sys/oracle@zky auxiliary sys/oracle@zkydg
duplicate target database for standby from active database nofilenamecheck;

DG环境新增节点-duplicate
适用场景：在现有dg环境中新增一个节点，或者说主库曾经是dg中的一个节点，或者是已经配置了DG相关参数
操作思想：tnsname.ora、dg参数

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(zky,zkydg,zkydgb)' scope=both;
alter system set LOG_ARCHIVE_DEST_3= 'service=zkydgb lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=zkydgb' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
create pfile='/u01/zky.txt' from spfile;

#备库调整
*.db_unique_name='zkydgb'
*.log_archive_config='DG_CONFIG=(zky,zkydg,zkydgb)'
*.log_archive_dest_1='LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=zkydgb'
*.log_archive_dest_2='service=zky lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=zky'
*.fal_server='zky'
*.db_file_name_convert='/u01/app/oracle/oradata/zky/','/u01/app/oracle/oradata/zky/'
*.log_file_name_convert='/u01/archive/','/u01/archive/'

create spfile from pfile='/u01/zky.txt';
shutdown immediate
startup nomount

rman target sys/oracle@zky auxiliary sys/oracle@zkydg
duplicate target database for standby from active database nofilenamecheck;

DG节点重建-duplicate

适用场景：之前该数据库以备库模式一致运行在，或者一直属于dg的一个物理节点。备库的监听啥的都可以直接复用
操作思想：重启数据库到mount状态，直接duplicate重新拉。拉之前可以将删除数据文件控制文件日志文件。
注意项：如果参数文件和密码报错就需要去主库重新考

shutdown immediate
startup nomount

rman target sys/oracle@zky auxiliary sys/oracle@zkydg
duplicate target database for standby from active database nofilenamecheck;

帐号		自动登录	找回密码
密码			注册

[Oracle] OracleDG常用命令集合（搭建、维护、测试、激活）