ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DATAGUARD日志

DATAGUARD日志

原创 Linux操作系统 作者:steve352 时间:2009-04-06 14:34:57 0 删除 编辑
uname -a
环境centos 39  oracle10.2.0
-----------standby database
[oracle@stevelinux u01]$ mkdir flash_recovery_area
[oracle@stevelinux u01]$ ls
admin   flash_recovery_area  oracle  oradata  oraInventory  
[oracle@stevelinux steve]$ mkdir adump bdump cdump dpdump udump pfile scripts
[oracle@stevelinux steve]$ ls
adump  bdump  cdump  dpdump  pfile  scripts  udump
[oracle@stevelinux steve]$ pwd
/u01/oradata/steve
------------primary database(现有DB)
SQL> alter database create standby controlfile as '/u01/oradata/steve/constandby.ctl';
SQL> shutdown immediate;
--scp 主库数据文件到备库机上
[oracle@stevelinux steve]$ scp * 192.168.72.189:/u01/oradata/steve/
oracle@192.168.72.189's password:
constandby.ctl                                                                 100% 7184KB  12.1MB/s   00:00   

 
control01.ctl                                                                  100% 7184KB  11.7MB/s   00:00  

 
control02.ctl                                                                  100% 7184KB  10.3MB/s   00:00  

 
control03.ctl                                                                  100% 7184KB   9.3MB/s   00:00  

 
example01.dbf                                                                  100%  100MB   8.8MB/s   00:11  

 
redo01.log                                                                     100%   50MB   6.9MB/s   00:07  

 
redo02.log                                                                     100%   50MB   5.7MB/s   00:08  

 
redo03.log                                                                     100%   50MB   4.8MB/s   00:10  

 
sysaux01.dbf                                                                   100%  240MB   7.7MB/s   00:30  

 
system01.dbf                                                                   100%  480MB   5.9MB/s   01:20  

 
temp01.dbf                                                                     100%   21MB   9.7MB/s   00:02  

 
undotbs01.dbf                                                                  100%   30MB   6.9MB/s   00:04  

 
users01.dbf                                                                    100% 5128KB  10.8MB/s
[oracle@stevelinux dbs]$ scp initsteve.ora orapwsteve 192.168.72.189:/u01/oracle/dbs/
---主库上的pfile
steve.__db_cache_size=88080384
steve.__java_pool_size=4194304
steve.__large_pool_size=4194304
steve.__shared_pool_size=67108864
steve.__streams_pool_size=0
*.audit_file_dest='/u01/admin/steve/adump'
*.background_dump_dest='/u01/admin/steve/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oradata/steve/control01.ctl','/u01/oradata/steve/control02.ctl','/u01/oradata/steve/cont

rol03.ctl'
*.core_dump_dest='/u01/admin/steve/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='steve'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=steveXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u01/oradata/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=steve'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/admin/steve/udump'
DB_UNIQUE_NAME=steve
LOG_ARCHIVE_CONFIG='DG_CONFIG=(steve,steve89)'  ##为db_unique_name
LOG_ARCHIVE_DEST_2=
 'SERVICE=steve89 LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=steve89'                      ##为 net server_name
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
fal_server=steve89                             ##用于角色转换fal_server为备库的tns
fal_client=steve
STANDBY_FILE_MANAGEMENT=AUTO
db_file_name_convert='/u01/oradata','/u01/oradata'
log_file_name_convert='/u01/oradata/arch','/u01/oradata/steve/arch'
db_recovery_file_dest='/u01/flash_recovery_area'
db_recovery_file_dest_size=1024000000
(FAL_SERVER:  An OracleNet service name that exist in the standby tnsnames.ora
             file that points to the primary database listener.  The FAL_SERVER
             parameter can contain a comma delimited list of locations that
             should be attempted during gap resolution.

FAL_CLIENT:  An OracleNet service name that exist in the primary tnsnames.ora
             file that points to the standby database listener.  The value of
             FAL_CLIENT should also be listed as the service in a remote
             archive destination pointing to the standby.
)
---主库上tns文件
steve89 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.189)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = steve89)
    )
  )


