DATAGUARD手记（PRIMARY+2STANDBY）（二）

congnen9588

于 2019-06-23 07:54:05 发布

阅读量127

点赞数

http://zhouwf0726.itpub.net/post/9689/395349
http://zhouwf0726.itpub.net/post/9689/395350
http://zhouwf0726.itpub.net/post/9689/395437
http://zhouwf0726.itpub.net/post/9689/401874
http://zhouwf0726.itpub.net/post/9689/403401

创建第一台STANDBY数据库：

一、备份主库：

oracle:db-tfusn2cn-a-r1.amazon.com: /arch-01/databases/yudong/redolog> $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.1.0.4.0 - Production

connected to target database: YUDONG (DBID=404134968)

RMAN> backup database include current controlfile plus archivelog; -- 很多方法，可以不备份归档。

Starting backup at 14-SEP-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=10634 recid=563 stamp=631886205
input archive log thread=1 sequence=10635 recid=564 stamp=631889807
input archive log thread=1 sequence=10636 recid=565 stamp=631893407
input archive log thread=1 sequence=10637 recid=566 stamp=631894418
input archive log thread=1 sequence=10638 recid=567 stamp=631894617
input archive log thread=1 sequence=10639 recid=568 stamp=631896897
input archive log thread=1 sequence=10640 recid=569 stamp=631896977
input archive log thread=1 sequence=10641 recid=570 stamp=631897127
input archive log thread=1 sequence=10642 recid=571 stamp=631900728
input archive log thread=1 sequence=10643 recid=572 stamp=631904335
input archive log thread=1 sequence=10644 recid=573 stamp=631907933
input archive log thread=1 sequence=10645 recid=574 stamp=631911526
input archive log thread=1 sequence=10646 recid=575 stamp=631915125
input archive log thread=1 sequence=10647 recid=576 stamp=631918728
input archive log thread=1 sequence=10648 recid=577 stamp=631922328
input archive log thread=1 sequence=10649 recid=578 stamp=631925927
input archive log thread=1 sequence=10650 recid=579 stamp=631929536
input archive log thread=1 sequence=10651 recid=580 stamp=631933135
input archive log thread=1 sequence=10652 recid=581 stamp=631936727
input archive log thread=1 sequence=10653 recid=582 stamp=631940332
input archive log thread=1 sequence=10654 recid=583 stamp=631942419
input archive log thread=1 sequence=10655 recid=584 stamp=631942467
input archive log thread=1 sequence=10656 recid=585 stamp=631942468
input archive log thread=1 sequence=10657 recid=586 stamp=631942474
input archive log thread=1 sequence=10658 recid=587 stamp=631942566
input archive log thread=1 sequence=10659 recid=588 stamp=631946158
input archive log thread=1 sequence=10660 recid=589 stamp=631949759
input archive log thread=1 sequence=10661 recid=590 stamp=631953359
input archive log thread=1 sequence=10662 recid=591 stamp=631956967
input archive log thread=1 sequence=10663 recid=592 stamp=631960569
input archive log thread=1 sequence=10664 recid=593 stamp=631960984
input archive log thread=1 sequence=10665 recid=594 stamp=631960993
input archive log thread=1 sequence=10666 recid=595 stamp=631960995
input archive log thread=1 sequence=10667 recid=596 stamp=631960996
channel ORA_DISK_1: starting piece 1 at 14-SEP-07
channel ORA_DISK_1: finished piece 1 at 14-SEP-07
piece handle=/opt/app/oracle/product/10.1.0.4/A04db/dbs/2girt4ms_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=399 recid=1796 stamp=633245550
input archive log thread=1 sequence=400 recid=1797 stamp=633245550
input archive log thread=1 sequence=401 recid=1798 stamp=633245602
input archive log thread=1 sequence=402 recid=1801 stamp=633245619
input archive log thread=1 sequence=403 recid=1799 stamp=633245603
input archive log thread=1 sequence=404 recid=1800 stamp=633245603
input archive log thread=1 sequence=405 recid=1802 stamp=633245632
input archive log thread=1 sequence=406 recid=1803 stamp=633245644
input archive log thread=1 sequence=407 recid=1804 stamp=633245760
input archive log thread=1 sequence=408 recid=1808 stamp=633245962
input archive log thread=1 sequence=409 recid=1812 stamp=633245974
input archive log thread=1 sequence=410 recid=1815 stamp=633245977
input archive log thread=1 sequence=411 recid=1818 stamp=633246335
input archive log thread=1 sequence=412 recid=1821 stamp=633246427
channel ORA_DISK_1: starting piece 1 at 14-SEP-07
channel ORA_DISK_1: finished piece 1 at 14-SEP-07
piece handle=/opt/app/oracle/product/10.1.0.4/A04db/dbs/2hirt4n4_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 14-SEP-07

