AnySQL.net English

    Someone required a database recovery after a sudden server crash due to the CPU fans. He asked to use AUL software to recovery the table structure, procedure & package source code etc. AUL is not good at recovering them, it's good at recover the data. And a server crash should not cause such a big problem usually, you should be able to open it in normal way in most cases, even the database is running in noarchivelog mode.

    Let me tell you the correct steps you should take :

1, Mount the database. If you lost the control file, recreate it.

2, Get the active online log file and current online log file.

3, Try to open the database with "alter database open" command.

4, If failed to open the database, then issue the "recover database" command, input the absolute path of the active online log file, then current online log file when prompted for the archive log file.

    Usually you should be able to open the database after a server crash. Don't issue the "ALTER DATABASE OPEN RESETLOGS" first, always backup all the files before you open it with resetlogs option, so when anything wrong, you can restore to the original state.

    For today's case, the DBA try to open the database with resetlogs option first without backup, so I have use some hidden parameters to skip the consistent check, and offline some rollback segments to skip the transaction recovery. The problem is he have to create a new database and export the data from old database, and import them to the new database.

    The data volume is 210GB, is it easy and quick to perform export and import?

    I saw somebody execute the delete statement on DBA_TABLESPACES directly without using drop tablespace statement by mistake. Non of us hit this issue before, so it's very hard to give him an answer. Today I test it on my personal database (10g version).

SQL> delete from dba_tablespaces where tablespace_name='USERS';

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

    I decide to restart the database because I am worrying about whether I can open the database. But lucky I open the database without any error, but get ORA-600 error when I try to query the table in that tablespace.

SQL> select * from anysql.emp;
select * from anysql.emp
                     *
ERROR at line 1:
ORA-00600: internal error code, arguments: [5119], [], [], [], [], [], [], []

    I find out the deleted row with AUL software, and then load it into sys.ts$ table, and restart the database successfully. Everything is fine now, no errors when I query that table again. But of cause, you should not make this kind of mistake.

    Usually I get it from the first row of V$MYSTAT, this is a good way.

SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM =1;

       SID
----------
     19949

    In Oracle 10g or above, we can use a more simple way with the USERENV function

SQL> SELECT USERENV('SID') FROM DUAL;

USERENV('SID')
--------------
         19949

    Sometime I also use the following method, but in Oracle 10g, it does work but return 2 rows for you, so actually not work for you in Oracle 10g or above. We should not use it any more.

SQL> SELECT SID FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');

       SID
----------
     19949
     19829

    Are there any other ways?.