Thursday 23 May 2013

Oracle database: While enabling archivelog mode ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


While trying to change the database into archivelog mode I got this error:
-----------------


SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

----------


Do not close the database in a way that it will require instance recovery(e.g. shutdown abort)
before you will mount it for archivelog mode change otherwise you
will get the below error "ORA-00265", always try to shutdown the database gracefully
by using "shutdown immediate".


SQL> alter system checkpoint;

System altered.

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup restrict
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/oracle11g
Oldest online log sequence     438
Next log sequence to archive   440
Current log sequence           440                                                     




Wednesday 1 May 2013

ORA-1653: unable to extend table SYS.AUD$ by 512 in tablespace SYSTEM


ORA-1653: unable to extend table SYS.AUD$ by 512 in tablespace SYSTEM

ORA-1653: unable to extend table SYS.AUD$ by 512 in tablespace SYSTEM


when I saw the allert log.

"alert.log" 
ORA-1653: unable to extend table SYS.AUD$ by 512 in tablespace SYSTEM

and even I cannot login into the DB via TOAD.


so logged into the unix server directly and invoked the sqlplus.
and then backed up the aud$ table and truncated the data in it.

SQL> select count(*) from aud$;

COUNT(*)
----------
200055


SQL> Create table aud_backup tablespace users as select * from aud$ ;

Table created.

SQL> truncate table aud$;

Table truncated.

SQL> select count(*) from aud$;

COUNT(*)
----------
0

Number of Visitors