L'Oracle de Victor: 07/16/15

Cette publication montre détail de l'upgrade manuel (sans dbua), mais afin d'alléger le texte, je vais laisser quelques indications de façon générale.

##############################################################
### ###
### UPGRADE DE LA BASE DE DONNÉES EN DATAGUARD ###
### ###

###********************************************************###

##############################################################

### Base de données Primaire : test_p ###

### Base de données Standby : test_s ###

### Home source : 11.2.0.1 ###

### Home destination : 11.2.0.4 ###

##############################################################

### Configuration du Broker : ###
### ###
### DGMGRL> show configuration ; ###

### Configuration - evta ###

### Protection Mode: MaxPerformance ###
### Databases: ###

###       test_p - Primary database                        ###

###       test_s - Physical standby database               ###

###                                                        ###

###  Fast-Start Failover: DISABLED                         ###

###                                                        ###

###  Configuration Status:                                 ###

###  SUCCESS                                               ###

###                                                        ###

##############################################################

Assurez-vous d'avoir créé les RESTORE POINT comme indiqué dans la partie I

*****************************************************************

**** Pre - Upgrade

*****************************************************************

Sous le HOME 11.2.0.4 :

    Chercher le fichier @?/rdbms/admin/utlu112i.sql et le copier vers le HOME 11.2.0.1

Sous le HOME 11.2.0.1 :

    Exécuter le fichier @?/rdbms/admin/utlu112i.sql

    Ceci, doit montrer un rapport avec les problèmes rencontrés et les choses à corriger avant de lancer l'upgrade.

*****************************************************************

**** Upgrade

*****************************************************************

*************************************

*************************************

**** Home Source - 11.2.0.1

**** Arrêter les bases de données

*************************************

*************************************

Primaire :

=======

echo $ORACLE_SID=TEST
sqlplus / as ssydba
SQL> SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE ;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TEST READ WRITE PRIMARY

Standby :

=======

Primaire :

=======

dgmgrl
DGMGRL> connect sys@test_p
Connected.

DGMGRL> show configuration ;
Configuration - evta
Protection Mode: MaxPerformance
Databases:
test_p - Primary database
test_s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database "test_p" ;
Database - test_p
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
TEST

Database Status:
SUCCESS

DGMGRL> show database "test_s" ;
Database - test_s
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
TEST

Database Status:
SUCCESS

DGMGRL> EDIT DATABASE "test_p" SET STATE='TRANSPORT-OFF';
Succeeded.
DGMGRL> EDIT DATABASE "test_s" SET STATE='APPLY-OFF';
Succeeded.

DGMGRL> DISABLE CONFIGURATION ;
Disabled.

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
System altered.

Standby :

=======

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
System altered.

Confirmer que le RESTORE POINT ait été créé
SET LINESIZE 1000
COL NAME FORMAT A20
COL SCN FORMAT A15
COL TIME FORMAT A20
SELECT NAME, TO_CHAR(SCN) SCN, TO_CHAR(TIME,'DD/MM/YYYY HH24:MI') TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE/1024/1024 MO
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES' ;

Arrêter le listener

Arrêter la base de données

Primaire :

=======

Confirmer que le RESTORE POINT ait été créé
SET LINESIZE 1000
COL NAME FORMAT A20
COL SCN FORMAT A15
COL TIME FORMAT A20
SELECT NAME, TO_CHAR(SCN) SCN, TO_CHAR(TIME,'DD/MM/YYYY HH24:MI') TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE/1024/1024 MO
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES' ;

Arrêter le listener

Arrêter la base de données

*************************************

*************************************

**** Home Source - 11.2.0.1

**** Copie de fichiers vers 11.2.0.4

*************************************

*************************************

Préparer ou modifier les fichiers *.ora
Copier le password file et tout autre fichier nécessaire pour permettre à notre bd de repartir avec le nouveau HOME.
S'assurer de modifier les variables d'environnement -au besoin- 

*************************************

*************************************

**** Home Destination - 11.2.0.4

**** Repartir les bases de données

*************************************

*************************************

Standby :

=======

sqlplus / as sysdba

SQL> startup mount ;
ORACLE instance started.

Total System Global Area 605450240 bytes
Fixed Size 2255632 bytes
Variable Size 398460144 bytes
Database Buffers 197132288 bytes
Redo Buffers 7602176 bytes
Database mounted.

Primaire :

=======

sqlplus / as sysdba

SQL> startup upgrade ;
ORACLE instance started.