Starting backup at 14-SEP-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00017 name=/fs-a01-a/databases/yudong/large_idx-01.dbf
input datafile fno=00014 name=/fs-a01-a/databases/yudong/administrator-02.dbf
input datafile fno=00001 name=/fs-a01-b/databases/yudong/system-01.dbf
input datafile fno=00004 name=/fs-a01-a/databases/yudong/administrator-01.dbf
input datafile fno=00006 name=/fs-a01-b/databases/yudong/replication-01.dbf
input datafile fno=00008 name=/fs-a01-a/databases/yudong/precise_or_tab-01.dbf
input datafile fno=00002 name=/fs-a01-a/databases/yudong/undo_t1-01.dbf
input datafile fno=00003 name=/fs-a01-a/databases/yudong/sysaux-01.dbf
input datafile fno=00005 name=/fs-a01-b/databases/yudong/administrator_idx-01.dbf
input datafile fno=00009 name=/fs-a01-b/databases/yudong/system-02.dbf
input datafile fno=00010 name=/fs-a01-a/databases/yudong/large_idx-02.dbf
input datafile fno=00007 name=/fs-a01-a/databases/yudong/replication_idx-01.dbf
input datafile fno=00011 name=/fs-a01-b/databases/yudong/system-03.dbf
input datafile fno=00016 name=/fs-a01-a/databases/yudong/zwf_new.dbf
input datafile fno=00012 name=/fs-a01-a/databases/yudong/large_idx-03.dbf
input datafile fno=00013 name=/fs-a01-a/databases/yudong/sysaux-02.dbf
input datafile fno=00015 name=/fs-a01-a/databases/yudong/zwf.dbf
input datafile fno=00018 name=/fs-a01-a/databases/yudong/zwf_new_new.dbf
input datafile fno=00019 name=/fs-a01-a/databases/yudong/zwf_new_new_new.dbf
channel ORA_DISK_1: starting piece 1 at 14-SEP-07
channel ORA_DISK_1: finished piece 1 at 14-SEP-07
piece handle=/opt/app/oracle/product/10.1.0.4/A04db/dbs/2iirt4n6_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:26
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
channel ORA_DISK_1: starting piece 1 at 14-SEP-07
channel ORA_DISK_1: finished piece 1 at 14-SEP-07
piece handle=/opt/app/oracle/product/10.1.0.4/A04db/dbs/2jirt4ro_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-SEP-07

Starting backup at 14-SEP-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=413 recid=1823 stamp=633246596
channel ORA_DISK_1: starting piece 1 at 14-SEP-07
channel ORA_DISK_1: finished piece 1 at 14-SEP-07
piece handle=/opt/app/oracle/product/10.1.0.4/A04db/dbs/2kirt4s4_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 14-SEP-07

Starting Control File Autobackup at 14-SEP-07
piece handle=/oracle-backup/db-backup/databases/yudong/c-404134968-20070914-04 comment=NONE
Finished Control File Autobackup at 14-SEP-07

二、拷贝备份文件集到第一台从库：

三、登录到第一台从库：

oracle:weifengz.desktop.amazon.com: /> $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.1.0.4.0 - Production

connected to target database (not started)

RMAN> set DBID=404134968

executing command: SET DBID

RMAN> startup nomount

Oracle instance started

Total System Global Area 490733568 bytes

Fixed Size 779456 bytes
Variable Size 407771968 bytes
Database Buffers 81920000 bytes
Redo Buffers 262144 bytes

RMAN> run{
set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle-backup/db-bac kup/databases/yudong/%F';
restore controlfile from autobackup;
}2> 3> 4>

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 14-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20070914
channel ORA_DISK_1: autobackup found: /oracle-backup/db-backup/databases/yudong/ c-404134968-20070914-04
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=/ctl-01/databases/yudong/control.ctl
output filename=/ctl-02/databases/yudong/control.ctl
Finished restore at 14-SEP-07

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1
ARCHIVED LOG records with recid from 131 to 452 were re-used before resync

RMAN> list backup;

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
75 Full 8M DISK 00:00:01 14-SEP-07
BP Key: 75 Status: AVAILABLE Compressed: NO Tag: TAG20070914T054718
Piece Name: /opt/app/oracle/product/10.1.0.4/A04db/dbs/2jirt4ro_1_1
Controlfile Included: Ckp SCN: 258761542 Ckp time: 14-SEP-07

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
76 93K DISK 00:00:01 14-SEP-07
BP Key: 76 Status: AVAILABLE Compressed: NO Tag: TAG20070914T054956
Piece Name: /opt/app/oracle/product/10.1.0.4/A04db/dbs/2kirt4s4_1_1

List of Archived Logs in backup set 76
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 413 258761351 14-SEP-07 258761545 14-SEP-07

RMAN> run
{ set until scn 258761542;
restore database;
recover database;
}2> 3> 4> 5>

executing command: SET until clause

