Thursday, May 2, 2013

Oracle: Steps to recover when Redo Files are Lost


RCAT>>select name from v$database;

NAME
---------
RCAT

RCAT>>alter system set db_recovery_file_dist='+DAT' scope=both;
alter system set db_recovery_file_dist='+DAT' scope=both
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dist='+DATA' scope=both
RCAT>>/
alter system set db_recovery_file_dist='+DATA' scope=both
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest='+DATA' scope=both
RCAT>>/
alter system set db_recovery_file_dest='+DATA' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE


RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest_size=10M scope=both
RCAT>>/

System altered.

RCAT>>/ed
SP2-0042: unknown command "/ed" - rest of line ignored.
RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest='+DAT' scope=both
RCAT>>/

System altered.

RCAT>>archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2
RCAT>>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
RCAT>>startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
RCAT>>shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
RCAT>>startuo
SP2-0042: unknown command "startuo" - rest of line ignored.
RCAT>>startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 1896
Session ID: 1 Serial number: 5


RCAT>>startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>shutdown
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>shutdown immediate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Administrator>
C:\Users\Administrator>
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:03 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> select * from v$instance
  2  ;
select * from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


SQL> exit
Disconnected

C:\Users\Administrator>set oracle_sid=rcat

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:42 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
ORA-03113: end-of-file on communication channel
Process ID: 3208
Session ID: 1 Serial number: 3


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Administrator>set oracle_sid=rcat

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:59 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
SQL> alter database mount ;

Database altered.

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL>





--
Fauzi
+965-97324246

  


Wednesday, May 1, 2013

Oracle : Recovery Steps when control file is lost. If backup was done


SQL> startup;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> select status fron v$instance;
select status fron v$instance
                   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> ed
Wrote file afiedt.buf

  1* select status from v$instance
SQL> /

STATUS
------------
STARTED

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      F:\RCAT\CONTROL01.CTL, F:\RCAT
                                                 \CONTROL02.CTL
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>
SQL> shutdown
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'F:\RCAT\SYSTEM01.DBF'


SQL> recover database using backup controlfile;
ORA-00279: change 967649 generated at 05/01/2013 10:32:22 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEBASE\DATABASE\RDBMS\ARC0000000003_0814268715.0001
ORA-00280: change 967649 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\rcat\redo01.log
ORA-00328: archived log ends at change 950490, need later change 967649
ORA-00334: archived log: 'F:\RCAT\REDO01.LOG'


SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 NO
INACTIVE                947455 01-MAY-13       950491 01-MAY-13

         3          1          3   52428800        512          1 NO
CURRENT                 959675 01-MAY-13   2.8147E+14

         2          1          2   52428800        512          1 NO
INACTIVE                950491 01-MAY-13       959675 01-MAY-13


SQL> recover database using backup controlfile;
ORA-00279: change 967649 generated at 05/01/2013 10:32:22 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEBASE\DATABASE\RDBMS\ARC0000000003_0814268715.0001
ORA-00280: change 967649 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\rcat\redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 NO
CURRENT                 972032 02-MAY-13   2.8147E+14

         2          1          0   52428800        512          1 YES
UNUSED                       0                      0

         3          1          0   52428800        512          1 YES
UNUSED                       0                      0


SQL> set sqlprompt RCAT>>
RCAT>>
--

Fauzi
+965-97324246

  


Oracle: To create spfile copy

From SP file:
 
SYS>>create pfile='f:\oracpara.ora' from spfile;
File created.
 
From Memory:
SYS>>create pfile='f:\oracpara.ora' from memory;
File created.

Oracle: To run a structural change sql statement from RMAN:

To run a structural change sql statement from RMAN:
 
RMAN> sql 'alter database enable block change tracking'
2> ;