Amit's Oracle DBA Blog: Upgrade Oracle ASM database from 12c to 18c in Data Guard environment

Upgrade Oracle ASM database from 12c to 18c in Data Guard environment (Primary first, Standby later) :-

1. Install new Oracle Home for version 18.3.0.1 on Primary and Standby

2. Stop log shipping from the primary to the standby.

3. Disable Data-Guard configuration

4. Stop Data-Guard Broker processes

5. Stop Standby database

6. Upgrade Primary database

7. Start Standby database from New Home

Environment:

Details	Primary ( RAC6)	Standby (RAC7)	Remark
OS version	Oracle Linux Server release 7.6	Oracle Linux Server release 7.6	Completed
ASM Version	18.3.0.1	18.3.0.1
ASM_HOME	/u02/app/18.3.0/grid	/u02/app/18.3.0/grid
Database Name	ORCL - ORCL (Instance)	ORCL - ORCLDG (Instance)
ORACLE_HOME	/u01/app/oracle/product/12.1.0.2/db_1	/u01/app/oracle/product/12.1.0.2/db_1
Upgrade from 12c database to 18c in a Data Guard environment
Database Name	ORCL - ORCL (Instance)	ORCL - ORCLDG (Instance)	Pending
ORACLE_HOME	/u01/app/oracle/product/18.3.0.0	/u01/app/oracle/product/18.3.0.0	Pending

Note: Stand-alone ASM Data-guard already upgraded.

Please find the below screenshots

Install new Oracle Home for version 18.3.0.1 on Primary and Standby ( In this case ORACLE_HOME has been already installed.)

Stop log shipping from the primary to the standby.

SQL> alter system set log_archive_dest_state_2=DEFER scope=both;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE

------------------------------------ ----------- ----------------

log_archive_dest_state_2 string DEFER

Disable Data-Guard configuration and Stop Data-Guard Broker processes

SQL> show parameter DG_BROKER_START

NAME TYPE VALUE

------------------------------------ ----------- -------------

dg_broker_start boolean FALSE

Stop Standby database:

*****Upgrade Primary database à rac6*****

Oracle 18c ASM -DG database Manual Upgrade Method

Manual upgrade steps should be as follows.

1. Take Full backup

2. Gather dictionary stats

3. Purge Recycle bin

4. Check Timezone

5. Run Preupgrade

6. Shutdown 12c Database and Open 18c instance in upgrade mode

7. Upgrade database

8. Run Post Upgrade steps

9. Compile Invalid Objects.

10. Start all Oracle 18c instances and check

1). Take Full backup --------à Done

2). Run the preupgrade tool (available on new home admin dir)

[oracle@rac6 ~]$ mkdir ORCL_upgrade

[oracle@rac6 ~]$

[oracle@rac6 ~]$ cd ORCL_upgrade

[oracle@rac6 ORCL_upgrade]$

[oracle@rac6 ORCL_upgrade]$ pwd

/home/oracle/ORCL_upgrade

Note: Preupgrade tool is available under new $ORACLE_HOME/rdbms/admin

[oracle@rac6 admin]$ ls -lrt preupgrade.jar

-rwxrwxr-x 1 oracle oinstall 682570 Jul 14 2018 preupgrade.jar

SYNTAX:

$OLD_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/preupgrade.jar -FILE DIR $OS_DIRECTORY

Above command will create below logfiles:

preupgrade.log

preupgrade_fixups.sql

postupgrade_fixups.sql

[oracle@rac6 ~]$ /u01/app/oracle/product/12.1.0.2/db_1/jdk/bin/java -jar /u01/app/oracle/product/18.3.0.0/rdbms/admin/preupgrade.jar

FILE DIR /home/oracle/ORCL_upgrade

==================

PREUPGRADE SUMMARY

==================

/home/oracle/ORCL_upgrade/preupgrade.log

/home/oracle/ORCL_upgrade/preupgrade_fixups.sql

