Technical solutions for excellence in business: May 2013

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

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

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

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

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> ;