Oracle HA 之基于活动数据库复制配置oracle 11.2 dataguard-白红宇

Oracle HA 之基于活动数据库复制配置oracle 11.2 dataguard

阅读量：7143 次

发布时间：2019-06-29

本文共 11208 字，大约阅读时间需要 37 分钟。

规划：

主库：db_name=dbking db_unique_name=dbkingpri

备库：db_name=dbking db_unique_name=dbkingstd

1、修改dbking库为归档模式

2、修改dbking库为force logging模式

修改后结果如下：

SQL> select name,open_mode,log_mode,force_logging from v$database;

NAME OPEN_MODE LOG_MODE FORCE_LOG

--------------------------- ------------------------------------------------------------ ------------------------------------ ---------

DBKING READ WRITE ARCHIVELOG YES

3、配置tnsnames.ora文件

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DBKINGPRI =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dbking)

)

DBKINGSTD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.41)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dbking)

)

4、设置备库静态监听模式

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.41)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = dbking)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = dbking)

)

ADR_BASE_LISTENER = /u01/app/oracle

5、创建主库slf文件：

SQL> alter database add standby logfile '/u01/app/oracle/oradata/dbking/srdo01.log' size 52428800;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/dbking/srdo02.log' size 52428800;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/dbking/srdo03.log' size 52428800;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/dbking/srdo04.log' size 52428800;

Database altered.

SQL> select member from v$logfile;

MEMBER

------------------------------------------------------------------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/dbking/redo03.log

/u01/app/oracle/oradata/dbking/redo02.log

/u01/app/oracle/oradata/dbking/redo01.log

/u01/app/oracle/oradata/dbking/srdo01.log

/u01/app/oracle/oradata/dbking/srdo02.log

/u01/app/oracle/oradata/dbking/srdo03.log

/u01/app/oracle/oradata/dbking/srdo04.log

7 rows selected.

SQL>

6、主库配置参数

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ --------------------------------- ------------------------------

db_name string dbking

SQL> alter system set db_unique_name=dbkingpri scope=spfile;

System altered.

SQL> alter system set log_archive_config='dg_config=(dbkingpri,dbkingstd)';

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter system set fal_client=dbkingstd;

System altered.

SQL> alter system set fal_server=dbkingpri;

System altered.

SQL> alter system set log_archive_dest_2='service=dbkingstd lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dbkingstd';

System altered.

7、准备备库密码文件

[oracle@kaifai dbs]$ scp 192.168.100.40:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdbking .

8、准备备库参数文件

[oracle@kaifai dbs]$ cat initdbing.ora

db_name=dbking

db_unique_name=dbkingstd

9、启动备库到nomount状态

