RMAN block change tracking

Enabling block change tracking file for RMAN backups enables the backups to perform better. By reading the tracking file, RMAN is able to determine the changed blocks much faster than scanning the entire datafile for changed blocks.

Enable blocking tracking for a database on ASM:
alter database enable block change tracking using file ‘/oracle/u01/backup/rman/change_tracking.ctl’;

The below command create a block change tracking file for the database on ASM.
alter database enable block change tracking using file ‘+DATA01′;

Check the status:
SELECT filename, status, bytes FROM v$block_change_tracking;

Disable block change tracking:
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Share

Find temporary tablespace usage

Use the below query to find out the current usage of temporary tablespace(sort usage) by active users.

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
         S.program, SUM (T.blocks) * TBS.block_size /1024/1024 mb_used, T.tablespace,
         COUNT(*) sort_ops
FROM     gv$sort_usage T, gv$session S, dba_tablespaces TBS, gv$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial
Share

nid failure and open_cursors

Last day I was working on changing the database name of a 4TB database. The database had nearly 4000 datafiles and I was slightly tensed about the operation. I had experience in which the nid operation failed resulting in a corrupt database and nothing could be done to recover from the scenario. nid utility provides an option to revert the operation, that too didn’t work as expected and even the attempt to recreate the controlfile did not succeed because of the partial updates(updates on some datafiles succeeded and failed for the rest). Another issue that I have seen with nid is with Oracle on AIX, wherein the nid utility fails if the tempfiles are not found during the rename operation and the failure was irreversible and catastrophic.

I’d prefer to recreate the controlfile with the “SET” clause for the database name in future instead of using nid utility after going through this failure.

So, here goes the story.

As always, take binary and trace backup of the control file before such operations.

1) Tried nid utility to change the dbname to the different name.

2) Disaster stuck! and the operation failed with the below error.

NID-00111: Oracle error reported from target database while executing
    begin       dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged,:nmchged);    end;
ORA-01000: maximum open cursors exceeded

Change of database name and database ID failed.
Must finish change or REVERT changes before attempting any database operation.
DBNEWID - Completed with errors.

3) Tried the REVERT operation by specifying the REVERT=Y option

nid target=/ REVERT=Y

Alas! the revert operation also failed with the same error :(

4) As the error message was pointing to the value of open_cursors parameter, I checked the parameter and it was set to 400. I decided to change the parameter and set it to 6000 – 2000 more than the number of datafiles in the database to be safe. Made the change to the parameter file, shutdown and mounted the database.

5) Ran the nid operation with REVERT=Y and it succeeded!

6) Ran the nid operation again with the new name and it went through without any issues.

7) Updated the new DB_NAME in parameter file, reverted the value of open_cursors to its original value, mounted the database and opened the database in RESETLOGS mode.

Oracle nid best practices

Take controlfile backups – binary and trace backups before the bid operation.

If there are tempfiles in the dictionary, make sure they are accessible. Check both v$tempfile and dba_temp_files, not just dba_temp_files. The nid operation tries to update the dbname in tempfiles too and if it is not found, the operation fails. If there are tempfiles that doesn’t exist, it is better to drop them from the dictionary before the nid operation.

nid requires the database to be mounted in exclusive mode, so set the cluster_database=false if it is RAC(mandatory, else nid will say that the DB needs to be mounted in exclusive mode.

Check the open_cursors parameter and it is safe to be set to a very high value during the operation, this needs to be reverted back to the original value after nid operation.

Good luck!

Share

Standby roll forward using RMAN incremental backup

Last week I came across a scenario where a standby database went out of synch with the primary and there was a big gap of archive logs to be applied. Instead of rebuilding the standby database from scratch or restoring and applying a zillion archives, we can make use of a feature available from the version 10.2 onwards called the standby roll forward that uses RMAN incremental backup to apply the changes on the standby.

For databases on non-ASM, it should work with the first 4 steps as indicated below. For databases on ASM, we need to perform some more steps.

To keep it simple, I have outlined the steps below and have attached the logs for reference.

1) Identify the last SCN on the Standby, take the minimum value returned by the below queries for further steps.

