oracle 11g data guard 搭建

johnnypeng

于 2025-02-26 15:53:09 发布

阅读量662

点赞数 6

CC 4.0 BY-SA版权

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/johnnypeng/article/details/145880729

#结合收集到的知识点，真实环境成功搭建后总结的详细过程记录笔记和验证方法#

#分享学习# #工作笔记#

内容提要：

环境准备

主库配置

备库配置

数据恢复（主库数据同步到备库）

同步测试验证

自启动配置

主备切换

常用命令

环境准备：
主库：IP:172.16.210.106，
ORACLE_SID：twms8200
ORACLE_BASE: /u01/app/oracle
ORACLE_HOME:$ORACLE_BASE/product/11.2.0/db_1
归档模式:是
数据库安装：软件、监听、建库(netca、dbca)

备库：IP:172.16.210.107，
ORACLE_SID：twms8200
ORACLE_BASE: /u01/app/oracle
ORACLE_HOME:$ORACLE_BASE/product/11.2.0/db_1
归档模式:否
数据库安装：软件、监听不建库

安装前的准备：

1、oracle 数据库已经按照要求安装；
2、服务器时钟同步完成配置（确保两台服务器时间一致）。

DG 配置：

一、主库配置
1、开启归档模式
执行以下命令：
SQL> archive log list;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
SQL> alter database force logging;
SQL> select name,log_mode,force_logging from v$database;

NAME LOG_MODE FOR
--------- ------------ ---
TWMS8200 ARCHIVELOG YES
2、创建standby redolog日志组
查看当前线程与日志组的对应关系及日志组的大小：
SQL> select thread#,group#,bytes/1024/1024 from v$log;

THREAD# GROUP# BYTES/1024/1024
---------- ---------- ---------------
1 1 50
1 2 50
1 3 50
这里有三组redo log，所以至少需要创建4组Standby redo log，大小均为50M：
SQL> alter database add standby logfile group 4('/u01/app/oracle/oradata/redolog/standbyredo01.log') size 50m;
SQL> alter database add standby logfile group 5('/u01/app/oracle/oradata/redolog/standbyredo02.log') size 50m;
SQL> alter database add standby logfile group 6('/u01/app/oracle/oradata/redolog/standbyredo03.log') size 50m;
SQL> alter database add standby logfile group 7('/u01/app/oracle/oradata/redolog/standbyredo04.log') size 50m;
注意：1、备库执行相同的操作。
2、删除日志组执行：alter database drop standby logfile group 4;
3、如果无法删除，可以先执行：alter database clear logfile group 4;后，再执行删除。
查看standy日志组的信息（备库可执行相同操作进行查询）：
SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;

GROUP# SEQUENCE# STATUS BYTES/1024/1024
---------- ---------- ---------- ---------------
4 0 UNASSIGNED 50
5 0 UNASSIGNED 50
6 0 UNASSIGNED 50
7 0 UNASSIGNED 50
3.创建主库密码文件（备库执行相同操作）
su - oracle
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y
注：orapwd 命令用于创建、管理和删除密码文件。它的一般语法如下：
orapwd file=<密码文件路径> password=<密码> [entries=<最大用户数>] [force=<y/n>]
file: 指定密码文件的路径和名称。
password: 指定要为密码文件设置的密码。
entries: 可选参数，指定密码文件中可以包含的最大用户数。默认值是 10。
force: 可选参数，如果指定为 y，则表示如果密码文件已存在，则会被覆盖。默认值是 n。

4.配置spfile文件
查看spfile文件路径：
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfiletwms8200.ora

用spfile创建一个pfile,用于修改：
create pfile='/tmp/spfiletwms8200.ora' from spfile;

修改pfile文件
在pfile文件追加以下内容：

*.db_unique_name='twms8200pr'
*.fal_client='twms8200pr'
*.fal_server='twms8200dg'
*.standby_file_management='AUTO'
*.log_archive_config='DG_CONFIG=(twms8200pr,twms8200dg)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/archivelog'
*.log_archive_dest_2='SERVICE=twms8200dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=twms8200dg'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oracle/oradata/twms8200','/u01/app/oracle/oradata/twms8200dg'
*.db_file_name_convert='/u01/app/oracle/oradata/twms8200','/u01/app/oracle/oradata/twms8200dg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'