/home/oracle/ORCL_upgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups

@/home/oracle/ORCL_upgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/home/oracle/ORCL_upgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-10-18T20:04:03

[oracle@rac6 ~]$ cd ORCL_upgrade

[oracle@rac6 ORCL_upgrade]$ ls -lrt

total 632

drwxr-xr-x 3 oracle oinstall 4096 Oct 18 20:03 oracle

-rw-r--r-- 1 oracle oinstall 14846 Oct 18 20:03 dbms_registry_extended.sql

-rw-r--r-- 1 oracle oinstall 7963 Oct 18 20:03 preupgrade_driver.sql

-rw-r--r-- 1 oracle oinstall 422048 Oct 18 20:03 preupgrade_package.sql

-rw-r--r-- 1 oracle oinstall 14383 Oct 18 20:03 parameters.properties

-rw-r--r-- 1 oracle oinstall 83854 Oct 18 20:03 preupgrade_messages.properties

drwxr-xr-x 3 oracle oinstall 4096 Oct 18 20:03 upgrade

-rw-r--r-- 1 oracle oinstall 50172 Oct 18 20:03 components.properties

-rw-r--r-- 1 oracle oinstall 2 Oct 18 20:03 checksBuffer.tmp

-rw-r--r-- 1 oracle asmadmin 9589 Oct 18 20:04 preupgrade_fixups.sql

-rw-r--r-- 1 oracle asmadmin 7581 Oct 18 20:04 postupgrade_fixups.sql

-rw-r--r-- 1 oracle asmadmin 7330 Oct 18 20:04 preupgrade.log

[oracle@rac6 ORCL_upgrade]$

Run pre-upgrade script-from old home (OLD env)

SQL> @preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 18.0.0.0.0 Build: 1

Generated on: 2020-10-18 20:03:54

For Source Database: ORCL

Source Database Version: 12.1.0.2.0

For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

------ ------------------------ ---------- --------------------------------

1. purge_recyclebin YES None.

2. apex_manual_upgrade NO Manual fixup recommended.

3. dictionary_stats YES None.

4. tablespaces_info NO Informational only.

Further action is optional.

5. sync_standby_db NO Informational only.

Further action is optional.

6. min_archive_dest_size NO Informational only.

Further action is optional.

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database is not ready

for upgrade. To resolve the outstanding issues, start by reviewing

the preupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

PL/SQL procedure successfully completed.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> select username,default_tablespace,account_status, temporary_tablespace from dba_users where username like 'APE%';

USERNAME DEFAULT_TABLESPACE ACCOUNT_STATUS TEMPORARY_TABLESPACE

------------------- --------------------------------------- ---------------------

APEX_040200 SYSAUX EXPIRED & LOCKED TEMP

APEX_PUBLIC_USER USERS EXPIRED & LOCKED TEMP

SQL> @dba_registry.sql

COMP_ID COMP_NAME VERSION

--------------- ----------------------------------- ---------------

DV Oracle Database Vault 12.1.0.2.0

APEX Oracle Application Express 4.2.5.00.08

OLS Oracle Label Security 12.1.0.2.0

SDO Spatial 12.1.0.2.0

ORDIM Oracle Multimedia 12.1.0.2.0

CONTEXT Oracle Text 12.1.0.2.0

OWM Oracle Workspace Manager 12.1.0.2.0

XDB Oracle XML Database 12.1.0.2.0

CATALOG Oracle Database Catalog Views 12.1.0.2.0

CATPROC Oracle Database Packages and Types 12.1.0.2.0

JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0

XML Oracle XDK 12.1.0.2.0

CATJAVA Oracle Database Java Packages 12.1.0.2.0

APS OLAP Analytic Workspace 12.1.0.2.0

XOQ Oracle OLAP API 12.1.0.2.0

RAC Oracle Real Application Clusters 12.1.0.2.0

How to install APEX?

Downloading and Installing Application Express

Unzip downloaded zip file: ...
Change your working directory to apex .
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. ...

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

