This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Category: Oracle

Recover Oracle DB With Missing DataFiles or Missing UNDO or Multiple ResetLogs Issue

If you have run through the Create ControlFile script method (why are you still using this and not the NID utility!!?) but it failed because you were missing one of the datafiles, then you will struggle to open and resetlogs the database due to Oracle errors.
You will then retry the process (maybe with the correct datafiles in place this time) and it will fail because you’ve run resetlogs too many times and the datafiles are all out of sync with the incarnation of the database.

The post also assumes that one of the missing datafiles is required for your UNDO tablespace.
This post is based on the post here: https://dbakevin.blogspot.co.uk/2011/02/simulate-one-ora-01161-solving-for-ora.html  and will guide you through the process of getting the database back, provided you now have the required datafiles in place (you don’t have to have them, but it would be good if you did).

1, Delete all existing database control files at the operating system level.
These control files are useless to us as they contain the new DB name and sequence numbers, but we can’t use them as we will have to re-run the CREATE CONTROLFILE statement.

2, Change the DB init file to use rollback segments (as you have no automatic UNDO without your undo tablespace) and allow resetlogs to corrupt the database, by putting the following in the init<SID>.ora:

undo_tablespace='SYSTEM'
undo_management='MANUAL'
_allow_resetlogs_corruption=true
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$,_SYSSMU17$,_SYSSMU18$,_SYSSMU19$,_SYSSMU20$)

3, Edit the CREATE CONTOLFILE SET DATABASE “<SID>” script to comment out ALL tablespaces except SYSTEM.

This will allow us to create a basic database will rollback.

4, Create the database using the CREATE CONTROLFILE script as you would have done previously.

5, Alter database open resetlogs.

6, Re-name all “MISSING” datafiles based on FILE_ID taken from production.

NOTE: If you don’t know this information (file_id to filename mapping), then you will become very stuck.

alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00004' to '/u01/app/oracle/oradata/day/users01.dbf';

7, Bring all “MISSING” data files online (first attempt):
alter database datafile 4 online;

You will see:

ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: '/u01/app/oracle/oradata/day/users01.dbf'

8, Shutdown immediate.

9, Startup mount.

10, Recover until cancel.

11, Bring all “MISSING” data files online (again):
alter database datafile 4 online;

12, Alter database open resetlogs.

13, Re-Create UNDO tablespace as per the previous specifications (take the spec from production) (maybe use REUSE command so it doesn’t need to create the file) . Use the same file name as before.

14, Shutdown IMMEDIATE.

15, Adjust the init<SID>.ora to re-add the UNDO tablespace and remove the entries that you added in step #2.

16, Startup.

Oracle 11gR2, KEWBMBTA: Maintain BSLN Thresholds failed

If you tighten up the security in an Oracle 11gR2 database, you may be tempted to remove access to the DBMS_OBFUSCATION_TOOLKIT package from PUBLIC (“revoke execute on DBMS_OBFUSCATION_TOOLKIT from PUBLIC;“).

This causes an issue, seen in 11gR2 11.2.0.3.
The error was visible in the user traces:

“*** 2012-09-11 20:00:43.692
KEWBMBTA: Maintain BSLN Thresholds failed, check for details.”

This was tracked to an invalid package body DBSNMP.BSLN.

SQL> alter package DBSNMP.BSLN compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY DBSNMP.BSLN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
88/5 PL/SQL: Statement ignored
88/19 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared
200/7 PL/SQL: Statement ignored
200/21 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared
241/7 PL/SQL: Statement ignored
242/8 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared
1332/7 PL/SQL: Statement ignored
1332/21 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared

Re-granting access to the DBMS_OBFUSCATION_TOOLKIT package to the PUBLIC user, fixes the issue again:

SQL> connect / as sysdba

Connected.

SQL> grant execute on DBMS_OBFUSCATION_TOOLKIT to PUBLIC;
Grant succeeded.

SQL> alter package DBSNMP.BSLN compile body;

Package body altered.

GRANT or REVOKE on User in DB Link Connection

If you GRANT additional privileges to an Oracle user who is used in a DB Link connection (from another database), then if the DB Link is already open and in use (the target DB has ACTIVE or INACTIVE sessions), you will need to close the DB link connection before you see the new privileges.
The same is true if you grant a new role to the user.

However, if you REVOKE privileges from an Oracle user who is used in a DB Link connection then these changes are seen immediately the next time the user is used to perform an operation across the DB link.

Oracle Support for GFS/2 Cluster Filesystem

When implementing Oracle into a RedHat Linux based landscape, you may be looking for a cluster filesystem other than Oracle’s own ASM offering (or clusterware if you like old hats).

Oracle have updated their support status for GFS/2.

Checking out My Oracle Support doc id 279069.1 for 11g and RHEL 5, it says:
“GFS2 is in technical preview in OEL5U2/RHEL5U2 and fully supported since OEL5U3/RHEL5U3.”

You can now get that RHEL cluster up and running in production.