Month: June 2019

RMAN recovery catalog registration errors RMAN-03009 ORA-01403

arcsdegeoLeave a comment

In a new Oracle 12cR2 database, i was attempting to register it with the Recovery Catalog:

RMAN> register database;

starting full resync of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of register command at 06/24/2019 06:56:42

RMAN-03014: implicit resync of recovery catalog failed

RMAN-03009: failure of full resync command on default channel at 06/24/2019 06:56:42

ORA-01403: no data found

In order to solve this problem I needed to re-create the control file:

SQL> alter database backup controlfile to trace as ‘/orcl/backup/CTL_DEF.txt’;

SQL> exit;

 

// A text file with control file definition is created, I will only use the NORESETLOG section and create a sql based file as shown below (this is template):

vi create_controlfile.sql

CREATE CONTROLFILE REUSE DATABASE “DBT11” NORESETLOGS  ARCHIVELOG

MAXLOGFILES 25

MAXLOGMEMBERS 5

MAXDATAFILES 850

MAXINSTANCES 1

MAXLOGHISTORY 18697

LOGFILE

GROUP 1 (

‘/opt/DBT11/t11LOGA1.log’,

‘/opt/DBT11/t11LOGA2.log’

) SIZE 256M BLOCKSIZE 512,

GROUP 2 (

‘/opt/DBT11/t11LOGB1.log’,

‘/opt/DBT11/t11LOGB2.log’

) SIZE 256M BLOCKSIZE 512,

GROUP 3 (

‘/opt/DBT11/t11LOGC1.log’,

‘/opt/DBT11/t11LOGC2.log’

) SIZE 256M BLOCKSIZE 512

— STANDBY LOGFILE

DATAFILE

‘/data1/DBT11/SYSTEM.ORA’,

‘/data2/DBT11/SYSAUX.ORA’,

‘/data2/DBT11/UNDO010.ORA’,

‘/data3/DBT11/USERD010.ORA’,

‘/data3/DBT11/AMS_TS.DBF’,

‘/data3/DBT11/SSDPROD_TS.DBF’,

‘/data3/DBT11/USERS02.DBF’

CHARACTER SET WE8MSWIN1252

;

 

SQL> STARTUP NOMOUNT

SQL> @create_controlfile.sql

SQL> alter database open;

 

// re-create the temp tablespace as follows:

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/data1/DBT11/TEMP01.DBF’

SIZE 6557M REUSE AUTOEXTEND ON ;

 

Now try registering the database again:

rman target /

RMAN> connect catalog catalog01/XXXXXX@catalogdb

RMAN> register database;

 

 

 

 

 

 

 

 

 

 

 

 

Oracle Database Migration for ESRI ArcGIS Geo-Database

arcsdegeoLeave a comment

Oracle database platform is a popular database to host ESRI ArcGIS Geo-Database, so I am going here to list the best approach to migrate your database for ESRI ArcGIS SDE:

From the source database, use expdp to fully export the Oracle database using SDE account (you need to temporary grant “DBA” role for such action):

expdp sde/XXXX dumpfile=GISDB_FULL%U.DMP directory=EXP_DIR FULL=Y PARALLEL=4 compression=all logfile=FULL_EXP_LOG.log

 

Now, in the target database start importing ONLY SDE schema first (SDE schema holds GIS data metadata and management scripts so its very important that this schema gets imported successfully):

impdp sde/XXXX dumpfile=GISDB_FULL%U.DMP directory=EXP_DIR SCHEMAS=SDE PARALLEL=4 logfile=SDE_IMP.log

After the import, check the log file and make sure that all objects were imported successfully…..you can compare the number of SDE schema objects by executing the following query in both source & target database:

SQL> select count(*) from dba_objects where OWNER=’SDE’;

In my case some objects were not created successfully !

One failed object is indextype ST_SPATIAL_INDEX , to view the definition of the index type from the source database:

SQL> set long 100000

SQL> set head off

SQL> set echo off

SQL> set pagesize 0

SQL> set verify off

SQL> set feedback off

SQL> SELECT dbms_metadata.get_ddl(‘INDEXTYPE’,’ST_SPATIAL_INDEX’,’SDE’) FROM dual;

 

I will execute the definition of the indextype:

 

CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX” FOR

“SDE”.”ST_BUFFER_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, NUMBER

),

“SDE”.”ST_CROSSES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER)

,

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER,

VARCHAR2),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),

 

“SDE”.”ST_EQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ORDERINGEQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_OVERLAPS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_RELATE” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),

“SDE”.”ST_TOUCHES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_WITHIN” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”)

USING “SDE”.”ST_DOMAIN_METHODS”

WITH LOCAL RANGE PARTITION;

 

The following error will be thrown:

ORA-29829: implementation type does not exist

After digging….the following two “types” are missing:

ST_DOMAIN_METHODS

ST_DOMAIN_STATS

 

Take their SQL definition from the source database and create them in the destination, and try re-creating the indextype ST_SPATIAL_INDEX..it should be created successfully.

 

Now after ensuring SDE schema is fully mimicking the source database, start importing other schemas (user geodatabase schemas) and test it using ArcGIS Desktop.

Oracle Database New Auto-Upgrade Tool Simulation

arcsdegeo3 Comments

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