SQL> @dba_registry;

COMP_ID COMP_NAME VERSION

--------------- ----------------------------------- ---------------

DV Oracle Database Vault 12.1.0.2.0

APEX Oracle Application Express 20.1.0.00.13

OLS Oracle Label Security 12.1.0.2.0

SDO Spatial 12.1.0.2.0

ORDIM Oracle Multimedia 12.1.0.2.0

CONTEXT Oracle Text 12.1.0.2.0

OWM Oracle Workspace Manager 12.1.0.2.0

XDB Oracle XML Database 12.1.0.2.0

CATALOG Oracle Database Catalog Views 12.1.0.2.0

CATPROC Oracle Database Packages and Types 12.1.0.2.0

JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0

COMP_ID COMP_NAME VERSION

--------------- ----------------------------------- ---------------

XML Oracle XDK 12.1.0.2.0

CATJAVA Oracle Database Java Packages 12.1.0.2.0

APS OLAP Analytic Workspace 12.1.0.2.0

XOQ Oracle OLAP API 12.1.0.2.0

RAC Oracle Real Application Clusters 12.1.0.2.0

Run Gather dictionary stats:-

SQL> exec dbms_stats.gather_dictionary_stats;

Ensure no materialized view and Refresh in progress:

SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;

no rows selected

Ensure no media recovery required currently:

SQL> select * from v$recover_file;

no rows selected

Ensure no file in backup mode before upgrade starts:

SQL> select * from v$backup WHERE status != 'NOT ACTIVE';

Ensure no pending distributed transactions:

SQL> Select * from dba_2pc_pending;

Check duplicate objects owned by system and sys

select object_name, object_type from dba_objects where object_name||object_type in

(select object_name||object_type from dba_objects where owner = 'SYS')

and owner = 'SYSTEM';

OBJECT_NAME OBJECT_TYPE

--------------------------------------------------- -------------------

DBMS_REPCAT_AUTH PACKAGE BODY

AQ$_SCHEDULES_PRIMARY INDEX

AQ$_SCHEDULES TABLE

DBMS_REPCAT_AUTH PACKAGE

Note: If you found any other objects other than these four, then those need to be cleaned up.

Synchronize the Standby Database with the Primary Database When Upgrading

SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)

FROM v$parameter

WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; 2 3

no rows selected

Check whether database has any externally authenticated SSL users

SQL> SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL

AND password = 'GLOBAL';

no rows selected

Review and Remove any unnecessary hidden/underscore parameters

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

no rows selected

Disable all db jobs as well as Cron jobs:

SQL> select job_name,state from dba_scheduler_jobs; ---Disable the jobs which has been scheuduled

select job_name,state,enabled from dba_scheduler_jobs;

select 'execute dbms_scheduler.disable('||''''||owner||'.'||job_name||''''||');' from dba_scheduler_jobs where enabled='TRUE';

execute dbms_scheduler.disable('SYS.SM$CLEAN_AUTO_SPLIT_MERGE');

execute dbms_scheduler.disable('SYS.RSE$CLEAN_RECOVERABLE_SCRIPT');

execute dbms_scheduler.disable('SYS.BSLN_MAINTAIN_STATS_JOB');

execute dbms_scheduler.disable('SYS.DRA_REEVALUATE_OPEN_FAILURES');

execute dbms_scheduler.disable('SYS.ORA$AUTOTASK_CLEAN');

execute dbms_scheduler.disable('SYS.FILE_SIZE_UPD');

execute dbms_scheduler.disable('SYS.CLEANUP_ONLINE_PMO');

execute dbms_scheduler.disable('SYS.CLEANUP_TRANSIENT_PKG');

execute dbms_scheduler.disable('SYS.CLEANUP_TRANSIENT_TYPE');

execute dbms_scheduler.disable('SYS.CLEANUP_TAB_IOT_PMO');

execute dbms_scheduler.disable('SYS.CLEANUP_ONLINE_IND_BUILD');

