利用rman构建physical standby

最新推荐文章于 2024-07-18 16:32:46 发布

conglvmou9965

最新推荐文章于 2024-07-18 16:32:46 发布

阅读量91

点赞数

文章标签：数据库网络

环境介绍：

dg1: rhel5.4 32位 192.168.0.201，db_unique_name: primary ,oracle_sid=primary

dg2: rhel5.4 32位 192.168.0.202，db_unique_name: standby ,oracle_sid=priamry

oracle版本： 11.2.0.1 32位企业版

一：primary服务器

1：主库上开启Forced Logging

SQL> alter database force logging;

Database altered.

SQL>select force_logging from v$database;

FOR

---

YES

2:创建standby数据库redo文件

alter database add standby logfile '/u01/app/oracle/oradata/primary/standby01.log' size 50M;

alter database add standby logfile '/u01/app/oracle/oradata/primary/standby02.log' size 50M;

alter database add standby logfile '/u01/app/oracle/oradata/primary/standby03.log' size 50M;

3:修改主库的初始化参数

SQL> show parameter db_name;

NAME TYPE VALUE

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

db_name string primary

SQL> show parameter db_unique_name;

NAME TYPE VALUE

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

db_unique_name string primary

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archive/orcl valid_for=(all_logfiles,all_roles) db_unique_name=primary' scope=spfile;

System altered.

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

System altered.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

System altered.

SQL> show parameter remote_login;

NAME TYPE VALUE

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

remote_login_passwordfile string EXCLUSIVE

SQL> alter system set log_archive_max_processes=30;

System altered.

SQL> alter system set fal_server=standby;

System altered.

SQL> alter system set fal_client=primary;

System altered.

SQL> alter system set standby_file_management=auto;

SQL> shutdown immediate;

SQL> startup

4：配置主库的tnsnames.ora文件，备库需要同样的操作(可通过netca--本地网络服务名配置)

[oracle@dg1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

[oracle@dg1 admin]$ vi tnsnames.ora

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = primary)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

5：在主库上准备备库需要的密码文件、pfile和数据库备份文件，控制文件等

创建standby数据库的initprimary.ora参数文件

sqlplus "/as sysdba"

SQL>create pfile from spfile;

将该参数文件复制到standby数据库目录下

[oracle@dg1 ~]$scp $ORACLE_HOME/dbs/initprimary.ora 192.168.0.202:$ORACLE_HOME/dbs/

oracle@192.168.0.202's password:

initprimary.ora 100% 1536 1.5KB/s 00:00

将主库的密码文件拷贝的备库（自己创建也可以，但是也保证和主库的密码必须一致）

[oracle@dg1 ~]$ cd $ORACLE_HOME/dbs

[oracle@dg1 dbs]$ scp orapwprimary 192.168.0.202:$ORACLE_HOME/dbs

The authenticity of host '192.168.0.202 (192.168.0.202)' can't be established.

RSA key fingerprint is bb:85:fb:97:60:d6:1d:22:ba:0f:b6:f5:93:7e:ff:ca.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.0.202' (RSA) to the list of known hosts.

oracle@192.168.0.202's password:

orapwprimary 100% 1536 1.5KB/s 00:00

创建主数据库的rman备份集

[oracle@dg1 ~]$ rman target /

RMAN> backup database format '/u01/app/oracle/bak/%d_%s.bak' plus archivelog format '/u01/app/oracle/bak/%d_%s.bak';

备份集创建完成以后，将该备份集复制到standby数据库目录下（注意：目录必须和主库备份目录必须一致）

[oracle@dg1 ~]$ cd /u01/app/oracle/bak

[oracle@dg1 bak]$ ll

总计 1174892

-rw-r----- 1 oracle oinstall 5527040 06-04 09:49 PRIMARY_1.bak

-rw-r----- 1 oracle oinstall 1186480128 06-04 09:51 PRIMARY_2.bak

-rw-r----- 1 oracle oinstall 9830400 06-04 09:52 PRIMARY_3.bak

-rw-r----- 1 oracle oinstall 53248 06-04 09:52 PRIMARY_4.bak

[oracle@dg1 bak]$ scp * 192.168.0.202:/u01/app/oracle/bak/

oracle@192.168.0.202's password:

PRIMARY_1.bak 100% 5398KB 5.3MB/s 00:00

PRIMARY_2.bak 100% 1132MB 6.2MB/s 03:03

PRIMARY_3.bak 100% 9600KB 9.4MB/s 00:00

PRIMARY_4.bak 100% 52KB 52.0KB/s 00:00

一：standby服务器

1：设置oracle_sid,配置tnsnames.ora和listener.ora

[oracle@orcl ~]$ export ORACLE_SID=primary

[oracle@orcl ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = primary)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

配置监听listener.ora

[oracle@orcl ~]$ cat $ORACLE_HOME/network/admin/listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

SID_LIST_LISTENER = --这一步骤主要是为了将standby静态加入监听

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = standby)

(SID_NAME = standby)

)