SQL>
col current_scn for 99999999999999999999999
col int_scn for 99999999999999999999999

select current_scn FROM V$DATABASE;
select min(fhscn) int_scn from x$kcvfh;  

CURRENT_SCN
-----------------
47987632

2) Take an incremental backup on the primary database from this SCN to the current point.
The incremental backup files will be generated on the filesystem under the directory “/ora001/backup/” in the example.
Need to make sure that there is sufficient space in the filesystem to hold the backup. In this case, the backup files sized nearly 1.2G.

RMAN> CONFIGURE CHANNEL c1 DEVICE TYPE DISK FORMAT   '/ora001/backup/std_%U';
RMAN> BACKUP INCREMENTAL FROM SCN 47987632 DATABASE;

3) SCP the backup files to the server where the standby database is running and catalog the backup.

RMAN> CATALOG START WITH '/ora001/backup/std_%U';

This makes the controlfile aware of the incremental backup.

4) Recover the standby database.

RMAN>
RECOVER DATABASE NOREDO;

5) Check the archive gap and the SCN to see if the DB has applied the changes.

SQL>
col CURRENT_SCN for 99999999999999999999999
select * from v$archive_gap;
select current_scn from v$database;

The standby is in good shape if the SCN returned is a bigger value and there are no gaps.

—- Steps above should be sufficient for non-ASM databases; continue the steps below for databases on ASM. —-
(It was found that the SCN was not as expected and the steps below were done)

5) Cancel the database recovery at the standby site.

SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

6) Take the controlfile backup again on primary database and SCP it over to the standby server.

RMAN>
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/ora001/ctrl_bkp.bak';

7) Take pfile, controlfile backups and note the current controlfile location on the standby and shutdown the standby database.

SQL>
select name  from v$controlfile;
shut immediate;
startup nomount;

8) Restore the standby controlfile created in step 6 on the standby site.

RMAN>
RESTORE STANDBY CONTROLFILE FROM '/ora001/ctrl_bkp.bak';
sql 'alter database mount';

9) At this point, the controlfiles on the standby database will have the datafiles pointed to the locations on primary. This needs to be updated so that they point to the right datafiles.

(For eg: On primary, it would be something like ‘+DATA01_R5/prod’ and on standby, the structure will be something like ‘+DATA01_R5/stby’)

RMAN>
CATALOG START WITH '+DATA01_R5/stby';

(May need to repeat this if there are files in multiple diskgroups)

10) Imp: If there are datafiles created on the primary database after the last SCN on standby (the one on step 1), we’ll need to backup the datafile(s), catalog the backup, do the restore of those file(s) too. The files can be identified with the below SQL:

SQL>
SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 47987632;

11) Perform the switch so that the controlfile points to the datafiles under the standby directory.

RMAN>
SWITCH DATABASE TO COPY;

12) Clear redo log groups on the standby.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

13) Check the SCN on the standby and start the managed recovery. The SCN should be number much higher than that was on step 1 and there shouldn’t be no archive gaps.

SQL>
select current_scn from v$database;
select * from v$archive_gap;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

14) Do a couple of log switches on the primary and verify from the alert log that the standby is catching up with the primary.

15) Verify the standby redologs and other settings as required depending on the configuration and make sure the standby is in good shape.

Share

Set trace on user sessions

Oracle 10046 sesssion tracing

There are different ways to enable tracing on an Oracle database user session. They are:

  1. Using DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procudure.
  2. Using oradebug ipc setorapid/setospiod
  3. Using a Logon trigger

The first 2 methods are used used when the sessions are already connected to the database. For the first method, use the session ID, Serial# and execute the procedure to start tracing.

Using oradebug

1) Connect to the database Instance, On RAC, connect to the right instance.

$ connect / as sysdba

2) Get the OS PID of the connected session and run the command
Eg:

oradebug setospid 16211

Alternatively, Oracle PID can also be used to start the tracing.
Eg:

oradebug setorapid 27853

Set the tracing level to 12 for an exhaustive trace(4 and 8 are lower levels).

  oradebug unlimit
  oradebug event 10046 trace name context forever,level 12

Once the tracing is done, tracing can be disabled using the below procedure:

connect “/ as sysdba”

