ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Data Guard 创建(ASM)

Data Guard 创建(ASM)

原创 Linux操作系统 作者:lyf625 时间:2010-11-16 13:34:11 0 删除 编辑

主库参数文件内容如下:

test.__db_cache_size=130023424
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__shared_pool_size=75497472
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA/test/controlfile/current.261.733318571','+DATA/test/controlfile/current.260.733318571'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=master'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=214958080
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'
DB_UNIQUE_NAME=master
LOG_ARCHIVE_CONFIG='DG_CONFIG=(master,standby)'
LOG_ARCHIVE_DEST_2='SERVICE=standby_db LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=standby_db
FAL_CLIENT=master_db
STANDBY_FILE_MANAGEMENT=AUTO

备库参数文件内如下:

test.__db_cache_size=130023424
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__shared_pool_size=75497472
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA/standby/controlfile/backup.273.735399141','+DATA/standby/controlfile/backup.277.735399141'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=214958080
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(master,standby)'
LOG_ARCHIVE_DEST_2='SERVICE=master_db LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=master'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=master_db
FAL_CLIENT=standby_db
STANDBY_FILE_MANAGEMENT=AUTO

主库LISTENER设置(包括ASM的相关设置):
# listener.ora Network Configuration File: /u01/app/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 = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  (SID_DESC =

    (GLOBAL_DBNAME = test)

    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)  

    (SID_NAME = test)

    )
  (SID_DESC =
 
  (SID_NAME = +ASM)

  (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)


 )

)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = db_master)(PORT = 1521))
    )
  )

LISTENERASM =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = db_master)(PORT = 1521))
    )
  )
客户端TNS设置(包括连接ASM的相关设置)

db_# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

MASTER_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

STANDBY_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

ASM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = +ASM)
    )
  )

C:\Documents and Settings\Administrator>sqlplus sys/lyf625@asm as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 12月 8 11:41:49 2010

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> show sga;

Total System Global Area   83886080 bytes
Fixed Size                  1217836 bytes
Variable Size              57502420 bytes
ASM Cache                  25165824 bytes
SQL>(连接到ASM实例)

-> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 10 18:01:53 2010

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

connected to target database: test (not mounted)