ADR_BASE_LISTENER = /u01/app/oracle

然后重启监听程序 lsnrctl restart

2:修改主库拷贝过来的参数文件

[oracle@dg2 ~]$ cd $ORACLE_HOME/dbs

[oracle@dg2 dbs]$ vi initprimary.ora

参数内容

primary.__pga_aggregate_target=150994944

primary.__sga_target=218103808

primary.__shared_io_pool_size=0

primary.__shared_pool_size=83886080

primary.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/primary/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

primary.__sga_target=218103808

primary.__shared_io_pool_size=0

primary.__shared_pool_size=83886080

primary.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/primary/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/primary/control01.ctl','/u01/app/oracle/flash_recovery_area/primary/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='primary'

*.db_unique_name=standby

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=5218762752

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'

*.fal_client='standby'

*.fal_server='primary'

*.log_archive_config='DG_CONFIG=(standby,primary)'*.log_archive_dest_1='LOCATION=/u01/app/oracle/archive/orcl valid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=primary lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=primary'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.arc'

*.memory_target=367001600

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

需要修改的参数

*.db_unique_name=standby---添加上这条

*.fal_client='standby'

*.fal_server='primary'

*.log_archive_config='DG_CONFIG=(standby,primary)'---这个地方官网是没有修改的，但是我改了。。。。，根据官方来

*.log_archive_dest_1='LOCATION=/u01/app/oracle/archive/orcl valid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=primary lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=primary'

3：建立oracle相应的目录（和主数据库保持一致）

[oracle@dg2 ~]$ mkdir -p /u01/app/oracle/bak

[oracle@dg2 ~]$ mkdir -p /u01/app/oracle/oradata/primary

[oracle@dg2 ~]$ mkdir -p /u01/app/oracle/admin/primary/{adump,bdump,cdump,dpdump,udump,pfile}

[oracle@dg2 ~]$ mkdir -p /u01/app/oracle/archive/orcl

[oracle@dg2 ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/primary

4：将备库启动到nomount状态

[oracle@dg2 ~]$ sqlplus "/as sysdba"

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initprimary.ora;

ORACLE instance started.

Total System Global Area 368263168 bytes

Fixed Size 1336596 bytes

Variable Size 243272428 bytes

Database Buffers 117440512 bytes

Redo Buffers 6213632 bytes

5：使用rman对备库进行恢复

[oracle@orcl ~]$ rman target sys/orcl@primary auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 4 10:11:28 2013

connected to target database: PRIMARY (DBID=1654634974)

connected to auxiliary database: PRIMARY (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

6: 将备库置于应用redolog模式

[oracle@orcl ~]$ sqlplus /nolog

SQL> conn /as sysdba

Connected.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/orcl

Oldest online log sequence 0

Next log sequence to archive 0

Current log sequence 0

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

SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

51 04-JUN-13 04-JUN-13 YES

50 04-JUN-13 04-JUN-13 YES

52 04-JUN-13 04-JUN-13 YES

53 04-JUN-13 04-JUN-13 YES

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/o1_mf_1_8ttmgv1p_.log

/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/o1_mf_2_8ttmgzxl_.log

/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/o1_mf_3_8ttmh5cb_.log

/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/o1_mf_4_8ttmh9x6_.log

/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/o1_mf_5_8ttmhds4_.log

/u01/app/oracle/flash_recovery_area/STANDBY/onlinelog/o1_mf_6_8ttmhjnr_.log

可以在主库上进行日志切换，加快备库应用日志的速度！

oracle@dg1 ~]$ sqlplus /nolog

SQL> conn /as sysdba

Connected.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/orcl

Oldest online log sequence 52

Next log sequence to archive 54

Current log sequence 54

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/orcl

Oldest online log sequence 53

Next log sequence to archive 55

Current log sequence 55

备库上再次查询

SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

51 04-JUN-13 04-JUN-13 YES

50 04-JUN-13 04-JUN-13 YES

52 04-JUN-13 04-JUN-13 YES

53 04-JUN-13 04-JUN-13 YES

54 04-JUN-13 04-JUN-13 YES

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/orcl

Oldest online log sequence 53

Next log sequence to archive 0

Current log sequence 55

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE

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

PRIMARY PHYSICAL STANDBY

三：测试

1.在主库上创建表空间，建表

SQL> create temporary tablespace orcl_temp tempfile '/u01/app/oracle/oradata/primary/orcl_temp.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local;

Tablespace created.

SQL> create tablespace orcl_data logging datafile '/u01/app/oracle/oradata/primary/orcl_data.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local;

Tablespace created.

SQL> create user orcl identified by orcl default tablespace orcl_data temporary tablespace orcl_temp;

User created.

SQL> grant connect,resource,dba to orcl;

Grant succeeded.

SQL> conn orcl/orcl

Connected.

SQL> create table b(id number,name varchar(10));

Table created.

SQL> insert into b values(2,'wang');

1 row created.

SQL> insert into b select * from b;

1 row created.

SQL> select * from b;

ID NAME

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

2 wang

手动切换日志

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/orcl

Oldest online log sequence 53

Next log sequence to archive 55

Current log sequence 55

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/orcl

Oldest online log sequence 54

Next log sequence to archive 56

Current log sequence 56

2.备库应用日志后以只读方式打开查看数据

SQL> alter database recover managed standby database cancel;

Database altered

SQL> alter database open read only;

Database altered.

SQL> conn orcl/orcl

Connected.

SQL> select * from b;

ID NAME

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

2 wang

3：从新将备库置于应用日志模式

[oracle@orcl ~]$ sqlplus /nolog

SQL> conn /as sysdba

Connected.

SQL> shutdown immediate;

SQL> startup nomount；

SQL> alter database mount standby database;

Database altered.

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

Database altered.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

primary MOUNTED

至此 standby 设置成功。

三：角色切换（来源于--三思笔记）

DataGuard有两种切换模式：Switchover和Failover。

Switchover：

无损转换，通常是用户手动触发或者有计划的让其自动触发，比如硬件升级啦，软件升级啦之类的。

通常它给你带来的工作量非常小并且都是可预计的。其执行分两个阶段，第一步， primary 数据库转换为

standby 角色，第二步，standby 数据库(之一)转换为primary 角色，primary 和standby 只是简单的角色互换，

这也印证了我们前面关于角色转换是primary/standby 互动的猜测。

Failover

：

不可预知原因导致primary 数据库故障并且短期内不能恢复就需要failover。如果是这种切换那你就要

小心点了，有可能只是虚惊一场，甚至连你可能损失的脑细胞的数量都能预估，但如果运气不好又没有完

备的备份恢复策略而且primary 数据并非处于最大数据保护或最高可用性模式地话，黑黑，哭是没用地，表

太伤心了，来，让三思GG 安慰安慰你，这种情况下呢丢失数据有可能是难免的，并且如果其故障未能修

复，那它甚至连快速修复成为standby 的机会也都失去了呐，咦，你脑门怎么好像在往外冒水，难道是强效

净肤液，你的脸也忽然好白皙哟~~~~

物理standby的Switchover

1、检查是否支持switchover 操作--primary 数据库操作

[oracle@dg1 ~]$ sqlplus "/as sysdba"

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

如果该列值为"TO STANDBY"则表示primary 数据库支持转换为standby 角色，否则的话你就需要重新

检查一下Data Guard 配置，比如看看LOG_ARCHIVE_DEST_n 之类参数值是否正确有效等等。

2、启动switchover --primary 数据库操作

首先将primary 转换为standby 的角色，通过下列语句：

SQL> alter database commit to switchover to physical standby;

Database altered.

（如果出现如下错误：

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

说明你有会话没有关闭，那么执行下列语句（或着shutdown immediate 数据库然后在startup）

alter database commit to switchover to physical standby with session shutdown;）

语句执行完毕后，primary 数据库将会转换为standby 数据库，并自动备份控制文件到trace。

3、重启动到mount --原primary 数据库操作

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 368263168 bytes

Fixed Size 1336596 bytes

Variable Size 310381292 bytes

Database Buffers 50331648 bytes

Redo Buffers 6213632 bytes

Database mounted.

4、检查是否支持switchover 操作--待转换standby 数据库操作

待原primary 切换为standby 角色之后，检查待转换的standby 数据库switchover_status 列，看看是否支

持角色转换。

[oracle@dg2 ~]$ sqlplus "/as sysdba"

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

此时待转换standby 数据库switchover_status 列值应该是"TO_PRIMARY"，如否则检查其初始化参数文

件中的设置，提示一下，比着原primary 数据库的初始化参数改改。

5、转换角色到primary --待转换standby 数据库操作

通过下列语句转换standby 到primary 角色：

SQL> alter database commit to switchover to primary;

Database altered.

注意：待转换的物理standby 可以处于mount 模式或open read only 模式，但不能处于open read write

模式。

6、完成转换，打开新的primary 数据库

SQL> alter database open;

Database altered.

注：如果数据库处于open read-only 模式的话，需要先shutdown 然后直接startup 即可。

7、验证

新的primary 数据库

SQL> show parameter db_unique

NAME TYPE VALUE

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

db_unique_name string standby

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/orcl

Oldest online log sequence 65

Next log sequence to archive 67

Current log sequence 67

新的standby 数据库

SQL> show parameter db_unique

NAME TYPE VALUE

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

db_unique_name string primary

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archive/orcl

Oldest online log sequence 65

Next log sequence to archive 0

Current log sequence 67

转换成功。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28698327/viewspace-762892/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28698327/viewspace-762892/

conglvmou9965

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
利用rman构建physical standby

环境介绍： dg1: rhel5.4 32位 192.168.0.201，db_unique_name: primary ,oracle_sid=primary dg2: rhel5.4 32位 1...
复制链接

扫一扫

利用rman构建physical standby

“相关推荐”对你有帮助么？