Tag Archives: Oracle

RMAN | ORA-19755: could not open change tracking file

Problem Description for ORA-19755

While performing a RMAN recovery on a database, you may receive an error “RMAN-03002: failure of recover command […]” followed by “ORA-19755: could not open change tracking file
”. 

A complete error stack example:

RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03002: failure of recover command at 03/24/2015 11:59:21
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '+BACKUP/arc_logs/1_56029_749586072.dbf'
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: '+DATA/blocktracking/blocktracking.log'
ORA-17503: ksfdopn:2 Failed to open file +DATA/blocktracking/blocktracking.log 
ORA-15173: entry 'blocktracking.log' does not exist in directory 'blocktracking'

Cause

The database block change tracking is enabled. It’s most likely that you are recovering from a complete disk failure, or transferring your database to another location where the log file was not created. This error will appear shortly after the restore operation, the moment where media recovery (database recovery phase) begins. Recovery operation start to rollup archive logs thus making modifications to datafiles’ blocks. This is where the block change tracking kicks in. But due to missing log file the recovery process to aborts.

The log file is created at first when the block tracking is enabled by alter database command. When recovering from disk failure, the pfile/spfile already has this information. Therefore, the file is expected to exist.

Solution

Login to the sqlplus and disable block tracking by issuing

alter database disable block change tracking;

Should you wish to keep the block tracking, you can easily re-enable it. First, ensure that the directory where the block tracking log file should reside exists. “+DATA/blocktracking/” In this case.

Then, issue

alter database enable block change tracking;

Restart the recovery operation in the RMAN. You may skip database restore and go to database recovery right away.

ORA-00054: resource busy / Oracle 10g And 11g Workaround

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ORA-00054 indicates that a resource is being locked by another ongoing (uncommitted) transaction. It usually arises when you try to acquire a lock on a whole table in order to execute a DDL (create index, alter table, etc.).

Usual recommendations are:

  • to wait until the table is freed
  • retry the operation during off-peak hours
  • kill the bloody session that locks the table
  • put the table in read-only mode
  • put the whole database in the maintenance mode
  • and so on

Unfortunately, some tables are never freed. Multiple sessions of automated data stream working 24/7 do not have off-peak hours. The table may never be free for an EXLUSIVE LOCK required for a DDL. Furthermore, it’s not an option to kill a session as it may disrupt the data flow and cause a Service Level Agreement violation. Database maintenance window is a solution, but next scheduled window may be far away. So what if we need this DDL right now.

Oracle Database 11g has a convenient workaround.

You may specify DDL_LOCK_TIMEOUT. This parameter will cause DDL statement to wait specified number of seconds for the table to become available. All concurrent sessions are enqueued as well. Therefore, this options works even if there are multiple sessions working with the table. You can specify DDL_LOCK_TIMEOUT for your session only by issuing:

ALTER SESSION SET DDL_LOCK_TIMEOUT=300;

Read more: http://oracle-base.com/articles/11g/ddl-lock-timeout-11gr1.php

Oracle Database 10g doesn’t have this option

However, there is a workaround. It is a little awkward and I didn’t see it anywhere else, but if you need the job done, it will do the trick. This has been successfully tested on Oracle Database 10.2.0.4. The idea behind the proposed solution is to:

  • First, make all sessions that execute statements on this table work in serial (consecutively).
  • Second, create a management session that will work in serial mode along with sessions in the first step.
  • Third, execute your DDL.
  • Finally, revert to normal functioning.

Basically, this mimics the 11g DDL_LOCK_TIMEOUT or DATABASE QUIESCE feature. How is it achievable? Using Resource Manager ACTIVE SESSIONS limit. If you are familiar RESOURCE MANAGER, then you probably already deduced the solution. Like the DDL_LOCK_TIMEOUT this method will cause a slowdown for sessions interacting with your table. However, in comparison to the QUIESCE method, you may limit the impact to an absolute minimum. QUIESCE, on the other hand, will pause every session across the whole database and sometimes takes forever to execute.

Common Misconception

You may probably have seen proposition to use LOCK TABLE function. This is wrong and will not work. Even if it seems to work – it doesn’t, only by pure luck. When performing any DDL operation, Oracle forces a COMMIT before the DDL is executed. So the lock set by LOCK TABLE is lost and then should be reacquired for the following DDL. If you have outstanding sessions that are waiting to place any type of lock on this table, the DDL will fail to execute with the same ORA-00054: resource busy error. Do not use LOCK TABLE.