RMAN> run {
2> set controlfile autobackup format for device type disk to '+DATA/test/controlfile/%F';
3> restore controlfile from '/u01/app/control.bak';
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

Starting restore at 10-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA/standby/controlfile/backup.256.734724225
output filename=+DATA/standby/controlfile/backup.257.734724225
Finished restore at 10-NOV-10

RMAN> exit


Recovery Manager complete.
db_Standby-> exit
exit

SQL> alter database mount;

Database altered.
db_Standby-> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 10 18:04:29 2010

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

connected to target database: TEST (DBID=2031465002, not open)

RMAN> restore database;

Starting restore at 10-NOV-10
Starting implicit crosscheck backup at 10-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=120 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 10-NOV-10

Starting implicit crosscheck copy at 10-NOV-10
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 10-NOV-10

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/test/datafile/system.256.733318485
restoring datafile 00002 to +DATA/test/datafile/undotbs1.258.733318485
restoring datafile 00003 to +DATA/test/datafile/sysaux.257.733318485
restoring datafile 00004 to +DATA/test/datafile/users.259.733318485
restoring datafile 00005 to +DATA/test/datafile/example.269.733318631
restoring datafile 00006 to +DATA/test/datafile/ceshi.271.733405923
restoring datafile 00007 to +DATA/test/datafile/ceshi.272.733406097
restoring datafile 00008 to +DATA/test/datafile/ceshi.273.733406115
channel ORA_DISK_1: reading from backup piece /u01/app/0jlsq5cr_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/0jlsq5cr_1_1 tag=TAG20101112T075819
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 10-NOV-10

RMAN> exit


Recovery Manager complete.
db_Standby-> exit
exit

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

Database altered.

Data Gurad 主库和备库的切换:

主库全部操作如下(切换后变为备库):

select switchover_status from  v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL>select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------- -------------------- -------------------- --------------------
READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO STANDBY

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

  COUNT(*)
----------
         1
SQL>  alter   database  commit to switchover to physical standby;


Database altered.
SQL> shutdown
immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> SP2-0042: unknown command "immediate" - rest of line ignored.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  218103808 bytes
Fixed Size                  1218604 bytes
Variable Size              83888084 bytes
Database Buffers          130023424 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            89

SQL> alter database open read only;

Database altered.

SQL> select * from leviton;

NAME
----------
HELLO
leviton
Good
Bye

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

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from leviton;(验证切换后,在主库上添加的两条记录,在备库有没有生成?关于添加的两条记录请参看下面在主库上的操作)

NAME
----------
HELLO
leviton
Good
Bye
ok
standby

6 rows selected.

备库全部操作如下(切换后变为主库):

select switchover_status from  v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> select DATABASE_ROLE from v$database;

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

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------- -------------------- -------------------- --------------------
MOUNTED    MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED

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

  COUNT(*)
----------
         2

SQL> select switchover_status from v$database;

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

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------- -------------------- -------------------- --------------------
READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  SESSIONS ACTIVE

SQL> insert into leviton values('ok');(在切换后的主库上插入两条新纪录)

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

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

MAX(SEQUENCE#)
--------------
            89

SQL> insert into leviton values('standby');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> commit;

Commit complete.

SQL> !

10g物理standby主备switchover方式切换详述

以下给大家展现一下10g物理standby主备之间通过switchover方式进行切换的详细步骤,供参考。

1、主库检查是否为“TO STANDBY”状态,若不是,需要重新启动一下主库(主库ora10g操作)
sys@ora10g> select switchover_status from v$database;

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

sys@ora10g> startup force ;
ORACLE instance started.

Total System Global Area  104857600 bytes
Fixed Size                  1266056 bytes
Variable Size              83889784 bytes
Database Buffers           16777216 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
sys@ora10g> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

2.将primary转换为standby角色(主库ora10g操作)
sys@ora10g> alter database commit to switchover to physical standby;

Database altered.

3.原主库重启动到mount状态(原主库ora10g操作)
NotConnected@> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
NotConnected@>
NotConnected@> startup mount;
ORACLE instance started.

Total System Global Area  104857600 bytes
Fixed Size                  1266056 bytes
Variable Size              88084088 bytes
Database Buffers           12582912 bytes
Redo Buffers                2924544 bytes
Database mounted.

4.检查原备库是否为“TO PRIMARY”状态,如果为“SWITCHOVER PENDING”状态,需要先进行一下恢复再切换 (待切换备库ora10gdg操作)
ora10g@linux5 /home/oracle$ export ORACLE_SID=ora10gdg
ora10gdg@linux5 /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 28 08:26:53 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options

NotConnected@> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SWITCHOVER PENDING

NotConnected@> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required

NotConnected@> alter database recover managed standby database disconnect from session;

Database altered.

NotConnected@> alter database recover managed standby database cancel;

Database altered.

NotConnected@> select switchover_status from v$database;

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

5.原备库转换角色到primary (待切换备库ora10gdg操作)
NotConnected@> alter database commit to switchover to primary;

Database altered.

6.主备切换完成,open新的primary数据库  (待切换备库ora10gdg操作)
NotConnected@> alter database open;

Database altered.

7.最后验证阶段
1).新的primary库在sec用户创建一个新表test_new
sec@ora10g> create table test_new (a int);

Table created.

sec@ora10g> insert into test_new values ( 100 );

1 row created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from test_new;

         A
----------
       100

sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter system switch logfile;

System altered.

sys@ora10g> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            69

2).查看新standby库,验证切换是否成功
ora10gdg@linux5 /home/oracle$ export ORACLE_SID=ora10g
ora10g@linux5 /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 28 08:55:09 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options

NotConnected@> alter database recover managed standby database disconnect from session;

Database altered.

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

MAX(SEQUENCE#)
--------------
            69

NotConnected@> alter database recover managed standby database cancel;

Database altered.

NotConnected@> alter database open read only;

Database altered.

NotConnected@> conn sec/sec
Connected.
sec@ora10g> select * from test_new;

         A
----------
       100

OK!到此主备之间的switchover方式切换成功。

secooler
09.03.27

-- The End --


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

请登录后发表评论 登录
全部评论

注册时间:2010-03-06

  • 博文量
    41
  • 访问量
    57213