Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "daily maintenance mode of DataGuard". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Part I Daily maintenance

Open the main library and standby library correctly

1 main library:

SQL > STARTUP MOUNT

SQL > ALTER DATABASE ARCHIVELOG

SQL > ALTER DATABASE OPEN

2 prepare the library:

SQL > STARTUP MOUNT

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Second, correct closing order

1 prepare the library:

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

SQL > SHUTDOWN IMMEDIATE

2 main library

SQL > SHUTDOWN IMMEDIATE

Open the third standby library in Read-Only mode

The current main library is in normal OPEN status.

The repository is in a state of log delivery.

1 stop log delivery in the standby database

SQL > recover managed standby database cancel

2. Open Read-only mode of standby library.

SQL > alter database open read only

3 prepare the library to return to log delivery mode

SQL > recover managed standby database disconnect from session

Media recovery complete.

SQL > select status from v$instance

STATUS

-

MOUNTED

4. Log delivery status monitoring

1 the main database to view the current log status

SQL > select sequence#,status from v$log

SEQUENCE# STATUS

--

51 ACTIVE

52 CURRENT

50 INACTIVE

2 standby database to view RFS (Remote File Service) receiving logs and MRP application log synchronization master database

SQL > SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS

2 FROM V$MANAGED_STANDBY

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--

ARCH CONNECTED 0 0 0

ARCH CONNECTED 0 0 0

RFS RECEIVING 0 0 0

MRP0 WAIT_FOR_LOG 1 52 0 0

RFS RECEIVING 0 0 0

You can see that the slave MPR0 is waiting for redo with a SEQUENCE# of 52.

3 check whether the standby database is synchronized with the main database

SQL > SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#

2 FROM V$ARCHIVE_DEST_STATUS

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#

--

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

1 51 1 50

You can see that the repository has archived SEQUENCE#51 logs and applied SEQUENCE#50 redo to the repository.

Since SEQUENCE#51 logs have been archived, previous SEQUENCE#51 data will not be lost.

4 check the redo that has been archived in the standby database

SQL > SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#

2 NEXT_CHANGE# FROM V$ARCHIVED_LOG

REGISTR CREATOR THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

--

SRMN SRMN 1 37 572907 573346

RFS ARCH 1 38 573346 573538

RFS ARCH 1 39 573538 573623

RFS ARCH 1 40 573623 573627

RFS ARCH 1 41 573627 574326

RFS ARCH 1 42 574326 574480

RFS ARCH 1 43 574480 590971

RFS ARCH 1 44 590971 593948

RFS FGRD 1 45 593948 595131

RFS FGRD 1 46 595131 595471

FGRD FGRD 1 46 595131 595471

REGISTR CREATOR THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

--

RFS ARCH 1 47 595471 595731

RFS ARCH 1 48 595731 601476

RFS ARCH 1 49 601476 601532

RFS ARCH 1 50 601532 606932

RFS ARCH 1 51 606932 607256

5 check the redo that has been applied in the standby library

SQL > SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#

2 FROM V$LOG_HISTORY

THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

--

1 1 366852 368222

1 2 368222 369590

1 3 369590 371071

1 4 371071 372388

1 5 372388 376781

1 6 376781 397744

1 7 397744 407738

1 8 407738 413035

1 9 413035 413037

1 10 413037 413039

1 11 413039 413098

THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

--

1 12 413098 428161

1 13 428161 444373

1 14 444373 457815

1 15 457815 463016

1 16 463016 476931

1 17 476931 492919

1 18 492919 505086

1 19 505086 520683

1 20 520683 530241

1 21 530241 545619

1 22 545619 549203

THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

--

1 23 549203 552403

1 24 552403 553230

1 25 553230 553398

1 26 553398 553695

1 27 553695 554327

1 28 554327 557569

1 29 557569 561279

1 30 561279 561385

1 31 561385 566069

1 32 566069 566825

1 33 566825 570683

THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

--

1 34 570683 571627

1 35 571627 571867

1 36 571867 572907

1 37 572907 573346

1 38 573346 573538

1 39 573538 573623

1 40 573623 573627

1 41 573627 574326

1 42 574326 574480

1 43 574480 590971

1 44 590971 593948

THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#

--

1 45 593948 595131

1 46 595131 595471

1 47 595471 595731

1 48 595731 601476

1 49 601476 601532

1 50 601532 606932

1 51 606932 607256

You can see that the repository has applied the archive file with SEQUENCE# 51 to the repository.

Check the standby database to receive, apply redo data process.

SQL > SELECT MESSAGE FROM V$DATAGUARD_STATUS

MESSAGE

ARC0: Archival started

ARC0: Becoming the'no FAL' ARCH

ARC0: Becoming the'no SRL' ARCH

ARC1: Archival started

ARC1: Becoming the heartbeat ARCH

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS [1]: Assigned to RFS process 19740

RFS [1]: Identified database type as' physical standby'

Primary database is in MAXIMUM PERFORMANCE mode

Attempt to start background Managed Standby Recovery process

MESSAGE

MRP0: Background Managed Standby Recovery process started

Managed Standby Recovery not using Real Time Apply

Clearing online redo logfile 7 / oraguard/redo1/redo_7_1.log

Clearing online redo logfile 7 complete

Media Recovery Waiting for thread 1 sequence 47

RFS [1]: No standby redo logfiles created

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS [2]: Assigned to RFS process 19746

RFS [2]: Identified database type as' physical standby'

Primary database is in MAXIMUM PERFORMANCE mode

MESSAGE

Committing creation of archivelog'/ arch/1_47_552308270.arc'

Media Recovery Log / arch/1_47_552308270.arc

Media Recovery Waiting for thread 1 sequence 48

MRP0: Background Media Recovery cancelled with status 16037