STEVE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.188)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = steve)
    )
  )

-----备库的上的PFILE
steve89.__db_cache_size=88080384
steve89.__java_pool_size=4194304
steve89.__large_pool_size=4194304
steve89.__shared_pool_size=67108864
steve89.__streams_pool_size=0
*.audit_file_dest='/u01/admin/steve/adump'
*.background_dump_dest='/u01/admin/steve/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oradata/steve/control01.ctl','/u01/oradata/steve/control02.ctl','/u01/oradata/steve/cont

rol03.ctl'
*.core_dump_dest='/u01/admin/steve/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='steve'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=steveXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u01/oradata/steve/arch/ valid_for=(all_logfiles,all_roles)

db_unique_name=steve89'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/admin/steve/udump'
DB_UNIQUE_NAME=steve89
LOG_ARCHIVE_CONFIG='DG_CONFIG=(steve,steve89)'
LOG_ARCHIVE_DEST_2=
 'SERVICE=steve LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=steve'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
fal_server=steve
fal_client=steve89
STANDBY_FILE_MANAGEMENT=AUTO
db_file_name_convert='/u01/oradata','/u01/oradata'
log_file_name_convert='/u01/oradata/steve/arch','/u01/oradata/arch'
db_recovery_file_dest='/u01/flash_recovery_area'
db_recovery_file_dest_size=1024000000

--密码文件
mv orapwsteve orapwsteve89
--备库上的 tns
steve89 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.189)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = steve89)
    )
  )

STEVE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.188)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = steve)
    )
  )
--备库上的listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (ORACLE_HOME = /u01/oracle)
      (SID_NAME = steve89)
    )
  )

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

--测试tnsping steve
tnsping steve89
---主库SQL> ALTER DATABASE FORCE LOGGING;
----启动备库
SQL> startup nomount;
SQL> alter database mount standby database
SQL> alter database recover managed standby database disconnect from session;
SQL> select database_role from v$database;

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

---测试是否成功!
主库SQL> alter system switch logfile;
查备库
select sequence#,first_time,next_time,applied,creator from v$archived_log
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
---------------------------------------------------------------------------
--创建standby redo logs
主要是为了最大保护,和最高可用性模式
SQL> select * from v$standby_log;
2)在主、备数据库分别创建比联机日志多至少一组的Standby redo log(在主机作standby redo log 是为了作swichover时候

用);
   standby redo log的大小应该跟online redo log的大小相等(select GROUP#,BYTES,MEMBERS,STATUS from v$log;)
注意:若备用库已进入自动恢复模式,需要先停止才能正确执行以上语句
SQL>  alter database recover managed standby database cancel;
SQL> alter  database add standby logfile('/u01/oradata/steve/standlog01.tdo' ) size 10m;

Database altered.

SQL> alter  database add standby logfile('/u01/oradata/steve/standlog02.tdo') size 10m;

Database altered.

SQL> alter  database add standby logfile('/u01/oradata/steve/standlog03.tdo') size 10m;
Database altered.

SQL> alter  database add standby logfile('/u01/oradata/steve/standlog04.tdo') size 10m;

Database altered.
-- 备库
SQL> alter database recover managed standby database disconnect from session;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;查看否使用(在最大性能保护模式下不会使用)
----在主库上更改为最大可用性模式
SQL> alter system set log_archive_dest_2='service=steve89 LGWR SYNC mandatory affirm reopen=200

DB_UNIQUE_NAME=STEVE89' scope=both;
--重新open primary database 以排他模式装载
SQL> shutdown immediate;
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              75499764 bytes
Database Buffers           88080384 bytes
Redo Buffers                2973696 bytes
SQL> alter database mount exclusive;

SQL> alter database set standby database to maximize  availability;
(ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE}
)
alter database open;

