2015年（9）
分类： Oracle
2015-02-05 15:08:49
---11.2.0.4 PATH
Primary
SQL> SPOOL /tmp/downgrade.log
SQL> STARTUP DOWNGRADE
SQL> @$ORACLE_HOME/rdbms/admin/catdwgrd.sql
ERROR at line 1:
ORA-20000: Direct downgrade of EM Database Control not supported.
ORA-06512: at line 6
[oracle@DG01 admin]$cd /u01/app/oracle/product/11.2.0.4/dbhome_1/sysman/admin/emdrep/sql/core/latest/admin
SQL> @admin_drop_users.sql SYSMAN
old 22: FROM &EM_REPOS_USER..MGMT_CREATED_USERS
new 22: FROM SYSMAN.MGMT_CREATED_USERS
PL/SQL procedure successfully completed.
SQL> DEFINE EM_REPOS_USER=SYSMAN @admin_drop_repos_user.sql SYSMAN
SQL> DEFINE EM_REPOS_USER=SYSMAN @admin_drop_synonyms.sql SYSMAN
SQL> DROP USER SYSMAN CASCADE;
User dropped.
###primary alert.log##
#Warning: drop_queue_table: No evaluation for the queue table: SYSMAN.MGMT_TASK_QTABLE
#Warning: drop_queue_table: No evaluation for the queue table: SYSMAN.MGMT_NOTIFY_INPUT_QTABLE
#Warning: drop_queue_table: No evaluation for the queue table: SYSMAN.MGMT_PAF_MSG_QTABLE_1
#Warning: drop_queue_table: No evaluation for the queue table: SYSMAN.MGMT_PAF_MSG_QTABLE_2
####standby archive apply##
#ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
###########################
SQL> spool /home/oracle/downgrade_01.log
SQL> @$ORACLE_HOME/rdbms/admin/catdwgrd.sql
#####################
#SERVER COMPONENT id=ORDIM: status=DOWNGRADED, version=11.2.0, timestamp=2015-02-05 15:01:13
#SERVER COMPONENT id=OWM: status=DOWNGRADED, version=11.2.0.1.0, timestamp=2015-02-05 15:01:16
#SERVER COMPONENT id=XDB: status=DOWNGRADED, version=11.2.0, timestamp=2015-02-05 15:01:19
#SERVER COMPONENT id=CONTEXT: status=DOWNGRADED, version=11.2.0, timestamp=2015-02-05 15:01:30
#SERVER COMPONENT id=XML: status=DOWNGRADED, version=11.2.0, timestamp=2015-02-05 15:04:01
#...
######################
SQL> commit;
SQL> SELECT 'COMP_TIMESTAMP DWGRD_END ' ||
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS ') ||
TO_CHAR(SYSTIMESTAMP,'J SSSSS ')
AS timestamp FROM DUAL;
----
COMP_TIMESTAMP DWGRD_END 2015-02-05 15:06:57 2457059 54417
SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_server_registry order by modified;
SQL> alter system checkpoint;
SQL> alter system switch logfile;
SQL> shutdown immediate
SQL> !lsnrctl stop
[oracle@DG01 admin]$ vim /home/oracle/.bash_profile
[oracle@DG01 admin]$ source /home/oracle/.bash_profile
[oracle@DG01 admin]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.2/dbhome_1
[oracle@DG01 admin]$ cp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initdg0.ora /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg_down.ora
[oracle@DG01 admin]$ mv /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg0.ora /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/bak_initdg0.ora
[oracle@DG01 admin]$ mv /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg_down.ora /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg0.ora
Standby
SQL> recover automatic standby database;
####
#ALTER DATABASE RECOVER automatic standby database
#All dispatchers and shared servers shutdown
#CLOSE: killing server sessions.
#CLOSE: all sessions shutdown successfully.
#Thu Feb 05 16:23:47 2015
#SMON: disabling cache recovery
#Media Recovery Start
#Serial Media Recovery started
#Managed Standby Recovery not using Real Time Apply
#Media Recovery Log /u01/archive/dg0/870364870_1_469.arc
#Media Recovery Log /u01/archive/dg0/870364870_1_470.arc
#Media Recovery Log /u01/archive/dg0/870364870_1_471.arc
#Media Recovery Log /u01/archive/dg0/870364870_1_472.arc
#Media Recovery Log /u01/archive/dg0/870364870_1_473.arc
#Media Recovery Log /u01/archive/dg0/870364870_1_474.arc
####
SQL> shutdown immediate
SQL> !lsnrctl stop
[oracle@DG02 admin]$ vim /home/oracle/.bash_profile
[oracle@DG02 admin]$ source /home/oracle/.bash_profile
[oracle@DG02 admin]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.2/dbhome_1
[oracle@DG02 ~]$ cp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initdg0.ora /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg_down.ora
[oracle@DG02 ~]$ mv /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg0.ora /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/bak_initdg0.ora
[oracle@DG02 ~]$ mv /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg_down.ora /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg0.ora
--11.2.0.2 PATH
Primary
SQL> STARTUP UPGRADE pfile='/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg0.ora'
SQL> spool /home/oracle/20150205_reload.log
SQL> !lsnrctl start
SQL> !tnsping dg02
SQL> archive log list
SQL> @$ORACLE_HOME/rdbms/admin/catrelod.sql
ORA-20000: Upgrade from version 11.2.0.2.0 cannot
be downgraded to version
[Bug 11811073 ]
-- The catrelod.sql script reloads the appropriate version of
-- all of the database components in the downgraded database.
SQL> SELECT UNIQUE object_name, object_type, owner FROM dba_objects WHERE status='INVALID';
1317 rows selected.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
#####
#SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlrp.sql
#SQL> exec dbms_utility.compile_schema('MDSYS',false);
#SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql
#####
[oracle@DG02 u01]$ unzip p11811073_112020_Generic.zip
[oracle@DG02 u01]$ cd 11811073/
[oracle@DG02 11811073]$ /u01/app/oracle/product/11.2.0.2/dbhome_1/OPatch/opatch apply
###
# Invoking OPatch 11.2.0.1.1
# Oracle Interim Patch Installer version 11.2.0.1.1
# Copyright (c) 2009, Oracle Corporation. All rights reserved.
#
# Oracle Home : /u01/app/oracle/product/11.2.0.2/dbhome_1
# Central Inventory : /u01/app/oraInventory
# from : /etc/oraInst.loc
# OPatch version : 11.2.0.1.1
# OUI version : 11.2.0.2.0
# OUI location : /u01/app/oracle/product/11.2.0.2/dbhome_1/oui
# Log file location : /u01/app/oracle/product/11.2.0.2/dbhome_1/cfgtoollogs/opatch /opatch2015-02-05_17-29-09PM.log
#
# Patch history file: /u01/app/oracle/product/11.2.0.2/dbhome_1/cfgtoollogs/opatch /opatch_history.txt
#
# ApplySession applying interim patch '11811073' to OH '/u01/app/oracle/product/11 .2.0.2/dbhome_1'
#
# Running prerequisite checks...
#
# OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
#
# Backing up files and inventory (not for auto-rollback) for the Oracle Home
# Backing up files affected by the patch '11811073' for restore. This might take awhile...
# Backing up files affected by the patch '11811073' for rollback. This might take a while...
#
# Patching component oracle.rdbms.dbscripts, 11.2.0.2.0...
# Copying file to "/u01/app/oracle/product/11.2.0.2/dbhome_1/rdbms/admin/catrelod.sql"
# ApplySession adding interim patch '11811073' to inventory
#
# Verifying the update...
# Inventory check OK: Patch ID 11811073 is registered in Oracle Home inventory with proper meta-data.
# Files check OK: Files from Patch ID 11811073 are present in Oracle Home.
#
# OPatch succeeded.
###
SQL> spool /home/oracle/primary_reload_02.log
SQL> @$ORACLE_HOME/rdbms/admin/catrelod.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> select owner,object_type, object_name from dba_objects where status='INVALID' order by object_name;
no rows selected
SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql
SQL>SELECT comp_name, status, substr(version,1,10) as version from dba_server_registry order by modified;
###
# COMP_NAME STATUS VERSION
#-------------------------------- --------------------- --------------
#Oracle Database Catalog Views VALID 11.2.0.2.0
#Oracle Workspace Manager VALID 11.2.0.2.0
#JServer JAVA Virtual Machine VALID 11.2.0.2.0
#Oracle Database Packages and Types VALID 11.2.0.2.0
#Oracle XDK VALID 11.2.0.2.0
#Oracle Database Java Packages VALID 11.2.0.2.0
#Oracle Expression Filter VALID 11.2.0.2.0
#Oracle Text VALID 11.2.0.2.0
#Oracle XML Database VALID 11.2.0.2.0
#Oracle OLAP API VALID 11.2.0.2.0
#Oracle Multimedia VALID 11.2.0.2.0
#Oracle Rules Manager VALID 11.2.0.2.0
#OLAP Analytic Workspace VALID 11.2.0.2.0
#Oracle Application Express VALID 3.2.1.00.1
#OWB VALID 11.2.0.2.0
#OLAP Catalog VALID 11.2.0.2.0
#Spatial VALID 11.2.0.2.0
#
#17 rows selected.
####
SQL> alter system checkpoint;
SQL> alter system switch logfile;
SQL> shutdown immediate
SQL> SPOOL OFF
Standby
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg0.ora'
SQL> spool /home/oracle/standby_reload.log
SQL> !lsnrctl start
SQL> !tnsping dg01
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> select sequence#,applied from v$archived_log order by sequence#;
[oracle@DG02 u01]$ unzip p11811073_112020_Generic.zip
[oracle@DG02 u01]$ cd 11811073/
[oracle@DG02 11811073]$ /u01/app/oracle/product/11.2.0.2/dbhome_1/OPatch/opatch apply
###
# Invoking OPatch 11.2.0.1.1
# Oracle Interim Patch Installer version 11.2.0.1.1
# Copyright (c) 2009, Oracle Corporation. All rights reserved.
#
# Oracle Home : /u01/app/oracle/product/11.2.0.2/dbhome_1
# Central Inventory : /u01/app/oraInventory
# from : /etc/oraInst.loc
# OPatch version : 11.2.0.1.1
# OUI version : 11.2.0.2.0
# OUI location : /u01/app/oracle/product/11.2.0.2/dbhome_1/oui
# Log file location : /u01/app/oracle/product/11.2.0.2/dbhome_1/cfgtoollogs/opatch /opatch2015-02-05_17-29-09PM.log
#
# Patch history file: /u01/app/oracle/product/11.2.0.2/dbhome_1/cfgtoollogs/opatch /opatch_history.txt
#
# ApplySession applying interim patch '11811073' to OH '/u01/app/oracle/product/11 .2.0.2/dbhome_1'
#
# Running prerequisite checks...
#
# OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
#
# Backing up files and inventory (not for auto-rollback) for the Oracle Home
# Backing up files affected by the patch '11811073' for restore. This might take awhile...
# Backing up files affected by the patch '11811073' for rollback. This might take a while...
#
# Patching component oracle.rdbms.dbscripts, 11.2.0.2.0...
# Copying file to "/u01/app/oracle/product/11.2.0.2/dbhome_1/rdbms/admin/catrelod. sql"
# ApplySession adding interim patch '11811073' to inventory
#
# Verifying the update...
# Inventory check OK: Patch ID 11811073 is registered in Oracle Home inventory with proper meta-data.
# Files check OK: Files from Patch ID 11811073 are present in Oracle Home.
#
# OPatch succeeded.
###
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select sequence#,applied from v$archived_log;
SQL> select comp_name, status, substr(version,1,10) as version
from dba_server_registry order by modified;
其他参考
http://blog.sina.com.cn/s/blog_8317516b01015h7q.html