[oracle@kaifai dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 8 14:10:49 2016

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 263090176 bytes

Fixed Size 2252256 bytes

Variable Size 205521440 bytes

Database Buffers 50331648 bytes

Redo Buffers 4984832 bytes

SQL>

10、rman连接主备库

[oracle@kaifai admin]$ rman target sys/oracle@dbkingpri auxiliary sys/oracle@dbkingstd

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 8 14:16:02 2016

connected to target database: DBKING (DBID=3207597140)

connected to auxiliary database: DBKING (not mounted)

RMAN>

11、创建相应目录

$mkdir -p /data/oradata/dbking

[oracle@kaifai admin]$ pwd

/u01/app/oracle/admin

[oracle@kaifai admin]$ mkdir dbking

[oracle@kaifai admin]$ cd dbking/

[oracle@kaifai dbking]$ mkdir adump

[oracle@kaifai dbking]$ mkdir cdump

[oracle@kaifai dbking]$ mkdir dpdump

12、以下脚本复制数据库

run{

duplicate target database

for standby

from active database

spfile

set db_unique_name='dbkingstd'

set log_file_name_convert '/u01/app/oracle/oradata/dbking/','/data/oradata/dbking/'

set db_file_name_convert '/u01/app/oracle/oradata/dbking/','/data/oradata/dbking/'

set control_files='/data/oradata/dbking/control.ctl'

set log_archive_dest_2='service=dbkingpri lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dbkingpri'

set fal_client='dbkingstd'

set fal_server='dbkingpri'

;

}

13、启动应用

alter database recover managed standby database disconnect from session;

14、测试

create tablespace efmis datafile '/u01/app/oracle/oradata/dbking/efmis01.dbf' size 200m;

15、active dataguard

1）查看当前备库运行状态

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

------------------------------------------------ ------------------------------------

dbking MOUNTED

2）进入到只读状态

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

------------------------------------------------------------

READ ONLY

3）启动应用日志

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

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

------------------------------------------------------------

READ ONLY WITH APPLY

备库以只读方式应用日志。

4）测试：

略

16、修改主库为最大保护模式：

1）查看当前主库运行模式

SQL> select db_unique_name,protection_mode,protection_level from v$database;

DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL

------------------------------ ------------------------------------------------------------ -------------------------------------

DBKINGPRI MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

2）设置主库为最大保护模式

SQL> alter database set standby database to maximize protection;

alter database set standby database to maximize protection

ERROR at line 1:

ORA-01126: database must be mounted in this instance and not open in any instance

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 499125816 bytes

Database Buffers 276824064 bytes

Redo Buffers 6791168 bytes

Database mounted.

SQL> alter database set standby database to maximize protection;

Database altered.

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 10000

Session ID: 191 Serial number: 3

3）修改log_archive_dest_2参数

SQL>alter system set log_archive_dest_2='service=dbkingstd lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=dbkingstd';

SQL> conn / as sysdba

Connected.

SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

现在已经是最大保护模式。

[oracle@kaifai ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 12 11:15:30 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate

ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL>

说明：最大保护模式下，备库不能先于主库关闭。如果只有一个最大保护模式的备库，则不能直接转换成快照数据库。

17、快照standby：首先关闭mrp进程，并且不能在只读及事实恢复的情况下进行转换。

1）更改主库为最大性能模式

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 499125816 bytes

Database Buffers 276824064 bytes

Redo Buffers 6791168 bytes

Database mounted.

SQL> alter database set standby database to maximize performance;

Database altered.

SQL> alter database open;

Database altered.

2）备库在只读恢复模式，不能进行转换

SQL> select open_mode from v$database;

OPEN_MODE

------------------------------------------------------------

READ ONLY WITH APPLY

SQL> alter database convert to snapshot standby;

alter database convert to snapshot standby

ERROR at line 1:

ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_07/12/2016

16:24:52'.

ORA-01153: an incompatible media recovery is active

3）关闭数据库，启动到mount状态，进行转换，然后再次关闭数据库，启动数据库至open状态：

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 515903032 bytes

Database Buffers 260046848 bytes

Redo Buffers 6791168 bytes

Database mounted.

SQL> select open_mode from v$database;

OPEN_MODE

------------------------------------------------------------

MOUNTED

SQL> alter database convert to snapshot standby;

Database altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 515903032 bytes

Database Buffers 260046848 bytes

Redo Buffers 6791168 bytes

Database mounted.

Database opened.

SQL> select open_mode from v$database;

OPEN_MODE

------------------------------------------------------------

READ WRITE

SQL>

4）备库开始可以对外提供读写模式了，主库创建用户及生成测试表：

SQL> create user t2 identified by oracle account unlock;

User created.

SQL> grant dba to t2;

Grant succeeded.

SQL> create table t2.emp as select * from scott.emp;

Table created.

5）备库查询步骤4创建的测试用户及表，查不到结果

SQL> conn t2/oracle

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba

Connected.

SQL> select * from t2.emp;

select * from t2.emp

ERROR at line 1:

ORA-00942: table or view does not exist

6）测试备库读写功能

SQL> create user t3 identified by oracle account unlock;

User created.

SQL> grant dba to t3;

Grant succeeded.

SQL> create table t3.emp as select * from scott.emp;

Table created.

18、snapshot standby数据库转换回物理数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 515903032 bytes

Database Buffers 260046848 bytes

Redo Buffers 6791168 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 515903032 bytes

Database Buffers 260046848 bytes

Redo Buffers 6791168 bytes

Database mounted.

Database opened.

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

Database altered.

SQL> conn t2/oracle

Connected.

SQL> select * from emp;

.......

14 rows selected.

SQL> conn t3/oracle

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL>

转载地址：http://eogrl.baihongyu.com/

你可能感兴趣的文章

windowsserver 2012 R2 创建群集失败

查看>>

wps的ppt放映时不能完全全屏的解决方法

react-native环境配置入坑指南.

洛谷P3569 [POI2014]KAR-Cards（线段树）