复制pfile文件到spfile

SQL> shutdown immediate;
SQL> create spfile from pfile='/tmp/spfiletwms8200.ora';
SQL> startup;

5.修改监听文件，添加静态监听
vim $ORACLE_HOME/network/admin/listener.ora
修改前：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tbvstudy01)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle

修改后：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tbvstudy01)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = twms8200))
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.210.106)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = twms8200)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = twms8200)
)
)

ADR_BASE_LISTENER = /u01/app/oracle
SAVE_CONFIG_ON_STOP_LISTENER = ON

重启监听服务：
SQL> lsnrctl stop
SQL> lsnrctl start

6.编辑网络服务名配置文件tnsnames.ora
vim $ORACLE_HOME/network/admin/tnsnames.ora
编辑前：
TWMS8200 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tbvstudy01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = twms8200)
)
)
编辑后：
TWMS8200PR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.210.106)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = twms8200)
)
)
TWMS8200DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.210.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = twms8200)
)
)

tnsping测试：
[oracle@primary ~]$ sqlplus sys/oracle@orclpr as sysdba
[oracle@primary ~]$ sqlplus sys/oracle@orcldg as sysdba
[oracle@primary ~]$ tnsping orcldg
[oracle@primary ~]$ tnsping orclpr

二、备库配置

1.将主库中的密码文件、pfile文件、监听文件复制到备库中
cd /u01/app/oracle/product/11.2.0/db_1/dbs
scp orapwtwms8200 oracle@172.16.210.107:/u01/app/oracle/product/11.2.0/db_1/dbs/
scp /tmp/spfiletwms8200.ora oracle@172.16.210.107:/tmp/
cd /u01/app/oracle/product/11.2.0/db_1/network/admin
scp listener.ora oracle@172.16.210.107:/u01/app/oracle/product/11.2.0/db_1/network/admin/
scp tnsnames.ora oracle@172.16.210.107:/u01/app/oracle/product/11.2.0/db_1/network/admin/
注意：拷贝前注意备份备库对应的文件，避免出现问题恢复。

2.配置spfile文件
修改pfile文件
vim /tmp/spfiletwms8200.ora

*.db_unique_name='twms8200dg'
*.fal_client='twms8200dg'
*.fal_server='twms8200pr'
*.standby_file_management='AUTO'
*.log_archive_config='DG_CONFIG=(twms8200pr,twms8200dg)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/archivelog'
*.log_archive_dest_2='SERVICE=twms8200pr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=twms8200pr'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oracle/oradata/twms8200dg','/u01/app/oracle/oradata/twms8200'
*.db_file_name_convert='/u01/app/oracle/oradata/twms8200dg','/u01/app/oracle/oradata/twms8200'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'

注意：log_archive_dest_1 路径目录如果不存在，请手动创建。

复制pfile文件到spfile：
SQL> create spfile from pfile='/tmp/spfiletwms8200.ora';
注意：在复制前请做以下检查和操作：
查看spfile文件路径，确认文件名称一致：
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfiletwms8200.ora

然后执行复制：
复制pfile文件到spfile
SQL> shutdown immediate;
SQL> create spfile from pfile='/tmp/spfiletwms8200.ora';
SQL> startup;

3.修改监听文件
vim $ORACLE_HOME/network/admin/listener.ora
修改后：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tbvstudy02)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = twms8200))
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.210.107)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = twms8200)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = twms8200)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SAVE_CONFIG_ON_STOP_LISTENER = ON

编辑网络服务名配置文件tnsnames.ora
vim $ORACLE_HOME/network/admin/tnsnames.ora
编辑后：
TWMS8200DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.210.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = twms8200)
)
)
TWMS8200PR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.210.106)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = twms8200)
)
)
重启监听：
SQL> lsnrctl stop
SQL> lsnrctl start