Total System Global Area 605450240 bytes
Fixed Size 2255632 bytes
Variable Size 398460144 bytes
Database Buffers 197132288 bytes
Redo Buffers 7602176 bytes
Database mounted.
Database opened.

Vérifier la version des composantes

SET LINESIZE 200
SET PAGESIZE 40
COL NAME FORMAT A40
COL VERSION FORMAT A20
COL COMPNAME FORMAT A30
SELECT SUBSTR(COMP_ID, 1,10) COMPID, SUBSTR(COMP_NAME,1,24) COMPNAME, STATUS,VERSION FROM DBA_REGISTRY;

COMPID COMPNAME STATUS VERSION
--------------------- ------------------------------ ----------- --------------------
OWB OWB VALID 11.2.0.1.0
APEX Oracle Application Expre VALID 3.2.1.00.10
EM Oracle Enterprise Manage VALID 11.2.0.1.0
AMD OLAP Catalog VALID 11.2.0.1.0
SDO Spatial VALID 11.2.0.1.0
ORDIM Oracle Multimedia VALID 11.2.0.1.0
XDB Oracle XML Database VALID 11.2.0.1.0
CONTEXT Oracle Text VALID 11.2.0.1.0
EXF Oracle Expression Filter VALID 11.2.0.1.0
RUL Oracle Rules Manager VALID 11.2.0.1.0
OWM Oracle Workspace Manager VALID 11.2.0.1.0
CATALOG Oracle Database Catalog VALID 11.2.0.1.0
CATPROC Oracle Database Packages VALID 11.2.0.1.0
JAVAVM JServer JAVA Virtual Mac VALID 11.2.0.1.0
XML Oracle XDK VALID 11.2.0.1.0
CATJAVA Oracle Database Java Pac VALID 11.2.0.1.0
APS OLAP Analytic Workspace VALID 11.2.0.1.0
XOQ Oracle OLAP API VALID 11.2.0.1.0

SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_STATE_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string RESET

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH ;
System altered.

SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_STATE_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE

SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DISK2/oracle/archives/dest1
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16

Standby :

=======

Confirmer qu'un RESTORE POINT ait été créé.

SET LINESIZE 1000
COL NAME FORMAT A20
COL SCN FORMAT A15
COL TIME FORMAT A20
SELECT NAME, TO_CHAR(SCN) SCN, TO_CHAR(TIME,'DD/MM/YYYY HH24:MI') TIME,
DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE/1024/1024 MO
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES' ;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

SQL> ARCHIVE LOG LIST ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DISK2/oracle/archives/dest1
Oldest online log sequence 14
Next log sequence to archive 0
Current log sequence 15

SELECT 'LAST APPLIED : ' LOGS, TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') TIME, SEQUENCE#
FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG C
WHERE SEQUENCE# = ( SELECT MAX(SEQUENCE#) FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG B
WHERE A.RESETLOGS_CHANGE# = B.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = B.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' AND B.APPLIED = 'YES') AND
A.RESETLOGS_CHANGE# = C.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = C.RESETLOGS_TIME AND
A.STATUS = 'CURRENT'
UNION
SELECT 'LAST RECEIVED : ' LOGS, TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') TIME, SEQUENCE#
FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG C
WHERE SEQUENCE# = ( SELECT MAX(SEQUENCE#) FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG B
WHERE A.RESETLOGS_CHANGE# = B.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = B.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' ) AND
A.RESETLOGS_CHANGE# = C.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = C.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' ;

LOGS TIME SEQUENCE#
---------------- -------------------- ----------
Last applied : 14-JUL-2015 00:18:35 15
Last received : 14-JUL-2015 00:18:35 15

Primaire :

=======

SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.

SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DISKFLASH/oracle/archives
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17

Standby :

=======

SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DISKFLASH/oracle/archives
Oldest online log sequence 15
Next log sequence to archive 0
Current log sequence 17

SELECT 'LAST APPLIED : ' LOGS, TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') TIME, SEQUENCE#
FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG C
WHERE SEQUENCE# = ( SELECT MAX(SEQUENCE#) FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG B
WHERE A.RESETLOGS_CHANGE# = B.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = B.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' AND B.APPLIED = 'YES') AND
A.RESETLOGS_CHANGE# = C.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = C.RESETLOGS_TIME AND
A.STATUS = 'CURRENT'
UNION
SELECT 'LAST RECEIVED : ' LOGS, TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') TIME, SEQUENCE#
FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG C
WHERE SEQUENCE# = ( SELECT MAX(SEQUENCE#) FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG B
WHERE A.RESETLOGS_CHANGE# = B.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = B.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' ) AND
A.RESETLOGS_CHANGE# = C.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = C.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' ;