execute dbms_scheduler.disable('SYS.CLEANUP_NON_EXIST_OBJ');

execute dbms_scheduler.disable('SYS.PMO_DEFERRED_GIDX_MAINT_JOB');

execute dbms_scheduler.disable('SYS.PURGE_LOG');

execute dbms_scheduler.disable('APEX_200100.ORACLE_APEX_BACKUP');

execute dbms_scheduler.disable('APEX_200100.ORACLE_APEX_ISSUE_EMAILS');

execute dbms_scheduler.disable('APEX_200100.ORACLE_APEX_DICTIONARY_CACHE');

execute dbms_scheduler.disable('APEX_200100.ORACLE_APEX_DAILY_MAINTENANCE');

execute dbms_scheduler.disable('APEX_200100.ORACLE_APEX_WS_NOTIFICATIONS');

execute dbms_scheduler.disable('APEX_200100.ORACLE_APEX_MAIL_QUEUE');

execute dbms_scheduler.disable('APEX_200100.ORACLE_APEX_PURGE_SESSIONS');

Oracle AUTO job disable before upgrade:

SELECT client_name, status FROM dba_autotask_client;

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);

Audit table must be in system tablespace. If not Move the AUD$ table to SYSTEM TS:

SQL> set lines 200

select SEGMENT_NAME,TABLESPACE_NAME,BLOCKS,BYTES/1024/1024 "Size Mb" from dba_segments where SEGMENT_NAME = 'AUD$';

SEGMENT_NA TABLESPACE_NAME BLOCKS Size Mb

---------- ------------------------------ ---------- ----------

AUD$ SYSTEM 8 .0625

Move the AUD$ table to SYSTEM TS:

SQL> BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

audit_trail_location_value => 'SYSTEM');

END;

Check the SYS & SYSTEM users TS and it must be SYSTEM tablespace

SQL> col username for a15

SQL>

SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where username in ('SYS','SYSTEM');

USERNAME DEFAULT_TABLESPACE

--------------- ------------------------------

SYSTEM SYSTEM

SYS SYSTEM

Oracle Components details

SQL> set lines 200

set pages 200

COLUMN comp_id FORMAT A10

COLUMN comp_name FORMAT A35

COLUMN version FORMAT A14

SELECT comp_id,comp_name,version FROM dba_registry;

COMP_ID COMP_NAME VERSION

---------- ----------------------------------- --------------

DV Oracle Database Vault 12.1.0.2.0

APEX Oracle Application Express 20.1.0.00.13

OLS Oracle Label Security 12.1.0.2.0

SDO Spatial 12.1.0.2.0

ORDIM Oracle Multimedia 12.1.0.2.0

CONTEXT Oracle Text 12.1.0.2.0

OWM Oracle Workspace Manager 12.1.0.2.0

XDB Oracle XML Database 12.1.0.2.0

CATALOG Oracle Database Catalog Views 12.1.0.2.0

CATPROC Oracle Database Packages and Types 12.1.0.2.0

JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0

XML Oracle XDK 12.1.0.2.0

CATJAVA Oracle Database Java Packages 12.1.0.2.0

APS OLAP Analytic Workspace 12.1.0.2.0

XOQ Oracle OLAP API 12.1.0.2.0

RAC Oracle Real Application Clusters 12.1.0.2.0

16 rows selected.

Move the Directory and Create another same directory and run below command:

[oracle@rac6 ~]$ /u01/app/oracle/product/12.1.0.2/db_1/jdk/bin/java -jar /u01/app/oracle/product/18.3.0.0/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/ORCL_upgrade

==================

PREUPGRADE SUMMARY

==================

/home/oracle/ORCL_upgrade/preupgrade.log

/home/oracle/ORCL_upgrade/preupgrade_fixups.sql

/home/oracle/ORCL_upgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups

@/home/oracle/ORCL_upgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/home/oracle/ORCL_upgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-10-19T02:41:41

