In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Monday, May 31, 2010

OUI-35000: Fatal cluster error encountered while adding a node.

PROBLEM:
OU1-35000: Fatal cluster error encountered ().
Correct the problem and try the operation again.

Solution:
Occurs during installation or adding a node in RAC environment.
Make sure the private node name or virtual hostnames are reachable. If they are not reachable, make sure they exist in DNS or /etc/hosts file.

Due to non availability or communication with clusterware. or wrong entry of host name, public IP, private IP or Virtual IP address during installation.
action: stop and start the CRS in all nodes. or correct the host IP Address.

[root@rac1 bin]# ./crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.demo.db    application    ONLINE    UNKNOWN   rac3
ora....o1.inst application    ONLINE    OFFLINE
ora....o2.inst application    ONLINE    OFFLINE
ora....osrv.cs application    ONLINE    UNKNOWN   rac1
ora....mo1.srv application    ONLINE    UNKNOWN   rac3
ora....mo2.srv application    ONLINE    UNKNOWN   rac1
ora....SM2.asm application    ONLINE    UNKNOWN   rac1
ora....C1.lsnr application    ONLINE    UNKNOWN   rac1
ora.rac1.gsd   application    ONLINE    UNKNOWN   rac1
ora.rac1.ons   application    ONLINE    UNKNOWN   rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM1.asm application    ONLINE    UNKNOWN   rac3
ora....C3.lsnr application    ONLINE    UNKNOWN   rac3
ora.rac3.gsd   application    ONLINE    UNKNOWN   rac3
ora.rac3.ons   application    ONLINE    UNKNOWN   rac3
ora.rac3.vip   application    ONLINE    ONLINE    rac3