4.tnsping测试
[oracle@standby ~]$ tnsping twms8200pr
[oracle@standby ~]$ tnsping twms8200dg

5.手工创建所需的目录
su - oracle
mkdir -p /u01/app/oracle/admin/twms8200/adump
mkdir -p /u01/app/oracle/admin/twms8200/dpdump
mkdir -p /u01/app/oracle/admin/twms8200/pfile
mkdir -p /u01/app/oracle/oradata/twms8200
mkdir -p /u01/app/oracle/flash_recovery_area/twms8200
mkdir -p /u01/app/oracle/oradata/archivelog
注：如果存在不用执行复制，同时注意区分大小写。
根据control_files参数项创建存放控制文件目录
SQL> SHOW PARAMETER control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/twms8200/control01.ctl, /u01/app/oracle/fast_recovery_area/twms8200/control02.ctl

6.启动备库到nomount
SQL> shutdown immediate;
SQL> startup nomount;

三、数据恢复（主库恢复到备库）
1.配置参数检查（备库，主库）
SQL> select status from v$instance;
SQL> show parameter db_unique_name;
SQL> select status from v$instance;

STATUS
------------------------------------
STARTED

SQL> show parameter db_unique_name;

NAME TYPE VALUE
------------------------------------ ---------------------------------------
db_unique_name string twms8200dg

SQL> show parameter db_unique_name;

NAME TYPE VALUE
------------------------------------ -----------
db_unique_name string twms8200pr
SQL> select status from v$instance;

STATUS
------------
OPEN

2.利用RMAN在主库上恢复备库
[oracle@tbvstudy01 twms8200]$ rman target sys/oracle@twms8200pr auxiliary sys/oracle@twms8200dg duplicate target database for standby from active database nofilenamecheck;

注意：一定要在主库上执行

3.登陆备库并查看数据库当前状态
SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ---------------------
twms8200 MOUNTED

RMAN恢复完直接就是mount状态

4.备库启动日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.
SQL> select sequence#,applied from v$archived_log order by 1;

SEQUENCE# APPLIED
---------- ---------------------------
88 YES
89 YES
90 IN-MEMORY

//查看备库的日志同步情况
SQL> SELECT SEQUENCE#, REGISTRAR, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# REGISTRAR FIRST_TIME NEXT_TIME APPLIED
---------- --------------------- ------------------- ------------------- ---------------------------
88 RFS 2024-03-26 13:31:36 2024-03-26 14:13:57 YES
89 RFS 2024-03-26 14:13:57 2024-03-26 14:14:12 YES
90 RFS 2024-03-26 14:14:12 2024-03-26 14:19:39 IN-MEMORY

查看当前会话状态：

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
2 FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED0 0 0 0
ARCH CONNECTED0 0 0 0
ARCH CONNECTED0 0 0 0
ARCH CLOSING1 91 2048 324
RFS IDLE0 0 0 0
RFS IDLE0 0 0 0
RFS IDLE0 0 0 0
RFS IDLE1 92 12014 1
MRP0 APPLYING_LOG1 92 12014 102400

5.分别查看主库和备库的归档序列号是否一致
先在主库手动切换一下日志再查看
SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/archivelog
Oldest online log sequence 90
Next log sequence to archive 92
Current log sequence 92

备库：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/archivelog
Oldest online log sequence 90
Next log sequence to archive 0
Current log sequence 92

6、备库设置只读模式
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;

EX:
SQL> alter database recover managed standby database cancel;

Database altered.

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

SQL> alter database open read only;

Database altered.

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

Database altered.
SQL> select a.status,b.open_mode,a.inst_id from gv$instance a,gv$database b where a.inst_id=b.inst_id;

STATUS OPEN_MODE INST_ID
------------ -------------------- ----------
OPEN READ ONLY WITH APPLY 1
注意：
1.ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 命令用于取消正在进行的实时应用（Redo Apply）操作。执行此命令后，数据库将停止实时应用进程，
但不会自动执行恢复或应用归档日志。
2、alter database recover managed standby database using current logfile disconnect from session;重新启动实时应用进程，并使用当前的归档日志文件进行恢复。

