Tuesday, June 27, 2017

Oracle : Downgrade 12c Databases to 11g Using Flashback Restore Point

Downgrading Database Without Executing catdwgrd.sql 


My Database was upgraded to 12.1.0.2 but due to performance issues, application team requested us to downgrade the database to 11.2.0.4 (previous version). 

The easiest way I could think of was to flashback the database using the restore point I created before upgrading the database to 12c


Below statements should hold true to flashback the database to the earlier version.
1) We have previous version binaries still present
2) A guarantee restore point created on earlier version before the upgrade is done
3) The compatible parameter is not changed. 


Steps to downgrade the database from 12.1.0.2 to 11.2.0.4


From 12.1.0.2 binaries

Check the restore point if exists

SQL> select NAME,SCN,TIME from v$restore_point;

NAME                     SCN                          TIME
--------------------------------------------  ---------------------
BEFORE_UPGRADE       1.5096E+13                22-JUN-17 01.55.58.000000000 PM


Crosscheck if the compatible parameter is not changed

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
noncdb_compatible                    boolean     FALSE


Mount the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Database mounted.

Flashback database to the restore point created

SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE;

Flashback complete.


Alert log shows below

Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Mon Jun 26 10:59:03 2017
FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE
Mon Jun 26 10:59:05 2017
Flashback Restore Start
Mon Jun 26 10:59:40 2017
Flashback Restore Complete
Flashback Media Recovery Start
 Started logmerger process
Mon Jun 26 10:59:41 2017
Parallel Media Recovery started with 12 slaves
Flashback Media Recovery Log /opt/app/prod1/oraflsh01/PROD1/archivelog/2017_06_22/o1_mf_1_1236_dnrds518_.arc
Mon Jun 26 10:59:42 2017
Incomplete Recovery applied until change 15095662683591 time 06/22/2017 13:56:00
Mon Jun 26 10:59:42 2017
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE

Once flashback completes shutdown the database

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


Now mount the database from older binaries, [in our case 11.2.0.4], set your environment to point to 11.2.0.4 binaries 

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.1379E+10 bytes
Fixed Size                  2262656 bytes
Variable Size            3221227904 bytes
Database Buffers         1.8119E+10 bytes
Redo Buffers               36073472 bytes
Database mounted.

Open Resetlogs

SQL> alter database open resetlogs;

Database altered.

Now check the DB version, it should show  11.2.0.4

SQL> select comp_name, version, status from dba_registry;

COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle XML Database
11.2.0.4.0                     VALID

Oracle Expression Filter
11.2.0.4.0                     VALID

Oracle Rules Manager
11.2.0.4.0                     VALID


COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle Workspace Manager
11.2.0.4.0                     VALID

Oracle Database Catalog Views
11.2.0.4.0                     VALID

Oracle Database Packages and Types
11.2.0.4.0                     VALID


COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
JServer JAVA Virtual Machine
11.2.0.4.0                     VALID

Oracle XDK
11.2.0.4.0                     VALID

Oracle Database Java Packages
11.2.0.4.0                     VALID


I hope these steps help you.. 


Saturday, June 10, 2017

ORA-01624 + ORA-00312 : While dropping Online Redo Logs

I had to resize my Online Redo Logs, but to do that I had to drop old Redo Logs after adding new ones


SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance testDB (thread 1)
ORA-00312: online log 1 thread 1:
'/opt/app/testDB/oraredo01/testDB/redo_1a.log'
ORA-00312: online log 1 thread 1:
'/opt/app/testDB/oraredo02/testDB/redo_1b.log'

I was not able to drop the old Redo Logs as all were showing up as Active, log switches didnt help

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1         31  536870912        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         2          1         29  536870912        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         3          1         30  536870912        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         4          1         32 1073741824        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         5          1         33 1073741824        512          2 NO
CURRENT             1.5060E+13 10-JUN-17   2.8147E+14

         6          1         28 1073741824        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


6 rows selected.


To mark them inactive, do a checkpoint on the database

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1         31  536870912        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         2          1         29  536870912        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         3          1         30  536870912        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         4          1         32 1073741824        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         5          1         33 1073741824        512          2 NO
CURRENT             1.5060E+13 10-JUN-17   2.8147E+14

         6          1         28 1073741824        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


6 rows selected.

Now we can drop the old redo logs

SQL> alter database drop logfile group 1;

Database altered.

Monday, May 15, 2017

Additional space required is more than max allowed space for these datafiles - During 12c Upgrades

I was upgrading a 11gR2 database to 12.1.0.2, and faced following error during upgrade using DBUA:

Upgrade Storage Check  

 Disk space usage summary  
/opt/app/testdb/oradata01/ has enough space. 
Required space is 0 MB , available space is 380732 MB.  

Details: Space usage summary  Space usage reason: Upgrade  

Tablespace Name: SYSTEM 
Additional space required: 0MB Datafile: /opt/app/testdb/oradata01/testdb/system_01.dbf Auto Extensible: No  

Tablespace Name: SYSAUX 
Additional space required: 0MB Datafile: /opt/app/testdb/oradata01/testdb/sysaux_01.dbf Auto Extensible: No  

Tablespace Name: UNDO 
Additional space required: 0MB Datafile: /opt/app/testdb/oradata01/testdb/undo_01.dbf Auto Extensible: Yes  