Step-By-Step How-To for 10g

For this example, suppose you have an ERP schema with TRANSACTION table. There are always at least two overlapping sessions that insert data into TRANSACTION table (test case with actual DML/DDL will be posted shortly).

First step: create a resource consumer group (ex: SERIAL_GROUP) and simple resource plan (ex: SERIAL_PLAN) with a limit of one Active Session for SERIAL_GROUP. It is important, that the group uses RUN-TO-COMPLETION scheduling. Grant SERIAL_GROUP to public.

Create consumer group:

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'SERIAL_GROUP'
     ,comment                      => 'Enforces serial execution.'
     ,cpu_mth                      => 'RUN-TO-COMPLETION');
  sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager_privs.grant_switch_consumer_group (
      grantee_name                 => 'PUBLIC'
     ,consumer_group               => 'SERIAL_GROUP'
     ,grant_option                 => TRUE);
  sys.dbms_resource_manager.submit_pending_area();
end;
/

Create Resource Plan:

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_plan (
      plan                         => 'SERIAL_PLAN'
     ,cpu_mth                      => 'EMPHASIS'
     ,active_sess_pool_mth         => 'ACTIVE_SESS_POOL_ABSOLUTE'
     ,parallel_degree_limit_mth    => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE'
     ,queueing_mth                 => 'FIFO_TIMEOUT'
     ,comment                      => 'Maintenance plan.');
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'SERIAL_PLAN'
     ,group_or_subplan             => 'OTHER_GROUPS'
     ,switch_estimate              => FALSE
     ,comment                      => 'Mandatory group'  );
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'SERIAL_PLAN'
     ,group_or_subplan             => 'SERIAL_GROUP'
     ,active_sess_pool_p1          => 1
     ,switch_estimate              => FALSE
     ,comment                      => 'Maintenance group'  );
  sys.dbms_resource_manager.submit_pending_area();
end;
/

Second step: assign sessions that are blocking our table to SERIAL_GROUP. This may be the trickiest part. The strategy to put sessions in the required group will vary depending on how the application software handles database connections and the performance impact one may tolerate. For the sake of this demonstration let’s use Client Program. We’ll pretend that our clients use SQLPlus. The mapping below will put every sqlplus.exe session into SERIAL_GROUP.

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
    dbms_resource_manager.client_program,
    'sqlplus.exe',
    'SERIAL_GROUP'
);
dbms_resource_manager.submit_pending_area();
END;

CAUTION: Use another session that will not be mapped to the SERIAL_GROUP to setup and control the environment. You risk to be locked-out of your database.

Optionally, make sure that your mapping is of a higher priority. We’ll put client program to be priority 2 (1 – for explicitly assigned groups)

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping_pri(
    EXPLICIT => 1,  CLIENT_PROGRAM => 2,
    SERVICE_MODULE_ACTION => 3,
    SERVICE_MODULE => 4,
    MODULE_NAME_ACTION => 5,
    MODULE_NAME => 6,
    SERVICE_NAME => 7,
    ORACLE_USER => 8,
    CLIENT_OS_USER => 9,
    CLIENT_MACHINE => 10
);
dbms_resource_manager.submit_pending_area();
END;

You may probably be required to use a combination of mapping to put users’ sessions and yours into serial group. Using finer grained mapping such as modules and/or actions may greatly reduce any performance hit. Check the documentation for more: https://docs.oracle.com/cd/B19306_01/server.102/b14231/dbrm.htm

Third step: Now when you are ready, connect to the database in such a way that your new session will be mapped to SERIAL_GROUP. In our case, we’ll use SQLPlus.

In the session that is NOT mapped to SERIAL_GROUP activate the SERIAL_PLAN.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'SERIAL_PLAN';

In the SERIAL_GROUP session, execute your DDL. If there is pending DDL LOCKS it will hang until the object is freed.

When done or just tired of waiting, deactivate SERIAL PLAN by issuing:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

That’s it. If you have any questions, write them in comments below!

 

 

 

Oracle ORA-28001: the password has expired / How to reset the old password if it’s unknown?

It’s a good practice to change passwords on the regular basis. Although, in certain circumstances it may cause some inconvenience.

