Category: ORA- ERRORS

ORA-00261: log 11 of thread 1 is being archived or modified

Try to drop standby logfile group, and get below errors. Because this standby logfile group is being used for real time standby database recovery.

SQL> alter database drop standby logfile group 11;
alter database drop standby logfile group 11
*
ERROR at line 1:
ORA-00261: log 11 of thread 1 is being archived or modified
ORA-00312: online log 11 thread 1: '/u02/redo/TESTDB/standby_011.log'

WORKAROUND

1)Clear standby logfiles:

SQL> alter database clear logfile group 11;

Database altered.

2) Drop standby logfile group:

SQL> alter database drop standby logfile group 11;

Database altered.

3) Add standby logfile groups, and enable recovering managed standby database.

4) Will see log shipping pauses with below errors :

*** 2019-01-21 15:11:39.944
Error 270 closing standby archive log file at host 'TESTDBSTY'
ORA-00270: error creating archive log
Archive destination LOG_ARCHIVE_DEST_2 made inactive: File close error
*** 2019-01-21 15:11:40.043603 4929 krsh.c
LGWR: Error 270 closing archivelog file 'TESTDBSTY'

5) Reset parameter “log_archive_dest_state_2” to wake up log shipping:

SQL>alter system set log_archive_dest_state_2=enable;

ORA-01031: insufficient privileges by EXECUTE IMMEDIATE

“ORA-01031: insufficient privileges” was received from a PL/SQL procedure  “EXECUTE IMMEDIATE” state.

1)Create a simple PL/SQL procedure.

SQL> create or replace procedure proc_test
is
begin
          EXECUTE IMMEDIATE 'create table tbl_test ( id number)';
end;

2) logon as user ,and then execute above procedure.

SQL> exec proc_test;
BEGIN proc_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST_USER.PROC_TEST", line 7
ORA-06512: at line 1

3) grant ‘create table’ privilege to user directly.

SQL> show user
USER is "SYS"
SQL> grant create table to test_user;

Grant succeeded.

4)logon as user ,and then execute above procedure.

SQL> show user
USER is "TEST_USER"

SQL> exec proc_test;

PL/SQL procedure successfully completed.

SQL> desc tbl_test;
Name   Null?   Type
----- -------- ----------------------------
ID             NUMBER

SQL> drop table tbl_test;

REASON: In stored procedure/function, any privileges granted by a role will NOT be effective. The privileges should be directly granted to the user.

DataGuard ORA-07286 ORA-16055 ORA-19809 ORA-19804 ORA-19815

SYMPTOM

On primary database  side, ORA errors in alert log:

Sat Sep 22 03:56:48 2018
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_arc4_20835.trc:
ORA-07286: sksagdi: cannot obtain device information.
Sat Sep 22 03:56:48 2018
ARC4: FAL archive failed with error 7286. See trace for details
Sat Sep 22 03:56:48 2018
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_arc4_20835.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing

On standby database side :

ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 536870912 bytes disk space from 214748364800 limit
ORA-19815: WARNING: db_recovery_file_dest_size of 214748364800 bytes
           is 100.00% used, and has 0 remaining bytes available.
Sat Sep 22 04:24:24 2018
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************

CAUSES

Standby database archive log retention policy is not configured properly.

RMAN> show all;
...
..
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
..

RESOLUTION

Change  the archive log retention policy on standby side.

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database

Oracle database instance hung due to recovery area full causing “Archival Stopped Error”.

Check alert.log with below ORA errors:

— Alert.log

ORA-19816: WARNING: Files may exist in db_recovery_file_dest that 
           are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. If files cannot be cataloged, then manually
delete them using OS command. This is most likely the
result of a crash during file creation.
*************************************************************

Check recovery area view , which says a lot of free space available:

SQL> set pagesize 120
SQL> set linesize 120
SQL> select * from v$recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED  PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-----------  ------------------- ------------------------- ---------------
...
..
ARCHIVED LOG               33.5                         0.8            248
BACKUP PIECE                0                           0                0
...
..
.
7 rows selected.

Check the database resetlog date and time:

SQL> alter session set nls_date_format='YYYYMMDD-HH24:MI:SS';

Session altered.

SQL> select CREATED,RESETLOGS_TIME,PRIOR_RESETLOGS_TIME,
            CONTROLFILE_TIME,VERSION_TIME from v$database;

CREATED           RESETLOGS_TIME    PRIOR_RESETLOGS_T CONTROLFILE_TIME  VERSION_TIME
----------------- ----------------- ----------------- ----------------- -----------------
20180817-11:05:10 20180817-11:05:43 20161009-20:55:02 20180821-15:19:21 20180817-11:05:10

