oracle 11g dataguard

同一台机器实现。主库 ORADB,备库ORACP.主库已已经存在。

第一部分，创建standby库。

创建ORACP实例
D:\> oradim -new -intpwd password -sid ORACP -startmode auto

主库备库的sys密码一定要相同。可以拷贝密码文件改名，也可用命令改。
D:\> orapwd file=C:\Oracle\ora11g\database\PWDORACP.ora password=password ignorecase=y
D:\> orapwd file=C:\Oracle\ora11g\database\PWDORADB.ora password=password ignorecase=y

从ORADP的spfile生成pfile，修改pfile，再生成spfile。
ORADB的pfile
---------------------------------------------------------------------
oradb.__db_cache_size=444596224
oradb.__java_pool_size=4194304
oradb.__large_pool_size=4194304
oradb.__oracle_base='C:\Oracle'#ORACLE_BASE set from environment
oradb.__pga_aggregate_target=432013312
oradb.__sga_target=641728512
oradb.__shared_io_pool_size=0
oradb.__shared_pool_size=180355072
oradb.__streams_pool_size=0
*.audit_file_dest='C:\Oracle\admin\ORADB\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='F:\Oracle_Data\ORADB\control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORADB'
*.db_recovery_file_dest='F:\Oracle_Data\ORADB\flash_recovery_area'
*.db_recovery_file_dest_size=8589934592
*.diagnostic_dest='F:\Oracle_Data\ORADB'
*.memory_target=1073741824
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#以下是手工增加的内容
*.instance_name='ORADB'
*.db_unique_name='ORADB'
*.log_archive_config='dg_config=(ORADB,ORACP)'
*.log_archive_dest_1='location=F:\Oracle_Data\ORADB\ARCHIVELOG valid_for=(all_logfiles,all_roles) db_unique_name=ORADB'
*.log_archive_dest_2='service=ORACP LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=ORACP'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='ORACP'
*.fal_client='ORADB'

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

拷贝ORADB的spfile，修改让ORACP使用。
ORACP的pfile
---------------------------------------------------------------------
ORACP.__db_cache_size=444596224
ORACP.__java_pool_size=4194304
ORACP.__large_pool_size=4194304
ORACP.__oracle_base='C:\Oracle'#ORACLE_BASE set from environment
ORACP.__pga_aggregate_target=432013312
ORACP.__sga_target=641728512
ORACP.__shared_io_pool_size=0
ORACP.__shared_pool_size=180355072
ORACP.__streams_pool_size=0
*.audit_file_dest='C:\Oracle\admin\ORACP\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='F:\Oracle_Data\ORACP\control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORADB'
*.db_recovery_file_dest='F:\Oracle_Data\ORACP\flash_recovery_area'
*.db_recovery_file_dest_size=8589934592
*.diagnostic_dest='F:\Oracle_Data\ORACP'
*.memory_target=1073741824
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#以下是手工增加的内容
*.db_unique_name='ORACP'
*.log_archive_config='dg_config=(ORADB,ORACP)'
*.log_archive_dest_1='location=F:\Oracle_Data\ORACP\ARCHIVELOG valid_for=(all_logfiles,all_roles) db_unique_name=ORACP'
*.log_archive_dest_2='service=ORADB LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=ORADB'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='ORADB'
*.fal_client='ORACP'
*.db_file_name_convert='F:\Oracle_Data\ORADB\','F:\Oracle_Data\ORACP\'
*.log_file_name_convert='F:\Oracle_Data\ORADB\','F:\Oracle_Data\ORACP\'
---------------------------------------------------------------------------
编辑tnsnames
---------------------------------------------------------------------------
ORADB =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORADB)
    )
)

ORACP =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORACP)
    )
)
---------------------------------------------------------------------------

listener
---------------------------------------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oradb)
      (ORACLE_HOME = c:\oracle\ora11g)
      (SID_NAME = oradb)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = oracp)
      (ORACLE_HOME = c:\oracle\ora11g)
      (SID_NAME = oracp)
    )
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = win2k8)(PORT = 1521))
)

ADR_BASE_LISTENER = C:\Oracle
---------------------------------------------------------------------------

好了，启动oracp 到nomount状态
D:\>set oracle_sid=oracp
D:\>sqlplus /nolog
SQL> connect / as sysdba ;
SQL> create spfile from pfile='f:\oracle_data\oracp\initoracp.ora' ;
SQL> startup nomount

启动oradb到open状态
D:\>set oracle_sid=oradb
D:\>sqlplus /nolog
SQL> connect / as sysdba ;
已连接到空闲例程。
SQL> startup ;
ORACLE 例程已经启动。
SQL> exit ;

ORADB RMAN备份数据作成并复制到备库上。
D:\>set oracle_sid=oradb
D:\>RMAN TARGET /
--------rman开始------------------------------------------------
RMAN>

RUN
{
backup device type disk format 'F:\Oracle_Data\rman_data/%U' database plus archivelog ;
backup device type disk format 'F:\Oracle_Data\rman_data/%U' current controlfile for standby ;
}

