Durham Oracle

Not something I expected to do but was asked to download some 19c dbs after the dev lot hit an issue with 19 and wanted to go back to 12!

Some quick notes as I don't expect ever to do this again - maybe 😉

[oracle@ora02 dbs]$ . oraenv

Pick the correct 19c databases

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

[oracle@ora02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 26 09:06:05 2020

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> create pfile='$ORACLE_HOME/dbs/initcms.ora' from spfile ** change as approp.

2 /

File created.

Shutdown immediate and change init file removing the __unified pga line

Copy init’dbname’.ora to 12c home/dbs/init’dbname’.ora - 12c will need this to startup

e.g.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

cp $ORACLE_HOME/dbs/initdb1.ora /u01/app/oracle/product/12.2.0/dbhome_1/dbs/.

Then

SQL> startup downgrade;

ORACLE instance started.

Total System Global Area 2147481656 bytes

Fixed Size 8898616 bytes

Variable Size 553648128 bytes

Database Buffers 1577058304 bytes

Redo Buffers 7876608 bytes

Database mounted.

Database opened.

SQL> set termout on serverout on echo on timing on

SQL> spool /home/oracle/downgrade_db1.log

Run the grant admin and the clean audit or you will see error when running the

ERROR at line 1:

ORA-20001: Downgrade cannot proceed - Unified Audit Trail data exists.Please

clean up the data first using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL.

ORA-06512: at line 75

SQL> grant ADMINISTER DATABASE TRIGGER to SYSTEM;

Grant succeeded.

SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);

PL/SQL procedure successfully completed.

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

Approx 5 mins…

…….

SQL>

SQL> Rem ***********************************************************************

SQL> Rem END catdwgrd.sql

SQL> Rem ***********************************************************************

SQL>

SQL>shutdown immediate;

SQL> exit

When migrating to 19c the timezone had been upgraded so had to do this to downgrade or hit issues.

export ORA_TZFILE=/u01/app/oracle/product/12.2.0/dbhome_1/oracore/zoneinfo/timezone_26.dat

set environment to Oracle 12 and change sid to the correct database i.e. db1

oracle@ora02 dbs]$ export

ORA_TZFILE=/u01/app/oracle/product/12.2.0/dbhome_1/oracore/zoneinfo/timezone_26.dat

[oracle@usze2qmbtora02 dbs]$ sqlplus / as sysdba

exit

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 26 09:51:18 2020

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

Connected to an idle instance.

SQL> startup upgrade;

Remember you need to use the 19c init.file copied earlier with the __unified parameter removed or you will hit

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initdb1.ora'

SQL> startup upgrade pfile='/u99/media/init_db1.ora'

LRM-00101: unknown parameter name '__unified_pga_pool_size'

ORA-01078: failure in processing system parameters

SQL> startup upgrade -> need init file copied earlier

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 8622776 bytes

Variable Size 654314824 bytes

Database Buffers 1476395008 bytes

Redo Buffers 8151040 bytes

Database mounted.

Database opened.

SQL>

SQL> set termout on echo on timing on

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

Check that this correctly

@?/rdbms/admin/utlrp.sql

SELECT comp_name, status, substr(version,1,10) as version

from dba_server_registry order by modified

COMP_NAME STATUS
----------------------------------- ----------------------------------------

VERSION
----------------------------------------

Oracle Real Application Clusters OPTION OFF

12.2.0.1.0

Oracle Database Catalog Views VALID

12.2.0.1.0

Oracle Database Packages and Types VALID

12.2.0.1.0

Oracle XML Database VALID

12.2.0.1.0

Oracle Workspace Manager VALID

12.2.0.1.0

JServer JAVA Virtual Machine VALID

12.2.0.1.0

Oracle Database Java Packages VALID

12.2.0.1.0

Oracle Text VALID

12.2.0.1.0

Oracle Multimedia VALID

12.2.0.1.0

Oracle XDK VALID

12.2.0.1.0

OLAP Analytic Workspace VALID

12.2.0.1.0

Spatial VALID

12.2.0.1.0

Oracle OLAP API VALID

12.2.0.1.0

Oracle Label Security VALID

12.2.0.1.0

Oracle Database Vault VALID

12.2.0.1.0

15 rows selected.

Shutdown - (change oratab to v12)

Set environment to grid 19c

. oraenv

+ASM

Remove database from srvctl

[oracle@usze2qmbtora02 ~]$ srvctl remove database -d ora01

This will remove it from the /etc/oratab so manually put it back as 12

Now we need add the database back as 12c and start it as 12c

Still using the grid 19c environment (19c grid to 12 for another day!)

[oracle@usze2qmbtora02 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/srvctl add database -d oradb1 -o /u01/app/oracle/product/12.2.0/dbhome_1

[oracle@usze2qmbtora02 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/srvctl start database -db oradb1

Run “crsctl stat res -t” and check the db is up and running – shows as 12c but under a 19c grid control - bit odd but hey ho.

After some research found this :

https://www.youtube.com/watch?v=Jm7g_RAoa4Y

After a test box filesystem had filled up.

Cleared down and checked Golden Gate for any issues.

One of the extracts had abended.

EXTRACT ABENDED OAM_X 00:00:00 00:30:03

Checked ggserr.log file :

020-07-06T10:26:37.295-0400 ERROR OGG-01416 Oracle GoldenGate Capture for Oracle, oam_x.prm: File ./dirdat/oam/et000003576, with format RELEASE 9.0/9.5, does not match current format specification of RELEASE 12.3. Modify the parameter file to specify format RELEASE 9.0/9.5 or issue ETROLLOVER prior to restart.
2020-07-06T10:26:37.360-0400 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, oam_x.prm: PROCESS ABENDING.

Quick search on the web and followed the instructions here :

OGG-01411 – Cannot convert input file ./dirdat/xx with format RELEASE 9.0/9.5 to output file ./dirdat/zz

[oracle@usze2qmbtora02 gghome_app]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 9 2017 00:51:03
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (usze2qmbtora02) 1> info OAM_X

EXTRACT OAM_X Last Started 2020-07-06 10:51 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:43:51 ago)
Log Read Checkpoint Oracle Redo Logs
2020-07-06 10:13:28 Seqno 1909, RBA 236535824
SCN 0.209622245 (209622245)

GGSCI (usze2qmbtora02) 2> stop OAM_X
EXTRACT OAM_X is already stopped.

GGSCI (usze2qmbtora02) 3> alter OAM_X etrollover

2020-07-06 10:58:28 INFO OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.
EXTRACT altered.

GGSCI (usze2qmbtora02) 4> alter OAM_X begin 2020-07-06 10:13:28
EXTRACT altered.

GGSCI (usze2qmbtora02) 5> start OAM_X

Sending START request to MANAGER ...
EXTRACT OAM_X starting

GGSCI (usze2qmbtora02) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING OAM_X 00:45:17 00:00:14

GGSCI (usze2qmbtora02) 7> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING OAM_X 00:00:01 00:00:00

Strange why only the one extract failed - will investigate further.

Durham Oracle

Downgrade 19c to 12c

RAC on Azure

OGG-01416 OGG-01668 Process Abending