Once Again run the preupgrade_fixups.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> set lines 200;

set pages 200;

col owner for a15;

col OBJECT_NAME for a35;

col SUBOBJECT_NAME for a15;

col object_type for a20;

select owner,OBJECT_NAME,SUBOBJECT_NAME,object_type from dba_objects where status='INVALID' and owner='SYS'; SQL> SQL> SQL> SQL> SQL> SQL>

no rows selected

Shutdown database and Listener

SQL> shut immediate;

Actual UPGRADE:

Copy the parameter and password file from the 12c Home to the new 18c Home.

Modify Oratab entry to change Home to the New Home

ORCL:/u01/app/oracle/product/18.3.0.0:N

Change environment variables to point out the new Oracle Homeà 18c

set environment variable echo $ORACLE_HOME

echo $ORACLE_BASE

Set new ORACLE HOME of 18c location

Start the database in upgrade mode.

Verify if the DB opened in READ WRITE mode

SQL> select name, open_mode,cdb,i.con_id, version from V$database,v$instance i;

NAME OPEN_MODE CDB CON_ID VERSION

--------- -------------------- --- ---------- -----------------

ORCL READ WRITE NO 0 18.0.0.0.0

Start the DB Upgrade process:

Run the upgrade script:

cd /applications/oracle/18.4.0.1/bin

$] ./dbupgrade -n 2 -l /home/oracle/ORCL_18c_upgrade

The whole upgrade process runs now and we have to wait patiently for it to finish.

Verify the logs also

Note: Database will be shutdown once above command run successfully.

Log location :

Startup database in normal mode

SQL> startup;

Run “postupgrade_fixups.sql” which was created earlier.

SQL> @postupgrade_fixups.sql

Execute utlu122s.sql

cd $ORACLE_HOME/rdbms/admin

run

SQL> @utlu122s.sql and then (give you the staus of upgrade i.e. Output of Component Name, status, version )

RUN à catuppst.sql

(Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform remaining upgrade actions that do not require the database to be in UPGRADE mode. If an Oracle bundle patch or patch set update (PSU or BP) is installed in the Oracle home, then this script automatically applies that patch set update to the database.)

SQL>@catuppst.sql

Lastly, execute utlrp.sql script to compile if there are any invalid objects

SQL>?/rdbms/admin/utlrp.sql

SQL> select count(1) from dba_objects where status='INVALID';

COUNT(1)

----------

To fix the manual error related to timezone:

The database is using timezone datafile version 18 and the target 12.1.0.2.0 database ships with timezone datafile version 31.

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@rac6 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Oct 19 18:46:12 2020

Version 18.3.0.0.0

Connected to an idle instance.

SQL>

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 1174403152 bytes

Fixed Size 8656976 bytes

Variable Size 436207616 bytes

Database Buffers 721420288 bytes

Redo Buffers 8118272 bytes

Database mounted.

Database opened.

SET SERVEROUTPUT ON

DECLARE

l_tz_version PLS_INTEGER;

BEGIN

l_tz_version := DBMS_DST.get_latest_timezone_version;

DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);

DBMS_DST.begin_upgrade(l_tz_version);

END;

SQL> 2 3 4 5 6 7 8

l_tz_version=31

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> SHUTDOWN IMMEDIATE;

$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Sep 6 15:06:51 2019

Version 18.4.0.0.0

Connected to an idle instance.

SQL> STARTUP;

ORACLE instance started.

SQL>

SET SERVEROUTPUT ON

DECLARE

l_failures PLS_INTEGER;

BEGIN

DBMS_DST.upgrade_database(l_failures);

DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);

DBMS_DST.end_upgrade(l_failures);

DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);

END;

SQL> 2 3 4 5 6 7 8 9

Table list: ""GSMADMIN_INTERNAL"".""AQ$_CHANGE_LOG_QUEUE_TABLE_S""

Number of failures: 0