四、同步测试
1、主库执行创建、删除操作
SQL>create table test (id number);
Table created.
SQL>desc test;
Name Null? Type
--------------------- -------- ----------------
ID NUMBER

备库执行：
SQL> desc test;
如果提示：ERRO:
ORA-04043: object test does not exist
表示同步没有成功，
如果有返回值，表示同步成功。

2.主库执行插入操作：
SQL> insert into test values(123);

1 row created.

SQL> commit;

Commit complete.
SQL> select * from test;

ID
----------
123

备库执行查询：
SQL> select * from test;

ID
----------
123

至此，数据库DATA GUARD 搭建成功。

五、自动启动
1.Oracle DG物理备库在数据库重启后，不能自动对日志进行应用，可通过以下触发器来让数据库应用日志。（待定，备库是只读模式，触发器可能会导致异常）

SQL> CREATE OR REPLACE TRIGGER STANDBY_APPLY_LOG
AFTER STARTUP ON DATABASE
BEGIN
DECLARE
DATABASE_ROLE VARCHAR(20);
BEGIN
SELECT DATABASE_ROLE INTO DATABASE_ROLE FROM V$DATABASE;
IF DATABASE_ROLE = 'PHYSICAL STANDBY' THEN
EXECUTE IMMEDIATE 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION';
ELSE
DBMS_OUTPUT.PUT_LINE(DATABASE_ROLE);
END IF;
END;
END STANDBY_APPLY_LOG;
/

六、主备切换

1.检查主备的环境
SQL> select name,switchover_status,database_role from v$database;
验证备库是否有gap
NO GAP，则表示归档目标没有与主数据库之间的归档日志差异。这意味着备库与主库之间的归档日志已经同步，没有任何缺失或延迟。这通常是理想状态，表明备库已经与主库保持了一致性，并且可以准备好用于故障切换或其他目的。
SQL> select t.status,t.gap_status from v$archive_dest_status t where t.dest_id='2';

STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP

2.在主库上执行切换命令(primary节点)
SQL> alter database commit to switchover to physical standby;
--备库启动日志应用
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> shutdown immediate;
SQL> startup mount;
SQL> select name,switchover_status,database_role from v$database;

3.在备库上执行(standby节点)
SQL> select name,switchover_status,database_role from v$database;
--查看standby备库角色的状态
SQL> alter database commit to switchover to primary with session shutdown;
--将备库的角色修改为primary
SQL> select name,switchover_status,database_role from v$database;

打开数据库
SQL> alter database open;
--修改以前主库为日志应用
--备库启动日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;

数据库运行时间长，切换的时间会很长。主备库执行：

SQL> alter system flush buffer_cache;
SQL> alter system checkpoint;
SQL> shutdown immediate;

七、常用命令
--Primary:
SELECT T.NAME,T.OPEN_MODE,T.DATABASE_ROLE,T.PROTECTION_MODE,T.PROTECTION_LEVEL FROM V$DATABASE T;
--Standby:
SELECT T.NAME,T.OPEN_MODE,T.DATABASE_ROLE,T.PROTECTION_MODE,T.PROTECTION_LEVEL FROM V$DATABASE T;
SQL> SELECT T.NAME,T.OPEN_MODE,T.DATABASE_ROLE,T.PROTECTION_MODE,T.PROTECTION_LEVEL FROM V$DATABASE T;

NAME OPEN_MODE DATABASE_ROLEPROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- ---------------- -------------------- --------------------
TWMS8200 READ WRITE PRIMARYMAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL> SELECT T.NAME,T.OPEN_MODE,T.DATABASE_ROLE,T.PROTECTION_MODE,T.PROTECTION_LEVEL FROM V$DATABASE T;

NAME OPEN_MODE DATABASE_ROLEPROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- ---------------- -------------------- --------------------
TWMS8200 READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE