侧边栏壁纸
博主头像
暮渔木鱼的技术小栈 博主等级

  • 累计撰写 76 篇文章
  • 累计创建 15 个标签
  • 累计收到 2 条评论

目 录CONTENT

文章目录
Oracle

CentOS环境下Oracle 11g DataGuard搭建教程

暮渔木鱼
暮渔木鱼
2025-01-12 / 0 评论 / 3 点赞 / 27 阅读 / 0 字 / 正在检测是否收录...

使用duplicate创建物理standby

1.环境准备

服务器信息

主库DG1 备库DG2
主机IP 192.168.5.79 192.168.5.80
db_name orcl11g orcl11g
db_unique_name orcl11g orcl11gdg
ORACLE_BASE /u01/app/oracle /u01/app/oracle
ORACLE_HOME /u01/app/oracle/product/11.2.0/db /u01/app/oracle/product/11.2.0/db
主机名 primarynode standbynode
数据库版本 11.2.0.4 11.2.0.4
操作系统 centos8 centos8
instance_name orcl11g orcl11g
service_name orcl11g orcl11g
数据库安装 安装数据库软件，创建监听和数据库实例 仅安装数据库软件

注意：

  • 数据盘目录需要一致。
  • 操作系统需要一致。
  • Oracle版本需要一致，必须是完全一致。
  • 两台服务器时间需要一致，否则会出现主备不同步。
  • 两台机器的网络带宽应该根据同步的数据量去评估。

上传软件

使用服务器将文件传输到192.168.5.80上

在192.168.5.80上查看

挂载镜像

在两台服务器分别执行以下语句

$ mkdir -p /mnt/cdrom
$ mount CentOS-8.4.2105-x86_64-dvd1.iso /mnt/cdrom
$ df -h|grep /mnt

2.安装数据库

  1. 主库

安装数据库软件，创建监听和数据库实例

执行脚本一键安装

$ chmod -R 775 OracleShellInstall
$ ./OracleShellInstall -lf ens160 `# local ip ifname`\
-n primarynode `# hostname`\
-op oracle `# oracle password`\
-d /u01 `# software base dir`\
-ord /oradata `# data dir`\
-o orcl11g `# dbname`\
-dp oracle `# sys/system password`\
-ds AL32UTF8 `# database character`\
-ns AL16UTF16 `# national character`\
-redo 50 `# redo size`\
-opd Y `# optimize db`

二、备库

只安装数据库软件

$ chmod -R 775 OracleShellInstall
$ ./OracleShellInstall -lf ens160 `# local ip ifname`\
-n standbynode `# hostname`\
-op oracle `# oracle password`\
-d /u01 `# software base dir`\
-ord /oradata `# data dir`\
-o orcl11g `# dbname`\
-dp oracle `# sys/system password`\
-ds AL32UTF8 `# database character`\
-ns AL16UTF16 `# national character`\
-ud Y `# 只安装数据库软件`

3.部署DG

（一）基础环境配置

两个节点都执行：
修改主备库的主机名并进行hosts文件配置

#主库
hostnamectl set-hostname primarynode  
#备库
hostnamectl set-hostname standbynode

将IP解析写入文件

cat <<EOF >>/etc/hosts
192.168.5.79 primarynode
192.168.5.80 standbynode
EOF

注意：注意修改listener.ora和tnsnames.ora两个文件对应的HOST。
路径为/u01/app/oracle/product/11.2.0/db/network/admin

（二）主库DG1配置

1、开启归档

关闭数据库

SQL> shutdown immediate

启动数据库到 mount

SQL> startup mount;

开启归档

SQL> alter database archivelog;

打开数据库

SQL> alter database open;

查询归档模式是否开启

select name,open_mode,log_mode from v$database;

查询是否启用强制归档

SQL>select force_logging from v$database;
SQL>archive log list;

2、主库启用强制记录日志功能

查询是否启用强制记录日志

SQL>select force_logging from v$database;

如果未启用，则使用下面语句来开启强制记录日志

SQL> alter database force logging;

3、主库参数配置

一共11个参数需要进行配置。这是关键，如果参数有误，那么搭建会失败。

1、db_unique_name

SQL> alter system set db_unique_name = 'orcl11g' scope=spfile;

2、log_archive_config**

SQL> alter system set log_archive_config='DG_CONFIG=(orcl11g,orcl11gdg)' scope=spfile;

3、log_archive_dest_1

查看归档日志的目录位置

SQL> select name, archived, dest_id, completion_time from v$archived_log order by completion_time desc;

把上面查到的目录路径【/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog】放到下面来。备注：各自的库不一样，根据自己的实际路径来，文章中只做示例

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl11g' scope=both;

4、log_archive_dest_2

SQL> alter system set log_archive_dest_2='SERVICE=tnsorcl11gstandby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl11gdg' scope=spfile;

5、log_archive_dest_state_1

SQL> alter system set log_archive_dest_state_1 = ENABLE;

6、log_archive_dest_state_2

SQL> alter system set log_archive_dest_state_2 = ENABLE;

7、db_file_name_convert

查找数据文件的位置

SQL> select name from v$datafile;

上述语句查出的目录路径 【/oradata/orcl11g】写入下面的语句中替换。备注：各自的库不一样，根据自己的实际路径来，文章中只做示例

SQL> alter system set db_file_name_convert='/oradata/orcl11g','/oradata/orcl11g' scope=spfile;

8、log_file_name_convert

查看在线日志文件的位置：

SQL> select member from v$logfile;

上述语句查出的目录路径 【/oradata/orcl11g】写入下面的语句中替换。备注：各自的库不一样，根据自己的实际路径来，文章中只做示例

SQL> alter system set log_file_name_convert='/oradata/orcl11g','/oradata/orcl11g' scope=spfile;

9、standby_file_management

SQL> alter system set standby_file_management=auto scope=spfile;

10、fal_client

SQL> alter system set fal_client='tnsorcl11g' scope=both;

11、fal_server

SQL> alter system set fal_server='tnsorcl11gstandby' scope=both;

4、主库静态监听配置

配置 listener.ora和 tnsnames.ora

$ cd /u01/app/oracle/product/11.2.0/db/network/admin


$ cat listener.ora

# listener.ora Network Configuration File:  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =  
  (DESCRIPTION_LIST =  
    (DESCRIPTION =  
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  
      (ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521))  
    )  
  )
SID_LIST_LISTENER =   
  (SID_LIST =   
    (SID_DESC =   
      (GLOBAL_DBNAME=orcl11g)  #修改GLOBAL_DBNAME  
      (SID_NAME = orcl11g)      #修改SID_NAME  
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)       
    )   
  ) 
ADR_BASE_LISTENER = /u01/app/oracle


$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
tnsorcl11g =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.79)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl11g)
    )
  )

tnsorcl11gstandby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.80)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl11g)
    )
  )

主库重启监听程序

$ lsnrctl status
$ lsnrctl stop
$ lsnrctl start

查看监听状态:

$ lsnrctl status

（三）备库DG2配置

1、密码文件

在dataguard中，主库与备库sys密码需一致，需要将主库的密码文件拷贝到备库中。

[oracle@primarynode admin]$ cd $ORACLE_HOME/dbs
[oracle@primarynode admin]$ scp orapworcl11g oracle@192.168.5.80:/u01/app/oracle/product/11.2.0/db/dbs

如果不拷贝密码文件，直接使用 orapwd生成一个新的密码文件，密码需与主库一致：

[oracle@primarynode dbs]$ orapwd file=orapworcl11g password='oracle';

2、监听文件

由于备库只安装数据库软件，未安装实例以及创建监听，备库可能不存在 tnsnames.ora，可以将主库的文件传输到备库。传输前先查看备库是否存在 tnsnames.ora；

[oracle@primarynode admin]$ scp tnsnames.ora oracle@192.168.5.80:/u01/app/oracle/product/11.2.0/db/network/admin

进入到监听文件存放位置，做下一步配置

[oracle@standbynode dbs]$ cd /u01/app/oracle/product/11.2.0/db/network/admin/

1、配置备库 listener.ora

配置listener.ora 文件

[oracle@standbynode admin]$ vi listener.ora
#新增以下内容
# listener.ora Network Configuration File: /u01/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521))
      )
    )
  )
