利用RMAN搭建DATAGARD进行主备切换

66 阅读 0 评论 44 点赞

我是靠谱客的博主怡然泥猴桃，最近开发中收集的这篇文章主要介绍利用RMAN搭建DATAGARD进行主备切换，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

使用rman的duplicate来创建备库，过程简洁了不少，无需在手动创建备库控制文件。

主库SPFILE

*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=C:/oracle/product/10.2.0/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='standby'

*.FAL_CLIENT='primary'

备库SPFILE 在主库创建SPFILE给备库并修改

*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='standby'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=C:/oracle/product/10.2.0/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'

配置好TNS 监听能互相TNSPING通

主库操作

修改LOGFILE大小为100M，添加4组备用联机日志

SQL> alter database add logfile group 4 'c:oracleproduct10.2.0oradataorclr
edolog04.log' size 50M;

SQL> alter database add logfile group 5 'c:oracleproduct10.2.0oradataorclr
edolog05.log' size 50M;

SQL> alter system switch logfile;

SQL> alter database drop logfile group 1;

SQL> alter database drop logfile group 2;

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 2 'c:oracleproduct10.2.0oradataorclr
edolog02.log' size 100M;

SQL> alter database add logfile group 3 'c:oracleproduct10.2.0oradataorclr
edolog03.log' size 100M;

SQL> alter database drop logfile group 4;

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

GROUP# SEQUENCE# STATUS
---------- ---------- --------------------------------
1 15 CURRENT
2 0 UNUSED
3 0 UNUSED
5 14 ACTIVE

SQL> alter system switch logfile;

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

GROUP# SEQUENCE# STATUS
---------- ---------- --------------------------------
1 19 INACTIVE
2 20 ACTIVE
3 21 CURRENT
5 18 INACTIVE

SQL> alter database drop logfile group 5;

数据库已更改。

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

GROUP# SEQUENCE# STATUS
---------- ---------- --------------------------------
1 19 INACTIVE
2 20 ACTIVE
3 21 CURRENT

SQL> alter database add standby logfile group 4 'c:oracleproduct10.2.0oradat
aorclstandbylog04.log' size 50M;

数据库已更改。

SQL> alter database add standby logfile group 5 'c:oracleproduct10.2.0oradat
aorclstandbylog05.log' size 50M;

数据库已更改。

SQL> alter database add standby logfile group 6 'c:oracleproduct10.2.0oradat
aorclstandbylog06.log' size 50M;

数据库已更改。

SQL> alter database add standby logfile group 7 'c:oracleproduct10.2.0oradat
aorclstandbylog07.log' size 50M;

数据库已更改。

-- 添加日志组成员方法
SQL> alter database add logfile member 'c:oracleproduct10.2.0oradataorclre
dolog012.log' to group 1;

数据库已更改。

--删除日志组成员方法

SQL> alter database drop logfile member 'c:oracleproduct10.2.0oradataorcl
redolog012.log';

数据库已更改。

RMAN全备数据库控制文件归档日志

RMAN> backup full format 'c:backupfullbackup_%d_%T_%s.bak' database include cu

rrent controlfile for standby;

RMAN> sql 'alter system archive log current';

sql 语句: alter system archive log current
RMAN> backup archivelog all format 'c:backuparchive_%d_%T_%s';

拷贝备份到备库相同目录c:backup下

备库操作

C:Documents and SettingsAdministrator>rman target sys/oracle@primary auxiliary
/

恢复管理器: Release 10.2.0.1.0 - Production on 星期三 11月 6 16:04:22 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到目标数据库: ORCL (DBID=1351749857)
已连接到辅助数据库: ORCL (未装载)

RMAN> duplicate target database for standby dorecover nofilenamecheck;
介质恢复完成, 用时: 00:00:03
完成 recover 于 06-11月-13
完成 Duplicate Db 于 06-11月-13

SQL> select status from v$instance;

STATUS
------------
MOUNTED

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

数据库已更改。

主库操作

SQL> archive log list
数据库日志模式存档模式
自动存档启用
存档终点 C:/oracle/product/10.2.0/oradata/arch
最早的联机日志序列 22
下一个存档日志序列 24
当前日志序列 24

插入一条数据测试

SQL> insert into wang2 values(1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> alter system switch logfile;

系统已更改。

SQL> archive log list;
数据库日志模式存档模式
自动存档启用
存档终点 C:/oracle/product/10.2.0/oradata/arch
最早的联机日志序列 23
下一个存档日志序列 25
当前日志序列 25

备库操作

SQL> archive log list;
数据库日志模式存档模式
自动存档启用
存档终点 C:/oracle/product/10.2.0/oradata/arch
最早的联机日志序列 24
下一个存档日志序列 0
当前日志序列 25

SQL> shutdown immediate;
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。

验证数据已插入过来
SQL> select * from wang2;

ID
----------
1

主备切换

主库操作
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
SESSIONS ACTIVE

SQL> alter database commit to switchover to physical standby with session shutdo
wn;

数据库已更改。

SQL> select status from v$instance;

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

SQL> shutdown immediate;
ORA-01507: ??????

ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 83887460 bytes
Database Buffers 79691776 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
-------------------
TO PRIMARY

SQL>alter database commit to switchover to primary;

数据库已更改。

主库切换备库操作

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

数据库已更改。

SQL> shutdown immediate;
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
ORA-01092: ORACLE 实例终止。强制断开连接

SQL>
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

C:Documents and SettingsAdministrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 6 16:42:21 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

已连接到空闲例程。

SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
ORA-01092: ORACLE 实例终止。强制断开连接

SQL> exit

从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

--通过查看ALER错误是SPFILE中UNDO表空间设置错误导致的

Wed Nov 06 16:42:32 2013
Errors in file c:oracleproduct10.2.0adminorclbdumporcl_mman_476.trc:
ORA-30012: undo tablespace '' does not exist or of wrong type

Wed Nov 06 16:42:32 2013
Errors in file c:oracleproduct10.2.0adminorclbdumporcl_psp0_1632.trc:
ORA-30012: undo tablespace '' does not exist or of wrong type

Wed Nov 06 16:42:32 2013
Instance terminated by USER, pid = 3408
ORA-1092 signalled during: ALTER DATABASE OPEN...

C:Documents and SettingsAdministrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 6 16:44:06 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

已连接到空闲例程。

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS01
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

C:Documents and SettingsAdministrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 6 16:44:52 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create pfile='c:pfile.ora' from spfile;

文件已创建。

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:ORACLEPRODUCT10.2.0DB_1
DATABASESPFILEORCL.ORA
SQL> alter system set undo_tablespace='undotbs1' scope=both;
alter system set undo_tablespace='undotbs1' scope=both
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-30012: 还原表空间 'undotbs1' 不存在或类型不正确

SQL> alter system set undo_tablespace='undotbs1' scope=spfile;

系统已更改。

SQL> shutdown immediate;
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs1

完毕