LOGS TIME SEQUENCE#
---------------- -------------------- ----------
Last applied : 14-JUL-2015 00:18:35 15
Last received : 14-JUL-2015 00:20:47 16

Primaire :

=======

mkdir -p /tmp/UPGRADE
sqlplus / as sysdba

SQL> spool /tmp/UPGRADE/EVTA_upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql

Après un bon moment ...

SQL> STARTUP

SET LINESIZE 200
SET PAGESIZE 50
COL NAME FORMAT A40
COL VERSION FORMAT A20
COL COMPNAME FORMAT A30
SELECT SUBSTR(COMP_ID, 1,10) COMPID, SUBSTR(COMP_NAME,1,24) COMPNAME, STATUS,VERSION FROM DBA_REGISTRY;

COMPID COMPNAME STATUS VERSION
---------------- ------------------------------ ------------ --------------------
OWB OWB VALID 11.2.0.1.0
APEX Oracle Application Expre VALID 3.2.1.00.10
EM Oracle Enterprise Manage VALID 11.2.0.4.0
AMD OLAP Catalog VALID 11.2.0.4.0
SDO Spatial VALID 11.2.0.4.0
ORDIM Oracle Multimedia VALID 11.2.0.4.0
XDB Oracle XML Database VALID 11.2.0.4.0
CONTEXT Oracle Text VALID 11.2.0.4.0
EXF Oracle Expression Filter VALID 11.2.0.4.0
RUL Oracle Rules Manager VALID 11.2.0.4.0
OWM Oracle Workspace Manager VALID 11.2.0.4.0
CATALOG Oracle Database Catalog VALID 11.2.0.4.0
CATPROC Oracle Database Packages VALID 11.2.0.4.0
JAVAVM JServer JAVA Virtual Mac VALID 11.2.0.4.0
XML Oracle XDK VALID 11.2.0.4.0
CATJAVA Oracle Database Java Pac VALID 11.2.0.4.0
APS OLAP Analytic Workspace VALID 11.2.0.4.0
XOQ Oracle OLAP API VALID 11.2.0.4.0

spool /tmp/UPGRADE/EVTA_utlu112s.log
@?/rdbms/admin/utlu112s.sql
spool off

.
Oracle Database 11.2 Post-Upgrade Status Tool 07-14-2015 06:57:52
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:35:00
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:16:21
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:01:31
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:01:40
OLAP Catalog
. VALID 11.2.0.4.0 00:01:52
Oracle OLAP API
. VALID 11.2.0.4.0 00:01:16
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:07:33
Oracle XDK
. VALID 11.2.0.4.0 00:01:36
Oracle Text
. VALID 11.2.0.4.0 00:01:45
Oracle XML Database
. VALID 11.2.0.4.0 00:07:08
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:51
Oracle Multimedia
. VALID 11.2.0.4.0 00:08:05
Spatial
. VALID 11.2.0.4.0 00:06:12
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:34
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:18
Oracle Application Express
. VALID 3.2.1.00.10
Final Actions
. 00:00:01
Total Upgrade Time: 01:31:53

PL/SQL procedure successfully completed.

spool /tmp/UPGRADE/EVTA_catuppst.log
@?/rdbms/admin/catuppst.sql

......

spool /tmp/UPGRADE/EVTA_utlrp.log
@?/rdbms/admin/utlrp.sql
spool off

......

spool /tmp/UPGRADE/EVTA_utluiobj.log
@?/rdbms/admin/utluiobj.sql
spool off
.
Oracle Database 11.2 Post-Upgrade Invalid Objects Tool 07-12-2015 23:35:37
.
This tool lists post-upgrade invalid objects that were not invalid
prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).
.
Owner Object Name Object Type
.
The table registry$sys_inv_objs does not exist. The pre-upgrade tool,
utlu111i.sql, creates and populates registry$sys_inv_objs. To use this
post-upgrade tool, you must have run utlu111i.sql prior to upgrading
the database.

PL/SQL procedure successfully completed.

spool /tmp/UPGRADE/EVTA_catbundle.log
@?/rdbms/admin/catbundle.sql psu apply
spool off

SQL> alter system switch logfile ;
System altered.

