RAC利用rman构建physical standby

最新推荐文章于 2024-06-17 10:26:55 发布

conglvmou9965

最新推荐文章于 2024-06-17 10:26:55 发布

阅读量109

点赞数

文章标签：数据库网络

环境介绍：

rac:

rac1: rhel6.4 64位 192.168.0.201，db_unique_name: racdb ,oracle_sid=racdb1

rac2: rhel5.4 32位 192.168.0.202，db_unique_name: racdb ,oracle_sid=racdb2

standby:

rhel6.4 64位 192.168.0.206，db_unique_name:racdb_standby ,oracle_sid=racdb

--检查环境

确定rac启动archivelog归档模式

SQL> archive log list;

Database log mode            Archive Mode

Automatic archival            Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     263

Next log sequence to archive   264

Current log sequence            264

SQL>

SQL> show parameter RECOVERY

NAME                         TYPE

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

VALUE

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

db_recovery_file_dest               string

+SUN_FAR

db_recovery_file_dest_size          big integer

5727M

recovery_parallelism               integer

0

启动FORCE_LOGGING模式

SQL> alter database FORCE LOGGING;

Database altered.

SQL> select FORCE_LOGGING from v$database;

FOR

---

YES

配置rac两节点数据库的tns以及创建standby服务器上的listener和tns文件（如果没用tns文件，那么oracle用户下新建即可，netca方式）

rac下

tnsnames.ora两节点相同

[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_2/network/admin/

[oracle@rac1 admin]$ cat tnsnames.ora

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

# Generated by Oracle configuration tools.

RACDB1 =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racdb)

      (INSTANCE_NAME = racdb2)

    )

)

RACDB =

(DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racdb)

    )

)

RACDB_STANDBY =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racdb)

      (INSTANCE_NAME = racdb)



    )

)

RACDB2 =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racdb)

      (INSTANCE_NAME = racdb2)

    )

)

standby服务器

[oracle@oracle admin]$ cat tnsnames.ora

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

# Generated by Oracle configuration tools.

RACDB1 =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racdb)

      (INSTANCE_NAME = racdb1)

    )

)

RACDB =

(DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racdb)

    )

)

RACDB_STANDBY =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racdb)

      (INSTANCE_NAME = racdb)



    )

)

RACDB2 =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racdb)

      (INSTANCE_NAME = racdb2)

    )

)

和rac上的tns保持一致即可。

[oracle@oracle admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

    )

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = racdb)

(SID_NAME = racdb)

)

)

ADR_BASE_LISTENER = /u01/app/oracle

准备参数文件

rac环境下数据库参数文件变化如下（添加和修改）（红色部分是需要添加的参数）

可通过2中方式修改rac数据库上的参数文件

第一种：sql命令的方式修改：alter system set 参数

第二种：通过spfile创建pfile，将pfile修改好以后，通过pfile重新创建spfile

racdb2.__db_cache_size=218103808

racdb1.__db_cache_size=192937984

racdb2.__java_pool_size=4194304

racdb1.__java_pool_size=4194304

racdb2.__large_pool_size=8388608

racdb1.__large_pool_size=8388608

racdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

racdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

racdb2.__pga_aggregate_target=297795584

racdb1.__pga_aggregate_target=322961408

racdb2.__sga_target=440401920

racdb1.__sga_target=415236096

racdb2.__shared_io_pool_size=0

racdb1.__shared_io_pool_size=0

racdb2.__shared_pool_size=201326592

racdb1.__shared_pool_size=201326592

racdb2.__streams_pool_size=0

racdb1.__streams_pool_size=0

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

*.audit_trail='DB'

*.cluster_database=true

*.compatible='11.2.0.0.0'

*.control_files='+SUN_DATA/racdb/controlfile/current.256.836501243','+SUN_FAR/racdb/controlfile/current.256.836501247'

*.db_block_size=8192

*.db_create_file_dest='+SUN_DATA'

*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/racdb/datafile','+SUN_DATA/racdb/datafile','/u01/app/oracle/oradata/racdb/tempfile','+SUN_DATA/racdb/tempfile'

*.db_name='racdb'

*.db_recovery_file_dest='+SUN_FAR'

*.db_recovery_file_dest_size=6005194752

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

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

*.event=''
racdb1.fal_client='racdb1'

racdb2.fal_client='racdb2'

*.fal_server='racdb_standby'

racdb1.instance_number=1

racdb2.instance_number=2
*.log_archive_config='DG_CONFIG=(racdb,racdb_standby)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb'

*.log_archive_dest_2='SERVICE=racdb_standby LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb_standby'
*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=4
*.log_file_name_convert='/u01/app/oracle/oradata/racdb/onlinelog','+SUN_DATA/racdb/onlinelog'

*.memory_target=738197504

*.open_cursors=300