Starting restore at 14-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /fs-a01-b/databases/yudong/system-01.dbf
restoring datafile 00002 to /fs-a01-a/databases/yudong/undo_t1-01.dbf
restoring datafile 00003 to /fs-a01-a/databases/yudong/sysaux-01.dbf
restoring datafile 00004 to /fs-a01-a/databases/yudong/administrator-01.dbf
restoring datafile 00005 to /fs-a01-b/databases/yudong/administrator_idx-01.dbf
restoring datafile 00006 to /fs-a01-b/databases/yudong/replication-01.dbf
restoring datafile 00007 to /fs-a01-a/databases/yudong/replication_idx-01.dbf
restoring datafile 00008 to /fs-a01-a/databases/yudong/precise_or_tab-01.dbf
restoring datafile 00009 to /fs-a01-b/databases/yudong/system-02.dbf
restoring datafile 00010 to /fs-a01-a/databases/yudong/large_idx-02.dbf
restoring datafile 00011 to /fs-a01-b/databases/yudong/system-03.dbf
restoring datafile 00012 to /fs-a01-a/databases/yudong/large_idx-03.dbf
restoring datafile 00013 to /fs-a01-a/databases/yudong/sysaux-02.dbf
restoring datafile 00014 to /fs-a01-a/databases/yudong/administrator-02.dbf
restoring datafile 00015 to /fs-a01-a/databases/yudong/zwf.dbf
restoring datafile 00016 to /fs-a01-a/databases/yudong/zwf_new.dbf
restoring datafile 00017 to /fs-a01-a/databases/yudong/large_idx-01.dbf
restoring datafile 00018 to /fs-a01-a/databases/yudong/zwf_new_new.dbf
restoring datafile 00019 to /fs-a01-a/databases/yudong/zwf_new_new_new.dbf

channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/app/oracle/product/10.1.0.4/A04db/dbs/2iirt4n6_1_1 tag=TAG20070914T054718
channel ORA_DISK_1: restore complete
Finished restore at 14-SEP-07

Starting recover at 14-SEP-07
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 413 is already on disk as file /arch-01/databases/yudong/redolog/-413-1-632058350.arc
archive log filename=/arch-01/databases/yudong/redolog/-413-1-632058350.arc thread=1 sequence=413
media recovery complete
Finished recover at 14-SEP-07

SQL> shutdown immediate

四、登录到主库：

SQL> alter database create standby controlfile as '/.../standby_control.ctl';

五、登陆到从库，拷贝主库standby_control.ctl到从库的相应位置，覆盖原有的控制文件，然后执行以下脚本：

SQL> startup mount
ORACLE instance started.

Total System Global Area 490733568 bytes
Fixed Size 779456 bytes
Variable Size 407771968 bytes
Database Buffers 81920000 bytes
Redo Buffers 262144 bytes
Database mounted.

SQL> select database_role from v$database;

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

SQL> select switchover_status from v$database;

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

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Media recovery complete.

六、验证:

登录到主库：

SYS.YUDONG> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
10715

1 row selected.

SYS.YUDONG>alter system switch logfile;

System altered.

SYS.YUDONG>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
10715

1 row selected.

登录到从库：

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

MAX(SEQUENCE#)
--------------
421

按照常理来讲，主从库的MAX SEQUENCE#应该是一样，由于我这里的测试环境的主库在以前曾经做过不完全恢复，所以会有以上的现象出现，但是不影响DATAGUARD的使用，
我们可以按照以下方法来验证。

登录到主库：

SYS.YUDONG>SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE NAME='yudong_b' AND FIRST_TIME>TO_DATE('200709','YYYYMM');

MAX(SEQUENCE#)
--------------
421

1 row selected.

继续验证，发现没有问题：

SYS.YUDONG>SELECT RESETLOGS_TIME,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE NAME='yudong_b' GROUP BY RESETLOGS_TIME;

RESETLOGS_TIME MAX(SEQUENCE#)
------------------- --------------
2006/01/18 06:57:19 10715
2007/08/31 11:45:50 421

2 rows selected.

SYS.YUDONG>COL DESTINATION FOR A60

SYS.YUDONG>SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ#
------------------------------------------------------------ --------- ---------------- -------------
/arch-01/databases/yudong/redolog VALID 1 421
yudong_b VALID 1 421

1 row selected.

COL DEST_NAME FOR A30
COL ERROR FOR A20

SYS.YUDONG>SELECT DEST_NAME,DESTINATION,ERROR FROM V$ARCHIVE_DEST WHERE SCHEDULE='ACTIVE';

DEST_NAME DESTINATION ERROR
------------------------------ ------------------------------------------------------------ --------------------
LOG_ARCHIVE_DEST_1 /arch-01/databases/yudong/redolog
LOG_ARCHIVE_DEST_2 yudong_b

SYS.YUDONG>SELECT * FROM V$ARCHIVE_GAP;

no rows selected

登录到从库：

SQL> SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

SQL> select process,status from v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
RFS RECEIVING
MRP0 WAIT_FOR_LOG
RFS WRITING
RFS RECEIVING

6 rows selected.

SQL> select sequence# , applied from v$archived_log;

SEQUENCE# APP
---------- ---
421 YES

至此，第一台从库创建完毕。

/***************************************/