CAUSE

There are archivelogs in recovery area from old databases, which were refreshed or restored with different DB ID, manually delete them, then everything is fine:

ASMCMD> ls -ltr
Type  Redund  Striped  Time             Sys  Name

                                        ...                                        ...
                                        ..
                                        .
                                        Y    2018_08_14/
                                        Y    2018_08_15/
                                        Y    2018_08_16/
           RESETLOGS DATE      ----->   Y    2018_08_17/
                                        Y    2018_08_18/
                                        Y    2018_08_19/
                                        Y    2018_08_20/

ORA-27140 ORA-27300 ORA-27301 ORA-27302 ORA-273003 When Start Up or Shut Down RAC Instance

ORACLE_HOME is shared by both RAC instance A and RAC instance B. Both instance A and B are shut down, then ORACLE_HOME is patched, the ORACLE_HOME/bin/oracle binary will be as below just after the patching:

$ id oracle
uid=122(oracle) gid=202(oinstall) groups=202(oinstall),201(dba)

$ id grid
uid=518(grid) gid=202(oinstall) groups=202(oinstall),201(dba)

$ ls -ltr /dev/oracleasm/disks/
total 0
brw-rw---- 1 grid dba 253, 16 Jul 30 16:38 OCR_VOTE01
brw-rw---- 1 grid dba 253, 50 Jul 30 16:38 ASM_FRA01
brw-rw---- 1 grid dba 253, 57 Jul 30 16:38 ASM_DISK01
..
.

$ ls -ltr /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 228655023 May 17 11:11 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

Manually start up instance A by using sqlplus without using ‘srvctl start instance’, and check oracle binary gid( oinstall) still the same:

$ sqlplus / as sysdba

...
..
.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
...
..
.
Database mounted.
Database opened.
SQL> exit
$
$ ls -ltr /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 228655023 May 17 11:11 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

Then start up instance B through clusterware by using ‘srvctl start ‘, and noticed clusterware changed the group of the oracle binary:

$srvctl start database -d TESTB
$ ls -ltr /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle dba 228655023 May 17 11:11 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

The instance A got below errors in trace files and alert log:

Alert.log:

Process m000 died, see its trace file
Wed Jul 25 16:37:42 2018
Process m000 died, see its trace file
..
.
Process q000 died, see its trace file
Wed Jul 25 16:41:54 2018
Process W000 died, see its trace file
Process W000 died, see its trace file

Trace file:

$ cat TESTA1_m000_7813.trc
..
.
/u01/app/oracle/diag/rdbms/testa/TESTA1/trace/TESTA1_m000_7813.trc
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

*** 2018-07-25 16:35:55.662
Died during process startup with error 27140 (seq=39770)
OPIRIP: Uncaught error 27140. Error stack:
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 202 (oinstall), 
current egid = 201 (dba)

crsd_oraagent_oracle trace file shows clusterware is changed RAC $ORACLE_HOME/bin/oracle binary by using “setasmgidwrap” while instance B is started up by using ‘srvctl start ‘ by clusterware.

crsd_oraagent_oracle_42.trc:2018-07-25 16:34:31.584046 :CLSDYNAM:2785666816: [ora.testb.db]{1:44997:10712} [start] command = '/u01/app/12.1.0.2/grid/bin/setasmgidwrap oracle_binary_path=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle'
crsd_oraagent_oracle_42.trc:2018-07-25 16:34:31.584068 :CLSDYNAM:2785666816: [ora.testb.db]{1:44997:10712} [start] Utils:execCmd action = 1 flags = 6 ohome = /u01/app/12.1.0.2/grid cmdname = setasmgidwrap.
crsd_oraagent_oracle_42.trc:2018-07-25 16:43:38.726230 :CLSDYNAM:2128570112: [ora.testb.db]{1:44997:10835} [start] command = '/u01/app/12.1.0.2/grid/bin/setasmgidwrap oracle_binary_path=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle'
crsd_oraagent_oracle_42.trc:2018-07-25 16:43:38.726270 :CLSDYNAM:2128570112: [ora.testb.db]{1:44997:10835} [start] Utils:execCmd action = 1 flags = 6 ohome = /u01/app/12.1.0.2/grid cmdname = setasmgidwrap.

CONCLUSION:

Always shutdown or startup RAC instances by using ‘srvctl start” through clusterware. Otherwise you might get ORA-27140 ORA-27300 ORA-27301 ORA-27302 ORA-273003 errors.