'Downgrade' 태그의 글 목록

Below are the steps for downgrading oracle databases from 11.2.0.3 to 11.2.0.1 and from 11g to 10g

This assumes a) you do not have oracle valult installed. 2) You do not have oracle application express 3) you do not have objects created from fixed tables 4) database is not configured for Label Security 5) this is single instance database

Step 1 : Timzone data types consideration:

A). If you previously had upgraded the database and then used the DBMS_DST PL/SQL package to update the database time zone version, then you must apply the patch for the same time zone file version into the earlier release's Oracle home before downgrading.

As an example scenario, assume that a release 10.2.0.4 database on Linux x64 using DSTv4 had been upgraded to release 11.2.0.2, and DBMS_DST was then run to update this database to DSTv14. Then, before downgrading from release 11.2.0.3 to 10.2.0.4, you need to apply on the release 10.2.0.4 side the DSTv14 patch for 10.2.0.4 for Linux x64. This ensures that your TIMESTAMP WITH TIME ZONE data is not logically corrupted during retrieval.

To find which time zone file version your database is using, run:

SELECT value$ FROM sys.props$ WHERE NAME = 'DST_PRIMARY_TT_VERSION';
B). If you had set the ORA_TZFILE environment variable to the full path name of the timezone.dat file when you upgraded to Oracle Database 11g Release 2 (11.2), then you must unset it if you subsequently downgrade your database.

Two time zone files are included in the Oracle home directory:

◦The default time zone file at
$ORACLE_HOME/oracore/zoneinfo/timezonelrg.dat
◦A smaller time zone file at
$ORACLE_HOME/oracore/zoneinfo/timezone.dat
If you do not unset the ORA_TZFILE variable, then connecting to the database using the smaller time zone file might produce the following errors:
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

Step 3: Connect to sys user from higher ORACLE_HOME and run downgrade scripts

sqlplus / as sysdba

SQL> STARTUP DOWNGRADE

SQL> drop user sysman cascade [ if you have existing sysman user]

SQL> spool downgrade.log

SQL> @?/rdbms/admin/catdwgrd.sql

SQL>spool off

Step 4: Start Oracle database service from lower ORACLE_HOME and reload old dictionary

A) Now copy init files and password files from higher ORACLE_HOME to lower ORACLE_HOME

B) If you are on windows then delete existing Oracle database service by

oradim -delete -sid SID_NAME from Higher ORACLE_HOME\bin

and create oracle service in lower ORACLE_HOME by

oradim -new -sid ORCL -startmode auto -srvcstart system

C) set ORACLE_HOME=LOWER_ORACLE_HOME or export ORACLE_HOME=LOWER_ORACLE_HOME

D) sqlplus / as sysdba [ using binaries of OLD_ORACLE_HOME]

SQL>spool reload.log

SQL> startup upgrade

SQL> @?/rdbms/admin/catrelod.sql

Additional steps:

If you are downgrading to Oracle Database 11g Release 1 (11.1.0.6), run the xsrelod.sql script:

SQL> @xsrelod.sql

If you are downgrading to Oracle Database 10g Release 1 (10.1.0.5) and you have XDB in your database, then run the dbmsxdbt.sql script:

@dbmsxdbt.sql

SQL>spool off

Step 6: Open the database in normal mode

sqlplus / as sysdba

SQL>shutdown immediate

SQL> startup

SQL>@?/rdbms/admin/utlrp.sql

SQL> col comp_name form a50

SQL> select comp_name,version,status from dba_registry ;

-- done--

Query from DBA_REGISTRY should show all components to earlier ORACLE_HOME versions.

Below is output from downgrading 11.2.0.3 to 11.2.0.1
Before upgrade i.e before running catdwgrd in higher ORACLE_HOME:

COMP_NAME                                          VERSION                        STATUS
-------------------------------------------------- ------------------------------ -----------
OWB                                                11.2.0.1.0                     VALID
Oracle Application Express                         3.2.1.00.10                    VALID
Oracle Enterprise Manager                          11.2.0.3.0                     VALID
LAP Catalog                                       11.2.0.3.0                    VALID
Spatial                                            11.2.0.3.0                     VALID
Oracle Multimedia                                  11.2.0.3.0                     VALID
Oracle XML Database                                11.2.0.3.0                     VALID
Oracle Text                                        11.2.0.3.0                     VALID
Oracle Expression Filter                           11.2.0.3.0                     VALID
Oracle Rules Manager                               11.2.0.3.0                     VALID
Oracle Workspace Manager                           11.2.0.3.0                     VALID
Oracle Database Catalog Views                      11.2.0.3.0                     VALID
Oracle Database Packages and Types                 11.2.0.3.0                     VALID
JServer JAVA Virtual Machine                       11.2.0.3.0                     VALID
Oracle XDK                                         11.2.0.3.0                     VALID
Oracle Database Java Packages                      11.2.0.3.0                     VALID
OLAP Analytic Workspace                            11.2.0.3.0                     VALID
Oracle OLAP API                                    11.2.0.3.0                     VALID

After upgrade:

COMP_NAME                           STATUS      VERSION
----------------------------------- ----------- ----------
Oracle Database Packages and Types VALID       11.2.0.1.0
Oracle Database Catalog Views       VALID       11.2.0.1.0
JServer JAVA Virtual Machine        VALID       11.2.0.1.0
Oracle XDK                          VALID       11.2.0.1.0
Oracle Database Java Packages       VALID       11.2.0.1.0
Oracle Text                         INVALID     11.2.0.1.0
Oracle XML Database                 VALID       11.2.0.1.0
Oracle Workspace Manager            VALID       11.2.0.1.0
OLAP Analytic Workspace             VALID       11.2.0.1.0
OLAP Catalog                        VALID       11.2.0.1.0
Oracle OLAP API                     VALID       11.2.0.1.0
Oracle Multimedia                   INVALID     11.2.0.1.0
Spatial                             INVALID     11.2.0.1.0
Oracle Expression Filter            VALID       11.2.0.1.0
Oracle Rules Manager                VALID       11.2.0.1.0
Oracle Application Express          VALID       3.2.1.00.1
OWB                                 VALID       11.2.0.1.0

1. Oracle MultiMedia component became VALID after running utlrp.sql
2. For making Oracle Text Valid perform below step in Lower Oracle home:

sqlplus / as sysdba
SQL>shutdown immediate
SQL>startup upgrade
SQL> drop public synonym ctx_filter_cache_statistics;
SQL> drop view ctx_filter_cache_statistics;
SQL>@?/rdbms/admin/catrelod.sql
SQL>shutdown immediate
SQL>startup
SQL>@utlrp.sql

오라클 role 에 관하여 (0)	2018.07.21
오라클 리스너 시작시 에러 tns-12546,12560,00516 (0)	2018.07.19
awk 프로세서 추출해서 한꺼번에 정리하기 (0)	2016.07.08
Truncate Table From a Remote Database (0)	2016.02.18
통계정보 수집 - DBMS_STATS.GATHER_TABLE_STATS (0)	2015.05.09

오라클 role 에 관하여 (0)

2018.07.21

오라클 리스너 시작시 에러 tns-12546,12560,00516 (0)

2018.07.19

awk 프로세서 추출해서 한꺼번에 정리하기 (0)

2016.07.08

Truncate Table From a Remote Database (0)

2016.02.18

통계정보 수집 - DBMS_STATS.GATHER_TABLE_STATS (0)

2015.05.09

Shared Server (MTS) Diagnostics [ID 1005259.6] (0)	2011.12.08
oracle MTS (0)	2011.12.08
oracle shared pool size얼마나 남았나? (0)	2011.11.22
(Oracle 10g) MMAN 백그라운드 프로세스를 통한 자동 공유 메모리 관리 (0)	2011.11.22
DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments (0)	2011.07.04

Shared Server (MTS) Diagnostics [ID 1005259.6] (0)

2011.12.08

oracle MTS (0)

2011.12.08

oracle shared pool size얼마나 남았나? (0)

2011.11.22

(Oracle 10g) MMAN 백그라운드 프로세스를 통한 자동 공유 메모리 관리 (0)

2011.11.22

DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments (0)

2011.07.04