restore UNDO tablespace from RMAN

[oracle@shiven instance]$ sqlplus sys/sys as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 3 11:01:08 2014


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /data1/shiven01/dbs/arch
Oldest online log sequence     1
Current log sequence           3
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             163580064 bytes
Database Buffers          364904448 bytes
Redo Buffers                5832704 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            /data1/shiven01/dbs/arch
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
You have new mail in /var/spool/mail/oracle
[oracle@shiven instance]$ rman target /


Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 3 11:02:28 2014


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


connected to target database: SHIVEN01 (DBID=900949679)


RMAN> backup database;


Starting backup at 03-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/data1/shiven01/dbs/sysaux01.dbf
input datafile file number=00001 name=/data1/shiven01/dbs/system01.dbf
input datafile file number=00003 name=/data1/shiven01/dbs/undotbs01.dbf
input datafile file number=00004 name=/data1/shiven01/dbs/users01.dbf
input datafile file number=00005 name=/data1/shiven01/dbs/indx01.dbf
input datafile file number=00006 name=/data1/shiven01/dbs/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-14
channel ORA_DISK_1: finished piece 1 at 03-JUN-14
piece handle=/data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_nnndf_TAG20140603T110238_9rtqhqqc_.bkp tag=TAG20140603T110238 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-JUN-14
channel ORA_DISK_1: finished piece 1 at 03-JUN-14
piece handle=/data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_ncsnf_TAG20140603T110238_9rtqjkty_.bkp tag=TAG20140603T110238 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JUN-14


RMAN> exit




Recovery Manager complete.
[oracle@shiven instance]$ rm -f /data1/shiven01/dbs/undotbs01.dbf
[oracle@shiven instance]$ sqlplus sys/sys as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 3 11:03:26 2014


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> shutdown immediate
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/data1/shiven01/dbs/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             163580064 bytes
Database Buffers          364904448 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
You have new mail in /var/spool/mail/oracle
[oracle@shiven instance]$ rman target /


Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 3 11:04:25 2014


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


connected to target database: SHIVEN01 (DBID=900949679, not open)


RMAN> restore tablespace UNDOTBS1;


Starting restore at 03-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /data1/shiven01/dbs/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_nnndf_TAG20140603T110238_9rtqhqqc_.bkp
channel ORA_DISK_1: piece handle=/data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_nnndf_TAG20140603T110238_9rtqhqqc_.bkp tag=TAG20140603T110238
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 03-JUN-14


RMAN> recover tablespace UNDOTBS1;


Starting recover at 03-JUN-14
using channel ORA_DISK_1


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


Finished recover at 03-JUN-14


RMAN> exit




Recovery Manager complete.
You have new mail in /var/spool/mail/oracle
[oracle@shiven instance]$ sqlplus sys/sys as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 3 11:05:43 2014


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> alter database open;


Database altered.



Comments

Post a Comment

Popular posts from this blog

Installing DBMS_JAVA package in Oracle and calling UTL_DBWS web services through Oracle database

Installing DBMS_JAVA package in Oracle following  files need to be run:-  sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%'; @?/javavm/install/initjvm.sql; @?/xdk/admin/initxml.sql; @?/xdk/admin/xmlja.sql; @?/rdbms/admin/catjava.sql; @?/rdbms/admin/catexf.sql; and verify your java installation in Oracle Database  sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%'; sql> select dbms_java.longname(object_name), status, object_type from all_objects where       object_type = 'JAVA CLASS'; ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ calling  UTL_DBWS  web services through Oracle Database :-  set the follwing parameters in .bash_profile of oracle user  export JAVA_HOME=$ORACLE_HOME/jdk ORACLE_BASE=/opt/oracle/product; export ORACLE_BASE ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1; exp...
Read more

stick the job to the specific instance in Oracle RAC

First method ( and easy one too ) :- note:-  must perform this activity on each instance one by one. ......stop jobs which is assigning to the specific instance..... $ export ORACLE_SID=SID connect database using job owner .... $ sqlplus username/password list of jobs and its instance....  sql> select JOB,INSTANCE from user_jobs;        JOB   INSTANCE ---------- ----------       2510          0       2951          0       2992          0       3091          0       3111          0       1570          0       1567          0       1568          0       1569     ...
Read more

finding fragmentation at table level and tablespace level in Oracle and steps to remove them

Use this query :- select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",     round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",     round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"     from all_tables  WHERE Owner NOT IN (        'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',        'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',        'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',        'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',        'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',        'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',        'SI_INFORMTN_SCHEMA', 'XDB', 'ODM'); Or This one :- It will collect the data which ...
Read more