--查看保护模式
SQL>select database_role,protection_mode,protection_level from v$database;
---确定当前重作日志顺序号
SQL> select thread#,sequence#,archived,status from v$log;
--确定最近的归档重作日志
SQL> select max(sequence#) from v$archived_log;
--确定每个目的地的最近归档日志
SQL> select destination,status,archived_thread#,archived_seq# from v$archive_dest_status
          where status <> 'DEFERRED' and  status<>'INACTIVE';
---查看某特殊位置日志是否收到
SQL> select dest_id from v$archive_dest;
SQL> select local.thread#,local.sequence# from
         (select thread#,sequence# from v$archived_log where dest_id=1)
         local where
         local.sequence# not in
         (select sequence# from v$archived_log where dest_id=2 and thread#=local.thread#);
检查是否已正确设置日志应用服务
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
          FROM V$MANAGED_STANDBY;
23、日志应用服务
1)启动/停止日志应用服务
SQL> alter database start logical standby apply;
SQL> alter database stop logical standby apply;
2)确认重做日志已应用
·V$LOGSTDBY
SQL> column status format A50
SQL> column type format A12
SQL> select type,high_scn,status from v$logstdby;
·DBA_LOGSTDBY_PROGRESS
SQL> select applied_scn,newest_scn from dba_logstdby_progress;
3)修补归档间隙
在备用服务器上
SQL> select * from v$archive_gap;
在主服务器
SQL> select name from v$archived_log where thread#=1 dest_id=1 and sequence# between 7 and 10;
拷贝归档日志到备用服务器,然后执行
SQL> alter database register logfile '/physical_standby1/thread1_dest/arcr_1_7.arc';
然后重启管理恢复操作

24、监视物理备用数据库日志应用服务
SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
SQL> select archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;
SQL> select registrar,creator,thread#,sequence#,first_change#,next_change# from v$archived_log;
SQL> select thread#,sequence#,first_change#,next_change# from v$log_history;
SQL> select message from v$dataguard_status;

SQL> select file_name,sequence#,first_change#,next_change#,timestamp,dict_begin,dict_end,thread# from

dba_logstdby_log order by sequence#;

SQL> select applied_scn,newest_scn from dba_logstdby_progress;
SQL> alter session set NLS_DATA_FORMAT='DD-MON-YY HH24:MI:SS';
SQL> select l.sequence#,l.first_time,(case when l.next_change# < p.read_scn then 'yes'
                                                                 when l.first_change# < p.applied_scn then

'current'
                                                                 else 'no' end) applied
          from dba_logstdby_log l, dba_logstdby_progress p
          order by sequence#;

--------------------------------------------------------
发现 standby redo log的大小与online redo log的大小不相等
在主,备上做修改
SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;
在操作系统rm后再建
SQL> alter  database add standby logfile('/u01/oradata/steve/standlog01.tdo' ) size 50m;

SQL>  alter  database add standby logfile('/u01/oradata/steve/standlog02.tdo') size 50m;

SQL> alter  database add standby logfile('/u01/oradata/steve/standlog03.tdo') size  50m;    

SQL>  alter  database add standby logfile('/u01/oradata/steve/standlog04.tdo') size 50m;
--备库同样操作(    先取消同步SQL> alter database recover managed standby database finish;)
----SQL> alter database recover managed standby database disconnect from session;

Client logon and security negotiation successful!
Error 16143 attaching RFS server to standby instance at host 'steve89'
Error 16143 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'steve89'
ORA-16143: RFS connections not allowed during or after terminal recovery
*** 2009-03-25 18:54:52.597 60679 kcrr.c
PING[ARC1]: Heartbeat failed to connect to standby 'steve89'. Error is 16143.
*** 2009-03-25 18:54:52.597 58941 kcrr.c
kcrrfail: dest:2 err:16143 force:0 blast:1
sql> alter system set log_archive_dest_2='service=steve89 LGWR SYNC AFFIRM VALID_FOR=