*.processes=150

*.remote_listener='scan:1521'

*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='racdb_rac'
*.standby_file_management='AUTO'

racdb1.thread=1

racdb2.thread=2

racdb1.undo_tablespace='UNDOTBS1'

racdb2.undo_tablespace='UNDOTBS2

rac上的参数文件修改完成以后，通过spfile创建pfile 的方式创建出一份standby的参数文件，然后拷贝到standby服务器下

standby数据库参数如下（红色部分是修改后的参数）

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

*.audit_trail='DB'

*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/racdb/controlfile/control01.ctl','/u01/app/oracle/racdb/controlfile/control02.ctl'

*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_domain=''
*.db_file_name_convert='+SUN_DATA/racdb/datafile','/u01/app/oracle/oradata/racdb/datafile','+SUN_DATA/racdb/tempfile','/u01/app/oracle/oradata/racdb/tempfile'

*.db_name='racdb'
*.db_unique_name='racdb_standby'
*.db_recovery_file_dest='/u01/app/oracle/racdb/flash_recovery_area'

*.db_recovery_file_dest_size=6005194752

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

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

*.event=''
*.fal_client='racdb_standby'

*.fal_server='racdb1','racdb2'
*.log_archive_config='DG_CONFIG=(racdb_standby,racdb)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/racdb/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb_standby'
*.log_archive_dest_2='SERVICE=racdb1 LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'

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

*.log_archive_max_processes=4
*.log_file_name_convert='+SUN_DATA/racdb/onlinelog','/u01/app/oracle/oradata/racdb/onlinelog'

*.memory_target=738197504

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='racdb_standby'

*.standby_file_management='AUTO'
*.thread=1

*.undo_tablespace='UNDOTBS1'

rac上rman全备数据库

创建备份文件路径（rac1和standby路径必须一致）

rac1节点

[oracle@rac1 ~]$ rman target /

RMAN> backup database format '/u01/app/oracle/bak/racdbfull%u_%s_%p';

将备份好的文件通过scp方式传送到standby服务器上的/u01/app/oracle/bak --注意路径必须一致

[oracle@rac1 bak]$ scp racdbfull0noui02p_23_1 192.168.0.206:/u01/app/oracle/bak/

oracle@192.168.0.206's password:

racdbfull0noui02p_23_1 100% 1223MB 1.2MB/s 17:12

standby服务器上创建相应的目录

根据修改好的standby参数文件创建相应的目录

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

[oracle@oracle ~]$ mkdir -p /u01/app/oracle/racdb/controlfile

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