Table list: ""GSMADMIN_INTERNAL"".""AQ$_CHANGE_LOG_QUEUE_TABLE_L""

Number of failures: 0

DBMS_DST.upgrade_database : l_failures=0

An upgrade window has been successfully ended.

DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT * FROM v$timezone_file;

FILENAME VERSION CON_ID

-------------------- ---------- ----------

timezlrg_31.dat 31 0

SQL>

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

SQL>

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL>

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

Purge Recyclebin after Time Zone Change

Enable all the jobs which disabled:

Set COMPATIBALE parameter value to 18.0.0

Warning: If the value of COMPATIBLE parameter is changed to 18.0.0 then if for some reasons database needs to be downgraded to 12.1.0.2 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.

SQL> create spfile='+DATA/ORCL/PARAMETERFILE/spfileORCL.ora' from pfile='/u01/app/oracle/product/18.3.0.0/dbs/initORCL.ora';

File created.

[oracle@rac6 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Oct 19 20:25:05 2020

Version 18.3.0.0.0

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1174403152 bytes

Fixed Size 8656976 bytes

Variable Size 436207616 bytes

Database Buffers 721420288 bytes

Redo Buffers 8118272 bytes

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string +DATA/ORCL/PARAMETERFILE/spfil

eorcl.ora

SQL>

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter comp

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cell_offload_compaction string ADAPTIVE

compatible string 12.1.0.2.0

db_index_compression_inheritance string NONE

inmemory_prefer_xmem_memcompress string

nls_comp string BINARY

noncdb_compatible boolean FALSE

plsql_v2_compatibility boolean FALSE

SQL>

SQL> ALTER SYSTEM SET COMPATIBLE = '18.0.0' SCOPE=SPFILE;

Finally to check the component and version using dba_registry view.

Standby Sever rac7:

Modify /etc/oratab file manually

[oracle@rac7 ~]$ vi /etc/oratab

ORCLDG:/u01/app/oracle/product/18.3.0.0:N

:wq

[oracle@rac7 ~]$ . oraenv

ORACLE_SID = [ORCLDG] ? ORCLDG

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac7 ~]$