ORA-28001 may surprise users of various software. Applications such as a WordPress, forums etc, or older enterprise software use application specific username and password to connect to a database. In the worst case, the application will suppress the original “ORA-28001: the password has expired” error. Instead, it will display some generic message like “unable to connect to database’. It may take time to figure out what did really happen.

The most annoying part is when the original application password was lost. And there is no easy way of recovering it from the application. However, there is a possibility to reset the password to the old value without knowing it. Plus, one should make changes to prevent Oracle password from expiring again in future.

How to recover from ORA-28001 assuming we don’t know the original password?

First, identify users with expired passwords. Connect to the database as SYSDBA and execute the following statement.

SELECT username, account_status, expiry_date
FROM dba_users
WHERE account_status != 'OPEN'
ORDER BY 3 DESC;

This will produce an output of all expired and/or locked accounts by expiry date in a descending order.

USERNAME                ACCOUNT_STATUS   EXPIRY_DATE
----------------------- ---------------- -------------------
APPUSER                 EXPIRED          29-NOV-13

Identify the most likely schema (username) of interest. In this example it’s APPUSER schema.
Now we have to retrieve the hash value of the original password. Issue the following statement:

SELECT name, password
FROM sys.user$
WHERE name = 'APPUSER';

The result should be similar to:

NAME                           PASSWORD
------------------------------ ------------------------------
APPUSER                        FE214E4F8379067B

To reset the password, use the following command:

ALTER USER APPUSER IDENTIFIED BY VALUES 'FE214E4F8379067B';

VALUES keyword indicates that the specified password is a hash value.

And finally verify the result using:

SELECT username, account_status
FROM dba_users
WHERE username = 'APPUSER';

The result:

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
APPUSER                        OPEN

“OPEN” indicates that password was successfully reset. The application should now be working as expected. In some cases, a restart of the application server may be required.

Preventing ORA-28001

How to prevent oracle password from expiring? Oracle uses user profiles to define certain user specific rules. It includes password complexity and life time. The DEFAULT profile is assigned to most users. It enforces a 180 + 7 (grace) days password life time. After that point, user is required to change password.

To prevent password expiry, one may change the appropriate profile setting. I would not recommend changing the original DEFAULT profile. It’s better to create a new one with unlimited expiry setting. Assign the new profile to users that do not require regular password change. Note that if the password is already expired changing profile setting will not revert the account status to OPEN.
I’ll create a new profile with the name NEW_PROFILE. However, you may choose any name you like.

It’s possible to perform all operations using GUI tools. I will show a manual procedure using SQLPLUS.

First, list profiles and their password life time setting:

SELECT profile, limit
FROM dba_profiles
WHERE resource_name = 'PASSWORD_LIFE_TIME';

You will get a list that may look like

PROFILE                        LIMIT
------------------------------ ----------------------------------------
DEFAULT                        180
MONITORING_PROFILE             DEFAULT

You have the option to alter existing profile (fast but not recommended):

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;

It is better to create a custom profile. It may be a copy of the DEFAULT.

1. Get the existing configuration for the DEFAULT user profile:

set pages 20
SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT';

2. Copy the result into a text editor
3. Replace PASSWORD_LIFE_TIME value of 180 to UNLIMITED
4. Remove the header and replace it with CREATE PROFILE name LIMIT statement.
5. Add semicolon at the end.

The result should look like:

CREATE PROFILE NEW_PROFILE LIMIT
COMPOSITE_LIMIT                  UNLIMITED
SESSIONS_PER_USER                UNLIMITED
CPU_PER_SESSION                  UNLIMITED
CPU_PER_CALL                     UNLIMITED
LOGICAL_READS_PER_SESSION        UNLIMITED
LOGICAL_READS_PER_CALL           UNLIMITED
IDLE_TIME                        UNLIMITED
CONNECT_TIME                     UNLIMITED
PRIVATE_SGA                      UNLIMITED
FAILED_LOGIN_ATTEMPTS            10
PASSWORD_LIFE_TIME               UNLIMITED
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7;

Oracle Database SQL Reference on CREATE PROFILE

To list users and their assigned profiles and whether or not their password has expired issue:

select username, profile, account_status from dba_users ORDER BY 1;

To assign the new profile to a use use:

ALTER USER username PROFILE NEW_PROFILE;

[fb_button]