SID_LIST_LISTENER = 
  (SID_LIST = 
    (SID_DESC = 
      (GLOBAL_DBNAME=orcl11g)
      (SID_NAME = orcl11g)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db) 
    ) 
  )   
ADR_BASE_LISTENER = /u01/oracle

2、配置备库 tnsnames.ora

[oracle@standbynode admin]$ cat tnsnames.ora
tnsorcl11g =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.79)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl11g)
    )
  )

tnsorcl11gstandby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.80)(PORT = 1521))
    )
    (CONNECT_DATA = 
      (SERVICE_NAME = orcl11g)
    )
  )

查看监听状态是否启用，未启用则开启

[oracle@standbynode admin]$ lsnrctl status;
[oracle@standbynode admin]$ lsnrctl start;

3、初始化参数文件

1、在主库生成初始化参数文件

SQL> create pfile from spfile;

2、拷贝主库的参数文件到备库

[oracle@primarynode dbs]$ scp initorcl11g.ora oracle@192.168.5.80:/u01/app/oracle/product/11.2.0/db/dbs

主库操作

备库查看

3、修改备库的参数文件内容

[oracle@standbynode:/u01/app/oracle/product/11.2.0/db/dbs]$ cat initorcl11g.ora
orcl11g.__db_cache_size=4311744512
orcl11g.__java_pool_size=16777216
orcl11g.__large_pool_size=33554432
orcl11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl11g.__pga_aggregate_target=1308622848
orcl11g.__sga_target=5200936960
orcl11g.__shared_io_pool_size=0
orcl11g.__shared_pool_size=805306368
orcl11g.__streams_pool_size=0
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._datafile_write_errors_crash_instance=FALSE
*._index_partition_large_extents='FALSE'
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/u01/app/oracle/admin/orcl11g/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.control_files='/oradata/orcl11g/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl11g/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.db_domain=''
*.db_file_name_convert='/oradata/orcl11g','/oradata/orcl11g'
*.db_files=5000
*.db_name='orcl11g' #数据库名保持一致
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='orcl11gdg' #修改为备库
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl11gXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.fal_server='tnsorcl11g' #与主库调换
*.fal_client='tnsorcl11gstandby' #与主库调换
*.log_archive_config='DG_CONFIG=(orcl11g,orcl11gdg)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl11gdg' #DB_UNIQUE_NAME与主库调换
*.log_archive_dest_2='SERVICE=tnsorcl11g LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl11g' #SERVICE配置主库 DB_UNIQUE_NAME与主库调换 
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.parallel_max_servers=64
*.pga_aggregate_target=1298137088
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sga_max_size=5194645504
*.sga_target=5194645504
*.standby_file_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

修改后的参数文件

orcl11g.__db_cache_size=4311744512
orcl11g.__java_pool_size=16777216
orcl11g.__large_pool_size=33554432
orcl11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl11g.__pga_aggregate_target=1308622848
orcl11g.__sga_target=5200936960
orcl11g.__shared_io_pool_size=0
orcl11g.__shared_pool_size=805306368
orcl11g.__streams_pool_size=0
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._datafile_write_errors_crash_instance=FALSE
*._index_partition_large_extents='FALSE'
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/u01/app/oracle/admin/orcl11g/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.control_files='/oradata/orcl11g/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl11g/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.db_domain=''
*.db_file_name_convert='/oradata/orcl11g','/oradata/orcl11g'
*.db_files=5000
*.db_name='orcl11g'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='orcl11gdg'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl11gXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.fal_server='tnsorcl11g'
*.fal_client='tnsorcl11gstandby'
*.log_archive_config='DG_CONFIG=(orcl11g,orcl11gdg)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl11gdg'
*.log_archive_dest_2='SERVICE=tnsorcl11gstandby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl11g'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.parallel_max_servers=64
*.pga_aggregate_target=1298137088
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sga_max_size=5194645504
*.sga_target=5194645504
*.standby_file_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

注意：里面涉及到路径的需要手动创建

[oracle@standbynode dbs]$ mkdir -p /u01/app/oracle/admin/orcl11g/adump
[oracle@standbynode oracle]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCL11G/archivelog
[oracle@standbynode dbs]$ mkdir -p /u01/app/oracle/oradata/orcl11g
[oracle@standbynode fast_recovery_area]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl11g/

4、测试网络连通性

主备之间互相测试连通性

[oracle@standbynode admin]$ tnsping tnsorcl11g
[oracle@primarynode dbs]$ tnsping tnsorcl11gstandby

主库：

备库：

5、在主库进行登录测试

[oracle@primarynode dbs]$ sqlplus sys/oracle@tnsorcl11g as sysdba


[oracle@primarynode dbs]$ sqlplus sys/oracle@tnsorcl11gstandby as sysdba

备库如果登录不上报错【ORA-01017】时，检查1、密码文件此步骤是否配置完成；

6、添加主库和备库的standby日志组

查询当前的 redo_log组。原则上 standby比当前多一组，且大小一致；

SQL> select group#,BYTES/1024/1024,members from v$log;

先查看 redo日志的位置

SQL> select member from v$logfile;

在主库创建 standby日志组
编写4个日志组，根据上面查出来的编号进行排序。

SQL> alter database add standby logfile group 9 '/oradata/orcl11g/standby09.log' size 50M;
alter database add standby logfile group 10 '/oradata/orcl11g/standby10.log' size 50M;
alter database add standby logfile group 11 '/oradata/orcl11g/standby11.log' size 50M;
alter database add standby logfile group 12 '/oradata/orcl11g/standby12.log' size 50M;
alter database add standby logfile group 13 '/oradata/orcl11g/standby13.log' size 50M;
alter database add standby logfile group 14 '/oradata/orcl11g/standby14.log' size 50M;
alter database add standby logfile group 15 '/oradata/orcl11g/standby15.log' size 50M;
alter database add standby logfile group 16 '/oradata/orcl11g/standby16.log' size 50M;
alter database add standby logfile group 17 '/oradata/orcl11g/standby17.log' size 50M;

SQL> alter system set standby_file_management=auto;

添加备库的standby日志组(可以 不添加)

SQL> alter database add standby logfile group 9 '/oradata/orcl11g/standby09.log' size 50M;
alter database add standby logfile group 10 '/oradata/orcl11g/standby10.log' size 50M;
alter database add standby logfile group 11 '/oradata/orcl11g/standby11.log' size 50M;
alter database add standby logfile group 12 '/oradata/orcl11g/standby12.log' size 50M;
alter database add standby logfile group 13 '/oradata/orcl11g/standby13.log' size 50M;
alter database add standby logfile group 14 '/oradata/orcl11g/standby14.log' size 50M;
alter database add standby logfile group 15 '/oradata/orcl11g/standby15.log' size 50M;
alter database add standby logfile group 16 '/oradata/orcl11g/standby16.log' size 50M;
alter database add standby logfile group 17 '/oradata/orcl11g/standby17.log' size 50M;

7、创建spfile

在备库使用 pfile文件创建 spfile文件

在备库中登陆到 idle数据库

[oracle@standbynode dbs]$ sqlplus / as sysdba;
SQL> create spfile from pfile;
SQL> startup nomount;

如有 ORA-00845报错，需要进行以下配置:

vi /etc/fstab
#新增
tmpfs                   /dev/shm                tmpfs   defaults,size=4096M        0 0

mount -o remount /dev/shm

然后继续执行 startup nomount

SQL> startup nomount

8、使用duplicate创建物理standby（有很多种办法，此处介绍一种）,在备库执行

（1）连接到主备库
[oracle@primarynode dbs]$ rman target sys/oracle@tnsorcl11g auxiliary sys/oracle@tnsorcl11gstandby nocatalog

（2）使用duplicate复制数据库
RMAN> duplicate target database for standby from active database nofilenamecheck;

...等待程序跑完...

9、开始同步数据库，在备库上执行

SQL>alter database open;

10、开启实时同步

SQL> alter database recover managed standby database using current logfile disconnect from session;

至此DG搭建完成

（四）查看主备库状态

主库检查

SQL>set line 200
set pagesize 200
select name,remote_archive,database_role,guard_status,db_unique_name from v$database;

备库检查

SQL>set line 200
set pagesize 200
select name,remote_archive,database_role,guard_status,db_unique_name from v$database;

查询主备库日志是否同步（确保最大 SEQUENCE#相同）

SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

主库：

备库：

检查日志是否被备库使用（APPLIED 值为 yes）

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

（五）DataGuard测试

在主库上创建测试表并插入数据

SQL> create table test(id  number,name  varchar2(255));
insert into test values(1,'zhangsan');
insert into test values(2,'lisi');
commit;

在 standby数据库上查询测试表，验证数据同步成功

SQL>select * from test;

日常运维必备命令

主备库角色状态查询

SQL> select switchover_status,database_role from v$database;

查看 dataguard最新状态

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set line 160
set pagesize 1000
col type for a20
select process, status, thread#,sequence#, block#, blocks from v$managed_standby;
select GROUP#,THREAD#,SEQUENCE#,STATUS,LAST_CHANGE#,LAST_TIME from v$standby_log;
select START_TIME,TYPE, ITEM,UNITS,SOFAR,TIMESTAMP from v$recovery_progress where ITEM='Last Applied Redo';

查看 redo_log

主库查看 redo log

SQL> select group#,thread#,bytes/1024/1024 M,status from v$log;

备库查看 standby log

SQL> select group#,thread#,bytes/1024/1024M,status,last_change#,last_time from v$standby_log;

主、备库查看当前日志状况

SQL> select sequence#,status from v$log;

手动启动MRP0进程

SQL> alter database recover managed standby database disconnect from session;
SQL> select open_mode,current_scn from v$database;
SQL> select * from v$standby_log;
SQL> select process,client_process,sequence#,status,BLOCK#,BLOCKS,THREAD# from v$managed_standby;

开启数据库闪回

SQL> alter database flashback on;

重启监听并查看状态

[oracle@myoracle admin]$ lsnrctl stop
[oracle@myoracle admin]$ lsnrctl start
[oracle@myoracle admin]$ lsnrctl status

如看不到服务，需执行以下命令：
显示服务名：

SQL>show parameter service_names

强制注册服务：

SQL>alter system register;

主备库切换

1、主库切换到备库

SQL> alter database commit to switchover to physical standby;
SQL> alter database commit to switchover to physical standby with session shutdown;
#如果状态显示SESSION ACTIVE，在切换的时候可以指定with session shutdown 子句强制关闭活动的会话。
SQL> shutdown immediate
SQL> startup mount

2、备库切换到主库

SQL> alter database commit to switchover to primary; 
SQL> alter database open;
#原主库(新备库)执行APPLY LOG命令
--启用备库mount状态下的APPLY LOG
SQL> alter database recover managed standby database disconnect from session;
--启用open状态（READ ONLY WITH APPLY）下的APPLY LOG
SQL> alter database recover managed standby database cancel;

SQL> alter database open;
SQL> alter database recover managed standby database disconnect from session;
SQL> select switchover_status,database_role,open_mode from v$database;

恢复主备GAP（适用于11g以后版本）

SQL> recover database from service orcl11g noredo using compressed backupset;

同步情况检查

SQL>archive log list;#检查当前log号 Current log sequence和主库一致

查看DG库归档是否有缺失

SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;

开启归档应用

SQL>alter database recover managed standby database  disconnect from session;

如果开启了实时归档，可以执行以下命令

SQL>alter database recover managed standby database cancel;
SQL>alter database open;
SQL>alter database recover managed standby database using current logfile disconnect;

开启和关闭 DG

开启 DG

#启动备库（mount）——启动主库（open）——启动备库日志同步
#登录备库：
$lsnrctl start
SQL>startup mount;
#登录主库：
$lsnrctl start
SQL>startup
#登录备库：
SQL> alter database recover managed standby database disconnect from session;

关闭 DG（请确保应用程序已经断开数据库连接或关闭）

#关闭备库日志同步——关闭主库——关闭备库
#登录备库：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
#登录主库：
SQL>shutdown  immediate
$lsnrctl  stop
#登录备库
SQL>shutdown  immediate
$lsnrctl  stop
3
数据库 Oracle

  1. 支付宝打赏

    qrcode alipay

  2. 微信打赏

    qrcode weixin
版权归属： 暮渔木鱼
本文链接： https://blog.krielwus.top/archives/3aa3a3ae-5420-40ca-993f-7967285fbe06
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区