(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=steve89' scope=both;
alter database recover managed standby database finish; 这是failover,主库崩溃的时候才用,用了它意味着备库成了

主库,.原来的主库没了
RC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC1: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
LGWR: Error 16143 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host
 'steve89'

Wed Mar 25 19:33:35 2009
Thread 1 advanced to log sequence 54
  Current log# 2 seq# 54 mem# 0: /u01/oradata/steve/redo02.log
Wed Mar 25 19:34:12 2009
Thread 1 advanced to log sequence 55
  Current log# 3 seq# 55 mem# 0: /u01/oradata/steve/redo03.log
Thread 1 cannot allocate new log, sequence 56
Checkpoint not complete
  Current log# 3 seq# 55 mem# 0: /u01/oradata/steve/redo03.log
Thread 1 advanced to log sequence 56
  Current log# 1 seq# 56 mem# 0: /u01/oradata/steve/redo01.log
Wed Mar 25 19:34:54 2009
Errors in file /u01/admin/steve/bdump/steve_arc1_6155.trc:
ORA-16143: RFS connections not allowed during or after terminal recovery
Wed Mar 25 19:34:54 2009
PING[ARC1]: Heartbeat failed to connect to standby 'steve89'. Error is 16143.
Wed Mar 25 19:35:19 2009
Thread 1 cannot allocate new log, sequence 57
Checkpoint not complete
  Current log# 1 seq# 56 mem# 0: /u01/oradata/steve/redo01.log
Thread 1 advanced to log sequence 57
  Current log# 2 seq# 57 mem# 0: /u01/oradata/steve/redo02.log

试着解决:
--主库上SQL> alter database set standby to maximize performance;
--备库上sql>alter database recover standby database disconnect;
再看是否有错误出现
---主库上alter system switch logfile
同样出错
Redo shipping client performing standby login
*** 2009-03-25 20:36:03.240 64561 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Error 16143 attaching RFS server to standby instance at host 'steve89'
Error 16143 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'steve89'
ORA-16143: RFS connections not allowed during or after terminal recovery
*** 2009-03-25 20:36:03.267 60679 kcrr.c
PING[ARC0]: Heartbeat failed to connect to standby 'steve89'. Error is 16143.
*** 2009-03-25 20:36:03.267 58941 kcrr.c
kcrrfail: dest:2 err:16143 force:0 blast:1
kcrrwkx: nothing to do (end)
*** 2009-03-25 20:37:03.266
kcrrwkx: nothing to do (end)
----查了一下备库报出如下错误\
lter database recover managed standby database disconnect from session
Sat Mar 28 23:19:36 2009
Attempt to start background Managed Standby Recovery process (steve89)
MRP0 started with pid=18, OS id=2855
Sat Mar 28 23:19:36 2009
MRP0: Background Managed Standby Recovery process started (steve89)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 16157
Sat Mar 28 23:19:41 2009
Errors in file /u01/admin/steve/bdump/steve89_mrp0_2855.trc:
ORA-16157: media recovery not allowed following successful FINISH recovery
Sat Mar 28 23:19:41 2009
Errors in file /u01/admin/steve/bdump/steve89_mrp0_2855.trc:
ORA-16157: media recovery not allowed following successful FINISH recovery
Sat Mar 28 23:19:41 2009
MRP0: Background Media Recovery process shutdown (steve89)
Sat Mar 28 23:19:42 2009
Completed: alter database recover managed standby database disconnect from session
Sat Mar 28 23:22:14 2009
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/oradata/steve/arch/
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
----在备库上设STANDBY_ARCHIVE_DEST
注(STANDBY_ARCHIVE_DEST is used when logs are transmitted automatically from the primary database.  We use

this parameter to determine where these logs should be placed.

LOG_ARCHIVE_DEST is the location where we look for logfiles by default when recovering the standby database. 

It is recommended to set the values of STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST to the same values in most

cases.)
SQL> alter system set standby_archive_dest='/u01/oradata/steve/standlog' scope=both;
---主库上SQL> alter system switch logfile;再次查看
还是报错 晕
--现在是最大性能模式
--主库上改下
SQL> alter system set log_archive_dest_2='service=steve89 lgwr async valid_for=(online_logfiles,primary_role)

db_unique_name=steve89' scope=both; 改成async
--重启下备库再看看
同样报错(再晕)
试着恢复一下standby database
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;查不一至的log
cp到备库alter database register physical logfile '/u01/oradata/steve/arch/1_55_654475338.dbf';(注册日志文件)
--还是不行
--从主库上健一个控制文件再试试
--sql>alter database create standby controlfile as 'standbycto.ctl';
---控制文件COPY到备库盖旧的控制文件
--重启备库
--sql>startup nomout
--sql>alter database mount standby database;
---sql>alter database recover managed standby database disconnect from session;
--查看OKselect sequence#,applied,creator from v$archived_Log;可以接LOG文件了
----主库上切到最大可用模式
SQL> alter system set log_archive_dest_2='service=steve89 optional lgwr sync affirm valid_for=

(online_logfiles,primary_role) db_unique_name=steve89';
sq>alter database set standby database to maximize  availability;
--测试主库上:

SQL> create user panhua identified by panhua;
SQL> alter system switch logfile;
--备库上steve89
--SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> select * from all_users;
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
PANHUA                                 61 25-MAR-09
BI                                     60 11-MAY-08
PM                                     59 11-MAY-08
SH                                     58 11-MAY-08
SQL> alter database recover managed standby database  disconnect from session;
----ok
----总结
SQL> alter database recover managed standby database finish不能随便用,finish相当于failover,执行后就不再送日志

了解,恢复只能重建备库的控制文件或FLASHBACK finish前(select FLASHBACK_ON  from v$database;要打开),或重建备库

  ----------------角色转换switchover
select switchover_status from v$database;查状态(开机先备再主,关机先主再备
TO STANDBY 或SESSIONS ACTIVE表可转换
--主库上sql>SQL> alter database commit to switchover to physical standby;再重启到mount状态下
--备库上select switchover_status from v$database;

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> alter database open;

Database altered.
------测试一下是否正确
在steve89(原备库)
SQL> create table test(name varchar2(20));

Table created.

SQL> insert into test values('潘潘');

1 row created.

SQL> insert into test values('潘潘');

1 row created.
sql>commit(这个不要忘)
SQL> alter system switch logfile;

System altered.
----在steve上查(原主库)现备库
SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read  only;

Database altered.

SQL> select * from test;

no rows selected

SQL> select * from test;

no rows selected

SQL> select * from test;

no rows selected

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

Database altered.

SQL> archvie log list;
SP2-0734: unknown command beginning "archvie lo..." - rest of line ignored.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/arch/
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           80
SQL> alter database recover managed standby database cancel
  2  ;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from test;

NAME
--------------------
?E?E
?E?E
?E?E
?E?E
?E?E
--在primary server上查询有哪些日志没有被传输到Standby 

   SQL>SELECTLOCAL.THREAD#,LOCAL.SEQUENCE#FROM(SELECTTHREAD#,SEQUENCE#FROMV$ARCHIVED_LOGWHEREDEST_ID=1)

LOCALWHERELOCAL.SEQUENCE#NOTIN
  (SELECTSEQUENCE#FROMV$ARCHIVED_LOGWHEREDEST_ID=2ANDTHREAD#=LOCAL.THREAD#);

  --对于troubleshooting有用

   SQL>SELECTMESSAGEFROMV$DATAGUARD_STATUS;

-----------------------------------------------------------------
-----现在模拟failover
现主库为steve89,备库为steve
---主库上停掉network
     >/etc/init.d/./network stop
---查备库上alert.log
报Thu Mar 26 02:30:21 2009
ksvcreate: Process(m000) creation failed
Thu Mar 26 02:31:24 2009
ksvcreate: Process(m000) creation failed
Thu Mar 26 02:32:27 2009
ksvcreate: Process(m000) creation failed
Thu Mar 26 02:33:30 2009
ksvcreate: Process(m000) creation failed
Thu Mar 26 02:34:33 2009
ksvcreate: Process(m000) creation failed
Thu Mar 26 02:35:36 2009
ksvcreate: Process(m000) creation failed
Thu Mar 26 02:36:39 2009
ksvcreate: Process(m000) creation failed
还不太明白这个错误(加个记号)
failover前
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;查查没有应用的日志;
(注如果有cp过来register  ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';)
SQL> alter database recover managed standby database finish;
alter database recover managed standby database finish
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-00261: log 4 of thread 1 is being archived or modified
---报误(查看alert.log)
RFS[2]: Successfully opened standby log 4: '/u01/oradata/steve/standlog01.tdo'(说明已经被启用standby log)
Thu Mar 26 02:13:26 2009
Media Recovery Log /u01/oradata/arch/1_79_654475338.dbf
......
......
.....
Thu Mar 26 02:44:52 2009
alter database recover managed standby database finish
Thu Mar 26 02:44:52 2009
Attempt to do a Terminal Recovery (steve)
Thu Mar 26 02:44:52 2009
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Thu Mar 26 02:44:54 2009
SMON: disabling cache recovery
Thu Mar 26 02:44:54 2009
Media Recovery Start: Managed Standby Recovery (steve)
Thu Mar 26 02:44:54 2009
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 80 (in transit)
Thu Mar 26 02:45:55 2009
idle dispatcher 'D000' terminated, pid = (10, 4)
Thu Mar 26 02:47:09 2009
Warning: log 4 of thread 1 is being archived or modified
Recovery interrupted!
Thu Mar 26 02:47:10 2009
Media Recovery failed with error 261
ORA-283 signalled during: alter database recover managed standby database finish...
                                                                                    
-------------------先不管,往下做做看
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;(,用于强行把备用数据库转换成主数据库)

Database altered.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted
重新启动一下
SQL> startup mount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              79694068 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> select database_role from v$database;

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

SQL> alter database open;

Database altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/arch/
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
-----database做了 resetlogs
------------------------------------------------------------完
--也可用用skip standby logfile选项作failover
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE
---------------------------------------------------------------
再恢复steve89(原主库)把它转主备库
>./network start
----查出failover时的SCN
SQL> select to_char(standby_became_primary_scn) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
670963
SQL> flashback database to scn 670963;
flashback database to scn 670963
*
ERROR at line 1:
ORA-38726: Flashback database logging is not on.(没有启动flashback on)可能丢换数据
SQL> alter database convert to physical standby;

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


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              71305460 bytes
Database Buffers           92274688 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> select database_role from v$database;

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

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

Database altered.
测试是否成功
steve上主库
SQL> insert into test values('success');

1 row created.

SQL> insert into test values('success');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.
---查备库steve89
没有应用
RCH: Connecting to console port...
*** 2009-03-29 06:22:43.493 60679 kcrr.c
MRP0: Background Managed Standby Recovery process started
*** 2009-03-29 06:22:48.489 1011 krsm.c
Managed Recovery: Initialization posted.
*** 2009-03-29 06:22:48.490 60679 kcrr.c
Managed Standby Recovery not using Real Time Apply
Datafile 1 belongs to incarnation with resetlogs SCN : 446075, timestamp: 2702804a
*** 2009-03-29 06:22:48.502 60679 kcrr.c
MRP0: Background Media Recovery terminated with error 19909
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/oradata/steve/system01.dbf'
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/oradata/steve/system01.dbf'
ARCH: Connecting to console port...
*** 2009-03-29 06:22:48.503 60679 kcrr.c
MRP0: Background Media Recovery process shutdown

SQL> alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/oradata/steve/system01.dbf'
----查standby db
SQL> SELECT SUBSTR(FILE_NAME,1,25) FILE_NAME, SUBSTR(SEQUENCE#,1,4) "SEQ#",
  FIRST_CHANGE#, NEXT_CHANGE#, TO_CHAR(TIMESTAMP, 'HH:MI:SS') TIMESTAMP,
  DICT_BEGIN BEG, DICT_END END, SUBSTR(THREAD#,1,4) "THR#"
  FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
判断备库的日志应用服务应用了所有的事务(一定要确认本步骤)

sq>SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS
SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
----无mrp0(MRP0: Background Media Recovery process),说明日志没有应用;
奇怪的问题,standby redo log 有应用到备库,但MRP进程却没有启来
alert.log中有
MRP0: Background Media Recovery process shutdown (steve89)
Sun Mar 29 07:08:44 2009
Completed: alter database recover managed standby database disconnect from session
----alter database recover managed standby database disconnect from session又不会报错
 ---重新启动备库观查启动过程
Attempt to start background Managed Standby Recovery process (steve89)
MRP0 started with pid=18, OS id=19942
Sun Mar 29 07:48:56 2009
MRP0: Background Managed Standby Recovery process started (steve89)
Managed Standby Recovery not using Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 676000) is orphaned on incarnation#=2
MRP0: Background Media Recovery terminated with error 19909
Sun Mar 29 07:49:01 2009
Errors in file /u01/admin/steve/bdump/steve89_mrp0_19942.trc:
ORA-01110: data file 1: '/u01/oradata/steve/system01.dbf'
Sun Mar 29 07:49:01 2009
Errors in file /u01/admin/steve/bdump/steve89_mrp0_19942.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/oradata/steve/system01.dbf'
Sun Mar 29 07:49:01 2009
MRP0: Background Media Recovery process shutdown (steve89)
Sun Mar 29 07:49:02 2009
Completed: alter database recover managed standby database disconnect
Sun Mar 29 07:50:57 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 19979
RFS[1]: Identified database type as 'physical standby'
Sun Mar 29 07:50:57 2009
RFS LogMiner: Client disabled from further notification
RFS[1]: Archived Log: '/u01/oradata/steve/standlog/1_9_682484271.dbf'
Sun Mar 29 07:51:02 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 19981
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to MAXIMUM AVAILABILITY level
RFS[2]: Successfully opened standby log 4: '/u01/oradata/steve/standlog01.tdo'
Sun Mar 29 07:51:03 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 19983
RFS[3]: Identified database type as 'physical standby'
RFS[3]: Successfully opened standby log 5: '/u01/oradata/steve/standlog02.tdo

----steve89_mrp0_19942.trc内容

*** SERVICE NAME:() 2009-03-29 07:48:56.754
*** SESSION ID:(154.1) 2009-03-29 07:48:56.754
ARCH: Connecting to console port...
*** 2009-03-29 07:48:56.755 60679 kcrr.c
MRP0: Background Managed Standby Recovery process started
*** 2009-03-29 07:49:01.750 1011 krsm.c
Managed Recovery: Initialization posted.
*** 2009-03-29 07:49:01.756 60679 kcrr.c
Managed Standby Recovery not using Real Time Apply
Datafile 1 belongs to incarnation with resetlogs SCN : 446075, timestamp: 2702804a
*** 2009-03-29 07:49:01.819 60679 kcrr.c
MRP0: Background Media Recovery terminated with error 19909
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/oradata/steve/system01.dbf'
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/oradata/steve/system01.dbf'
ARCH: Connecting to console port...
*** 2009-03-29 07:49:01.822 60679 kcrr.c
MRP0: Background Media Recovery process shutdown
"steve89_mrp0_19942.trc" 35L, 1479C                
--------------------------------------------------------
select * from v$archive_gap;
 网上一位朋友写的,1那是因为切换一定在主备库都一致之后才能进行,而且必须是先把原主库成功切换为备库后;才能着手

去把原备库切换为主库,否则系统中有可能同时存在两个主库,从而最后不得不failover。 3 目前技术水平来说,只能重做

备库。
=============================================================
一些sql
14.1 ALTER DATABASE ACTIVATE STANDBY DATABASE
功能:当主数据库down的时候,用于强行把备用数据库转换成主数据库。

语法:ALTER DATABASE ACTIVATE [PHYSICAL | LOGICAL] STANDBY DATABASE [SKIP [STANDBY LOGFILE]];

PHYSICAL:当启用的是物理备用数据库的时候,加此参数,但如果不加的时候,默认就是这个参数,所以加不加都无所谓。

LOGICAL:当启用的是逻辑备用数据库的时候,加此参数

SKIP [STANDBY LOGFILE]:即使standby的redo log没有恢复到备用数据库,也强行启动。

14.2 ALTER DATABASE ADD/DROP [STANDBY] LOGFILE
功能:为主数据库或备用数据库添加/删除日志组

语法:ALTER DATABASE ADD/DROP [STANDBY] LOGFILE [THREAD integer] [GROUP integer] filespec;

STANDBY:为备用数据库添加/删除日志文件

其它的就和普通的建立日志语法一样,这里就不多作解释了。

14.3 ALTER DATABASE ADD/DROP [STANDBY] LOGFILE MEMBER
功能:为主数据库或备用数据库添加/删除日志成员

语法:ALTER DATABASE ADD/DROP [STANDBY] LOGFILE MEMBER ’filename’ [REUSE] TO logfile-descriptor;

14.4ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
功能:在创建logical standby前,必须启用SUPPLEMENTAL LOG,它存放着logical standby的更改信息源。

语法:ALTER DATABASE ADD SUPPLEMENTAL LOG DATA {PRIMARY KEY | UNIQUE INDEX} COLUMNS;

14.5 ALTER DATABASE COMMIT TO SWITCHOVER
功能:用于转换数据库的角色。

语法:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [[WITH | WITHOUT]

SESSION SHUTDOWN ] [WAIT | NOWAIT];

把主数据库转成物理备用数据库

ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY [WAIT | NOWAIT];

把主数据库转成逻辑备用数据库

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [[WITH | WITHOUT] SESSION

SHUTDOWN ] [WAIT | NOWAIT];

把物理备用数据库转成主数据库

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [WAIT | NOWAIT];

把逻辑备用数据库转成主数据库

14.6 ALTER DATABASE CREATE STANDBY CONTROLFILE AS
功能:用于创建备用库的控制文件

语法:ALTER DATABASE CREATE STANDBY CONTROLFILE AS ’filename’ [REUSE];


14.7 ALTER DATABASE MOUNT STANDBY DATABASE
功能:把物理备用数据库启动到mount状态

14.8 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
功能:用于启用、修改、关闭恢复的进程。

语法ALTER DATABASE RECOVER MANAGED STANDBY DATABASE [ startup_clause | modify_clause | cancel_clause ];

startup_clause:启动恢复的进程

启动前台的会话

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

启动后台的会话

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT [FROM SESSION];

modify_clause:修改恢复的进程

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE [

[ NO TIMEOUT | TIMEOUT [integer] ]

[ NODELAY | DELAY [integer] ]

[ DEFAULT DELAY ]

[ NO EXPIRE | EXPIRE [integer] ]

[ NEXT [integer] ]

[ NOPARALLEL | PARALLEL [integer]]

[ THROUGH { ALL | NEXT | LAST } SWITCHOVER ]

[ THROUGH ALL ARCHIVELOG [ THREAD n ] SEQUENCE n ]

[ FINISH [ SKIP [STANDBY LOGFILE] [NOWAIT | WAIT] ] ]

]

这里,最有用的就是finish 选项,它会应用所有有效的归档文件,如果加上skip standby logfile就会忽略standby的在线日

志中的信息。

cancel_clause:关闭恢复的进程,在关闭前,会应用所有已传到备用机上的归档日志。

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL [IMMEDIATE]

IMMEDIATE:在应用完当前的redo block后,马上关闭恢复进程。

14.9 ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE{PROTECTION | AVAILABILITY | PERFORMANCE}
功能:把备用数据库切换到不同的保护模式

14.10 ALTER DATABASE START/STOP/ABORT LOGICAL STANDBY APPLY
在逻辑备用数据库中启用/关闭日志应用服务

ALTER DATABASE START LOGICAL STANDBY APPLY [INITIAL [scn-value] ] [NEW PRIMARY dblink];

INITIAL [scn-value]:指定一个开始进程恢复的SCN号
-------------------------------------------
 emca -config dbcontrol db






 

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

上一篇: oracle 事务处理
请登录后发表评论 登录
全部评论

注册时间:2008-01-10

  • 博文量
    30
  • 访问量
    84593