SQL> alter system switch logfile ;
System altered.

SQL> alter system switch logfile ;
System altered.

SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DISKFLASH/oracle/archives
Oldest online log sequence 75
Next log sequence to archive 77
Current log sequence 77

Standby :

=======

SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DISKFLASH/oracle/archives
Oldest online log sequence 75
Next log sequence to archive 0
Current log sequence 77

SELECT 'LAST APPLIED : ' LOGS, TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') TIME, SEQUENCE#
FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG C
WHERE SEQUENCE# = ( SELECT MAX(SEQUENCE#) FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG B
WHERE A.RESETLOGS_CHANGE# = B.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = B.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' AND B.APPLIED = 'YES') AND
A.RESETLOGS_CHANGE# = C.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = C.RESETLOGS_TIME AND
A.STATUS = 'CURRENT'
UNION
SELECT 'LAST RECEIVED : ' LOGS, TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') TIME, SEQUENCE#
FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG C
WHERE SEQUENCE# = ( SELECT MAX(SEQUENCE#) FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG B
WHERE A.RESETLOGS_CHANGE# = B.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = B.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' ) AND
A.RESETLOGS_CHANGE# = C.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = C.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' ;

LOGS TIME SEQUENCE#
---------------- -------------------- ----------
Last applied : 14-JUL-2015 07:07:19 75
Last received : 14-JUL-2015 07:07:37 76

Primaire :

=======

SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /DISK2/oracle/product/11.2.0.4/dbs/dr1TEST_P.dat
dg_broker_config_file2 string /DISK2/oracle/product/11.2.0.4/dbs/dr2TEST_P.dat

S'assurer que les fichiers *.dat de la configuration du broker ont été copiés.

sqlplus / as sysdba
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
System altered.

SET LINESIZE 1000
COL COMMENTS FORMAT A40
SELECT TO_CHAR(ACTION_TIME,'DD-MON-YYYY') JOUR,ACTION,VERSION,ID,BUNDLE_SERIES,COMMENTS
FROM DBA_REGISTRY_HISTORY WHERE ACTION='UPGRADE';

JOUR ACTION VERSION ID BUNDLE_SERIES COMMENTS
-------------------- ------------- -------------- ---- -------------- --------------------------
14-JUL-2015 UPGRADE 11.2.0.4.0 Upgraded from 11.2.0.1.0

Standby :

=======

        S'assurer que les fichiers *.dat de la configuration du broker ont été copiés.

sqlplus / as sysdba
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
System altered.

Primaire :

=======

dgmgrl

DGMGRL> connect sys@test_p
Connected.

DGMGRL> enable configuration ;

DGMGRL> SHOW CONFIGURATION
Configuration - evta

Protection Mode: MaxPerformance
Databases:
test_p - Primary database
test_s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> SHOW DATABASE "test_p"
Database - test_p

Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
TEST

Database Status:
SUCCESS

DGMGRL> SHOW DATABASE "test_s"
Database - test_s

Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 2 minutes 18 seconds (computed 1 second ago)
Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
TEST

Database Status:
SUCCESS

DGMGRL> EDIT DATABASE "test_s" SET STATE='APPLY-ON';
DGMGRL> EDIT DATABASE "test_p" SET STATE='TRANSPORT-ON';

DGMGRL> SHOW DATABASE "test_p"
Database - test_p

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
TEST

Database Status:
SUCCESS

DGMGRL> SHOW DATABASE "test_s"
Database - test_s

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
TEST

Database Status:
SUCCESS

Primaire :

=======

ALTER SYSTEM SWITCH LOGFILE ;
ALTER SYSTEM SWITCH LOGFILE ;
ALTER SYSTEM SWITCH LOGFILE ;

Standby :

=======

SELECT 'LAST APPLIED : ' LOGS, TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') TIME, SEQUENCE#
FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG C
WHERE SEQUENCE# = ( SELECT MAX(SEQUENCE#) FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG B
WHERE A.RESETLOGS_CHANGE# = B.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = B.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' AND B.APPLIED = 'YES') AND
A.RESETLOGS_CHANGE# = C.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = C.RESETLOGS_TIME AND
A.STATUS = 'CURRENT'
UNION
SELECT 'LAST RECEIVED : ' LOGS, TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') TIME, SEQUENCE#
FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG C
WHERE SEQUENCE# = ( SELECT MAX(SEQUENCE#) FROM V$DATABASE_INCARNATION A, V$ARCHIVED_LOG B
WHERE A.RESETLOGS_CHANGE# = B.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = B.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' ) AND
A.RESETLOGS_CHANGE# = C.RESETLOGS_CHANGE# AND A.RESETLOGS_TIME = C.RESETLOGS_TIME AND
A.STATUS = 'CURRENT' ;

