ORA-00392: log X of thread X is being cleared, operation not allowed

Some days before when I was cloning one of the production database into UAT box, step to open database with resetlogs option failed due to insufficient space in one of the diskgroup.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log '+RADACTL1'
ORA-17502: ksfdcre:4 Failed to create file +RADACTL1
ORA-15041: diskgroup "RADACTL1" space exhausted

So I checked for the diskgroup with sufficient free space for creating redo logs and changed parameter accordingly.

SQL> alter system set db_create_online_log_dest_1='+RADA';
System altered.
SQL> alter system set db_create_online_log_dest_2='+RADA';
System altered.
SQL> sho parameter online
NAME TYPE VALUE
----------------------------------- ----------- ------------------------------
db_create_online_log_dest_1 string +RADA
db_create_online_log_dest_2 string +RADA
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string

Now once again I tried opening database with resetlogs option. This attempt once again failed but giving different error.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 12 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 12 thread 1: '+RADACTL1'
ORA-00312: online log 12 thread 1: '+RADACTL2'

I checked for status of redo log group 12:

 SQL> select status from v$log where GROUP#=12;
STATUS 
----------------
CLEARING_CURRENT

I decided to clear this group manually:

SQL> alter database clear unarchived logfile group 12;
Database altered.

Now once again I attempted opening database with resetlogs option & it worked without issues. 🙂

SQL> alter database open resetlogs;
Database altered.

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Advertisements

Writing custom messages in alert log

Writing custom messages in alert log … IS IT POSSIBLE???

Yes it is… We can edit, update or delete alert log entries with any editor, but oracle has a package that does the same work: dbms_system.ksdwrt.

We can write our own customized messages in alert.log sitting at SQL*PLUS session using undocumented KSDWRT procedure (part of DBMS_SYSTEM).


 

begin
sys.dbms_system.ksdwrt(2, '<Message Text>');
end;
/

 

The first parameter of sys.dbms_system.ksdwrt indicates the destination:

* Use 1 to write to the trace file.
* Use 2 to write to the “alert.log”.
* Use 3 to write to both.

The second parameter is the message.

This feature can be very useful in testing – Generic Alert Log Error metric in Enterprise Manager or other monitoring tools.

Note from Metalink about DBMS_SYSTEM:


 

This package should in fact only be installed when requested by Oracle Support. 
It is not documented in the server documentation.
As such the package functionality may change without notice.
It is to be used only as directed by Oracle Support and its use otherwise is not supported.
As per internal Note 153324.1:
Generally, if a package is not in the Oracle documentation at all, it is intentional, as it is not for end user use. Non-documented packages should be avoided by customers unless specifically instructed to use them by either Oracle Support or Oracle Development.

 

Reference : How To Edit, Read, and Query the Alert.Log (Doc ID 1072547.1)

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

ORA-01031: Insufficient Privileges while creating mview in different schema.

Recently I faced very interesting issue, when user was complaining that he is getting (SCHEMA:TEST1) ORA-01031: insufficient privileges while creating materialized view under different schema (SCHEMA:TEST2) though TEST1 has DBA role assigned.

For TEST1:

SQL> select * from dba_sys_privs where grantee='TEST1';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST1 UNLIMITED TABLESPACE NO
SQL> select * from dba_role_privs where grantee='TEST1';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST1 DBA NO YES
TEST1 CONNECT NO YES
TEST1 RESOURCE NO YES

For TEST2:

SQL> select * from dba_sys_privs where grantee='TEST2';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST2 UNLIMITED TABLESPACE NO
SQL> select * from dba_role_privs where grantee='TEST2';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST2 CONNECT NO YES
TEST2 RESOURCE NO YES

 

When user tried creating mview in TEST2 schema by connecting schema TEST1 (though having DBA role assigned) was getting ORA-01031: insufficient privileges. I also tried same using SYSTEM user but same error:

SQL> conn test1/test1
Connected.
SQL> create materialized view test2.mv_t as select * from test2.t;
create materialized view test2.mv_t as select * from test2.t
 *
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn system/rmbman08
Connected.
SQL> create materialized view test2.mv_t as select * from test2.t;
create materialized view test2.mv_t as select * from test2.t
 *
ERROR at line 1:
ORA-01031: insufficient privileges

After searching on Metalink, I found very interesting note : ORA-01031: Insufficient Privileges When Create A Mview In Different Schema (Doc ID 749112.1). It clearly stats:

This is not a code bug; this is expected behavior. As explained above, the owner of the base table should be granted with the ‘create table’ privilege explicitly and not through a role. Granting the create table privilege explicitly to the owner of the base table allows us to create the materialized view in another schema.

After this I tried provided solution:

SQL> conn / as sysdba
Connected.
SQL> grant create table to test2;
Grant succeeded.
SQL> conn test1/test1
Connected.
SQL> create materialized view test2.mv_t as select * from test2.t;
Materialized view created.

Solved 🙂

Just to put some light on this “expected behavior“, Mview creation also creates one table under mview owner with same name.

SQL> @obj
Enter value for object_name: MV_T
old 3: where object_NAME = upper('&object_name')
new 3: where object_NAME = upper('MV_T')
OBJECT_NAME OWNER OBJECT_TYPE
-------------------------------------------------- ------------------------------ -------------------
MV_T TEST2 TABLE
MV_T TEST2 MATERIALIZED VIEW

 

This means TEST2 should have create table privilege which had been already assigned to TEST2 though role RESOURCE.

In metalink note : Role Restrictions (Doc ID 11740.1) u will find following information:

“USERS CANNOT PERFORM DDL BASED ON PRIVILEGES RECEIVED THROUGH A ROLE”:
This restriction is necessary because roles can be enabled or disabled dynamically, making security checking at runtime ambiguous. For example, if a user is granted SELECT on a table via a role, enables that role, creates a procedure to select from the table, and then disables that role,should the procedure be allowed to execute? The user still has the privileges, but they are not currently active.Roles are intended for use as a tool to administer privileges for end users; they are not intended for use by application developers.

So here we got solution with perfect explanation 🙂


Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath