1. 云图网首页
  2. 技术专区
  3. 数据库

DataGuard之一：Oracle10g dg搭建

• 数据库

最近准备给一个生产项目上oracle10g Dataguard, 两台上机上均装好oracle 10204软件，并在一台上已经建库。
OS: Red Hat Enterprise 5
DB: Oracle 10204

这篇日志不讲述 DataGuard 的原理，只是oracle 10g DataGuard 搭建的详细过程。

1 将主库设为LOGGING模式,并生成初始化参数文件和，standby 控制文件

1
 
SQL> alter database force logging

将主库设为归档模式

1
2
3
4
 
SQL> SHUTDOWN IMMEDIATE;  
SQL> STARTUP MOUNT;  
SQL> ALTER DATABASE ARCHIVELOG;  
SQL> ALTER DATABASE OPEN;

创建初始化参数文件

1
2
 
SQL> create pfile='/oradata/initbc_ocp5.ora' from spfile;
File created.

创建standby控制文件,在主库上执行

1
2
 
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/script/control01.ctl';
Database altered.

2 关闭主库,复制数据文件,redo和 standby 控制文件到备机的相应目录

1
2
 
tar cvf skycard.tar /oradata/skycard  
-- ftp控制文件(standby controfile),数据文件(skycard.tar)到从库相应目录

生成三份控制文件

1
2
 
cp control01.ctl control02.ctl  
cp control01.ctl control03.ctl

3 修改主库的初始化参数文件,增加以下内容在主库$ORACLE_HOME/dbs/initskycard.ora,增加以下内容

1
2
3
4
5
6
7
8
9
10
 
*.DB_UNIQUE_NAME='PRIMARY_1'  
*.log_archive_config='DG_CONFIG=(PRIMARY_1,STANDBY_1)'  
*.log_archive_dest_1='LOCATION=/opt/oracle/archive/skycard/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY_1'  
*.log_archive_dest_2='SERVICE=STANDBY_1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY_1'  
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE  
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE  
*.FAL_SERVER=STANDBY_1  
*.FAL_CLIENT=PRIMARY_1  
*.standby_file_management='AUTO'  
*.LOG_ARCHIVE_MAX_PROCESSES=3

4 修改从库$ORACLE_HOME/dbs/initskycard.ora参数，增加以下内容

1
2
3
4
5
6
7
8
9
10
 
*.DB_UNIQUE_NAME='STANDBY_1'  
*.log_archive_config='DG_CONFIG=(PRIMARY_1,STANDBY_1)'  
*.log_archive_dest_1='LOCATION=/opt/oracle/archive/skycard/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY_1'  
*.log_archive_dest_2='SERVICE=PRIMARY_1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY_1'  
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE  
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE  
*.FAL_SERVER=PRIMARY_1  
*.FAL_CLIENT=STANDBY_1  
*.standby_file_management='AUTO'  
*.LOG_ARCHIVE_MAX_PROCESSES=3

5 配置主库和备库上的 tnsnames.ora ,并测试连通性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
PRIMARY_1=  
 (DESCRIPTION=  
 (ADDRESS=  
 (PROTOCOL=TCP)  
 (HOST=134.109.171.72)  
 (PORT=1521)  
 )  
 (CONNECT_DATA=  
 (SERVICE_NAME=skycard)  
 )  
 )
STANDBY_1=  
 (DESCRIPTION=  
 (ADDRESS=  
 (PROTOCOL=TCP)  
 (HOST=134.109.171.73)  
 (PORT=1521)  
 )  
 (CONNECT_DATA=  
 (SERVICE_NAME=skycard)  
 )  
 )

6 主库的 listen.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora  
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =  
 (SID_LIST =  
 (SID_DESC =  
 (SID_NAME = PLSExtProc)  
 (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)  
 #(PROGRAM = extproc)
 )  
(SID_DESC =  
 (GLOBAL_DBNAME = skycard)  
 (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)  
 (SID_NAME = skycard)  
 )  
 )
LISTENER =  
 (DESCRIPTION_LIST =  
 (DESCRIPTION =  
 (ADDRESS = (PROTOCOL = TCP)(HOST = db-134-109-171-72.test-db.com.hz.sandun )(PORT = 1521))  
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))  
 )  
 )

7 从库的 listen.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora  
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =  
 (SID_LIST =  
 (SID_DESC =  
 (SID_NAME = PLSExtProc)  
 (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)  
 #(PROGRAM = extproc)
 )  
(SID_DESC =  
 (GLOBAL_DBNAME = skycard)  
 (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)  
 (SID_NAME = skycard)  
 )  
 )
LISTENER =  
 (DESCRIPTION_LIST =  
 (DESCRIPTION =  
 (ADDRESS = (PROTOCOL = TCP)(HOST = db-134-109-171-73.test-db.com.hz.sandun )(PORT = 1521))  
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))  
 )  
 )

8 在从库上执行,创建密码文件

1
 
$ORACLE_HOME/dbs/orapwd file=orapwskycard password=12dfdf entries=10

9 创建相应的目录

1
2
3
 
cd $ORACLE_BASE/admin  
mkdir skycard  
mkdir adump bdump cdump dpdump pfile scripts udump

10 启动从库到mount状态

1
2
3
4
5
6
7
8
 
SQL> startup nomount pfile='/opt/oracle/product/10.2.0/db_1/dbs/initskycard.ora';  
ORACLE instance started.
Total System Global Area 3355443200 bytes  
Fixed Size 2087640 bytes  
Variable Size 671089960 bytes  
Database Buffers 2667577344 bytes  
Redo Buffers
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

初始化log apply服务

1
 
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11 打开主库并测试archive log 是否传送
主库上执行

1
2
3
4
5
6
7
8
9
 
SQL> startup;  
ORACLE instance started.
Total System Global Area 251658240 bytes  
Fixed Size 1218820 bytes  
Variable Size 96470780 bytes  
Database Buffers 150994944 bytes  
Redo Buffers 2973696 bytes  
Database mounted.  
Database opened.

主库上执行

1
2
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
Database altered.

到备库主机上查看归档文件是否传来

1
2
3
4
 
cd /opt/oracle/archive/skycard/  
[oracle@db-134-109-171-73](mailto:oracle@db-134-109-171-73)-> ll  
-rw-r----- 1 oracle oinstall 196K Dec 16 09:42 1_20_737391288.dbf  
-rw-r----- 1 oracle oinstall 120K Dec 16 09:45 1_21_737391288.dbf

说明日志文件已经传送

12 切换测试(停主库,启备库)

主库上操作

1
 
alter database commit to switchover to physical standby;

说明：如果此时有连接着的会话，这个命令会报错，需要加个”with session shutdown” 属性

1
2
3
4
5
6
7
8
9
10
 
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup nomount;  
alter database mount standby database;
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE  
--------- ---------- -------------------- ----------------  
SKYCARD MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

在备库上操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE  
--------- ---------- -------------------- ----------------  
SKYCARD READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE  
--------- ---------- -------------------- ----------------  
SKYCARD MOUNTED MAXIMUM PERFORMANCE PRIMARY
SQL> shutdown immediate;
SQL> startup nomount pfile='/opt/oracle/product/10.2.0/db_1/dbs/initskycard.ora';  
ORACLE instance started.
Total System Global Area 3355443200 bytes  
Fixed Size 2087640 bytes  
Variable Size 671089960 bytes  
Database Buffers 2667577344 bytes  
Redo Buffers 14688256 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
  
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE  
--------- ---------- -------------------- ----------------  
SKYCARD READ WRITE MAXIMUM PERFORMANCE PRIMARY

完成切换。

13 测试一:数据测试
主库上创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
SQL> create table test_tf (id integer ,remark varchar2(32));
Table created.
SQL> insert into test_tf values (1,'tf');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_tf;
 ID REMARK  
---------- --------------------------------  
 1 tf
SQL> alter system switch logfile;
System altered.
--从库上验证  
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE  
--------- ---------- -------------------- ----------------  
SKYCARD READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY
SQL> select * from test_tf;
 ID REMARK  
---------- --------------------------------  
 1 tf

数据已经过来，测试成功。

测试二，在主库上新建表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SQL> select name from v$tablespace;
NAME  
------------------------------  
SYSTEM  
UNDOTBS1  
SYSAUX  
USERS  
TEMP  
TS_SKYCARD  
TS_TEST_02
SQL> create tablespace ts_test_03 datafile '/opt/oradata/skycard/ts_skyts_test_03.dbf' size 20M autoextend off;
Tablespace created.
SQL> alter system switch logfile;
System altered.

备库日志情况

1
2
3
4
5
6
7
8
9
10
11
 
Thu Dec 16 10:38:29 2010  
RFS[1]: Archived Log: '/opt/oracle/archive/skycard/1_33_737391288.dbf'  
Primary database is in MAXIMUM PERFORMANCE mode  
Thu Dec 16 10:38:34 2010  
Media Recovery Log /opt/oracle/archive/skycard/1_33_737391288.dbf  
WARNING: File being created with same name as in Primary  
Existing file may be overwritten  
Recovery created file /opt/oradata/skycard/ts_skyts_test_03.dbf  
Successfully added datafile 7 to media recovery  
Datafile #7: '/opt/oradata/skycard/ts_skyts_test_03.dbf'  
Media Recovery Waiting for thread 1 sequence 34 (in transit)

到备库上验证一下

1
2
3
4
5
6
7
8
9
10
11
12
 
SQL> select name from v$tablespace;
NAME  
------------------------------  
SYSTEM  
UNDOTBS1  
SYSAUX  
USERS  
TEMP  
TS_SKYCARD  
TS_TEST_02  
TS_TEST_03
8 rows selected.

表空间 “TS_TEST_03” 已经同步过来了，成功。

14 再切回到主节点
在当前的主节点（原来的备节点进行）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE  
--------- ---------- -------------------- ----------------  
SKYCARD READ WRITE MAXIMUM PERFORMANCE PRIMARY
SQL>  
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE  
--------- ---------- ----------------  
SKYCARD READ WRITE PRIMARY
SQL> alter database commit to switchover to physical standby;
Database altered.  
  
SQL> shutdown immediate;  
ORA-01507: database not mounted
[oracle@db-134-109-171-73](mailto:oracle@db-134-109-171-73)-> sqlplus " / as sysdba"
SQL> startup nomount pfile='/opt/oracle/product/10.2.0/db_1/dbs/initskycard.ora';  
ORACLE instance started.
Total System Global Area 3355443200 bytes  
Fixed Size 2087640 bytes  
Variable Size 671089960 bytes  
Database Buffers 2667577344 bytes  
Redo Buffers 14688256 bytes  
SQL> alter database mount standby database;
Database altered.
SQL> select name,open_mode ,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE  
--------- ---------- ----------------  
SKYCARD MOUNTED PHYSICAL STANDBY
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select name,open_mode ,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE  
--------- ---------- ----------------  
SKYCARD MOUNTED PHYSICAL STANDBY

在备节点（原先的主节点上进行）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
SQL> select open_mode ,database_role from v$database;
OPEN_MODE DATABASE_ROLE  
---------- ----------------  
MOUNTED PHYSICAL STANDBY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediate;  
ORA-01507: database not mounted
[oracle@db-134-109-171-72](mailto:oracle@db-134-109-171-72)-> sqlplus " / as sysdba"  
SQL> startup;  
ORACLE instance started.
Total System Global Area 3355443200 bytes  
Fixed Size 2087640 bytes  
Variable Size 671089960 bytes  
Database Buffers 2667577344 bytes  
Redo Buffers 14688256 bytes  
Database mounted.  
Database opened.
SQL> select open_mode ,database_role from v$database;
OPEN_MODE DATABASE_ROLE  
---------- ----------------  
READ WRITE PRIMARY

到这里，完成主库和备库之间自由切换。

15 附：主库从库开启顺序。
开启顺序: 先将从库启动到Mount状态,然后再开主库
关闭顺序: 先关闭主库,再关闭从库

16 附上一张LGWR ASYNC原理图，来自官网,这也是这次实验应用的DATAGUARD 模式
DataGuard之一：Oracle10g dg搭建
说明：

When the LGWR and ASYNC attributes are specified, the log writer process writes to the local online redo log file,
while the network server (LNSn) processes (one for each destination) asynchronously transmit the redo to remote destinations.
The LGWR process continues processing the next request without waiting for the LNS network I/O to complete.
If redo transport services transmit redo data to multiple remote destinations, the LNSn processes (one for each destination)
initiate the network I/O to all of the destinations in parallel.
When an online redo log file fills up, a log switch occurs and an archiver process archives the log file locally, as usual.
ARC0 process:
ARC0 process archives the local online redo log to the local destination (LOG_ARCHIVE_DEST_1)。

ARC1 process:
transmits redo from the local archived redo log files (instead of the online redo log files) to
the remote standby destination (LOG_ARCHIVE_DEST_2).

RFS:the remote file server process
receives redo data over the network from the LGWR process and writes the redo data to the standby redo log files

MRP: Redo Apply process
apply the redo to the standby database

再次附上主库参数

1
2
3
4
5
6
7
8
9
10
 
*.DB_UNIQUE_NAME='PRIMARY_1'  
*.log_archive_config='DG_CONFIG=(PRIMARY_1,STANDBY_1)'  
*.log_archive_dest_1='LOCATION=/opt/oracle/archive/skycard/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY_1'  
*.log_archive_dest_2='SERVICE=STANDBY_1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY_1'  
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE  
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE  
*.FAL_SERVER=STANDBY_1  
*.FAL_CLIENT=PRIMARY_1  
*.standby_file_management='AUTO'  
*.LOG_ARCHIVE_MAX_PROCESSES=3

原创文章，作者：506227337，如若转载，请注明出处：https://blog.ytso.com/236345.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论