[oracle@rac7 ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/18.3.0.0

Set environmental variables

Copy init.ora & pwd file to new ORACLE_HOME

[oracle@rac7 dbs]$ cp initORCLDG.ora /u01/app/oracle/product/18.3.0.0/dbs

[oracle@rac7 dbs]$

[oracle@rac7 dbs]$ cp orapwORCLDG /u01/app/oracle/product/18.3.0.0/dbs

[oracle@rac7 dbs]$

[oracle@rac7 dbs]$ cp orapwORCL /u01/app/oracle/product/18.3.0.0/dbs

[oracle@rac7 dbs]$

Start Standby from new ORACLE_HOME

SQL> startup nomount;

Start Redo Apply Process from Primary Server – rac6

SQL> alter system set log_archive_dest_state_2='enable' scope=both;

Mount the Standby Database rac7:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

[oracle@rac7 admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Oct 19 22:58:15 2020

Version 18.3.0.0.0

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL>

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Database: 18c
Error code: ORA-16484
Description: compatibility setting is too low
Cause: The compatibility setting in the redo log transported to this standby database exceeded the current compatibility setting.
Action: Check the compatibility setting on the primary and standby database, and increase the compatibility setting in the initialization parameter file on the standby database accordingly.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string +DATA/DB_UNKNOWN/PARAMETERFILE

/spfile.276.1034520961

SQL>

SQL> ALTER SYSTEM SET COMPATIBLE = '18.0.0' SCOPE=SPFILE;

System altered.

SQL>

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> exit

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

[oracle@rac7 admin]$

[oracle@rac7 admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Oct 19 23:03:08 2020

Version 18.3.0.0.0

Connected to an idle instance.

SQL> startup mount;

SQL> show parameter COMPATIBLE

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

compatible string 18.0.0

noncdb_compatible boolean FALSE

SQL>

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> SELECT ARCH.THREAD# "Thread",

ARCH.SEQUENCE# "Last Sequence Received",

APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD#; 2 3 4 5 6 7 8

Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

1 168 121 47

Open Physical Standby and verify upgrade

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL>

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL>

SQL> select instance_name,version,status from v$instance;

INSTANCE_NAME VERSION STATUS

---------------- ----------------- ------------

ORCLDG 18.0.0.0.0 OPEN

SQL>

SELECT to_char(action_time, 'DD-MON-YYYY HH:MI:SS AM') upgrade_date,comments source_version, version Target_Version

FROM dba_registry_history WHERE action = 'UPGRADE';

UPGRADE_DATE SOURCE_VERSION TARGET_VERSION

----------------------- --------------------------------------------- --------------------

19-OCT-2020 05:02:21 PM Upgraded from 12.1.0.2.0 to 18.3.0.0.0 18.0.0.0.0

SQL>select comp_name,version,status from dba_registry;

[oracle@rac6 ~]$ srvctl status database -d ORCL

PRCD-1229 : An attempt to access configuration of database ORCL was rejected because its version 12.1.0.2.0 differs from the program version 18.0.0.0.0. Instead run the program from /u01/app/oracle/product/12.1.0.2/db_1.

[oracle@rac6 ~]$

[oracle@rac6 ~]$ srvctl status database -d ORCL

[oracle@rac6 ~]$

[oracle@rac6 ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/18.3.0.0

[oracle@rac6 ~]$

[oracle@rac6 ~]$ srvctl add database -d ORCL -o /u01/app/oracle/product/18.3.0.0

PRCD-1000 : Database ORCL already exists

PRCR-1086 : resource ora.orcl.db is already registered

[oracle@rac6 ~]$

[oracle@rac6 ~]$ srvctl remove database -d ORCL

[oracle@rac6 ~]$

[oracle@rac6 ~]$ pwd

/home/oracle

[oracle@rac6 ~]$ srvctl upgrade database -d ORCL -o /u01/app/oracle/product/18.3.0.0

[oracle@rac6 ~]$

[oracle@rac6 ~]$ srvctl status database -d ORCL

Database is not running.

[oracle@rac7 ~]$ srvctl add database -d ORCLDG -o /u01/app/oracle/product/18.3.0.0

[oracle@rac7 ~]$

[oracle@rac7 ~]$ srvctl config service -d ORCLDG

[oracle@rac7 ~]$

[oracle@rac7 ~]$ srvctl config database -d ORCLDG

Database unique name: ORCLDG

Database name:

Oracle home: /u01/app/oracle/product/18.3.0.0

Oracle user: oracle

Spfile:

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups:

Services:

OSDBA group:

OSOPER group:

Database instance: ORCLDG

[oracle@rac7 ~]$

Details	Primary ( RAC6)	Standby (RAC7)	Remark
OS version	Oracle Linux Server release 7.6	Oracle Linux Server release 7.6	Completed
ASM Version	18.3.0.1	18.3.0.1
ASM_HOME	/u02/app/18.3.0/grid	/u02/app/18.3.0/grid
Database Name	ORCL - ORCL (Instance)	ORCL - ORCLDG (Instance)
ORACLE_HOME	/u01/app/oracle/product/12.1.0.2/db_1	/u01/app/oracle/product/12.1.0.2/db_1
Upgrade from 12c database to 18c in a Data Guard environment
Database Name	ORCL - ORCL (Instance)	ORCL - ORCLDG (Instance)	Completed
ORACLE_HOME	/u01/app/oracle/product/18.3.0.0	/u01/app/oracle/product/18.3.0.0	Completed

Amit's Oracle DBA Blog

Disclaimer

Tuesday, 20 October 2020

Upgrade Oracle ASM database from 12c to 18c in Data Guard environment

Actual UPGRADE:

1 comment:

How to recovery PDB when PDB database is dropped in Oracle

Labels