[root@rac1 bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

[root@rac3 bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

[root@rac1 bin]# ./crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly

[root@rac3 bin]# ./crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly

[root@rac3 bin]# ./crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.demo.db    application    ONLINE    ONLINE    rac3
ora....o1.inst application    ONLINE    ONLINE    rac3
ora....o2.inst application    ONLINE    ONLINE    rac1
ora....osrv.cs application    ONLINE    ONLINE    rac1
ora....mo1.srv application    ONLINE    ONLINE    rac3
ora....mo2.srv application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM1.asm application    ONLINE    ONLINE    rac3
ora....C3.lsnr application    ONLINE    ONLINE    rac3
ora.rac3.gsd   application    ONLINE    ONLINE    rac3
ora.rac3.ons   application    ONLINE    ONLINE    rac3
ora.rac3.vip   application    ONLINE    ONLINE    rac3


Due to entering wrong IP address also OU1-35000 Fatal error occurs:
figure 1: mismatching of ip addresses
correcting the ip address: after correcting the ip address , no fatal cluster error occurs, lets try  as show in below figure.
hope, it will helps you. 
regards,
Rajeshkumar.

Thursday, May 27, 2010

Operating System Authentication to Communicate with the DATABASE.

To enable operating system authentication of an administrative user:
with and without OSOPER, OSDBA Groups granted to the operating system user.

1.Create an operating system account for the user.
2.Add the account to the OSDBA or OSOPER operating system defined groups

CREATE A USER 
[root@rac3 ~]# useradd -G dba,oper vivek
[root@rac3 ~]# passwd vivek
Changing password for user vivek.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

[root@rac3 ~]# su - vivek
[vivek@rac3 ~]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[vivek@rac3 ~]$ export ORACLE_PATH=$PATH:$ORACLE_HOME/bin
[vivek@rac3 ~]$ export ORACLE_SID=rev
[vivek@rac3 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/bin
[vivek@rac3 bin]$ ./sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 27 14:34:09 2010

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

ERROR:
ORA-01017: invalid username/password; logon denied

we can create a database user to allow an OS authenticated connection.
SQL> show parameter os_authent_prefix

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$

SQL> create user ops$vivek identified externally;

User created.

SQL> grant connect,resource to ops$vivek;

Grant succeeded.


[vivek@rac3 bin]$ ./sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 27 14:35:00 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select username from user_users;

USERNAME
------------------------------
OPS$VIVEK

SQL> show user;
USER is "OPS$VIVEK"
SQL>

workshop 2:
 without adding osoper, osdba groups to the user.
here i faced some errors, i have posted it with errors and solutions.
[oracle@rac3 ~]$ su -
Password:
[root@rac3 ~]# useradd jay
[root@rac3 ~]# passwd jay
Changing password for user jay.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@rac3 ~]#

[root@rac3 ~]# su - jay
[jay@rac3 ~]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[jay@rac3 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[jay@rac3 ~]$ export ORACLE_SID=rev
[jay@rac3 ~]$

[jay@rac3 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/bin
[jay@rac3 bin]$ ./sqlplus /
./sqlplus: error while loading shared libraries: libsqlplus.so:
cannot open shared object file: No such file or directory

SQL> show parameter os_authent_prefix

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
SQL> create user ops$jay identified externally;

User created.

SQL> grant connect,resource to ops$jay;

Grant succeeded.

[jay@rac3 bin]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[jay@rac3 bin]$ export PATH=$PATH:$ORACLE_HOME/bin
[jay@rac3 bin]$ ./sqlplus /

./sqlplus: error while loading shared libraries: libsqlplus.so:
cannot open shared object file: No such file or directory

---to solve this problem libsqlplus.so relink oracle as follows

[root@rac3 ~]# chmod -R 755 /u01/app/oracle/product/10.2.0/db_1/

[jay@rac3 bin]$ $ORACLE_HOME/bin/relink all


[jay@rac3 bin]$ ./sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 27 14:15:38 2010

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

ERROR:
ORA-01034: ORACLE not available
ORA-27121: unable to determine size of shared memory segment
Linux Error: 13: Permission denied


Enter user-name:

--to rectify this error , do the following steps
[jay@rac3 bin]$ ls -l $ORACLE_HOME/bin/oracle
-rwxr-xr-x 1 oracle oinstall 93300109 May 27 14:05 /u01/app/oracle/product/10.2.0/db_1/bin/oracle


[root@rac3 ~]# chmod -R 6751 /u01/app/oracle/product/10.2.0/db_1/

[jay@rac3 bin]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 93300109 May 27 14:05 /u01/app/oracle/product/10.2.0/db_1/bin/oracle
[jay@rac3 bin]$

[jay@rac3 bin]$ ./sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 27 14:19:09 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show user;
USER is "OPS$JAY"
SQL> select username from user_users;

USERNAME
------------------------------
OPS$JAY


Note:
1.Operating system authentication takes precedence over password file authentication.
If you meet the requirements for operating system authentication, then even if you use a password file, you will be authenticated by operating system authentication.
2.If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.
3.If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.

regards,
Rajeshkumar Govindarajan.

reference:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#i1006534

Saturday, May 8, 2010

Block Recovery using RMAN Backup

Block media recovery recovers an individual corrupt datablock or set of datablocks within a datafile. In cases when a small number of blocks require media recovery, you can selectively restore and recover damaged blocks rather than whole datafiles.
More theoretical information read

Here , I have done block recover using rman with datafile number and corrupted block number. using this there is no need of taking datafile offline.
you can perform this with smile steps,

for experiment i have created a user KIRAN and created a table DEMO_CORRUPT, corrupting datafile of tablespace users using operating system dd command, then i have recovered the corrupted blocks using RMAN Blockrecover command.

step 1: creating user and table in that schema.
step 2: taking rman backup for that particular tablespace or datafile.
step 3: corrupting datafile block using linux command dd(testing purpose only).
step 4: recover corrupted block using rman.
step 5: check the recovered table in that schema.

SQL> CREATE USER kiran IDENTIFIED BY kiran;

User created.

SQL> GRANT DBA TO kiran;

Grant succeeded.

SQL> CONN kiran/kiran;
Connected.
SQL> CREATE TABLE demo_corrupt(id NUMBER);

Table created.

SQL> INSERT INTO  demo_corrupt VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> COLUMN segment_name format a15
SQL>
SQL> SELECT segment_name,tablespace_name
  2  FROM dba_segments
  3  WHERE segment_name='DEMO_CORRUPT';

SEGMENT_NAME    TABLESPACE_NAME
--------------- ------------------------------
DEMO_CORRUPT    USERS

SQL>

SQL> COLUMN tablespace_name FORMAT a10
SQL> COL name FORMAT a43
SQL>
SQL> SELECT segment_name, a.tablespace_name, b.name
  2  FROM dba_segments a, v$datafile b
  3  WHERE a.header_file=b.file#
  4  AND a.segment_name='DEMO_CORRUPT'
  5  ;

SEGMENT_NAME    TABLESPACE NAME
--------------- ---------- -------------------------------------------
DEMO_CORRUPT    USERS      /u01/app/oracle/oradata/orcl/users01.dbf

SQL>

[oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 5 18:46:01 2010

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

connected to target database: ORCL (DBID=1245940166)
RMAN> BACKUP TABLESPACE USERS;

Starting backup at 05-MAY-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_05_05/o1_mf_nnndf_TAG20100505T185519_5y2wg0gj_.bkp tag=TAG20100505T185519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-MAY-10
RMAN> exit


Recovery Manager complete.


SQL> SELECT header_block from dba_segments
  2  WHERE segment_name='DEMO_CORRUPT';

HEADER_BLOCK
------------
          59

SQL>  

[oracle@cdbs1 ~]$ dd of=/u01/app/oracle/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=60 < corruption testing by rajesh
> EOF
0+1 records in
0+1 records out
[oracle@cdbs1 ~]$

SQL> CONN kiran/kiran;
Connected.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> SELECT * FROM demo_corrupt;
SELECT * FROM demo_corrupt
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 60)
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'


SQL> 

oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 5 19:03:38 2010

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

bconnected to target database: ORCL (DBID=1245940166)

RMAN>
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 60;

Starting blockrecover at 05-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_05_05/o1_mf_nnndf_TAG20100505T185519_5y2wg0gj_.bkp
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_05_05/o1_mf_nnndf_TAG20100505T185519_5y2wg0gj_.bkp tag=TAG20100505T185519
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished blockrecover at 05-MAY-10

RMAN> 

SQL> CONN kiran/kiran;
Connected.
SQL> SELECT * FROM demo_corrupt;

        ID
----------
         1
Regards,
Rajeshkumar Govindarajan.

Complete Recovery With RMAN Backup.

BACKUP AND RECOVERY SCENARIOS Complete Recovery With RMAN Backup.
previous post i have posted a complete recovery with user-managed backup,
here we are going to see the complete recovery using rman backup.
you can perform complete recovery in the following 5 situations.
  RMAN Recovery Scenarios of complete recovery.
1. Complete Closed Database Recovery. System datafile is missing
2. Complete Open Database Recovery. Non system datafile is missing
3. Complete Open Database Recovery (when the database is initially closed). Non system datafile is missing
4. Recovery of a Datafile that has no backups.
5. Restore and Recovery of a Datafile to a different location.
1.Complete Closed Database Recovery. System Datafile is missing
   In this case complete recovery is performed, only the system datafile is missing,
   so the database can be opened without reseting the redologs.
       1.  rman target /
       2.  startup mount;
       3.  restore database or datafile file#;
       4.  recover database or datafile file#;
       5.  alter database open;
workshop1:
SQL> create user sweety identified by sweety;

User created.

SQL> grant dba to sweety;

Grant succeeded.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host rm -rf /u01/app/oracle/oradata/testdb/system01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             130024128 bytes
Database Buffers          310378496 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/testdb/system01.dbf'


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>

[oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 7 23:53:51 2010

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     444596224 bytes

Fixed Size                     1219904 bytes
Variable Size                130024128 bytes
Database Buffers             310378496 bytes
Redo Buffers                   2973696 bytes

RMAN> RESTORE DATABASE;

Starting restore at 07-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/testdb/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/testdb/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/testdb/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/testdb/users01.dbf
restoring datafile 00005 to /u03/oradata/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TESTDB/backupset/2010_05_07/o1_mf_nnndf_TAG20100507T232259_5y8nvxt2_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TESTDB/backupset/2010_05_07/o1_mf_nnndf_TAG20100507T232259_5y8nvxt2_.bkp tag=TAG20100507T232259
channel ORA_DISK_1: restore complete, elapsed time: 00:02:52
Finished restore at 07-MAY-10

RMAN> RECOVER DATABASE;

Starting recover at 07-MAY-10
using channel ORA_DISK_1

starting media recovery


RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>
SQL> conn sys/oracle as sysdba;
Connected.
SQL> col name format a45
SQL> select name , status from v$datafile;

NAME                                          STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/testdb/system01.dbf   SYSTEM
/u01/app/oracle/oradata/testdb/undotbs01.dbf  ONLINE
/u01/app/oracle/oradata/testdb/sysaux01.dbf   ONLINE
/u01/app/oracle/oradata/testdb/users01.dbf    ONLINE
/u03/oradata/test01.dbf                       ONLINE

SQL> select username from dba_users
  2  where username='SWEETY';

USERNAME
------------------------------
SWEETY
2.Complete Open Database Recovery. Non system datafile is missing,
database is up
1. rman target /
2. sql 'alter tablespace offline immediate'; 
                                or
     sql 'alter database datafile file# offline;
3. restore datafile 3;
4. recover datafile 3;
5. sql 'alter tablespace online';
                          or

      sql 'alter database datafile file# online;
workshop2:

 

SQL> conn sweety/sweety;
Connected.
SQL> create table demo(id number);

Table created.

SQL> insert into demo values(123);

1 row created.

SQL> commit;

Commit complete.

SQL> conn sys/oracle as sysdba;
Connected.
SQL> select username,default_tablespace from dba_users   2  where username='SWEETY';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SWEETY                         USERS

SQL> host rm -rf /u01/app/oracle/oradata/testdb/users01.dbf

SQL> conn sweety/sweety
Connected.
SQL> alter system flush buffer_cache;

System altered.

SQL> select * from demo;
select * from demo
              *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

[oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 8 01:35:09 2010

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

connected to target database: TESTDB (DBID=2501713962)

RMAN> sql 'alter database datafile 4 offline';

using target database control file instead of recovery catalog
sql statement: alter database datafile 4 offline

RMAN> restore datafile 4;

Starting restore at 08-MAY-10
using channel ORA_DISK_1
...
channel ORA_DISK_1: restore complete, elapsed time: 00:00:09
Finished restore at 08-MAY-10

RMAN> recover datafile 4;

Starting recover at 08-MAY-10
using channel ORA_DISK_1

starting media recovery
......
media recovery complete, elapsed time: 00:00:05
Finished recover at 08-MAY-10

RMAN> sql 'alter database datafile 4 online';

sql statement: alter database datafile 4 online

RMAN>exit

SQL> conn sweety/sweety;
Connected.
SQL> select * from demo;

        ID
----------
       123

SQL>


3.Complete Open Database Recovery (when the database is initially closed).
Non system datafile is missing
A user datafile is reported missing when trying to startup the database. The datafile can be turned offline and the database started up. Restore and
recovery are performed using Rman. After recovery is performed the datafile can be turned online again.
1. sqlplus /nolog
2. connect / as sysdba
3. startup mount
4. alter database datafile '' offline;
5. alter database open;
6. exit;
7. rman target /
8. restore datafile '';
9. recover datafile '';
10. sql 'alter tablespace online';



workshop3:
 

SQL> conn sweety/sweety;
Connected.

SQL> create table test ( testid number);

Table created.

SQL> insert into test values(54321);

1 row created.

SQL> commit;

Commit complete.

SQL> conn sys/oracle as sysdba;
Connected.
SQL>shu immediate
SQL> host rm -rf /u01/app/oracle/oradata/testdb/users01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             138412736 bytes
Database Buffers          301989888 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'

SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database open;

Database altered.

[oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 8 01:51:45 2010

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

connected to target database: TESTDB (DBID=2501713962)

RMAN> restore datafile 4;

Starting restore at 08-MAY-10
using target database control file instead of recovery catalog
.....
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 08-MAY-10

RMAN> recover datafile 4;

Starting recover at 08-MAY-10
using channel ORA_DISK_1

starting media recovery
.....
media recovery complete, elapsed time: 00:00:08
Finished recover at 08-MAY-10

RMAN> exit

SQL> alter database datafile 4 online;

Database altered.

SQL> conn sweety/sweety;
Connected.
SQL> select * from test;

    TESTID
----------
     54321

4.Recovery of a Datafile that has no backups (database is up).
If a non system datafile that was not backed up since the last backup is missing,
recovery can be performed if all archived logs since the creation
of the missing datafile exist. Since the database is up you can check the tablespace name and put it offline. The option offline immediate is used
to avoid that the update of the datafile header.
Pre requisites: All relevant archived logs.
1. sqlplus '/ as sysdba'
2. alter tablespace offline immediate;
3. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf;
4. exit
5. rman target /
6. recover tablespace ;
7. sql 'alter tablespace online';
If the create datafile command needs to be executed to place the datafile on a
location different than the original use:
alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as
'/user/oradata/u02/dbtst/newdata01.dbf'

restriction: controlfile creation time must be prior than datafile creation time.
for more reference refer previous blog post.(user-managed complete recovery).
workshop4:
SQL> create user john identified by john
  2  default tablespace testing;

User created.

SQL> grant dba to john;

Grant succeeded.

SQL> conn john/john;
Connected.
SQL> create table test_tb( testid number);

Table created.

SQL> insert into test_tb values(1001);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_tb;

    TESTID
----------
      1001

SQL> conn sys/oracle as sysdba;
Connected.
SQL> host rm -rf /u03/oradata/test01.dbf

SQL> alter system flush buffer_cache;

System altered.

SQL> conn john/john;
Connected.
SQL> select * from test_tb;
select * from test_tb
              *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u03/oradata/test01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> conn sys/oracle as sysdba;
Connected.
SQL> alter tablespace testing offline immediate;

Tablespace altered.
---if you want to create datafile in same location
SQL> alter database create datafile '/u03/oradata/test01.dbf';

Database altered.
---if you want to create a datafile in different location(disk).
SQL> alter database create datafile '/u03/oradata/test01.dbf' as '/u01/app/oracle/oradata/testdb/test01.dbf';

Database altered.
[oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 8 02:15:28 2010

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

connected to target database: TESTDB (DBID=2501713962)

RMAN> recover tablespace testing;

Starting recover at 08-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK

starting media recovery

SQL> alter tablespace testing online;

Tablespace altered.

SQL> conn john/john;
Connected.
SQL> select * from test_tb;

    TESTID
----------
      1001

5.Restore and Recovery of a Datafile to a different location. Database is up.
If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs, complete cold or hot backup.
1. Use OS commands to restore the missing or corrupted datafile to the new
location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf
2. alter tablespace offline immediate;
3. alter tablespace rename datafile
'/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';
4. rman target /
5. recover tablespace ;
6. sql 'alter tablespace online';

workshop5: 

follow the same example workshop4 for workshop 5 except creating new datafile, here you have to copy the recent backup file to the new disk location and perform recovery. thats it , rest of the procedures are same.

regards,
Rajeshkumar Govindarajan.

Friday, May 7, 2010

Complete Recovery With User-managed Backup with Examples

BACKUP AND RECOVERY SCENARIOS
Complete Recovery With User-managed Backup.
you can perform complete recovery in the below 5 situations.

User Managed Recovery Scenarios of complete recovery.
   1. Complete Closed Database Recovery. System datafile is missing(with recent backups)                                                                                                   
   2. Complete Open Database Recovery. Non system datafile is missing(with backups).                                                                                                       
   3. Complete Open Database Recovery (when the database is initially closed). Non system datafile is missing(with backups)                       
  4. Recovery of a Missing Datafile that has no backups.(Disk corrupted and no backups available)
 restriction: datafile should be created after controlfile creation.(i.e,controlfile creation time is prior than datafile creation time).
you cannot recover or create datafile without backup in the following situation:
SQL> select CONTROLFILE_CREATED from v$database;
CONTROLFILE_CREATED
--------------------
07-MAY-2010 01:23:43  
SQL> select creation_time,name from v$datafile;
CREATION_TIME        NAME
-------------------- ---------------------------------------------
30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf
30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf
30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf
30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf 

  5. Restore and Recovery of a Datafile to a different location.(Disk corrupted having recent backup and recover the datafile in new Disk location).

  User Managed Recovery Scenarios
User managed recovery scenarios do require that the database is in archive log mode, and that backups of all datafiles and control files are made with the tablespaces set to begin backup, if the database is open while the copy is made. At the end of the copy of each tablespace it is necessaire to take it out of backup mode. Alternatively complete backups can be made with the database shutdown. Online redologs can be optionally backed up.
Files to be copied:
select name from v$datafile;
select member from v$logfile; # optional
select name from v$controlfile;
1.Complete Closed Database Recovery. System tablespace is missing
   If the system tablespace is missing or corrupted the database cannot be started up
   so a complete closed database recovery must be performed.
   Pre requisites: A closed or open database backup and archived logs.
        1. Use OS commands to restore the missing or corrupted system datafile to its original location from recent backup, ie:
           cp -p /user/backup/uman/system01.dbf  /user/oradata/u01/dbtst/system01.dbf
        2. startup mount;
        3. recover datafile 1;
        4. alter database open;
workshop1: system datafile recovery with recent backup


SQL> create user rajesh identified by rajesh;
User created.
SQL> grant dba to rajesh;
Grant succeeded.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
i manually deleted the datafile system01.dbf for testing purpose only
SQL> startup
ORACLE instance started.
Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             138412736 bytes
Database Buffers          301989888 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/testdb/system01.dbf'


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> host cp /u01/app/oracle/oradata/backup/system01.dbf /u01/app/oracle/oradata/testdb/system01.dbf
 system datafile restored from recent backup

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 7 12:51:16 2010

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

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             138412736 bytes
Database Buffers          301989888 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> recover datafile 1;
ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
ORA-00280: change 454383 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
ORA-00280: change 456007 for thread 1 is in sequence #8
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
.
.
.
ORA-00279: change 456039 generated at 05/07/2010 12:46:22 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_11_%u_.arc
ORA-00280: change 456039 for thread 1 is in sequence #11
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_10_5y7hl7dr_.arc' no longer needed for this recovery


Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     12
Next log sequence to archive   14
Current log sequence           14
SQL> select username from dba_users
  2  where username='RAJESH';

USERNAME
------------------------------
RAJESH 

2.Complete Open Database Recovery. Non system tablespace is missing
   If a non system tablespace is missing or corrupted while the database is open,   recovery can be performed while the database remain open.
   Pre requisites: A closed or open database backup and archived logs.
        1. Use OS commands to restore the missing or corrupted datafile to its original location, ie:
           cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf

        2. alter tablespace offline immediate;
        3. recover tablespace ;
        4. alter tablespace online;

workshop2: Non-system datafile recovery from recent backup when database is open 
SQL> ALTER USER rajesh DEFAULT TABLESPACE users;

User altered.

SQL> conn rajesh/rajesh;
Connected.
SQL> create table demo(id number);

Table created.

SQL> insert into demo values(123);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from demo;

        ID
----------
       123

SQL> conn sys/oracle as sysdba;
Connected.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16
i manually deleted the datafile users01.dbf for testing purpose only
SQL> conn rajesh/rajesh;
Connected.
SQL> alter system flush buffer_cache;

System altered.

SQL> select * from demo;
select * from demo
              *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'


SQL> conn sys/oracle as sysdba;
Connected.
SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u01/app/oracle/oradata/testdb/users01.dbf
restore the users01.dbf datafile from recent backup to the testdb folder

SQL> alter tablespace users offline immediate;

Tablespace altered.

SQL> recover tablespace users;
ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
ORA-00280: change 454383 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
ORA-00280: change 456007 for thread 1 is in sequence #8
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
.....
......
ORA-00279: change 456044 generated at 05/07/2010 12:46:28 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 456044 for thread 1 is in sequence #13
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_12_5y7hldl2_.arc' no longer needed for this recovery


Log applied.
Media recovery complete.
SQL> alter tablespace users online;

Tablespace altered.

SQL> conn rajesh/rajesh;
Connected.
SQL> select * from demo;

        ID
----------
       123 
3.Complete Open Database Recovery (when the database is initially closed).Non system datafile is missing
    If a non system tablespace is missing or corrupted and the database crashed, recovery can be performed after the database is open.
    Pre requisites: A closed or open database backup and archived logs.
 1.   startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)
 2.   alter database datafile3 offline; (tablespace cannot be used because the database is not open)
 3.   alter database open;
 4.   Use OS commands to restore the missing or corrupted datafile to its original location, ie:
    cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf
 5.   recover datafile 3;
 6.   alter tablespace online;
workshop 3:Non system datafile is missing


SQL> conn sys/oracle as sysdba;
Connected.
SQL> alter system switch logfile;

System altered.

SQL> select username,default_tablespace from dba_users
  2  where username='RAJESH';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
RAJESH                         USERS

SQL> conn rajesh/rajesh;
Connected.
SQL> create table testtbl (id number);

Table created.

SQL> insert into testtbl values(786);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from testtbl;

        ID
----------
       786

SQL> conn sys/oracle as sysdba;
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> --manually deleting the users01.dbf datafile from testdb folder
warning:for testing purpose only
SQL> host rm -rf /u01/app/oracle/oradata/testdb/users01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             142607040 bytes
Database Buffers          297795584 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'


SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u01/app/oracle/oradata/testdb/users01.dbf
copying user01.dbf from the recent backup to the testdb folder
SQL> recover datafile 4;
ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
ORA-00280: change 454383 for thread 1 is in sequence #7


Specify log: { =suggested | filename | AUTO | CANCEL} auto
ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
ORA-00280: change 456007 for thread 1 is in sequence #8
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
......
.........
ORA-00279: change 456046 generated at 05/07/2010 12:46:29 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_14_%u_.arc
ORA-00280: change 456046 for thread 1 is in sequence #14
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_13_5y7hlfbc_.arc' no longer needed for this recovery


Log applied.
Media recovery complete.
SQL> alter database datafile 4 online;

Database altered.

SQL> conn rajesh/rajesh;
Connected.
SQL> select * from testtbl;

        ID
----------
       786

4.Recovery of a Missing Datafile that has no backups (database is open). 
If a non system datafile that was not backed up since the last backup is missing,

recovery can be performed if all archived logs since the creation
of the missing datafile exist.
Pre requisites: All relevant archived logs.
1. alter tablespace offline immediate;
2. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf';
3. recover tablespace ;
4. alter tablespace online;

If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as
'/user/oradata/u02/dbtst/newdata01.dbf'

restriction: datafile should be created after controlfile creation.(i.e,controlfile creation time is prior than datafile creation time).
workshop 4: Missing Non-system Datafile having no backups
SQL> alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';

Session altered.

SQL> select controlfile_created from v$database;

CONTROLFILE_CREATED
--------------------
07-MAY-2010 16:27:22

SQL> col name format a45
SQL> select creation_time,name from v$datafile;

CREATION_TIME        NAME
-------------------- ---------------------------------------------
30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf
30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf
30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf
30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf
you cannot re-create the any one of the listed above datafile , without backup.
SQL> create tablespace testing datafile
  2  '/u01/app/oracle/oradata/testdb/test01.dbf' size 2m;

Tablespace created.

SQL> select creation_time,name from v$datafile;

CREATION_TIME        NAME
-------------------- ---------------------------------------------
30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf
30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf
30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf
30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf
07-MAY-2010 16:32:07 /u01/app/oracle/oradata/testdb/test01.dbf 
we can re-create test01.dbf file without backup.
SQL> select controlfile_created from v$database;

CONTROLFILE_CREATED
--------------------
07-MAY-2010 16:27:22

---we can recover the datafile test01.dbf without backup using
create datafile command in recovery
---in this example i am going to create a table in testing tablespace 
and deleted the test01.dbf datafile and recover it without backup and 
create datafile recovery command.

SQL> create user jay identified by jay
  2  default tablespace testing;

User created.

SQL> grant dba to jay;

Grant succeeded.

SQL> select username,default_tablespace from dba_users
  2  where username='JAY';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
JAY                            TESTING

SQL> conn jay/jay;
Connected.
SQL> create table demo (id number);

Table created.

SQL> insert into demo values(321);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from demo;

        ID
----------
       321

SQL> conn sys/oracle as sysdba;
Connected.
SQL> host rm -rf /u01/app/oracle/oradata/testdb/test01.dbf
---manually deleting datafile test01.dbf for testing purpose

SQL> conn jay/jay;
Connected.
SQL> select * from demo;

        ID
----------
       321

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from demo;
select * from demo
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/testdb/test01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL> alter database datafile 5 offline;

Database altered.
----TO CREATE A NEW RECOVERED DATAFILE IN SAME LOCATION.
SQL> alter database create datafile '/u01/app/oracle/oradata/testdb/test01.dbf';
Database altered.
----TO CREATE A NEW RECOVERED DATAFILE IN DIFFERENT LOCATION.
SQL> alter database create datafile '/u01/app/oracle/oradata/testdb/test01.dbf' as '/u03/oradata/test01.dbf';

Database altered.

SQL> recover datafile 5;
ORA-00279: change 454443 generated at 05/07/2010 16:32:07 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
ORA-00280: change 454443 for thread 1 is in sequence #8


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 454869 generated at 05/07/2010 16:41:38 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_9_%u_.arc
ORA-00280: change 454869 for thread 1 is in sequence #9
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_5y7xcbrm_.arc' no longer needed for this recovery
.....
.......
ORA-00279: change 454874 generated at 05/07/2010 16:41:45 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_11_%u_.arc
ORA-00280: change 454874 for thread 1 is in sequence #11
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_10_5y7xck8j_.arc' no longer needed for this recovery


Log applied.
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.

SQL> conn jay/jay;
Connected.
SQL> select * from demo;

        ID
----------
       321

SQL>


5.Restore and Recovery of a Datafile to a different location.
If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs.

1. Use OS commands to restore the missing or corrupted datafile to the new location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf
2. alter tablespace offline immediate;
3. alter tablespace rename datafile
'/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';
4. recover tablespace ;
5. alter tablespace online;
workshop 5:
SQL> create user lachu identified by lachu
  2  default tablespace users;

User created.

SQL> grant dba to lachu;

Grant succeeded.

SQL> conn lachu/lachu;
Connected.
SQL> create table test_tb(id number);

Table created.

SQL> insert into test_tb values(123);

1 row created.

SQL> commit;

Commit complete.

SQL> conn sys/oracle as sysdba;
Connected.
SQL> ---manually deleting users01.dbf datafile for testing purpose
SQL> host rm -rf '/u01/app/oracle/oradata/testdb/users01.dbf'

SQL> conn lachu/lachu;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST_TB                        TABLE
SQL> select * from test_tb;
select * from test_tb
              *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'


SQL> conn sys/oracle as sysdba;
Connected.
SQL> alter database datafile 4 offline;

Database altered.

SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u03/oradata/users01.dbf
--restore datafile user01.dbf to new disk from the recent backup of the database.

SQL> alter tablespace users rename datafile
  2  '/u01/app/oracle/oradata/testdb/users01.dbf' to '/u03/oradata/users01.dbf';
Tablespace altered.

SQL> recover datafile 4;
ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
ORA-00280: change 454383 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
ORA-00280: change 456007 for thread 1 is in sequence #8
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
....
......
ORA-00279: change 457480 generated at 05/07/2010 13:09:30 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_15_%u_.arc
ORA-00280: change 457480 for thread 1 is in sequence #15
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_14_5y7jxlvg_.arc' no longer needed for this recovery


Log applied.
Media recovery complete.
SQL> alter database datafile 4 online;

Database altered.

SQL> select name from v$datafile;

NAME
---------------------------------------------
/u01/app/oracle/oradata/testdb/system01.dbf
/u01/app/oracle/oradata/testdb/undotbs01.dbf
/u01/app/oracle/oradata/testdb/sysaux01.dbf
/u03/oradata/users01.dbf ----------restored in new location (disk)

SQL> conn lachu/lachu;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST_TB                        TABLE

SQL> select * from test_tb;

        ID
----------
       123

regards,
rajeshkumar govindarajan

Thursday, May 6, 2010

BLOCK RECOVERY Without Having RMAN Backup

Block media recovery recovers an individual corrupt datablock or set of datablocks within a datafile. In cases when a small number of blocks require media recovery, you can selectively restore and recover damaged blocks rather than whole datafiles.
More theoretical information read

It’s possible to perform Block Media Recovery with having only OS based “hot” backups and having NO RMAN backups.
Look at the following demonstration. Here:
1. Create a new user antony and a table corrupt_test in that schema.
2. Take OS backup (hot backup) of the users01.dbf where the table resides
3. Corrupt the data in that table and get block corruption error.
4. Connect with RMAN and try to use BLOCKRECOVER command. As we haven’t any backup, we get an error.
5. Catalog the “hot backup” to the RMAN repository.
6. Use BLOCKRECOVER command and recover the corrupted data block using cataloged “hot backup” of the datafile.
7. Query the table and get the data back!
Here is the scenario
SQL> CREATE USER antony IDENTIFIED BY antony;

User created.

SQL> GRANT DBA TO antony;

Grant succeeded.

SQL> CONN antony/antony;
Connected.
SQL> CREATE TABLE corrupt_test (id NUMBER);

Table created.

SQL> INSERT INTO corrupt_test VALUES(123);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> COLUMN segment_name format a15
SQL>  SELECT segment_name, tablespace_name from dba_segments
  2  WHERE segment_name='CORRUPT_TEST';

SEGMENT_NAME    TABLESPACE_NAME
--------------- ------------------------------
CORRUPT_TEST    USERS

SQL> COLUMN tablespace_name format a15
SQL> COLUMN name FORMAT a43
SQL> SELECT segment_name, a.tablespace_name, b.name
  2  FROM dba_segments a, v$datafile b
  3  WHERE a.header_file=b.file#
  4  AND a.segment_name='CORRUPT_TEST';

SEGMENT_NAME    TABLESPACE_NAME NAME
--------------- --------------- -------------------------------------------
CORRUPT_TEST    USERS           /u01/app/oracle/oradata/orcl/users01.dbf

SQL> ALTER TABLESPACE USERS BEGIN BACKUP;

Tablespace altered.

SQL> host cp  /u01/app/oracle/oradata/orcl/users01.dbf  /u01/app/oracle/oradata/backup/users01_backup.dbf

SQL> ALTER TABLESPACE USERS END BACKUP;

Tablespace altered.

SQL> SELECT header_block FROM dba_segments WHERE segment_name='CORRUPT_TEST';

HEADER_BLOCK
------------
          67

SQL>

 [oracle@cdbs1 ~]$ dd of=/u01/app/oracle/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=68 << EOF
> rajeshkumar testing block corruption
> EOF
0+1 records in
0+1 records out
[oracle@cdbs1 ~]$

SQL> Conn antony/antony
Connected.


 SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> select * from corrupt_test;
select * from corrupt_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 67)
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'

SQL> EXIT

 [oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 6 01:41:46 2010

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

connected to target database: ORCL (DBID=1245940166)
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 68;

Starting blockrecover at 06-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 05/06/2010 01:42:25
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore

RMAN>
RMAN> CATALOG DATAFILECOPY '/u01/app/oracle/oradata/backup/users01_backup.dbf';

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/backup/users01_backup.dbf recid=1 stamp=718249432
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 68;

Starting blockrecover at 06-MAY-10
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/oracle/oradata/backup/users01_backup.dbf

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished blockrecover at 06-MAY-10

RMAN> EXIT


Recovery Manager complete.
[oracle@cdbs1 ~]$sqlplus 

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 6 01:45:04 2010

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn antony/antony
Connected.
SQL> select * from CORRUPT_TEST;

        ID
----------
       123

SQL>


regards,
rajeshkumar govindarajan.
 reference:
 http://kamranagayev.wordpress.com

Wednesday, May 5, 2010

Command Line History and Editing in SQL*Plus and RMAN on Linux

rlwrap (readline wrapper) utility provides a command history and editing of keyboard input for any other command. 

This article explains how to install rlwrap and set it up for SQL*Plus and RMAN.

Download the latest rlwrap software from the following URL.
Unzip and install the software using the following commands.
gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install
Run the following commands, or better still append then to the ".bashrc" of the oracle software owner.
alias rlsqlplus='rlwrap sqlplus'
alias rlrman='rlwrap rman'
You can now start SQL*Plus or RMAN using "rlsqlplus" and "rlrman" respectively, and you will have a basic command history and the current line will be editable using the arrow and delete keys.

 [oracle@cdbs1 ~]$ rlrmanRecovery Manager: Release 10.2.0.1.0 - Production on Wed May 5 17:14:57 2010

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

RMAN> exit

Recovery Manager complete. 


Instead of rlrman and rlsqlplus, you can use your own alias name for rman and sqlplus. More than that now you can use up and down arrow for previous past queries.
 [oracle@cdbs1 ~]$ alias rajesh='rlwrap rman'
[oracle@cdbs1 ~]$ rajesh

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 5 17:15:27 2010

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

RMAN>


 [oracle@cdbs1 ~]$ alias lakshmi='rlwrap sqlplus'
[oracle@cdbs1 ~]$ lakshmi

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 5 17:21:38 2010

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SQL> select name from v$database;

NAME
---------
ORCL

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SQL> select name from v$database;

NAME
---------
ORCL
hope, it will be useful to you.

regards,
Rajeshkumar Govindarajan.
free counters
 
Share/Bookmark