[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/racdb/datafile

[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/racdb/tempfile

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

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

[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/racdb/onlinelog

将rac1上的密码文件复制到standby服务器的相应目录下

[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_2/dbs/

[oracle@rac1 dbs]$ scp orapwracdb1 192.168.0.206:/u01/app/oracle/product/11.2.0/db_1/dbs/

oracle@192.168.0.206's password:

orapwracdb1 100% 1536 1.5KB/s 00:00

将复制过来的密码文件重新命名

[oracle@oracle ~]$ mv /u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdb1 /u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdb

通过修改后的standby的pfile文件将数据库启动到nomount状态

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

SQL*Plus: Release 11.2.0.4.0 Production on 星期一 1月 20 14:55:11 2014

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

Connected to an idle instance.

SQL> startup nomount pfile=/home/oracle/pfileracdb.ora;

ORACLE instance started.

Total System Global Area 734892032 bytes

Fixed Size              2256872 bytes

Variable Size          486539288 bytes

Database Buffers     243269632 bytes

Redo Buffers              2826240 bytes

SQL>

利用rman创建standby数据库

rac1节点上

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

Recovery Manager: Release 11.2.0.4.0 - Production on 星期一 1月 20 15:02:19 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: RACDB (DBID=834118003)

connected to auxiliary database: RACDB (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

Starting Duplicate Db at 2014-01-20 15:03:17

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

   restore clone standby controlfile;

}

executing Memory Script

Starting restore at 2014-01-20 15:03:19

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/bak/racdbfull0ooui072_24_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/bak/racdbfull0ooui072_24_1 tag=TAG20140120T141241

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/app/oracle/racdb/controlfile/control01.ctl

output file name=/u01/app/oracle/racdb/controlfile/control02.ctl

Finished restore at 2014-01-20 15:03:23

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile 1 to

"/u01/app/oracle/oradata/racdb/tempfile/temp.262.836501345";

   set newname for tempfile 2 to

"/u01/app/oracle/oradata/racdb/datafile/orcl_temp.dbf";

   switch clone tempfile all;

   set newname for datafile 1 to

"/u01/app/oracle/oradata/racdb/datafile/system.259.836501265";

   set newname for datafile 2 to

"/u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305";

   set newname for datafile 3 to

"/u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335";

   set newname for datafile 4 to

"/u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359";

   set newname for datafile 5 to

"/u01/app/oracle/oradata/racdb/datafile/users.264.836501371";

   set newname for datafile 6 to

"/u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf";

   restore

   clone database

   ;

}

executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/racdb/tempfile/temp.262.836501345 in control file

renamed tempfile 2 to /u01/app/oracle/oradata/racdb/datafile/orcl_temp.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2014-01-20 15:03:33

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/racdb/datafile/system.259.836501265

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/racdb/datafile/users.264.836501371

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/bak/racdbfull0noui02p_23_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/bak/racdbfull0noui02p_23_1 tag=TAG20140120T141241

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15

Finished restore at 2014-01-20 15:05:49

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/system.259.836501265

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359

datafile 5 switched to datafile copy

input datafile copy RECID=5 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/users.264.836501371

datafile 6 switched to datafile copy

input datafile copy RECID=6 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf

Finished Duplicate Db at 2014-01-20 15:06:25

检查standby数据库

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/racdb/onlinelog/group_1.257.836501249

/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_1_9fslpjws_.log

/u01/app/oracle/oradata/racdb/onlinelog/group_2.258.836501257

/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_2_9fslpy1n_.log

/u01/app/oracle/oradata/racdb/onlinelog/group_3.265.836506275

/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_3_9fslq5vf_.log

/u01/app/oracle/oradata/racdb/onlinelog/group_4.266.836506279

/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_4_9fslqcf4_.log

/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_1_9fslpk

26_.log

MEMBER

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

/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_2_9fslpy

42_.log

/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_3_9fslq5

z6_.log

/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_4_9fslqc

gy_.log

12 rows selected.

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/racdb/datafile/system.259.836501265

/u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305

/u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335

/u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359

/u01/app/oracle/oradata/racdb/datafile/users.264.836501371

/u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf

6 rows selected.

SQL> select name from v$tempfile;

NAME

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

/u01/app/oracle/oradata/racdb/tempfile/temp.262.836501345

/u01/app/oracle/oradata/racdb/datafile/orcl_temp.dbf

SQL> show parameter control

NAME                         TYPE

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

VALUE

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

control_file_record_keep_time          integer

7

control_files                    string

/u01/app/oracle/racdb/controlf

ile/control01.ctl, /u01/app/or

acle/racdb/controlfile/control

02.ctl

control_management_pack_access          string

DIAGNOSTIC+TUNING

SQL>

创建standby的redo log日志

注意：standby的redo log日志要比rac的redo log日志多一组，例如rac数据库的 redo log 为2组4个，那么standby 的redo log应该为3组6个

例如rac 的redo log为6组12个，那么standby的redo log应为7组14个，以此类推。

查看rac数据库的redo log

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

    GROUP#    THREAD# SEQUENCE#    MEMBERS ARCHIV

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

STATUS

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

     1         1          267     2 YES

ACTIVE

     2         1          268     2 NO

CURRENT

     3         2          129     2 NO

CURRENT

    GROUP#    THREAD# SEQUENCE#    MEMBERS ARCHIV

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

STATUS

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

     4        2          128     2 YES

ACTIVE

本实验的rac的每个线程的联机日志都是2组4个，所以standby的redo log要创建3组6个

standby服务器上创建redo log

alter database add standby logfile thread 1 group 5 '/u01/app/oracle/oradata/racdb/onlinelog/group_5.log' size 50M;

alter database add standby logfile thread 1 group 6 '/u01/app/oracle/oradata/racdb/onlinelog/group_6.log' size 50M;

alter database add standby logfile thread 1 group 7 '/u01/app/oracle/oradata/racdb/onlinelog/group_7.log' size 50M;

alter database add standby logfile thread 2 group 8 '/u01/app/oracle/oradata/racdb/onlinelog/group_8.log' size 50M;

alter database add standby logfile thread 2 group 9 '/u01/app/oracle/oradata/racdb/onlinelog/group_9.log' size 50M;

alter database add standby logfile thread 2 group 10 '/u01/app/oracle/oradata/racdb/onlinelog/group_10.log' size 50M;

standby服务器上开启同步

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

在rac各个实例上和standby上查看日志传输情况

select dest_name,status,error from v$archive_dest;

查看归档日志

rac1上

SQL> archive log list;

Database log mode            Archive Mode

Automatic archival            Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     268

Next log sequence to archive   269

Current log sequence            269

SQL>

standby上

SQL> archive log list;

Database log mode            Archive Mode

Automatic archival            Enabled

Archive destination            /u01/app/oracle/racdb/archive

Oldest online log sequence     269

Next log sequence to archive   0

Current log sequence            269

SQL>

rac1上

SQL> alter system switch logfile;

System altered.

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

MAX(SEQUENCE#)

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

269

SQL>

standby上

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

MAX(SEQUENCE#)

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

269

SQL>

或者更详细的：

select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;

测试

rac1上创建表空间、用户、表

SQL> create temporary tablespace sun_temp tempfile '+SUN_DATA/racdb/datafile/sun_temp.dbf' size 50m autoextend on next 50m maxsize 50m extent management local;

Tablespace created.

SQL> create tablespace sun_data logging datafile '+SUN_DATA/racdb/datafile/sun_data.dbf' size 50m autoextend on next 50m maxsize 100m extent management local;

Tablespace created.

SQL> create user sun identified by sun default tablespace sun_data temporary tablespace sun_temp;

User created.

SQL> grant connect,resource,dba to sun;

Grant succeeded.

SQL> conn sun/sun

Connected.

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

Table created.

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

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> select * from b;

     ID NAME

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

     2 wang

     2 wang

     2 wang

     2 wang

     2 wang

     2 wang

6 rows selected.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

ORA-01031: 权限不足

SQL> conn /as sysdba

Connected.

SQL> archive log list;

Database log mode            Archive Mode

Automatic archival            Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     270

Next log sequence to archive   271

Current log sequence            271

SQL>

手动切换日志

注意rac上手动切换日志必须在2个实例上都切换，如果只切换了rac1的归档，那么数据是不能同步的。

rac1实例上切换

SQL> alter system switch logfile;

rac2实例上切换

SQL> alter system switch logfile;

standby上查看数据是否同步

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

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> conn sun/sun

Connected.

SQL> select * from b;

     ID NAME

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

     2 wang

     2 wang

     2 wang

     2 wang

     2 wang

     2 wang

6 rows selected.

SQL>

注意：由于本次standby数据库实例启动是通过pfile参数文件启动，为了下次启动通过spfile，所以要通过pfile创建spfile。

SQL> create spfile from pfile='/home/oracle/pfileracdb.ora';

File created.

从新将备库（standby）置于应用日志模式

[oracle@oracle ~]$ 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

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

racdb MOUNTED

维护的通常步骤；

关闭：先关主库后关从库

启动：先启动从库然后启动主库。

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

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

Switchover：

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

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

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

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

Failover

：

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

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

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

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

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

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

Switchover：

注意：在switchover过程中，只能有一个主实例和一个standby实例处于active状态中，因此，在switchover之前，停止集群中的其他实例（rac环境下，如果要将rac数据库切换成standby，那么在切换之前，需要关闭其他节点的实例，只能保留一个）

物理STANDBY的SWITCHOVER切换会把当前的一个物理STANDBY切换为PRIMARY数据库，而PRIMARY数据库且变成物理STNADBY数据库。
一般SWITCHOVER切换都是计划中的切换，特点是在切换后，不会丢失任何的数据，而且这个过程是可逆的，整个DATA GUARD环境不会被破坏，原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。
在进行DATA GUARD的物理STANDBY切换前需要注意：
确认主库和从库间网络连接通畅；
确认没有活动的会话连接在数据库中；
PRIMARY数据库处于打开的状态，STANDBY数据库处于MOUNT状态；
确保STANDBY数据库处于ARCHIVELOG模式；
如果设置了REDO应用的延迟，那么将这个设置去掉；
确保配置了主库和从库的初始化参数，使得切换完成后，DATA GUARD机制可以顺利的运行。

登陆PRIMARY数据库：

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

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

SESSIONS ACTIVE

SQL> SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL;

COUNT(*)

----------

1

虽然当前数据库的状态是SESSIONS ACTIVE而不是TO STANDBY，但是查询V$SESSION会话，确认除了当前会话外，其他都是系统会话，那么就可以在主库进行SWITCHOVER切换了：

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

SQL> SHUTDOWN IMMEDIATE

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 267825152 bytes

Fixed Size 1299316 bytes

Variable Size 159386764 bytes

Database Buffers 104857600 bytes

Redo Buffers 2281472 bytes

Database mounted.

登陆STANDBY数据库：

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

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

下面就可以将STANDBY数据库切换到PRIMARY数据库：

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

STANDBY数据库已经切换为PRIMARY数据库，下面只需要启动将STANDBY开始接收并恢复主库的日志就可以了。回到切换前的主库现在的从库：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SWITCHOVER切换完成，最后检查一下归档是否可以正常传递到STANDBY数据库即可。

这里关于角色切换没有详细讲解，请看【oracle11g dataguard完全手册--switchover】【oracle11g dataguard完全手册--failover】

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

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

conglvmou9965

关注

0
点赞
踩
0

收藏

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

环境介绍： rac: rac1: rhel6.4 64位 192.168.0.201，db_unique_name: racdb ,oracle_sid=racdb1...
复制链接

扫一扫