Shulou(Shulou.com)05/31 Report--

This article introduces how to recover after the master-slave interruption in ORACLE. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

I. Review of knowledge

1. Normally, when we configure ADG in production, we will use the maximum available mode with the parameters lgwr and sync.

SQL > show parameter log_archive_dest_2

NAME TYPE VALUE

-

Log_archive_dest_2 string SERVICE=stdtest lgwr sync affi

Rm VALID_FOR= (ONLINE_LOGFILES

PRIMARY_ROLE) DB_UNIQUE_NAME=s

Tdtest

This configuration maximizes the real-time performance of the main database without affecting the synchronization of the standby database.

SQL > select name,dbid,database_role,protection_mode from v$database

NAME DBID DATABASE_ROLE PROTECTION_MODE

--

PRITEST 2249383711 PRIMARY MAXIMUM AVAILABILITY

2. Monitor the process

Main library:

SQL > SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY

PROCESS CLIENT_P SEQUENCE# STATUS

--

ARCH ARCH 145 CLOSING

ARCH ARCH 135 CLOSING

ARCH ARCH 141 CLOSING

ARCH ARCH 146 CLOSING

LGWR LGWR 147 WRITING

Prepare the library:

SQL > SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY

PROCESS CLIENT_P SEQUENCE# STATUS

--

ARCH ARCH 145 CLOSING

ARCH ARCH 146 CLOSING

ARCH ARCH 0 CONNECTED

ARCH ARCH 144 CLOSING

RFS ARCH 0 IDLE

RFS UNKNOWN 0 IDLE

RFS LGWR 147 IDLE

RFS UNKNOWN 0 IDLE

MRP0 N/A 147 APPLYING_LOG

3. Monitor the progress of the recovery operation

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#

--

1 146 0 0

.

1 146 1 146

32 rows selected.

4. Review the three synchronization modes

4.1 maximum Protection Mode (Maximum Protection)

1) this model provides the highest level of data protection

2) at least one physical standby database is required to receive the redo log before the transaction of the main database can be committed

3) when the master database cannot find a suitable backup library for writing, the master library will shut down automatically to prevent unprotected data from appearing.

4) advantage: this mode can ensure that there is no data loss in the standby database.

5) disadvantages: the automatic shutdown of the main library will affect the availability of the main library, and it can only be submitted after the backup database is restored, which requires very high objective conditions such as the network.

As a result, the performance of the main library will be greatly affected.

4.2 maximum availability Mode (Maximum Availability)

1) this mode provides data protection capability second only to "maximum protection mode".

2) at least one physical standby database is required to receive the redo log before the transaction of the main database can be committed

3) when the master database cannot find a suitable standby database to write, the master database will not be closed. Before the net_timeout is reached, the master database will hang, but it will not be shutdown. And then the master database with

Maximum performance mode runs until the fault is eliminated, and interruptions of all redo log files are resolved. When all interrupts are resolved, the primary database automatically continues to run in maximum availability mode

4) advantages: this mode can ensure that there is no data loss in the standby database without problems, and it is a compromise method.

5) disadvantage: in the process of normal operation, the disadvantage is that the performance of the main library is affected by many factors.

4.3 maximum performance Mode (Maximum Performance)

1) this mode is the default mode and ensures the highest availability of the primary database.

2) ensure that the operation of the main database will not be affected by the standby database, the transaction of the main database will be committed normally, and the operation of the main database will not be affected by any problems in the standby database.

3) advantages: avoid the impact of standby database on the performance and availability of the primary database

4) disadvantage: if the recovery data related to the transaction committed by the main database is not sent to the standby database, the transaction data will be lost and no data loss can be guaranteed.

Second, how to resume synchronization after the synchronization of the main and standby libraries is interrupted

In many occasions, master-slave synchronization is interrupted, such as restoring synchronization may encounter many different situations, now according to several scenarios to do the following experiments.

Experiment one

After the master / slave is interrupted, when the archive log of the master database is complete, the slave database will be automatically restored when it is started.

1. Check the log of the master and slave database

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

