Oracle Database New Auto-Upgrade Tool Simulation

Oracle has released a new tool “Auto-Upgrade” to automate the process of upgrading Oracle Database Management System. This tool is much needed now, as the upgrade process is “lengthy” procedure ,and having such automation capability will shorten the period of upgrading any organization’s Large Oracle database infrastructure.

The tool will let you upgrade from 11g (11.2.0.4) as the “source” minimum version to either 12cR2, 18c , 19c releases as “target” version.

 

target versions

In this simulation of Oracle Auto-Upgrade tool, I am simulating the upgrade from 18c (18.6) to 19c (19.3) release.

Pre-requisite checks:

Check Java version under Oracle Home, it should be version 8. Java 8 is available in Oracle database homes for Oracle 12cR2 release and beyond. If your Oracle release is 11g (11.2.0.4) either install java 8 to your Oracle Home or use the “target” release java Oracle Home directory.

To check which java release you have , execute the following command:

$ORACLE_HOME/jdk/bin/java -version

java8

Since my “source” database is 18c , so its expected to have java 8 version available.

Preparations before upgrading the database:

I will install and setup Oracle 19c binaries within the same database server:

Will prepare the profile:

cd

cp .bashrc .bashrc_original

cp .bashrc .bashrc_19c

// edit the 19c profile with new Oracle Home

vi .bashrc_19c

profile

Then overwrite it the main profile:

cp .bashrc_19c .bashrc

source .bashrc

I will unzip the 19c oracle binaries under the new Oracle Home Path:

unzip LINUX.X64_193000_db_home.zip -d /opt/oracle/product/19c/dbhome_2

And then I will perform silent-installation:

./runInstaller -ignorePrereq -waitforcompletion -silent -responseFile /opt/oracle/product/19c/dbhome_2/install/response/db_install.rsp

*** run root.sh after successful installation

/opt/oracle/product/19c/dbhome_2/root.sh

Reset your profile to point back to 18c database binaries, and startup the 18c database.

Auto-Upgrade Setup:

*** go to the location where you downloaded autoupgrade.jar file , the file can be downloaded from Oracle Support (Doc ID 2485457.1) , and create the configuration file

cd /vagrant/autoupgrade

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -create_sample_file config

config file

*** create your sample configuration file – the following is a TEMPLATE I have used:

cp sample_config.cfg my_config.txt

vi my_config.txt

#

# sample config file

#

#

# Global configurations

#

# This directory will include the following

#   (1) AutoUpgrade’s global directory

#   (2) Any logs, not directly tied to a job

#   (3) Config files

#   (4) progress.json and status.json

global.autoupg_log_dir=/vagrant/autoupgrade/autoupgrade_log

 #

# Database number 1

#

upg1.dbname=ORCLCDB

upg1.start_time=NOW

upg1.source_home=/opt/oracle/product/18c/dbhome_1

upg1.target_home=/opt/oracle/product/19c/dbhome_2

upg1.sid=ORCLCDB

upg1.log_dir=/vagrant/autoupgrade/autoupgrade_log

upg1.upgrade_node=localhost

upg1.target_version=19

#upg1.run_utlrp=yes

#upg1.timezone_upg=yes

*** you can execute the following help command to check the options available:

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -help

help-command

The following image illustrate different auto-upgrade modes:

auto-upgrade-modes

I will start with “analyze” mode:

The analyze mode will basically perform the pre-checks required in for the upgrade to succeed.

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config /vagrant/autoupgrade/my_config.txt -mode analyze -noconsole

*** please note that -noconsole option specified in the command, will make AutoUpgrade runs based ONLY on the settings in the configuration file, without requiring console input.

analyze-mode

The command executes successfully and the Job number is 100 !

I will go to the location where the logs are generated to grab the produced HTML file:

cd /vagrant/autoupgrade/autoupgrade_log/ORCLCDB/100/prechecks

Inspecting orclcdb_preupgrade.html file….the report will be as shown below:

htmil_report

In the report you will see different severity levels: INFO , WARNING , ERROR , RECOMMEND

Of course “ERROR” indicates that you need Fix the issue before attempting to upgrade the database.

I will explore now “FIXUPS” mode:

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config /vagrant/autoupgrade/my_config.txt -mode fixups -noconsole

fixups_mode