Tablespace Name: TEMP 
Additional space required: 0MB Datafile: /opt/app/testdb/oradata01/testdb/temp_01.dbf Auto Extensible: Yes  

Disk space usage summary  
/opt/app/testdb/oradata01/ has enough space. 
Required space is 0 MB , available space is 380732 MB.  

Additional space required is more than max allowed space for these datafiles  

Tablespace Name: 
TEMP Datafile: /opt/app/testdb/oradata01/testdb/temp_01.dbf 
Additional space required: 0 MB 
Maximum allowed space on datafile: -27647 MB  

This is because the maximum value of the datafile is being shown with a lower value than required and the solution for this error is to increase the maxsize limit (MaxBytes) to a value greater than the datafile size.

Example :
alter database tempfile '/opt/app/testdb/oradata01/testdb/temp_01.dbf' autoextend on next 10M maxsize 32767M; 

And then reprocess the DBUA:

Hope this resolves your issue!!


Monday, February 13, 2017

ORA-38784 + ORA-01153 + ORA-16000 : During restore point creation/Deletion on Standby Database

Create or Remove Restore Point on Active Dataguard Standby databases



It is required to stop the managed recovery on the standby, otherwise the following error will happen:

SQL> create restore point restore_point_2017 guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'restore_point_2017'.
ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database cancel;
SQL> create restore point ;



To drop the restore point on standby7 database which is running in Active Dataguard, database shutdown is required

We get below error when we try to drop the restore point in read only mode


SQL> DROP RESTORE POINT restore_point_2017;
DROP RESTORE POINT restore_point_2017
*
ERROR at line 1:
ORA-16000: database open for read-only access


Check the restore Point Name

SELECT NAME,SCN, TIME,DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM GV$RESTORE_POINT;

NAME                        SCN TIME                                     DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- ---------------------------------------- --------------------- --- ------------
restore_point_2017          1.4933E+13 17-Jan-17 11.33.52.000000000 PM                              2 YES   2.4934E+11


sqlplus / as sysdba
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup mount
ORACLE instance started.

Database mounted.
SQL> DROP RESTORE POINT restore_point_2017;

Restore point dropped.

SET LINESIZE 200
SET PAGESIZE 200
COL NAME FORMAT A20
COL TIME FORMAT A40
SET TRIMS ON
SELECT NAME,SCN, TIME,DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM GV$RESTORE_POINT;

no rows selected

SQL> alter database open read only;

Database altered.

Restart the Managed Recovery
SQL> alter database recover managed standby database using current logfile disconnect; 

Sunday, December 18, 2016

ORA-01516: nonexistent log file, data file, or temporary file - Oracle while resizing tempfile

Got below error while resizing the tempfile. 


Error:

sql> alter database tempfile '/user/app/db_home1/data3/db_home1/temp1.dbf' resize 12001m;

alter database tempfile '/user/app/db_home1/data3/db_home1/temp1.dbf' resize 12001m
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "/user/app/db_home1/data3/db_home1/temp1.dbf"

Fix: 

sql> alter tablespace temp shrink tempfile '/user/app/db_home1/data3/db_home1/temp1.dbf' keep 12G;

Tablespace altered.

Monday, September 19, 2016

ORA-00600: internal error code, arguments: [13013], Table/Index row count mismatch ; SQL error 600 occurred when updating duplicate row in table.

My Oracle 11.2.0.4 database started reporting Ora-600 errors

Upon checking the alert log and Oracle Support (Doc ID 1438920.1) , 

Turns out the Index was corrupted.. Ran the Analyze table command to analyze the problem table

SQL> analyze table APP_USER.ENTRY_HIT VALIDATE STRUCTURE CASCADE;
analyze table APP_USER.ENTRY_HIT VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

Trace file shows below

Table/Index row count mismatch
table 384001 : index 384028, 0
Index root = tsn: 7 rdba: 0x02400a5a

The error says the indexes are still bad.

If you run the query through SQL*PLUS it will ask for a rdba number. Enter the value from error message '0x02400a5a' (no quotes). This will return a file number and a block number.

SQL> SELECT dbms_utility.data_block_address_file(
  2           to_number(trim(leading '0' from
  3  replace('&&rdba','0x','')),'XXXXXXXX')
  4         ) AS rfile#,
  5         dbms_utility.data_block_address_block(
         to_number(trim(leading '0' from
  6    7  replace('&&rdba','0x','')),'XXXXXXXX')
  8         ) AS block#
  9  FROM dual;
Enter value for rdba: 0x02400a5a
old   3: replace('&&rdba','0x','')),'XXXXXXXX')
new   3: replace('0x02400a5a','0x','')),'XXXXXXXX')
old   7: replace('&&rdba','0x','')),'XXXXXXXX')
new   7: replace('0x02400a5a','0x','')),'XXXXXXXX')

    RFILE#     BLOCK#
---------- ----------
         9       2650

Next run the following query:

select owner, segment_name, segment_type 
from  dba_segments 
where header_file = <rfile#>
  and header_block = <block#>;

This will give you the offending index to be dropped and recreated.


Wednesday, September 7, 2016

ORA-00245: control file backup failed; target is likely on a local file system

Error : ORA-00245: control file backup failed; target is likely on a local file system

Cause : the control file is saved on local file system., in case of RAC databases the control file snapshot should be saved on the shared location.

Solution : Make change in RMAN to point the control file snapshot to be saved under shared location

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DB4_ORADATA/snapcf_db4.f';