connect auxiliary sys/password@oracp ;

RUN
{
duplicate target database for standby;
}

exit

--------rman结束------------------------------------------------

看看两个库的情况
oradb instnace ;
SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY TO STANDBY READ WRITE

oracp instance ;
SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY NOT ALLOWED MOUNTED

这里的"NOT ALLOWED"，google了好久。找到了一个说法，可以解决。
从主库里创建控制文件给备库用。
关闭备库ORACP。

To create a control file, you have to do the following on the Primary server...
SQL> startup mount;
SQL> alter database create standby controlfile as 'F:\Oracle_Data\ORACP\CONTROL01.CTL';
SQL> alter database open;

启动备库ORACP到mount状态。嗯，很漂亮。
SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY TO PRIMARY MOUNTED

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

数据库已更改。

SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE MOUNTED

SQL> alter database recover managed standby database cancel;
完成介质恢复。
SQL> alter database recover managed standby database finish ;
数据库已更改。
这是failover,主库崩溃的时候才用，用了它意味着备库成了主库,.原来的主库没了。一般情况下不用。

SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY TO PRIMARY MOUNTED

SQL> alter database open read only ;

数据库已更改。

到主库ORADB中强制归档一下。
SQL> ALTER SYSTEM SWITCH LOGFILE;

系统已更改。

查看一下两边的归档日志
SQL> SELECT a.SEQUENCE#, a.FIRST_TIME, a.NEXT_TIME,a.NEXT_CHANGE# FROM V$ARCHIVED_LOG a ORDER BY a.SEQUENCE#;

完成。

第二部分，主从库切换。

最初状态（ORADB 实例）
SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY TO STANDBY READ WRITE

最初状态（ORACP 实例）
SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY TO PRIMARY MOUNTED

碰到好几回SWITCHOVER_STATUS的状态经常是 "NOT ALLOWED"的，也可以切换。

切换动作，主库 ORADB上操作

SQL> alter database commit to switchover to physical standby ;

数据库已更改。
SQL> shutdown immediate ;
SQL> startup mount ;
ORACLE 例程已经启动。

Total System Global Area 644468736 bytes
Fixed Size                  1376520 bytes
Variable Size             192941816 bytes
Database Buffers          444596224 bytes
Redo Buffers                5554176 bytes
数据库装载完毕。

SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY TO PRIMARY MOUNTED

备库 ORACP 上操作。
SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY SWITCHOVER LATENT MOUNTED
看到状态已经变了。

SQL> alter database recover managed standby database disconnect from session;
数据库已更改。

SQL> alter database commit to switchover to primary;
数据库已更改。

SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY NOT ALLOWED MOUNTED

SQL> alter database open ;

数据库已更改。

SQL> select db.DATABASE_ROLE,db.SWITCHOVER_STATUS,db.OPEN_MODE from v$database db ;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY TO STANDBY READ WRITE

数据库Open之后，状态变成正常了。

完成。

反复用到的几条语句
SQL> alter database recover managed standby database disconnect from session;
以下这句需要standby redo log file.
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> alter database recover managed standby database cancel;
SQL> alter database commit to switchover to physical standby ;
SQL> alter database commit to switchover to primary;

增加standby redo log 可以让备库实时的反应主库的变化，两边都要加，数量是正常redofile 数量+1 .
alter database add standby logfile group 4 ('F:\ORACLE_DATA\ORADB\REDO04.LOG') size 50M;
alter database add standby logfile group 5 ('F:\ORACLE_DATA\ORADB\REDO05.LOG') size 50M;
alter database add standby logfile group 6 ('F:\ORACLE_DATA\ORADB\REDO06.LOG') size 50M;
alter database add standby logfile group 7 ('F:\ORACLE_DATA\ORADB\REDO07.LOG') size 50M;

ASM 下的DATAGUARD
基本上和文件系统一样的。

主库oradb pfile增加
*.instance_name='oradb'
*.db_unique_name='oradb'
*.standby_file_management='auto'
*.log_archive_config='dg_config=(oradb,oracp)'
*.log_archive_dest_1='location=/oradata/oradb valid_for=(all_logfiles,all_roles) db_unique_name=oradb'
*.log_archive_dest_2='service=oracp lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=oracp'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_client='oradb'
*.fal_server='oracp'
##################################################

创建备库的时候，PFILE中control_files不用定义
db_file_name_convert,log_file_name_convert 也不需要。

备库oracp pfile增加
*.instance_name='oracp'
*.db_unique_name='oracp'
*.standby_file_management='auto'
*.log_archive_config='dg_config=(oradb,oracp)'
*.log_archive_dest_1='location=/oradata/oracp valid_for=(all_logfiles,all_roles) db_unique_name=oracp'
*.log_archive_dest_2='service=oradb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=oradb'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.fal_client='oracp'
*.fal_server='oradb'
###################################################

posted @ 2011-05-22 22:01 abenz 阅读(1114) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

abenz's blog

oracle 11g dataguard

公告