RMAN Recover Error ORA-01547

When attempting to perform a point-in-time recovery from a hot backup you receive an ORA-01547 followed by ORA-01195 and ORA-01110: 

SQL> recover database until time '2004-02-23:05:00:00' using backup controlfile;

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u02/oradata/scr10/system01.dbf'

Reason: 

The most likely reason is that you have specified a time that occurred  while the backup was taking place.

FIX: 

Either restore an earlier backup and roll it forward, or specify a time that occurred after the last file in the current backup was written. If the backup was written to disk (rman or manual) a simple 'ls -ltr' in the directory containing the backup files will give you a rough idea of the earliest possible restore point.

Rename or Move Datafiles and Logfiles

 To rename or relocate datafiles in the SYSTEM tablespace one must use option II, 'Renaming or Moving a Datafile with the Database Shut Down', because you cannot take the SYSTEM tablespace offline.

I. RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN

To do this you must follow these steps:

1. Determine how many datafiles are associated with the tablespace.

       > SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES

                 WHERE TABLESPACE_NAME = '';

2. Keep tablespace OFFLINE before moving in OS LEVEL

      > ALTER TABLESPACE  SCOTT_TABLESPACE OFFLINE;

   * At this point the tablespace is not accessible to users.

3. Go to OS box and run move command

MV /u02/oradata/scott_data.dbf /u03/oradata/datafile/scott_data.dbf

4. Once moving done on OS level, run followings:

* This updates the entry for the datafile(s) in the  controlfile.

       > ALTER DATABASE RENAME FILE

                 '/u02/oradata/scott_data.dbf ' TO '/u03/oradata/datafile/scott_data.dbf’;

     

5. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online.

       > ALTER TABLESPACE YOUR_TABLESPACE_NAME ONLINE;

 6. You can also verify that the controlfile has been updated by doing the

    Following:

        > ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

    This will produce a readable copy of the contents of your controlfile

    which will be placed in your user_dump_dest directory.

    Optionally, you can query V$DATAFILE, which gets information from the

    controlfile as well.

II. RENAME AND OR MOVE DATAFILE(S) WITH THE DATABASE SHUT DOWN

1. If the database is up, shut it down.

2. Copy the datafile to the new name/location at operating system level.

3. Mount the database.

        > STARTUP MOUNT                                                          

         

   This command will read the control file but will not mount the datafiles.   

4. Rename the file inside Oracle.

        > ALTER DATABASE RENAME FILE

                 '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'

                 TO

                 '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';

   Do this for all the datafiles that were renamed or moved at the operating

   system level.

5. Open the database.

        > ALTER DATABASE OPEN;

6. Query v$dbfile to confirm that the changes made were correct.

        > SELECT * FROM V$DBFILE;

7. Remove the datafile(s) from the old location at the operating system level.

 

III. RENAME AND OR MOVE A LOGFILE

1. Shutdown the database. 

2. Copy the logfile to the new name/location at operating system level.

3. Mount the database.

        > STARTUP MOUNT

4. Rename the file.

       > ALTER DATABASE RENAME FILE

                 '/FULL_PATH_OF_OLD_LOCATION/AND_REDO_LOG_NAME.LOG'

                 TO

                 '/FULL_PATH_OF_NEW_LOCATION/AND_REDO_LOG_NAME.LOG';

5. Open the database.

        > ALTER DATABASE OPEN;

6. Remove the logfile(s) from the old location at the operating system level.

Dropping user with QUEUE tables

I don't know whether it's a bug or something...but if you try to drop a user with cascade that is having some queue tables. It won't allow you to drop!!!

Would throw the below error:

ORA-00604: error occurred at recursive SQL level 1
ORA-24170: SCOTT.IAI_QUEUE_R is created by AQ, cannot be dropped directly
ORA-06512: at "SYS.DBMS_RULE_ADM", line 158
ORA-06512: at line 1

How to ressolve?

1. Grant AQADM execute permission to the user

GRANT EXECUTE ON DBMS_AQADM to SCOTT.

2. Login as scott and find the QUEUE tables it belongs to.

select * from user_tables

where table_name like 'AQ%'

3. Drop the QUEUE tables using the following command.

 exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_IAI_NR',force => TRUE);

4. Login as DBA user and issue

drop user SCOTT cascade

Now it works!!!

Buffer Cache Hit Ratio

Tuning the buffer cache hit ratio


A logical read occurs whenever a user requests data from the database. It occurs whenever the data is in the buffer cache or whether the user process must read it from disk. If the data must be read from disk then a physical read occurs. Oracle keeps track of logical and physical reads in the V$SYSSTAT table.

Use the following SQL statement to determine the values required for the hit radio calculation:
 

select name, value From v$sysstat
where name in ('db block gets', 'consistent gets', 'physical reads');

       Hit ratio = 1 - (physical reads / (db block gets + consistent gets))

Resizing buffer_cache and shared_pool:

If the cache-hit ratio goes below 90% then:

• For Oracle 8 and earlier: increase the initialisation parameter DB_BLOCK_BUFFERS.
• For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE.

calculating the cache hit ratio:

select
100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
from
v$sysstat v1, v$sysstat v2, v$sysstat v3
where
v1.name = 'db block gets' and
v2.name = 'consistent gets' and
v3.name = 'physical reads'

SQL> show parameter db_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 721420288

SQL> show parameter show parameter shared_pool
SQL> show parameter shared_pool_size


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 134217728


Activity:


1. Resize Buffer cache
alter system set db_cache_size=4096M scope=spfile;

2. Resize Shared pool size
alter system set shared_pool_size=4096M scope=spfile;

3. Restart database
Shutdown immediate;
Startup;

5. Checking parameter change.

show parameter db_cache_size
show parameter shared_pool


6. Align pfile with spfile
Create pfile from spfile;

sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

After a successful RDBMS installation sqlplus may through the below error:

[oracle@test ~]$ sqlplus ‘/as sysdba’
sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

The reason is that SELinux is running in “enforcing” mode.

You can check it on file /etc/pam.d/login . Oracle development has recommended the following workaround while they correct the problem:

Switch SELinux from the default “Enforcing” mode that it is running in, to the “Permissive” mode.

Commands, as root:
======================
getenforce       (returns “Enforcing”)
setenforce 0
getenforce       (returns “Permissive”)

This allows SELinux to continue running, and logging denial messages, but SELinux will not actually deny any operations. Once Development has resolved this issue, you can (and should) return SELinux to the default “Enforcing” mode as follows:

Commands, as root:
======================
setenforce 1
getenforce       (returns “Enforcing”)