MRP0: Background Media Recovery process shutdown

Managed Standby Recovery Canceled

Attempt to start background Managed Standby Recovery process

MRP0: Background Managed Standby Recovery process started

Managed Standby Recovery not using Real Time Apply

Media Recovery Waiting for thread 1 sequence 48

RFS [1]: No standby redo logfiles created

MESSAGE

Committing creation of archivelog'/ arch/1_48_552308270.arc'

Media Recovery Log / arch/1_48_552308270.arc

Media Recovery Waiting for thread 1 sequence 49

RFS [1]: No standby redo logfiles created

Committing creation of archivelog'/ arch/1_49_552308270.arc'

Media Recovery Log / arch/1_49_552308270.arc

Media Recovery Waiting for thread 1 sequence 50

RFS [1]: No standby redo logfiles created

Committing creation of archivelog'/ arch/1_50_552308270.arc'

Media Recovery Log / arch/1_50_552308270.arc

Media Recovery Waiting for thread 1 sequence 51

MESSAGE

RFS [1]: No standby redo logfiles created

Committing creation of archivelog'/ arch/1_51_552308270.arc'

Media Recovery Log / arch/1_51_552308270.arc

Media Recovery Waiting for thread 1 sequence 52

You can see that RFS received the archive file with sequence# 51 and saved it to the repository archive directory / arch/1_51_552308270.arc.

Oracle automatically applies the file / arch/1_51_552308270.arc to synchronize the backup library with the main library

Oracle continues to wait for archived files from the main library sequence 52

Fifth, the maintenance of the archived catalogue of the library.

1 find the archive directory of the preparation library

SQL > show parameter log_archive_dest_1

NAME TYPE

VALUE

-

Log_archive_dest_1 string

LOCATION=/arch

VALID_FOR= (ALL_LOGFILES,ALL_RO

LES)

DB_UNIQUE_NAME=ora2

Log_archive_dest_10 string

2 maintenance strategy

Delete archive files that have been applied every week

See Appendix II for details.

Part II: the main library is switched normally.

Manual intervention for the normal switching of the main database

1 verify the switchable state of the database on the main database side

SQL > SELECT SWITCHOVER_STATUS FROM V$DATABASE

SWITCHOVER_STATUS

-

TO STANDBY

1 row selected

SWITCHOVER_STATUS:TO STANDBY indicates that it can be switched normally.

If the value of SWITCHOVER_STATUS is SESSIONS ACTIVE, there is currently a session in the ACTIVE state

2 start the normal switching of the main library

If the value of SWITCHOVER_STATUS is TO STANDBY:

SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

If the value of SWITCHOVER_STATUS is SESSIONS ACTIVE:

SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

After successfully running this command, the main library is modified to be a standby library

3 restart the previous main library

SQL > SHUTDOWN IMMEDIATE

SQL > STARTUP MOUNT

4 verify switchable status in standby database

SQL > SELECT SWITCHOVER_STATUS FROM V$DATABASE

SWITCHOVER_STATUS

-

TO_PRIMARY

1 row selected

5 convert the target standby database to the primary database

If the value of SWITCHOVER_STATUS is TO STANDBY:

SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY

If the value of SWITCHOVER_STATUS is SESSIONS ACTIVE:

SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

After successfully running this command, the standby library is modified to be the primary library

6 restart the target repository

SQL > SHUTDOWN IMMEDIATE

SQL > STARTUP

7 previously, the main library started the log delivery process

SQL > alter database recover managed standby database disconnect

Summary: in this way, a normal switch of the main library is completed. After switching, the original master library becomes the standby library, and the original standby library becomes the main library.

Second, realize the normal switching of the main library by running the script.

1 switch the main library to the standby library

Run the script on the main library

/ admin/dataGuard/switchover/primary_to_standby.sh

2 switch the standby library to the main library

Run the script on the repository

/ admin/dataGuard/switchover/standby_to_primary.sh

After script 1 runs successfully, run script 2, not both scripts at the same time.

After this switch, the original master database becomes the standby database, the original standby database becomes the master data, and OPEN provides services to the application.

3 restore the original state

Run the script on the original library

/ admin/dataGuard/switchover/primary_to_standby.sh

After successful completion

Run the script on the original main library

/ admin/dataGuard/switchover/standby_to_primary.sh

The third part: disaster switching of main library.

Manual intervention in disaster switching of the main database

Second, realize the disaster switch of the main database by running the script.

SQL > alter database recover managed standby database cancel

SQL > shutdown immediate

SQL > startup mount

SQL > ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

SQL > alter database recover managed standby database finish

-- switch

SQL > alter database commit to switchover to primary with session shutdown

-- open

SQL > shutdown immediate

SQL > startup

Attached:

As soon as you choose to view the transmission and application of redo

Select message from v$dataguard_status

Where message_num > & message_num

Second, the maintenance script of the archive directory of the library

Customize the daily execution of removeCommand.sh in crontab.

Process: daily 11:50PM executes removeCommand.sh

Suppose 2005-04-05 today delete 04-04 and 04-03 applied archive logs. Keep today's applied archive logs

[oracle@db_gurid admin] $crontab-l

50 23 * sh / oraguard/admin/removeCommand.sh > > removeArch.log

#

[oracle@db_gurid admin] $cat removeCommand.sh

#! / bin/sh

Export ORACLE_BASE=/ora10g/app

Export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1

Export ORACLE_SID=ora2

Cd / oraguard/admin

$ORACLE_HOME/bin/sqlplus / nologremoveArch3.log

#

[oracle@db_gurid admin] $cat removeArch.sql

Set feed off

Set heading off

Set echo off

Spool removeArch.sh

Select'rm'| | name from v$archived_log where applied='YES' and completion_time > trunc (sysdate-3) and completion_time

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.