MAX (SEQUENCE#)

-

sixty-three

2. Prepare the library:

Simulate the failure of standby database and shut down the computer directly.

SQL > shutdown abort

ORACLE instance shut down

3. Main library:

SQL > alter system switch logfile

System altered.

SQL > create table a (id integer)

Table created.

SQL > alter system switch logfile

System altered.

SQL > insert into a values (11)

1 row created.

SQL > commit

Commit complete.

SQL > alter system switch logfile

System altered.

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

MAX (SEQUENCE#)

-

sixty-eight

4. Prepare the library:

Simulate troubleshooting and resynchronize the backup database.

SQL > startup nomount

ORACLE instance started.

Total System Global Area 1185853440 bytes

Fixed Size 2252664 bytes

Variable Size 754974856 bytes

Database Buffers 419430400 bytes

Redo Buffers 9195520 bytes

SQL > alter database mount standby database

Database altered.

SQL > alter database recover managed standby database using current logfile disconnect from session

Database altered.

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

MAX (SEQUENCE#)

-

sixty-eight

Since the archived logs of the master database are all there, the arch process is responsible for transferring the archived log files to the slave database during the log file gap during the failure of the standby database.

At the same time, the archive log is transferred to the slave database through LNSn, which is accepted by RFS, and the MRP process is applied to standby redo log.

Check the log application.

SQL > select sequence#, applied from v$archived_log

SEQUENCE# APPLIED

--

7 YES

.

68 IN-MEMORY

Experiment two

Due to the loss of archives or damage to the control files of the backup database, full backup and recovery of the main library is required.

1. The database is simulated to be down, and the database is closed directly.

SQL > shutdown abort

ORACLE instance shut down.

2. During the downtime of the standby database, the main library does some operations.

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

MAX (SEQUENCE#)

-

seventy

SQL > create table b (name char (1))

Table created.

SQL > insert into b values ('a')

1 row created.

SQL > commit

Commit complete.

SQL > alter system switch logfile

System altered.

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

MAX (SEQUENCE#)

-

seventy-two

[oracle@pritest archivelog] $pwd

/ u01/app/oracle/archivelog

[oracle@pritest archivelog] $ls-trl

.

-rw-r- 1 oracle oinstall 444416 Nov 10 11:00 arch_1_69_956333727.arc

-rw-r- 1 oracle oinstall 1224704 Nov 10 11:00 arch_1_70_956333727.arc

-rw-r- 1 oracle oinstall 1097216 Nov 10 11:32 arch_1_71_956333727.arc

-rw-r- 1 oracle oinstall 4003840 Nov 10 13:53 arch_1_72_956333727.arc

3. The master database deletes the archive logs generated during the standby database downtime.

[oracle@pritest archivelog] $rm arch_1_7*

4. Start the standby library

Due to the loss of archive logs, the repository cannot be recovered directly.

SQL > startup nomount

ORACLE instance started.

Total System Global Area 1185853440 bytes

Fixed Size 2252664 bytes

Variable Size 754974856 bytes

Database Buffers 419430400 bytes

Redo Buffers 9195520 bytes

SQL > alter database mount standby database

Database altered.

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-10458: standby database requires recovery

ORA-01196: file 1 is inconsistent due to a failed media recovery session

ORA-01110: data file 1:'/ u01 *

5. Back up the control files of the main library and make complete preparations, and synchronize the main library.

SQL > alter database create standby controlfile as'/ tmp/controldg01.ctl'

Database altered.

[oracle@pritest pritest] $scp-r / tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/oradata/pritest/control01.ctl

Oracle@192.168.91.129's password:

Controldg01.ctl 100% 9808KB 9.6MB/s 00:00

[oracle@pritest pritest] $scp-r / tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/fast_recovery_area/pritest/control02.ctl

Oracle@192.168.91.129's password:

Controldg01.ctl 100% 9808KB 9.6MB/s 00:00

[oracle@pritest archivelog] $rman target /

RMAN > backup database format'/ u01ActionActionoracleActionBackup fullness% Destiny% Turing% slots% U'plus archivelog FORMAT'/ u01ActionApplicationoracleswap BackupThink% slots% p.bak'

Starting backup at 2017-11-10 14:18:11

Current log archived

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=38 device type=DISK

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of backup plus archivelog command at 11/10/2017 14:18:13

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

ORA-19625: error identifying file / u01/app/oracle/archivelog/arch_1_7_956333727.arc

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

RMAN > crosscheck archivelog all

.

RMAN > delete expired archivelog all

Released channel: ORA_DISK_1

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=38 device type=DISK

List of Archived Log Copies for database with db_unique_name PRITEST

=

Key Thrd Seq S Low Time

-

2 17 X 2017-10-02 16:29:17

Name: / u01/app/oracle/archivelog/arch_1_7_956333727.arc

123 1 70 X 2017-11-10 11:00:02

Name: / u01/app/oracle/archivelog/arch_1_70_956333727.arc

124 1 71 X 2017-11-10 11:00:05

Name: / u01/app/oracle/archivelog/arch_1_71_956333727.arc

125 1 72 X 2017-11-10 11:32:15

Name: / u01/app/oracle/archivelog/arch_1_72_956333727.arc

Do you really want to delete the above objects (enter YES or NO)? Yes

Deleted archived log

Archived log file name=/u01/app/oracle/archivelog/arch_1_7_956333727.arc RECID=2 STAMP=956335716

Deleted archived log

Archived log file name=/u01/app/oracle/archivelog/arch_1_70_956333727.arc RECID=123 STAMP=959684405

Deleted archived log

Archived log file name=/u01/app/oracle/archivelog/arch_1_71_956333727.arc RECID=124 STAMP=959686335

Deleted archived log

Archived log file name=/u01/app/oracle/archivelog/arch_1_72_956333727.arc RECID=125 STAMP=959694835

Deleted 4 EXPIRED objects

RMAN > backup database format'/ u01ActionActionoracleActionBackup fullness% Destiny% Turing% slots% U'plus archivelog FORMAT'/ u01ActionApplicationoracleswap BackupThink% slots% p.bak'

.

Starting backup at 2017-11-10 15:43:09

Current log archived

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting archived log backup set

Channel ORA_DISK_1: specifying archived log (s) in backup set

Input archived log thread=1 sequence=81 RECID=141 STAMP=959701390

Channel ORA_DISK_1: starting piece 1 at 2017-11-10 15:43:10

Channel ORA_DISK_1: finished piece 1 at 2017-11-10 15:43:11

Piece handle=/u01/app/oracle/backup/arc_PRITEST_20_1.bak tag=TAG20171110T154310 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2017-11-10 15:43:11

RMAN > exit

[oracle@pritest backup] $ll

Total 1152500

-rw-r- 1 oracle oinstall 72775680 Nov 10 15:42 arc_PRITEST_16_1.bak

-rw-r- 1 oracle oinstall 25392128 Nov 10 15:42 arc_PRITEST_17_1.bak

-rw-r- 1 oracle oinstall 22528 Nov 10 15:43 arc_PRITEST_20_1.bak

-rw-r- 1 oracle oinstall 1071833088 Nov 10 15:43 full_PRITEST_20171110_18_0isj7ob8_1_1

-rw-r- 1 oracle oinstall 10125312 Nov 10 15:43 full_PRITEST_20171110_19_0jsj7ocb_1_1

[oracle@pritest backup] $pwd

/ u01/app/oracle/backup

[oracle@pritest backup] $scp-r. / * oracle@192.168.91.129:/u01/app/oracle/backup/

Oracle@192.168.91.129's password:

Arc_PRITEST_11_1.bak 100% 69MB 69.4MB/s 00:01

Arc_PRITEST_12_1.bak 100% 23MB 23.0MB/s 00:00

Arc_PRITEST_15_1.bak 100% 40KB 39.5KB/s 00:00

Full_PRITEST_20171110_13_0dsj7m2i_1_1 100% 1023MB 39.3MB/s 00:26

Full_PRITEST_20171110_14_0esj7m4b_1_1 100% 9888KB 9.7MB/s 00:00

6. Restore the backup database with backup

SQL > shutdown abort

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

[oracle@stdtest backup] $rman target /

RMAN > catalog start with'/ u01Accord

Starting implicit crosscheck backup at 2017-11-10 15:47:17

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=32 device type=DISK

Finished implicit crosscheck backup at 2017-11-10 15:47:18

Starting implicit crosscheck copy at 2017-11-10 15:47:18

Using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at 2017-11-10 15:47:18

Searching for all files in the recovery area

Cataloging files...

No files cataloged

Searching for all files that match the pattern / u01/app/oracle/backup

List of Files Unknown to the Database

= =

File Name: / u01/app/oracle/backup/arc_PRITEST_11_1.bak

File Name: / u01/app/oracle/backup/arc_PRITEST_12_1.bak

File Name: / u01/app/oracle/backup/arc_PRITEST_17_1.bak

File Name: / u01/app/oracle/backup/arc_PRITEST_15_1.bak

File Name: / u01/app/oracle/backup/arc_PRITEST_16_1.bak

File Name: / u01/app/oracle/backup/full_PRITEST_20171110_18_0isj7ob8_1_1

File Name: / u01/app/oracle/backup/full_PRITEST_20171110_19_0jsj7ocb_1_1

File Name: / u01/app/oracle/backup/full_PRITEST_20171110_14_0esj7m4b_1_1

File Name: / u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1

File Name: / u01/app/oracle/backup/arc_PRITEST_20_1.bak

Do you really want to catalog the above files (enter YES or NO)? Yes

Cataloging files...

Cataloging done

List of Cataloged Files

=

File Name: / u01/app/oracle/backup/arc_PRITEST_11_1.bak

File Name: / u01/app/oracle/backup/arc_PRITEST_12_1.bak

File Name: / u01/app/oracle/backup/arc_PRITEST_17_1.bak

File Name: / u01/app/oracle/backup/arc_PRITEST_15_1.bak

File Name: / u01/app/oracle/backup/arc_PRITEST_16_1.bak

File Name: / u01/app/oracle/backup/full_PRITEST_20171110_18_0isj7ob8_1_1

File Name: / u01/app/oracle/backup/full_PRITEST_20171110_19_0jsj7ocb_1_1

File Name: / u01/app/oracle/backup/full_PRITEST_20171110_14_0esj7m4b_1_1

File Name: / u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1

File Name: / u01/app/oracle/backup/arc_PRITEST_20_1.bak

RMAN > restore database

Starting restore at 2017-11-10 15:18:38

Starting implicit crosscheck backup at 2017-11-10 15:18:38

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=20 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 2017-11-10 15:18:39

Starting implicit crosscheck copy at 2017-11-10 15:18:39

Using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at 2017-11-10 15:18:39

Searching for all files in the recovery area

Cataloging files...

No files cataloged

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00001 to / u01/app/oracle/oradata/pritest/system01.dbf

Channel ORA_DISK_1: restoring datafile 00002 to / u01/app/oracle/oradata/pritest/sysaux01.dbf

Channel ORA_DISK_1: restoring datafile 00003 to / u01/app/oracle/oradata/pritest/undotbs01.dbf

Channel ORA_DISK_1: restoring datafile 00004 to / u01/app/oracle/oradata/pritest/users01.dbf

Channel ORA_DISK_1: reading from backup piece / u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1

Channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1 tag=TAG20171110T150346

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 2017-11-10 15:19:14

RMAN > recover database

Starting recover at 2017-11-10 15:49:39

Using channel ORA_DISK_1

Starting media recovery

Archived log for thread 1 with sequence 80 is already on disk as file / u01/app/oracle/archivelog/arch_1_80_956333727.arc

Archived log for thread 1 with sequence 81 is already on disk as file / u01/app/oracle/archivelog/arch_1_81_956333727.arc

Archived log for thread 1 with sequence 82 is already on disk as file / u01/app/oracle/archivelog/arch_1_82_956333727.arc

Archived log for thread 1 with sequence 83 is already on disk as file / u01/app/oracle/archivelog/arch_1_83_956333727.arc

Archived log file name=/u01/app/oracle/archivelog/arch_1_80_956333727.arc thread=1 sequence=80

Archived log file name=/u01/app/oracle/archivelog/arch_1_81_956333727.arc thread=1 sequence=81

Archived log file name=/u01/app/oracle/archivelog/arch_1_82_956333727.arc thread=1 sequence=82

Archived log file name=/u01/app/oracle/archivelog/arch_1_83_956333727.arc thread=1 sequence=83

Unable to find archived log

Archived log thread=1 sequence=84

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of recover command at 11/10/2017 15:49:40

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 84 and starting SCN of 1089419

This error can be ignored, the host 84 archive log has not been archived.

SQL > alter database open read only

Database altered.

SQL > alter database recover managed standby database using current logfile disconnect from session

Database altered.

7. Verify the log synchronization between the master and slave libraries

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

MAX (SEQUENCE#)

-

eighty-three

Experiment 3

1. Scene introduction

When the synchronization between the master and slave is interrupted, the slave database wants to recover as soon as possible, but at this time, too many archives cannot be recovered, or the needed archives are lost directly. You can choose.

Re-build the repository. It is OK if the library is small, but it may take a long time if the main library is large, and it is prone to some problems. Just the whole library.

Backup recovery time will not be short, not to mention that there will be a lot of things involved. In fact, we use scn-based backup to restore our repository, thus bypassing the middle.

Excessive or lost archiving.

We all know that our traditional dg belongs to physical dg. Here is a simple explanation of physical dg:

Physical standby database: with the same disk database structure as the block-to-block primary database, the physical standby database is physically equivalent to the primary database.

Properties:

1. The logical location of each block in the database, including the block, is exactly the same as that of the main library.

2.DG maintains a physical standby database by performing redo applications

3. After the physical STANDBY opens the flashbackdatabase, it can be fully read-write open.

4. The physical standby database uses the oracle recovery mechanism to recover from archived redo log files or directly from standby redo log files on the slave system with redo data.

5. The physical standby database can be used to perform backups

6. The physical standby database uses redo application technology to apply changes using a low-level recovery mechanism, bypassing all SQL base code layers, so it is most effective to apply massive redo data

Performance is better than logical backup.

We find the lowest scn in the data file of the standby database, and then go to the main database to make a backup based on this scn. At this time, rman goes back to scan the block of the whole main library, if the scn in the block is less than

The lowest scn in the database data file proves that the block has not changed from the point in time it was applied to the database, so the block is ignored. If the scn in the block is greater than the data on the standby side

The lowest scn in the file proves that changes have been made at this stage and records the contents of the block. Replace the contents of this block when you get it to the slave end to recover.

The id of the official document mos (Doc ID 836986.1), you can check it by yourself.

Steps to perform for Rolling Forward aPhysical Standby Database using RMAN Incremental Backup.

2. Start the simulation experiment

2.1 directly close the standby database

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

MAX (SEQUENCE#)

-

one hundred and twenty three

SQL > shutdown abort

ORACLE instance shut down.

2.2 changes in the data of the main database during standby downtime

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

MAX (SEQUENCE#)

-

one hundred and twenty three

SQL > alter database enable block change tracking using file'/ u01 reuse

Database altered.

SQL > col filename for A55

SQL > select * from v$block_change_tracking

STATUS FILENAME BYTES

-

ENABLED / u01/app/oracle/oradata/pritest/rman_change_track.bct 11599872

SQL > update a set id=18

1 row updated.

SQL > commit

Commit complete.

SQL > alter system switch logfile

System altered.

SQL > create table ee (id integer)

Table created.

SQL > alter system switch logfile

System altered.

SQL > alter table ee move tablespace USERS

Table altered.

SQL > alter system switch logfile

System altered.

2.3 simulate the loss of archive logs in the main library

[oracle@pritest archivelog] $ls-trl

...

-rw-r- 1 oracle oinstall 989184 Nov 16 16:20 arch_1_124_956333727.arc

-rw-r- 1 oracle oinstall 24576 Nov 16 16:21 arch_1_125_956333727.arc

-rw-r- 1 oracle oinstall 31232 Nov 16 16:21 arch_1_126_956333727.arc

-rw-r- 1 oracle oinstall 17920 Nov 16 16:22 arch_1_127_956333727.arc

[oracle@pritest archivelog] $rm arch_1_125_956333727.arc arch_1_126_956333727.arc

2.4 find the lowest scn in the database data file

[oracle@stdtest ~] $sqlplus / as sysdba

SQL > startup mount

SQL > select CHECKPOINT_CHANGE# from v$datafile_header order by 1

CHECKPOINT_CHANGE#

-

1160832

1160832

1160832

1160832

SQL > select CHECKPOINT_CHANGE# from v$database order by 1

CHECKPOINT_CHANGE#

-

1159823

SQL > shutdown abort

ORACLE instance shut down.

2.5 back up the master library control file and update the backup library control file

SQL > alter database create standby controlfile as'/ tmp/controldg01.ctl'

Database altered.

[oracle@pritest] $scp-r / tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/oradata/pritest/control01.ctl

Oracle@192.168.91.129's password:

Controldg01.ctl 100% 9872KB 9.6MB/s 00:00

[oracle@pritest] $scp-r / tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/fast_recovery_area/pritest/control02.ctl

Oracle@192.168.91.129's password:

Controldg01.ctl 100% 9872KB 9.6MB/s 00:00

2.6 perform SCN-based incremental backups

[oracle@pritest backup] $rman target /

RMAN > run {

Sql 'alter system switch logfile'

Backup incremental from scn 1159823 database format'/ u01 database format'/ u01ActionAccording to an oracle FORSTANDBY' increment% daring% Turing% slots% U' tag 'FORSTANDBY'

} 2 > 3 > 4 >

Using target database control file instead of recovery catalog

Sql statement: alter system switch logfile

Starting backup at 2017-11-14 11:07:02

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=53 device type=DISK

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Input datafile file number=00001 name=/u01/app/oracle/oradata/pritest/system01.dbf

Input datafile file number=00002 name=/u01/app/oracle/oradata/pritest/sysaux01.dbf

Input datafile file number=00003 name=/u01/app/oracle/oradata/pritest/undotbs01.dbf

Input datafile file number=00004 name=/u01/app/oracle/oradata/pritest/users01.dbf

Channel ORA_DISK_1: starting piece 1 at 2017-11-14 11:07:02

Channel ORA_DISK_1: finished piece 1 at 2017-11-14 11:07:03

Piece handle=/u01/app/oracle/backup/incre_PRITEST_20171114_32_10sjhpmm_1_1 tag=FORSTANDBY comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Including current control file in backup set

Channel ORA_DISK_1: starting piece 1 at 2017-11-14 11:07:04

Channel ORA_DISK_1: finished piece 1 at 2017-11-14 11:07:05

Piece handle=/u01/app/oracle/backup/incre_PRITEST_20171114_33_11sjhpmn_1_1 tag=FORSTANDBY comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2017-11-14 11:07:05

RMAN > list backupset; (I'm a little confused?)

Using target database control file instead of recovery catalog

Specification does not match any backup in the repository

[oracle@pritest backup] $ls-trl

Total 13808

-rw-r- 1 oracle oinstall 3981312 Nov 16 16:28 incre_PRITEST_20171116_39_17sjnl9v_1_1

-rw-r- 1 oracle oinstall 10158080 Nov 16 16:28 incre_PRITEST_20171116_40_18sjnla3_1_1

[oracle@pritest backup] $scp-r. / incre_PRITEST_20171116* oracle@192.168.91.129:/u01/app/oracle/backup

Oracle@192.168.91.129's password:

Incre_PRITEST_20171114_32_10sjhpmm_1_1 100% 16MB 15.6MB/s 00:00

Incre_PRITEST_20171114_33_11sjhpmn_1_1 100% 9920KB 9.7MB/s 00:00

2.7 prepare library recovery control files

[oracle@stdtest backup] $rman target/

RMAN > startup mount

Oracle instance started

Database mounted

Total System Global Area 1185853440 bytes

Fixed Size 2252664 bytes

Variable Size 754974856 bytes

Database Buffers 419430400 bytes

Redo Buffers 9195520 bytes

RMAN > catalog start with'/ u01Accord

Starting implicit crosscheck backup at 2017-11-16 16:31:25

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=30 device type=DISK

Finished implicit crosscheck backup at 2017-11-16 16:31:26

Starting implicit crosscheck copy at 2017-11-16 16:31:26

Using channel ORA_DISK_1

Crosschecked 4 objects

Finished implicit crosscheck copy at 2017-11-16 16:31:26

Searching for all files in the recovery area

Cataloging files...

Cataloging done

List of Cataloged Files

=

File Name: / u01/app/oracle/fast_recovery_area/STDTEST/autobackup/2017_11_13/o1_mf_s_959944964_f0l4lkk8_.bkp

File Name: / u01/app/oracle/fast_recovery_area/STDTEST/autobackup/2017_11_13/o1_mf_s_959707108_f0kyqno7_.bkp

Searching for all files that match the pattern / u01/app/oracle/backup

List of Files Unknown to the Database

= =

File Name: / u01/app/oracle/backup/incre_PRITEST_20171116_40_18sjnla3_1_1

File Name: / u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1

Do you really want to catalog the above files (enter YES or NO)? Yes

Cataloging files...

Cataloging done

List of Cataloged Files

=

File Name: / u01/app/oracle/backup/incre_PRITEST_20171116_40_18sjnla3_1_1

File Name: / u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1

2.8 restore the database

Restore is a restore, file-level recovery. Is the physical file restore.

Recover is recovery, data-level recovery. Logical recovery, such as applying archived logs and redoing logs, are all synchronized and consistent.

Here the data file is based on the original, so there is no need for restore database.

RMAN > recover database

Starting recover at 2017-11-16 16:32:15

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting incremental datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Destination for restore of datafile 00001: / u01/app/oracle/oradata/pritest/system01.dbf

Destination for restore of datafile 00002: / u01/app/oracle/oradata/pritest/sysaux01.dbf

Destination for restore of datafile 00003: / u01/app/oracle/oradata/pritest/undotbs01.dbf

Destination for restore of datafile 00004: / u01/app/oracle/oradata/pritest/users01.dbf

Channel ORA_DISK_1: reading from backup piece / u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1

Channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1 tag=FORSTANDBY

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Starting media recovery

Archived log for thread 1 with sequence 131 is already on disk as file / u01/app/oracle/archivelog/arch_1_131_956333727.arc

Archived log for thread 1 with sequence 132 is already on disk as file / u01/app/oracle/archivelog/arch_1_132_956333727.arc

Archived log for thread 1 with sequence 133 is already on disk as file / u01/app/oracle/archivelog/arch_1_133_956333727.arc

Archived log file name=/u01/app/oracle/archivelog/arch_1_131_956333727.arc thread=1 sequence=131

Archived log file name=/u01/app/oracle/archivelog/arch_1_132_956333727.arc thread=1 sequence=132

Archived log file name=/u01/app/oracle/archivelog/arch_1_133_956333727.arc thread=1 sequence=133

Unable to find archived log

Archived log thread=1 sequence=134

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of recover command at 11/16/2017 16:32:16

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 134 and starting SCN of 1162339

This error can be ignored, and the host 134 archive log has not been archived yet.

2.9 start the database application MRP and verify the synchronization between the master and standby

[oracle@stdtest ~] $sqlplus / as sysdba

SQL > alter database open read only

Database altered.

SQL > alter database recover managed standby database using current logfile disconnect from session

Database altered.

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

MAX (SEQUENCE#)

-

one hundred and thirty three

SQL > select * from ee

No rows selected

3. Knowledge supplement

When we perform an incremental backup based on scn, he needs to scan the whole database and make the difference between two scn points to determine whether this block needs to be backed up.

So if the main database is very large, then the incremental backup will not be very fast. Of course, it will be much faster than the full backup, he only needs to scan all the blocks, not necessarily a lot of records.

Experiment 4

1. Scene

Since some tests must be carried out in a production environment, you can test them with a library and restore them after testing.

The main process is to enable database flashback and switch the standby database from PHYSICAL STANDBY mode to SNAPSHOT STANDBY mode, which can be read and written until the test is finished.

PHYSICAL STANDBY mode, using flashback to the pre-test state, and then applying the archive log to restore the original state.

2. Check whether the slave database is enabled for flashback

SQL > select flashback_on from v$database

FLASHBACK_ON

-

NO

SQL > alter database recover managed standby database cancel

Database altered.

SQL > alter database flashback on

Database altered.

SQL > select flashback_on from v$database

FLASHBACK_ON

-

YES

SQL > show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE

-

Db_recovery_file_dest string / u01/app/oracle/fast_recovery_area

Db_recovery_file_dest_size big integer 4182M

View flashback data files

SQL >! ls-trl / u01/app/oracle/fast_recovery_area/STDTEST/flashback

Total 102416

-rw-r- 1 oracle oinstall 52436992 Nov 17 09:51 o1_mf_f0wj4n6t_.flb

-rw-r- 1 oracle oinstall 52436992 Nov 17 10:42 o1_mf_f0wj4jkw_.flb

3. View the current status of the slave database.

SQL > select database_role,db_unique_name,open_mode from v$database

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE

PHYSICAL STANDBY stdtest READ ONLY WITH APPLY

4. Switch to snapshot standby

SQL > alter database convert to snapshot standby

Database altered.

5. View the log

[oracle@stdtest trace] $tail-f / u01/app/oracle/diag/rdbms/stdtest/pritest/trace/alert_pritest.log

Fri Nov 17 10:19:35 2017

Alter database convert to snapshot standby

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_11/17/2017 10:19:35

Killing 4 processes with pids 2902 2896 in order to disallow current and future RFS connections. Requested by OS process 2850

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

CLOSE: all sessions shutdown successfully.

Fri Nov 17 10:19:38 2017

SMON: disabling cache recovery

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

RESETLOGS after incomplete recovery UNTIL CHANGE 1170706

Resetting resetlogs activation ID 2249370806 (0x8612acb6)

Online log / u01/app/oracle/oradata/pritest/redo01.log: Thread 1 Group 1 was previously cleared

Online log / u01/app/oracle/oradata/pritest/redo02.log: Thread 1 Group 2 was previously cleared

Online log / u01/app/oracle/oradata/pritest/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 1170704

Fri Nov 17 10:19:38 2017

Setting recovery target incarnation to 3

AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.

CONVERT TO SNAPSHOT STANDBY: Complete-Database mounted as snapshot standby

Completed: alter database convert to snapshot standby

6. Open the preparation library

SQL > select database_role,open_mode from v$database

DATABASE_ROLE OPEN_MODE

--

SNAPSHOT STANDBY MOUNTED

SQL > alter database open

Database altered.

7. Do some operations on the preparation database

SQL > select * from ee

No rows selected

SQL > drop table ee purge

Table dropped.

SQL > create user test identified by test123

User created.

SQL > grant dba to test

Grant succeeded.

SQL > conn test/test123

Connected.

SQL > create table abc as select * from dba_users

Table created.

8. In SNAPSHOT STANDBY mode, the log is transferred normally but not applied. Confirm with the following statement

SQL > col CTIME for A18

SQL > col NAME for A15

SQL > col VALUE for A13

SQL > col DATUM_TIME for A20

SQL > select to_char (SYSDATE,'yyyymmdd hh34:mi:ss') CTIME,NAME,VALUE,DATUM_TIME

SQL > from V$DATAGUARD_STATS WHERE NAME LIKE'% lag'

CTIME NAME VALUE DATUM_TIME

--

20171117 11:03:21 transport lag + 00:00:00 11 Greater 17 Greater 11:03:20

20171117 11:03:21 apply lag + 00:44:20 11 Greater 17 Greater 11:03:20

Looking at the log, we switched it at 10:19, which is about 44 minutes.

9. Switch back to PHYSICAL STADNBY

SQL > alter database convert to physical standby

Alter database convert to physical standby

*

ERROR at line 1:

ORA-01126: database must be mounted in this instance and not open in any instance

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

.

Database mounted.

SQL > alter database convert to physical standby

Database altered.

10. After the switch is completed, it is found that the recovery of the flashback log is complete, and the flashback log is deleted automatically, as shown below:

Fri Nov 17 11:12:58 2017

Alter database convert to physical standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (pritest)

Killing 3 processes with pids 3491 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 3487

Flashback Restore Start

Flashback Restore Complete

Drop guaranteed restore point

Guaranteed restore point dropped

Clearing standby activation ID 2253336566 (0x864f2ff6)

The primary database controlfile was created using the

'MAXLOGFILES 16 'clause.

There is space for up to 13 standby redo logfiles

Use the following SQL commands on the standby database to create

Standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800

Shutting down archive processes

Archiving is disabled

Fri Nov 17 11:12:59 2017

ARCH shutting down

ARC3: Archival stopped

Fri Nov 17 11:12:59 2017

ARCH shutting down

ARC2: Archival stopped

Fri Nov 17 11:12:59 2017

ARCH shutting down

ARC1: Archival stopped

Fri Nov 17 11:12:59 2017

ARCH shutting down

ARC0: Archival stopped

Completed: alter database convert to physical standby

11. Open the synchronization master library

SQL > startup mount force

ORACLE instance started.

Total System Global Area 1185853440 bytes

Fixed Size 2252664 bytes

Variable Size 754974856 bytes

Database Buffers 419430400 bytes

Redo Buffers 9195520 bytes

Database mounted.

SQL > select database_role,db_unique_name,open_mode from v$database

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE

PHYSICAL STANDBY stdtest MOUNTED

SQL > alter database open read only

Database altered.

SQL > alter database recover managed standby database using current logfile disconnect from session

Database altered.

12. Verify that it is restored to before the initial switch

SQL > select database_role,db_unique_name,open_mode from v$database

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE

PHYSICAL STANDBY stdtest READ ONLY WITH APPLY

SQL > select * from ee

No rows selected

SQL > conn test/test123

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

On how to recover from the interruption in ORACLE to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.