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

SQL> ALTER TABLE SAID.STFACMAS
ADD (OVRDUE_APP VARCHAR2 (1 BYTE) DEFAULT 'N');

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Reason:

The error message says it all: the table is being used right now, probably by a transaction, so getting an exclusive lock on the table may be next to impossible. Of course, the rows of the table are not locked forever. When sessions perform commit the locks on those rows are released, but before that unlock period gets very far, other sessions may update some other rows of the table—and thus the slice of time to get the exclusive lock on the table vanishes. In a typical business environment, the window for locking the table exclusively does open periodically, but the DBA may not be able to perform the alter command exactly at that time.

Solution

In Oracle Database 11g, there is a better option: the DDL Wait option.

SQL> alter session set ddl_lock_timeout = 10;

Session altered.

Now, when a DDL statement in the session does not get the exclusive lock, it will not error out.
Instead, it will wait for 10 seconds. In that 10 seconds, it continually re-tries the DDL operation
until it's successful or the time expires, whichever comes first.

SQL> ALTER TABLE SAID.STFACMAS
ADD (OVRDUE_APP VARCHAR2 (1 BYTE) DEFAULT 'N');

Error: Package: oracleasmlib-2.0.12-1.el7.x86_64 (/oracleasmlib-2.0.12-1.el7.x86_64)

Error

[root@oracle-rac-node1 yum]# yum install oracleasm*
Loaded plugins: langpacks, product-id, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Examining oracleasmlib-2.0.12-1.el7.x86_64.rpm: oracleasmlib-2.0.12-1.el7.x86_64
Marking oracleasmlib-2.0.12-1.el7.x86_64.rpm to be installed
Examining oracleasm-support-2.1.8-3.el7.x86_64.rpm: oracleasm-support-2.1.8-3.el7.x86_64
Marking oracleasm-support-2.1.8-3.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracleasm-support.x86_64 0:2.1.8-3.el7 will be installed
---> Package oracleasmlib.x86_64 0:2.0.12-1.el7 will be installed
--> Processing Dependency: oracleasm >= 1.0.4 for package: oracleasmlib-2.0.12-1.el7.x86_64
--> Finished Dependency Resolution
Error: Package: oracleasmlib-2.0.12-1.el7.x86_64 (/oracleasmlib-2.0.12-1.el7.x86_64)
           Requires: oracleasm >= 1.0.4
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

Solution

[root@oracle-rac-node1 yum]# rpm -ivh --nodeps --force oracleasmlib-2.0.12-1.el7.x86_64.rpm
warning: oracleasmlib-2.0.12-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:oracleasmlib-2.0.12-1.el7        ################################# [100%]
[root@oracle-rac-node1 yum]# rpm -ivh --nodeps --force oracleasm-support-2.1.8-3.el7.x86_64.rpm
warning: oracleasm-support-2.1.8-3.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:oracleasm-support-2.1.8-3.el7    ################################# [100%]
Note: Forwarding request to 'systemctl enable oracleasm.service'.
ln -s '/usr/lib/systemd/system/oracleasm.service' '/etc/systemd/system/multi-user.target.wants/oracleasm.service'
[root@oracle-rac-node1 yum]#

Resize Redo Log size in Data Guard ( Primary and Standby) Oracle 11g

-----------Resize Redo Log in Primary Database-------

alter database drop logfile group 2;
alter database add logfile  group 2 '/u01/app/oracle/oradata/orcl/redo02.log' size 5m;

alter database drop logfile group 3;
alter database add logfile  group 3 '/u01/app/oracle/oradata/orcl/redo03.log' size 5m;

alter system switch logfile

alter database drop logfile group 1;

--Error Come----

ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'

select group#,status from v$log;

alter system checkpoint;

alter database drop logfile group 1;

alter database add logfile  group 1 '/u01/app/oracle/oradata/orcl/redo01.log' size 5m;


alter database drop logfile group 4;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcl/stby04.log' size 5m;

alter database drop logfile group 5;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/orcl/stby05.log' size 5m;

alter database drop logfile group 6;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/orcl/stby06.log' size 5m;

-----------Resize Redo Log in Standby Database-------

Check Database Status

SQL> select status,instance_name,database_role from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         orcls         PHYSICAL STANDBY

SQL>

Check if the parameter standby_file_management is set to AUTO or MANUAL on standby database. If it is not set to MANUAL, then set it.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      auto
SQL>


SQL> alter system set standby_file_management=manual;

System altered.

SQL>
SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL>

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         4 ACTIVE
         5 UNASSIGNED
         6 UNASSIGNED

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CLEARING
         2 CLEARING
         3 CURRENT

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Now to solve this issue we have cancel the managed recovery session
and set  "standby_file_management"  to manual and drop the standby redolog file  as

SQL> alter database recover managed standby database cancel ;

Database altered.

SQL>
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 orcls (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcls/redo01.log'

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CLEARING
         2 CLEARING
         3 CURRENT


If the status of standby redolog show the "clearing_current" then we cannot drop "clearing_current" status
logs,and for that we have to sync with Primary and clear the log first before dropping as

SQL> alter database clear logfile group 1;

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 CLEARING
         3 CURRENT

SQL>  alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile  group 1 '/u01/app/oracle/oradata/orcls/redo01.log' size 5m;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL>  alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile  group 2 '/u01/app/oracle/oradata/orcls/redo02.log' size 5m;
SQL> alter database add logfile  group 3 '/u01/app/oracle/oradata/orcls/redo03.log' size 5m;

---Now Remove Standby Redolog file---

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         4 UNASSIGNED
         5 ACTIVE
         6 UNASSIGNED

SQL> alter database drop standby logfile group 4;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcls/stby04.log' size 5m;

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/orcls/stby05.log' size 5m;

SQL> alter database drop standby logfile group 6;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/orcls/stby06.log' size 5m;

----Now check the status of red log and standby redo log status----

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         4 UNASSIGNED
         5 UNASSIGNED
         6 UNASSIGNED

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 UNUSED

-------Check the size of redo log and standby redo log file size

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6          5
         4          5
         5          5

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
         1          5
         2          5
         3          5


Once all the Standby Redo Logs and Online Redo Logs have been resize on both Primary and standby database,
set the STANDBY_FILE_MANAGEMENT to AUTO on the standby database and start the recovery (MRP) on standby database.

SQL> alter system set standby_file_management=auto;
SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
.
.
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING              66
.
.
.

RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                 68
MRP0      APPLYING_LOG         68

41 rows selected.

SQL>


-----In Primary Database

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            67

-----In Standby Database

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            66


Cheers !!!!!!!!!!!!! If any confusion Please give me buzz !!!!!