I will configure flashback, and guaranteed restore point:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST =’/opt/oracle/product/18c/dbhome_1/fra’ SCOPE=BOTH;

SQL> create restore point before_19c_upgrade_emad guarantee flashback database;

I will explore now “UPGRADE” mode:

Shutdown your current database, and switch your environment variables to point to the target binaries (in my case 19c).

Don’t forget to copy your init.ora file from old binaries to new binaries…. also anything under $ORACLE_HOME/network/admin from old binaries to new binaries for illustration:

cp -r /opt/oracle/product/18c/dbhome_1/dbs/*  $ORACLE_HOME/dbs

cp -r /opt/oracle/product/18c/dbhome_1/network/admin/*  $ORACLE_HOME/network/admin/

sqlplus / as sysdba

SQL> startup upgrade

exit;

Then, execute the following command to upgrade:

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config /vagrant/autoupgrade/my_config.txt -mode upgrade -noconsole

upgrade_mode

In my simulation I had a problem with FRA size…so I fixed it and the process completed successfully.

Important Remark: in “upgrade” mode No post-upgrade actions will be performed. The post-upgrade actions are expected to be handled by the DBA. For example timezone upgrade will be implemented manually by the DBA. [check the image of different modes at the beginning of the blog and what stages each mode will cover, “deploy” mode will run all stages]

In case you want to roll-back to previous release (in my case 18c):

shutdown your current database(19c) and listener.

set your environment variables to point to 18c binaries.

sqlplus / as sysdba;

SQL> select current_scn from v$database;

SQL> shutdown immediate;

SQL> startup mount;

SQL> select * from v$restore_point;

SQL> flashback database to restore point before_19c_upgrade_emad;

SQL> alter database open resetlogs;

SQL> select current_scn from v$database;

 

I hope this blog post about Oracle Auto-Upgrade tool will be a good starting point for your to try it out !

Resources/References:

The new AutoUpgrade utility in Oracle 19c

AutoUpgrade Tool (Doc ID 2485457.1)

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/using-autoupgrade-oracle-database-upgrades.html#GUID-71883C8C-7A34-4E93-8955-040CB04F2109

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Oracle datapatch errors ORA-29913 ORA-25153

After successfully applying the database patch on the binary, the next step is to run the datapatch, however when running it….it failed with below errors:

./datapatch –verbose

SQL Patching tool version 12.1.0.2.0 Production on Wed May  1 10:03:02 2019

Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /orcl/oradbx26/cfgtoollogs/sqlpatch/sqlpatch_79352_2019_05_01_10_03_02/sqlpatch_invocation.log

Connecting to database…OK

Bootstrapping registry and package to current versions…done

Queryable inventory could not determine the current opatch status.

Execute ‘select dbms_sqlpatch.verify_queryable_inventory from dual’

and/or check the invocation log

/orcl/oradbx26/cfgtoollogs/sqlpatch/sqlpatch_79352_2019_05_01_10_03_02/sqlpatch_invocation.log

for the complete error.

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log

/orcl/oradbx26/cfgtoollogs/sqlpatch/sqlpatch_79352_2019_05_01_10_03_02/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Wed May  1 10:03:08 2019

Solution:

After reviewing the log file sqlpatch_invocation.log:

verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table

so I have executed the following query:

SQL> select * from OPATCH_XML_INV ;

ERROR:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-25153: Temporary Tablespace is Empty

So this is an indication of temporary tabelspace issue, so I have first created a “new” temporary tablespace:

SQL> CREATE TEMPORARY TABLESPACE TEMP_DATA_02 TEMPFILE

  ‘/oradata1/oradbx26/TEMPTD01.dbf’ SIZE 120M AUTOEXTEND ON

TABLESPACE GROUP ”

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Then, I have made the new temporary tablespace the default one:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_DATA_02;

And finally dropped the old temporary tablespace:

SQL> DROP TABLESPACE TEMP_DATA_01 INCLUDING CONTENTS AND DATAFILES;

 

I hope this helps someone !

 

Oracle 18c new feature: ALTER SYSTEM CANCEL SQL

In the past when you wanted to terminate an Oracle database session that is causing a problem we used the classical SQL command:

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In 18c a new command is introduced , that will cancel the SQL statement being executed in the session without actually terminating the session:

SQL> ALTER SYSTEM CANCEL SQL ‘SID, SERIAL,SQLID’;

To simulate:

To find the 3 values SID, SERIAL, and SQLID you can use the following query:

SELECT s.inst_id,

s.sid,

s.serial#,

s.sql_id,

p.spid,

s.username,

s.program

FROM   gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != ‘BACKGROUND’;

PIC1

SQL> ALTER SYSTEM CANCEL SQL ‘874,51416,a5ks9fhw2v9s1’;

PIC2

The user session will generate the following error:

ORA-01013: user requested cancel of current operation

However, the user will still continue working fine (his session will not be terminated as shown below)

PIC3

 

 

 

 

 

 

ORA-00600 & ORA-48254 when upgrading to Oracle 12cR2

while upgrading a database from Oracle 12cR1 to 12cR2….the following errors were thrown in the alert log file:

Errors in file /oraclex/orcl/diag/rdbms/orcl/orcl/trace/orcl_m000_125839.trc (incident=708075):

ORA-00600: internal error code, arguments: [Migrate Relation Failure], [], [], [], [], [], [], [], [], [], [], []

ORA-48254: Migration Error [dbgrmmdmr_max_length_change] [PDB_SPACE_MGMT] [2] [66] [32] [9]

Incident details in: /oraclex/orcl/diag/rdbms/orcl/orcl/incident/incdir_708075/orcl_m000_125839_i708075.trc

Use ADRCI or Support Workbench to package the incident.

 

This is due to old ADR setup, so you need to reset ADR location

SQL> show parameter diagnostic_dest
SQL> ALTER SYSTEM SET diagnostic_dest=/orcl/ora23/diagnostic/ scop=spfile;

ORA-00600: internal error code, arguments: [qerixAddNotNullStopKeyPredicate:1] in the alert log !

one of the databases i am supporting ( on version 12cR1 12.1.0.2) was throwing ORA-600 in the alert log:

Archived Log entry 94742 added for thread 1 sequence 48014 ID 0x6a20c315 dest 2:
Errors in file /oracle/orcl12/diag/rdbms/orcl12/orcl12/trace/orcl12_j003_115141.trc (incident=582200):
ORA-00600: internal error code, arguments: [qerixAddNotNullStopKeyPredicate:1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oraclex/orcl12/diag/rdbms/orcl12/orcl12/incident/incdir_582200/orcl12_j003_115141_i582200.trc
Non critical error ORA-48913 caught while writing to trace file “/oraclex/orcl12/diag/rdbms/orcl12/orcl12/incident/incdir_582200/orcl12_j003_115141_i582200.trc”
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached

Solution:

if this error is infrequent then you can ignore, otherwise as a workaround is to disable the Automatic SQL Tuning Tasks by executing the following:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL );
END;
/

As a consequence, this will disable all automatic SQL tuning tasks but you can still perform “on-demand” SQL tuning to get advice on tuning specific SQL statements.

 

Presenting NoSQL Technology in Imam Abdulrahman Bin Faisal University

I have presented at Imam Abdulrahman Bin Faisal University to Computer Science Students “Introduction to NoSQL Technology

EMAD_SadaIT

Introduction_To_No_SQL

 

 

 

 

physical standby error when applying archive logs ORA-00317

in a physical standby that was out of synchronization….i found that the below errors were thrown:

ORA-00317: file type 0 in header is not log file

ORA-00334: archived log: ‘/stdby/arc/arch_t211_1_972641998_48514.arc’

To resolve this issue:

the error is indicating that the archive log was is corrupted. so i restored the archive log using RMAN:

rman target /

RMAN> restore archivelog logseq=48514;

the archive log restored successfully and then applied on the standby.

hope this helps

LOCAL TEMPORARY TABLESPACE in 12cR2 and 18c

when upgrading a database from 12cR1 to either 12cR2 or 18c , you will notice when querying dba_users view a new column added: LOCAL_TEMP_TABLESPACE

local temp tablespace is most probably used for RAC.

you need to ensure that its not assigned to “SYSTEM” tablesapce, to check this:

SQL>  select username from dba_users where local_temp_tablespace=’SYSTEM’;

if there any….you can set it to temp tablespace as follows:

SQL> alter user C##ADAM LOCAL TEMPORARY TABLESPACE TEMP;

 

Automate Startup/Shutdown Of Oracle Database 12cR2 in Linux Red Hat

if you are not using Grid Infrastructure and Oracle Restart, this guide will help you
automate single instance Oracle database.

create a directory for your shell scripts for example:

mkdir /app/oracle/scripts

1. create a script called setEnv.sh

vi setEnv.sh

# Oracle Settings

export TMP=$ORACLE_BASE/tmp

export TMPDIR=$ORACLE_BASE/tmp

export ORACLE_HOSTNAME=mycomputer.com

export ORACLE_UNQNAME=db12c

export ORACLE_BASE=/orcl/db12

export ORACLE_HOME=/orcl/db12/product/12.2.0.1

export ORACLE_SID=/orcl/db12

export PATH=$PATH:$ORACLE_HOME/bin:/usr/vacpp/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:/$ORACLE_HOME/rdbms/jlib

2. create two scripts called start_all.sh & stop_all.sh

vi start_all.sh

#!/bin/sh

. /app/oracle/scripts/setEnv.sh

export ORAENV_ASK=NO

. oraenv

export ORAENV_ASK=YES

dbstart \$ORACLE_HOME

vi stop_all.sh

#!/bin/sh

. /app/oracle/scripts/setEnv.sh

export ORAENV_ASK=NO

. oraenv

export ORAENV_ASK=YES

dbshut \$ORACLE_HOME

3. edit scripts unde $ORACLE_HOME/bin for dbstart and dbshut by performing the following:

replace ORACLE_HOME_LISTNER=$1

with

ORACLE_HOME_LISTNER=$ORACLE_HOME

and replace $ORACLE_HOME/bin/lsnrctl start >> $LOG 2>&1 &

with your listener name configured in your listener.ora file

$ORACLE_HOME/bin/lsnrctl start listener_db12c >> $LOG 2>&1 &

4. make sure /etc/oratab is reflecting the right oracle home path

5. Ask Linux Admin to create the following file:

vi /lib/systemd/system/dbora.service

[Unit]

Description=Oracle DB 12cR2

After=syslog.target network.target

[Service]

RemainAfterExit=yes

User=oracle

Group=dba

ExecStart=/app/oracle/scripts/start_all.sh

ExecStop=/app/oracle/scripts/stop_all.sh

[Install]

WantedBy=multi-user.target

6. Linux Admin should execute the following:

systemctl daemon-reload

systemctl start dbora.service

systemctl enable dbora.service

8. Ask Linux Admin to reboot the Linux server to TEST automatic startup of the database

Oracle 18c installation in Linux

in this blog post i will describe the necessary steps to install Oracle 18c in Linux Red Hat.

First, pre-requisite steps:

check that all packages required are installed, check the following link as reference:

https://docs.oracle.com/en/database/oracle/oracle-database/18/cwlin/supported-red-hat-enterprise-linux-7-distributions-for-x86-64.html#GUID-2E11B561-6587-4789-A583-2E33D705E498

Kernel values: https://docs.oracle.com/en/database/oracle/oracle-database/18/cwlin/changing-kernel-parameter-values.html#GUID-FB0CC366-61C9-4AA2-9BE7-233EB6810A31

Another way to check pre-requisites is ORACHK tool , this tool can be downloaded from Oracle Support Doc ID 1268927.2.

copy the zip folder downloaded for ORACHK to database file system:

unzip orachk.zip

To check Installation Readiness, execute the following:

./orachk -profile preinstall

To check Upgrade Readiness, execute the following:

./orachk -u -o pre

Second, Installation using GUI:

reconfigure your profile to point to Oracle 18c binaries (new home).

use the following command to unzip the binaries:

unzip -q /oracle/app/LINUX.X64_180000_db_home.zip -d /oracle/ora15/product/18.3

 

cd /oracle/ora15/product/18.3

./runInstaller

11

22

33

44

55

66

77

88

Third, Silent Installation Option:

please note that you need to edit(modify) the file db_reponse_tmp.rsp located under $ORACLE_HOME/install/response

*** checking prerequisites before installation :

$ORACLE_HOME/runInstaller -silent -executePrereqs -responseFile $ORACLE_HOME/install/response/db_reponse_tmp.rsp

*** Silent Installation :

$ORACLE_HOME/runInstaller -silent -responseFile $ORACLE_HOME/install/response/db_reponse_tmp.rsp

 

you will need to run root.sh