*****************************************************************

*****************************************************************

**** Post - Upgrade 

*****************************************************************

*****************************************************************

*************************************

*************************************

**** Home Source - 11.2.0.4

**** Confirmer la version de la StandBy

**** Changer à snapshot standby

*************************************

*************************************

Primaire :

=======

Standby :

=======

SQL> SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE ;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TEST READ WRITE SNAPSHOT STANDBY

SET LINESIZE 200
SET PAGESIZE 50
COL NAME FORMAT A40
COL VERSION FORMAT A20
COL COMPNAME FORMAT A30
SELECT SUBSTR(COMP_ID, 1,10) COMPID, SUBSTR(COMP_NAME,1,24) COMPNAME, STATUS,VERSION FROM DBA_REGISTRY;

COMPID COMPNAME STATUS VERSION
------------------ ------------------------------ ----------- --------------------
OWB OWB VALID 11.2.0.1.0
APEX Oracle Application Expre VALID 3.2.1.00.10
EM Oracle Enterprise Manage VALID 11.2.0.4.0
AMD OLAP Catalog VALID 11.2.0.4.0
SDO Spatial VALID 11.2.0.4.0
ORDIM Oracle Multimedia VALID 11.2.0.4.0
XDB Oracle XML Database VALID 11.2.0.4.0
CONTEXT Oracle Text VALID 11.2.0.4.0
EXF Oracle Expression Filter VALID 11.2.0.4.0
RUL Oracle Rules Manager VALID 11.2.0.4.0
OWM Oracle Workspace Manager VALID 11.2.0.4.0
CATALOG Oracle Database Catalog VALID 11.2.0.4.0
CATPROC Oracle Database Packages VALID 11.2.0.4.0
JAVAVM JServer JAVA Virtual Mac VALID 11.2.0.4.0
XML Oracle XDK VALID 11.2.0.4.0
CATJAVA Oracle Database Java Pac VALID 11.2.0.4.0
APS OLAP Analytic Workspace VALID 11.2.0.4.0
XOQ Oracle OLAP API VALID 11.2.0.4.0

SET LINESIZE 1000
COL COMMENTS FORMAT A40
SELECT TO_CHAR(ACTION_TIME,'DD-MON-YYYY') JOUR,ACTION,VERSION,ID,BUNDLE_SERIES,COMMENTS
FROM DBA_REGISTRY_HISTORY WHERE ACTION='UPGRADE';

JOUR ACTION VERSION ID BUNDLE_SERIES COMMENTS
-------------------- ------------ ------------ ---- --------------- ----------------------------------------
14-JUL-2015 UPGRADE 11.2.0.4.0 Upgraded from 11.2.0.1.0

SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.

SQL> ARCHIVE LOG LIST ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DISKFLASH/oracle/archives
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 3

*************************************
*************************************
**** Home Source - 11.2.0.4
**** Confirmer la version de la StandBy
**** Revenir à physical standby
*************************************
*************************************

Primaire :

=======

dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@test_p
Connected.

DGMGRL> CONVERT DATABASE TEST_S TO PHYSICAL STANDBY ;

Converting database "test_s" to a Physical Standby database, please wait...
Operation requires shutdown of instance "TEST" on database "test_s"
Shutting down instance "TEST"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "TEST" on database "test_s"
Starting instance "TEST"...
ORACLE instance started.
Database mounted.
Continuing to convert database "test_s" ...
Operation requires shutdown of instance "TEST" on database "test_s"
Shutting down instance "TEST"...
ORA-01109: base de données non ouverte

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "TEST" on database "test_s"
Starting instance "TEST"...
ORACLE instance started.
Database mounted.
Database "test_s" converted successfully

DGMGRL> SHOW CONFIGURATION ;

Configuration - evta

Protection Mode: MaxPerformance
Databases:
test_p - Primary database
test_s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Standby :

=======

SQL> SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE ;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TEST MOUNTED PHYSICAL STANDBY

La partie III vous pouvez la voir ici.

L'Oracle de Victor

Translate

jeudi 16 juillet 2015

Partie II - Upgrade DataGuard - Downgrade ( Restore Points ) - (DataGuard - Broker - 11g )