oradebug setospid 16211
oradebug event 10046 trace name context off

Using a LOGON trigger

Using a LOGON trigger may be the preferred method to trace sessions in situations where it is difficult to start tracing when user sessions get active after getting connected to the database. Also, in RAC environments where the services are configured as multi-preferred, it may be difficult to logon to each instance to set the tracing. In such scenarios, setting a logon trigger at the database can help to set tracing on sessions. Here are the steps:

1) Check to see if “alter session” privilege is granted to the required users. Else grant “alter session” privilege to the users.

For eg, to trace the sessions of SCOTT and HR users:

grant alter session to SCOTT;
grant alter session to HR;

2) Create a logon trigger at the database level:

CREATE OR REPLACE TRIGGER SYS.set_trace_scott
  AFTER LOGON ON DATABASE
  WHEN (USER in ('SCOTT','HR'))
  DECLARE
      lcommand varchar(200);
  BEGIN<br />
      EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
      EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
      EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
  END set_trace;

3) Trace files are generated once users sessions are established. Check the user_dump_dest for the trace files once the sessions are active. On RAC, Identify the instance to which the user(s) sessions got connected and check the appropriate server.

4) Once the trace files are obtained, remove the trigger to disable further tracing without fail. Otherwise, there are high chances of generating huge trace files which may fill up the filesystem. You may also want to remove the ALTER SESSION privilege from the users if granted for this purpose.

Use Oracle supplied utility TRCA(Trace Analyzer) to analyze the trace files.

drop trigger SYS.set_trace_scott;

Good luck in finding what you are looking for!

Share

Oracle 10g – ORA-01031 insufficient privileges

The CONNECT role in Oracle 10g has fewer system privileges than it had in earlier versions of Oracle. When migrating databases from versions below Oracle 10g and especially if the users are recreated in the 10g databases with the privileges from the old version, certain privileges are required to be granted so that the application users can connect to the database with proper privileges. This article discusses this special case on the occurrence of ORA-01031 error in Oracle 10g databases.

The ‘CONNECT’ role in Oracle 10g has only ‘CREATE SESSION’ privilege, whereas the previous version had some additional privileges. If the privileges are missing, the user won’t be able to create objects which will result in ‘ORA-01031 insufficient privileges’ messages to the user.

To fix the issue, grant the 8 privileges listed below to the users.

In Oracle 9i, the CONNECT role had the following privileges:

SQL>  select * from dba_sys_privs  where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE VIEW                              NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE DATABASE LINK                     NO

8 rows selected.

In Oracle 10g, only the ‘CREATE SESSION’ privilege is granted to the CONNECT ROLE.

SQL>  select * from dba_sys_privs  where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

SQL>
Share

ORA-00942/ORA-00904/EXP-00056 with Oracle exp

Oracle export utility when used to export from a lower version of database may not work due to compatibility issues as the export utility expects a set of views that are not present in the database. Even patchset difference between the exp utility and the database can lead to similar issues.

The best bet to deal with the issue is to use the lowest version available at the source/target database.

If you are using Oracle client, opt for the lowest version available for taking the export.

The errors differ based on the version difference between exp utility and the database, the following errors are possible:

9i client against lower versions of 9i database or 10g clients against 9.2.0.4 database can cause the following messages in the export log:

EXP-00056 Oracle error 942 encountered
ORA-00942 table or view does not exist
EXP-00000 Export terminated unsuccessfully

On some 9i R2 versions, the error may be reported as below:

EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully

9.2.0.6 export client against a 9.2.0.5 can cause the following messages in the export log during the export of partitioned tables.

EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 48:
PLS-00302: component ‘CHECK_MATCH_TEMPLATE’ must be declared
ORA-06550: line 1, column 14:
PL/SQL: Statement ignored

10.2.0.4 client against a 10.2.0.3 database can cause the following messages in the export log during the export of operators.

. exporting operators
EXP-00008: ORACLE error 904 encountered
ORA-00904: “OLEVEL”: invalid identifier
EXP-00000: Export terminated unsuccessfully

Share

dbms_system.ksdwrt–Write messages to Oracle alert log

