L'autre jour chez un client, une des bd's standby était désynchronisée de plusieurs jours. Les archives n'étaient plus sur place, et comme la base de données n'était pas si volumineuse j'ai décide de faire un Rolling Forward sur la bd 12c.
Voici la procédure appliquée :
################
### Primaire ###
################
SYS@BDRMAN> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 +DATINXRWBDRMAN/PBDRMAN/datafile/system.26 9801556159502
2 +DATINXRWBDRMAN/PBDRMAN/datafile/sysaux.26 9801556159611
3 +DATINXRWBDRMAN/PBDRMAN/datafile/undotbs1. 9801556159800
4 +DATINXRWBDRMAN/PBDRMAN/datafile/users.269 9801556159830
5 +DATINXRWBDRMAN/PBDRMAN/datafile/refrmanpf 9801556159835
6 +DATINXRWBDRMAN/PBDRMAN/datafile/refrmanca 9801556159887
7 +DATINXRWBDRMAN/PBDRMAN/datafile/refrmanpf 9801556159904
7 rows selected.
################
### Standby ###
################
owner@serverb:/export/home/owner# tnsping pbdrman
TNS Ping Utility for Solaris: Version 12.1.0.2.0 - Production on 08-NOV-2017 16:25:23
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = servera.domaine)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pbdrman.domaine)))
OK (0 msec)
SYS@BDRMAN> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/system.25 9801132353171
2 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/sysaux.25 9801132353171
3 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/undotbs1. 9801132353171
4 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/users.267 9801132353171
5 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanpf 9801132353171
6 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanca 9801132353171
7 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanpf 9801132353171
7 rows selected.
SYS@BDRMAN> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
TO_CHAR(CURRENT_SCN)
----------------------------------------
9801132353170
SYS@BDRMAN> exit
Il faut passer par rman et se servir du nom du service utilisé pour la synchronisation:
owner@serverb:/export/home/owner# rman target/
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Nov 7 16:15:42 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: BDRMAN (not mounted)
RMAN> recover database from service pbdrman noredo using compressed backupset;
Starting recover at 07-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=607 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service pbdrman
destination for restore of datafile 00001: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/system.258.821180069
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service pbdrman
destination for restore of datafile 00002: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/sysaux.259.821180069
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service pbdrman
destination for restore of datafile 00003: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/undotbs1.260.821180069
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service pbdrman
destination for restore of datafile 00004: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/users.267.821180073
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service pbdrman
destination for restore of datafile 00005: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanpfd.257.821180069
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service pbdrman
destination for restore of datafile 00006: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmancad.273.866618929
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service pbdrman
destination for restore of datafile 00007: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanpfp_tempo.256.874499281
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 07-NOV-17
SYS@BDRMAN> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/system.25 9801556159502
2 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/sysaux.25 9801556159611
3 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/undotbs1. 9801556159800
4 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/users.267 9801556159830
5 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanpf 9801556159835
6 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanca 9801556159887
7 +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanpf 9801556159904
SYS@BDRMAN> SHUTDOWN IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@BDRMAN> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 3702536 bytes
Variable Size 1526726904 bytes
Database Buffers 603979776 bytes
Redo Buffers 13074432 bytes
Maintenant il faut faire un restore du controlfile :
owner@serverb:/export/home/owner# rman target/
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Nov 7 16:15:42 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: BDRMAN (not mounted)
RMAN> restore standby controlfile from service pbdrman ;
Starting restore at 07-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=873 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service PBDRMAN
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATINXRWBDRMAN/SBDRMAN/controlfile/current.274.868272013
output file name=+ARCHFLASBDRMAN/SBDRMAN/controlfile/current.367.868272013
Finished restore at 07-NOV-17
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
RMAN> report schema;
Starting implicit crosscheck backup at 07-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=873 device type=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 07-NOV-17
Starting implicit crosscheck copy at 07-NOV-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 07-NOV-17
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +ARCHFLASBDRMAN/SBDRMAN/ARCHIVELOG/2017_11_05/thread_1_seq_25532.859.959213437
File Name: +ARCHFLASBDRMAN/SBDRMAN/ARCHIVELOG/2017_11_05/thread_1_seq_25533.858.959214639
...
...
File Name: +ARCHFLASBDRMAN/SBDRMAN/ARCHIVELOG/2017_11_07/thread_1_seq_25747.753.959439639
File Name: +ARCHFLASBDRMAN/SBDRMAN/ARCHIVELOG/2017_11_07/thread_1_seq_25748.752.959439819
File Name: +ARCHFLASBDRMAN/SBDRMAN/ARCHIVELOG/2017_11_07/thread_1_seq_25749.1715.959440997
File Name: +ARCHFLASBDRMAN/SBDRMAN/ARCHIVELOG/2017_11_07/thread_1_seq_25750.1753.959442199
File Name: +ARCHFLASBDRMAN/SBDRMAN/ARCHIVELOG/2017_11_07/thread_1_seq_25751.1714.959443399
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name SBDRMAN
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +DATINXRWBDRMAN/SBDRMAN/datafile/system.266.820912527
2 0 SYSAUX *** +DATINXRWBDRMAN/SBDRMAN/datafile/sysaux.267.820912527
3 0 UNDOTBS1 *** +DATINXRWBDRMAN/SBDRMAN/datafile/undotbs1.268.820912529
4 0 USERS *** +DATINXRWBDRMAN/SBDRMAN/datafile/users.269.820912529
5 0 REFRMANPFD *** +DATINXRWBDRMAN/SBDRMAN/datafile/refrmanpfd.275.820913979
6 0 REFRMANCAD *** +DATINXRWBDRMAN/SBDRMAN/datafile/refrmancad.257.866618911
7 0 TBS_OTHERS *** +DATINXRWBDRMAN/SBDRMAN/datafile/datafile_a_tempo.265.874499275
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATINXRWBDRMAN/SBDRMAN/tempfile/temp.274.820912675
On doit cataloguer les datafiles :
RMAN> catalog start with '+DATINXRWBDRMAN/SBDRMAN' ;
searching for all files that match the pattern +DATINXRWBDRMAN/SBDRMAN
List of Files Unknown to the Database
=====================================
File Name: +DATINXRWBDRMAN/SBDRMAN/TEMPFILE/temp.268.822253261
File Name: +DATINXRWBDRMAN/SBDRMAN/TEMPFILE/temp.266.902243751
File Name: +DATINXRWBDRMAN/SBDRMAN/TEMPFILE/temp.291.917695929
File Name: +DATINXRWBDRMAN/SBDRMAN/TEMPFILE/temp.300.949315305
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_1.264.902243735
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_2.265.902243737
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_1.282.910188609
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_7.263.821180431
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_8.262.821180431
...
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_9.295.948357781
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_1.297.948359319
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_2.298.948359325
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_3.299.948359327
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanpfd.257.821180069
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/system.258.821180069
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/sysaux.259.821180069
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/undotbs1.260.821180069
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/users.267.821180073
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmancad.273.866618929
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/datafile_a_tempo.256.874499281
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +DATINXRWBDRMAN/SBDRMAN/TEMPFILE/temp.268.822253261
File Name: +DATINXRWBDRMAN/SBDRMAN/TEMPFILE/temp.266.902243751
File Name: +DATINXRWBDRMAN/SBDRMAN/TEMPFILE/temp.291.917695929
File Name: +DATINXRWBDRMAN/SBDRMAN/TEMPFILE/temp.300.949315305
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanpfd.257.821180069
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/system.258.821180069
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/sysaux.259.821180069
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/undotbs1.260.821180069
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/users.267.821180073
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmancad.273.866618929
File Name: +DATINXRWBDRMAN/SBDRMAN/DATAFILE/datafile_a_tempo.256.874499281
List of Files Which Were Not Cataloged
=======================================
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_1.264.902243735
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_2.265.902243737
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_1.282.910188609
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_7.263.821180431
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_8.262.821180431
RMAN-07529: Reason: catalog is not supported for this file type
...
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_8.289.948357781
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_9.295.948357781
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_1.297.948359319
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_2.298.948359325
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATINXRWBDRMAN/SBDRMAN/ONLINELOG/group_3.299.948359327
RMAN-07529: Reason: catalog is not supported for this file type
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATINXRWBDRMAN/SBDRMAN/DATAFILE/system.258.821180069"
datafile 2 switched to datafile copy "+DATINXRWBDRMAN/SBDRMAN/DATAFILE/sysaux.259.821180069"
datafile 3 switched to datafile copy "+DATINXRWBDRMAN/SBDRMAN/DATAFILE/undotbs1.260.821180069"
datafile 4 switched to datafile copy "+DATINXRWBDRMAN/SBDRMAN/DATAFILE/users.267.821180073"
datafile 5 switched to datafile copy "+DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmanpfd.257.821180069"
datafile 6 switched to datafile copy "+DATINXRWBDRMAN/SBDRMAN/DATAFILE/refrmancad.273.866618929"
datafile 7 switched to datafile copy "+DATINXRWBDRMAN/SBDRMAN/DATAFILE/datafile_a_tempo.256.874499281"
Comme ma base de données se trouve sur un autre serveur je n'ai pas de risque de faire ça sur mes trois redologs:
RMAN> ALTER DATABASE CLEAR LOGFILE GROUP 1;
Statement processed
RMAN> ALTER DATABASE CLEAR LOGFILE GROUP 2;
Statement processed
RMAN> ALTER DATABASE CLEAR LOGFILE GROUP 3;
Statement processed
RMAN> EXIT
Recovery Manager complete.
Par la suite, on va repartir la base de données :
owner@serverb:/export/home/owner# srvctl stop database -d BDRMAN
owner@serverb:/export/home/owner# srvctl start database -d BDRMAN
owner@serverb:/export/home/owner# sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 7 16:30:56 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SYS@BDRMAN>
SET PAGESIZE 100
SET LINESIZE 1000
SELECT DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE, FLASHBACK_ON FROM V$DATABASE ;
SELECT CLIENT_PROCESS, PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
------------------------------ -------------------- ---------------- ------------------
SBDRMAN MOUNTED PHYSICAL STANDBY NO
SYS@BDRMAN>
CLIENT_P PROCESS SEQUENCE# STATUS
-------- --------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 25755 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH RFS 0 IDLE
LGWR RFS 25756 IDLE
7 rows selected.
SYS@BDRMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
SYS@BDRMAN>
SET PAGESIZE 100
SET LINESIZE 1000
SELECT DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE, FLASHBACK_ON FROM V$DATABASE ;
SELECT CLIENT_PROCESS, PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
------------------------------ -------------------- ---------------- ------------------
SBDRMAN MOUNTED PHYSICAL STANDBY NO
SYS@BDRMAN>
CLIENT_P PROCESS SEQUENCE# STATUS
-------- --------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 25755 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH RFS 0 IDLE
LGWR RFS 25756 IDLE
N/A MRP0 25756 APPLYING_LOG
La base de données StandBye est une autre fois synchronisée :
SYS@BDRMAN> @check_synchro.sql
LOGS TIME SEQUENCE# INCARNATION#
---------------- -------------------- ---------- ------------
LAST APPLIED : 07-NOV-2017 16:44:52 25756 2
LAST RECEIVED : 07-NOV-2017 16:42:50 25757 2