Oracle provides a procedure to insert messages to the alert log and/or trace files for testing/development purposes. This can be used to check the effectiveness of the monitoring tools/scripts used in the environment, to understand how well the monitoring tool captures the messages in the alert log.

Usage:

SQL> exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(2, 'This message goes to the alert log');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location');

PL/SQL procedure successfully completed.

SQL>

To test whether your monitoring tool captures error messages such as an ORA-00600, try executing the below:


SQL> exec dbms_system.ksdwrt(2, 'ORA-00600: Testing monitoring tool');

PL/SQL procedure successfully completed.

SQL>
Share

FIXED_DATE Init.ora parameter

Setting this parameter to a specified timestamp will make the time constant for the database engine (the clock will not tick) FIXED_DATE is a dynamic parameter and can be changed using the ALTER SYSTEM command.

SQL> ALTER SYSTEM SET FIXED_DATE='2009-09-22-13:00:00';

Once you set parameter, the value will be returned when ‘sysdate()’ is used in the queries/application.
To see the server time, you can use systimestamp.

Eg:


SQL> set lines 100
SQL> select sysdate, systimestamp from dual;

SYSDATE					SYSTIMESTAMP
-------					-------------------------
2009 09 22 13:00:00 	10-DEC-09 03.01.41.142940 AM -06:00

This is usually used for testing/development purposes, when the application logic depends on a specific date/time combination.

To set the date back to the ‘system’ clock, set the parameter to ‘none’

SQL> alter system set fixed_date='none';
Share

ORA-00054 resource busy and acquire with NOWAIT specified

The below query helps to identify the session that has locked the resource that your session is trying to lock.

You can try executing the query/job after some time (to allow the other session to complete) or kill the session that has locked the resource to proceed with this situation, use the below query to identify the session.


set linesize 140
set pages 100
col username       format a20
col "SID,SESSION#" format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ' (' || s.osuser || ')' username
  ,  s.sid || ',' || s.serial# "SID,SESSION#"
  ,  owner || '.' || object_name object
  ,  object_type
  ,  decode( l.block
     ,       0, 'Not Blocking'
     ,       1, 'Blocking'
     ,       2, 'Global') status
  ,  decode(v.locked_mode
    ,       0, 'None'
    ,       1, 'Null'
    ,       2, 'Row-S (SS)'
    ,       3, 'Row-X (SX)'
    ,       4, 'Share'
    ,       5, 'S/Row-X (SSX)'
    ,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
 from       v$locked_object v
 ,  dba_objects d
 ,  v$lock l
 ,  v$session s
 where      v.object_id = d.object_id
 and        v.object_id = l.id1
 and        v.session_id = s.sid
 order by oracle_username,session_id;

Sample output:

USERNAME	SID,SESSION#	OBJECT		OBJECT_TYPE	STATUS		MODE_HELD
--------	------------	-------------	-----------	------		---------
SCOTT (oracle)	86,30		SCOTT.LOCK_TEST	TABLE		Not Blocking 	Row-X (SX)

To kill the locking session, do as below:

SQL> alter system kill session '86,30';

To identify the locked rows, use the below query:

set lines 200
col object_name for a30
select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
				ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) ROW_ID
from	v$session s
,	dba_objects do
where	s.ROW_WAIT_OBJ# = do.OBJECT_ID
and do.object_name=upper('<object_name_from_above_query>')
;

OBJECT_NAME	ROW_WAIT_OBJ#	ROW_WAIT_FILE#	ROW_WAIT_BLOCK#	ROW_WAIT_ROW#	ROW_ID
-----------	-------------	--------------	---------------	-------------	------
LOCK_TEST	41862		4		70		0		AAAKOGAAEAAAABGAAA

To simulate the occurrence of this error for testing/learning purposes, do as below in 2 sessions:

Session 1:

SQL> conn scott/tiger;
Connected.

SQL> create table lock_test (x number);
Table created.

SQL> insert into lock_test values(100);
1 row created.

SQL> commit;
Commit complete.

SQL> update lock_test set x=500;
1 row updated.

-- Do not commit.

Session 2:


SQL> conn scott/tiger;
Connected.
declare
y number;
begin
select x into y from lock_test for update nowait;
end;
/

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 4
Share