SQL> select name,user from v$database;NAME USER
--------- ------------------------------
ORCL SYSSQL> select open_mode from v$database;OPEN_MODE
--------------------
READ WRITESQL> select username,default_tablespace from dba_users where username='SH';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SH USERS

SQL> conn scott/oracle
Connected.
SQL> select count(1) from emp;

COUNT(1)
----------
14

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> cl scr

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
[oracle@localhost ~]$ cd /home/oracle/app/oracle/oradata/orcl/
[oracle@localhost orcl]$ ls -lrt
total 2754360
-rw-r----- 1 oracle oracle 239869952 Mar 23 21:40 users01.dbf
-rw-r----- 1 oracle oracle 52429312 Mar 23 21:40 redo03.log
-rw-r----- 1 oracle oracle 52429312 Mar 23 21:40 redo02.log
-rw-r----- 1 oracle oracle 85991424 Mar 23 21:40 example01.dbf
-rw-rw---- 1 oracle oracle 8396800 Mar 23 21:40 APEX_1265209995679366.dbf
-rw-rw---- 1 oracle oracle 2105344 Mar 23 21:40 APEX_1246426611663638.dbf
-rw-r----- 1 oracle oracle 165683200 Mar 23 22:40 temp01.dbf
-rw-r----- 1 oracle oracle 845160448 Mar 23 23:05 system01.dbf
-rw-r----- 1 oracle oracle 1158684672 Mar 23 23:07 sysaux01.dbf
-rw-r----- 1 oracle oracle 144711680 Mar 23 23:08 undotbs01.dbf
-rw-r----- 1 oracle oracle 52429312 Mar 23 23:08 redo01.log
-rw-r----- 1 oracle oracle 9748480 Mar 23 23:08 control01.ctl
[oracle@localhost orcl]$ rm *.dbf
[oracle@localhost orcl]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Mar 23 23:09:18 2013

Copyright (c) 1982, 2008, 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> conn scoot/tiger
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn scott/oracle
Connected.
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> alter system flush sga;
alter system flush sga
*
ERROR at line 1:
ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

SQL> alter system flush shared pool;
alter system flush shared pool
*
ERROR at line 1:
ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

SQL> conn /as sysdba
Connected.
SQL> alter system flush shared pool;
alter system flush shared pool
*
ERROR at line 1:
ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

SQL> alter system flush shared_pool;

System altered.

SQL> conn scott/oracle
ERROR:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> exit
Disconnected
[oracle@localhost orcl]$ ps -ef | grep -i pmon
oracle 2309 1 0 21:39 ? 00:00:11 ora_pmon_orcl
oracle 3513 3193 0 23:12 pts/1 00:00:00 grep -i pmon
[oracle@localhost orcl]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Mar 23 23:12:55 2013

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

Connected.
SQL> shut immediate
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shut abort
ORACLE instance shut down.

========================== break =================================================
[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Mar 23 23:13:11 2013

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
[oracle@localhost ~]$

========================= break ==============================

SQL> startup nomount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
SQL>

============================= Break ===========================================

RMAN> run {
2> alter database mount;
3> restore database;
4> recover database;
5> }

database mounted
released channel: ORA_DISK_1

Starting restore at 23-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0
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 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_21/o1_mf_nnnd0_FULL_WEEKLY_HOT_8lcsnr7j_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_21/o1_mf_nnnd0_FULL_WEEKLY_HOT_8lcsnr7j_.bkp tag=FULL_WEEKLY_HOT
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:26
Finished restore at 23-MAR-13

Starting recover at 23-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /home/oracle/app/oracle/oradata/orcl/system01.dbf
destination for restore of datafile 00002: /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
destination for restore of datafile 00003: /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
destination for restore of datafile 00005: /home/oracle/app/oracle/oradata/orcl/example01.dbf
destination for restore of datafile 00006: /home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf
destination for restore of datafile 00007: /home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_nnnd1_LVL1C_FULL_DB_8lmjd52r_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_nnnd1_LVL1C_FULL_DB_8lmjd52r_.bkp tag=LVL1C_FULL_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=360
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=361
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=362
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=363
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp tag=TAG20130224T034327
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_360_8nx712yq_.arc thread=1 sequence=360
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_360_8nx712yq_.arc RECID=17 STAMP=810861876
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_361_8nx7131f_.arc thread=1 sequence=361
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_361_8nx7131f_.arc RECID=14 STAMP=810861875
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_362_8nx713x6_.arc thread=1 sequence=362
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_362_8nx713x6_.arc RECID=15 STAMP=810861876
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_363_8nx714b8_.arc thread=1 sequence=363
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_363_8nx714b8_.arc RECID=16 STAMP=810861876
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
media recovery complete, elapsed time: 00:00:14
Finished recover at 23-MAR-13
starting full resync of recovery catalog
full resync complete

RMAN> alter database open;

database opened

RMAN>

============================ Break =============================

SQL> select name,user,open_mode from v$database;

NAME USER OPEN_MODE
--------- ------------------------------ --------------------
ORCL SYS READ WRITE

SQL> select count(1) from scott.emp;

COUNT(1)
----------
14

SQL> select count(1) from scott.dept;

COUNT(1)
----------
4

SQL>

=======================================================

[oracle@localhost orcl]$ ls -lrt
total 2593432
-rw-rw---- 1 oracle oracle 239869952 Mar 23 23:25 users01.dbf
-rw-r----- 1 oracle oracle 52429312 Mar 23 23:25 redo03.log
-rw-r----- 1 oracle oracle 52429312 Mar 23 23:25 redo01.log
-rw-rw---- 1 oracle oracle 85991424 Mar 23 23:25 example01.dbf
-rw-rw---- 1 oracle oracle 8396800 Mar 23 23:25 APEX_1265209995679366.dbf
-rw-rw---- 1 oracle oracle 2105344 Mar 23 23:25 APEX_1246426611663638.dbf
-rw-rw---- 1 oracle oracle 20979712 Mar 23 23:25 temp01.dbf
-rw-rw---- 1 oracle oracle 144711680 Mar 23 23:31 undotbs01.dbf
-rw-rw---- 1 oracle oracle 845160448 Mar 23 23:32 system01.dbf
-rw-rw---- 1 oracle oracle 1158684672 Mar 23 23:34 sysaux01.dbf
-rw-r----- 1 oracle oracle 52429312 Mar 23 23:35 redo02.log
-rw-r----- 1 oracle oracle 9748480 Mar 23 23:35 control01.ctl
[oracle@localhost orcl]$ rm system01.dbf
[oracle@localhost orcl]$ sqlplus '/as sysdba'SQL*Plus: Release 11.1.0.7.0 - Production on Sat Mar 23 23:35:12 2013Copyright (c) 1982, 2008, Oracle. All rights reserved.ERROR:
ORA-01075: you are currently logged onEnter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

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

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@localhost orcl]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Mar 23 23:35:22 2013

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

SQL> conn /as sysdba
ERROR:
ORA-01075: you are currently logged on

SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORA-00000: normal, successful completion
SQL> select open_mode from v$database;
SP2-0640: Not connected
SQL> SELECT file#, name FROM v$datafile;
SP2-0640: Not connected
SQL> conn '/as sysdba'
Enter password:
ERROR:
ORA-01005: null password given; logon denied

SQL> shut abort
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> SELECT file#, name FROM v$datafile;
SELECT file#, name FROM v$datafile
*
ERROR at line 1:
ORA-01507: database not mounted

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

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> SELECT file#, name FROM v$datafile;

FILE#
----------
NAME
--------------------------------------------------------------------------------
1
/home/oracle/app/oracle/oradata/orcl/system01.dbf

2
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

3
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

FILE#
----------
NAME
--------------------------------------------------------------------------------
4
/home/oracle/app/oracle/oradata/orcl/users01.dbf

5
/home/oracle/app/oracle/oradata/orcl/example01.dbf

6
/home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf

FILE#
----------
NAME
--------------------------------------------------------------------------------
7
/home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf

7 rows selected.

SQL> desc v$datafile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
FOREIGN_DBID NUMBER
FOREIGN_CREATION_CHANGE# NUMBER
FOREIGN_CREATION_TIME DATE
PLUGGED_READONLY VARCHAR2(3)
PLUGIN_CHANGE# NUMBER
PLUGIN_RESETLOGS_CHANGE# NUMBER
PLUGIN_RESETLOGS_TIME DATE

SQL> select file#,name,status from v$datafile;

FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------
1
/home/oracle/app/oracle/oradata/orcl/system01.dbf
SYSTEM

2
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
ONLINE

FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------

3
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
ONLINE

4
/home/oracle/app/oracle/oradata/orcl/users01.dbf

FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------
ONLINE

5
/home/oracle/app/oracle/oradata/orcl/example01.dbf
ONLINE

6

FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------
/home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.dbf
ONLINE

7
/home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.dbf
ONLINE

7 rows selected.

SQL>

====================== break ===============================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Mar 23 23:39:06 2013

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

connected to target database: ORCL (DBID=1229390655, not open)
connected to recovery catalog database

RMAN> run {
2> restore datafile 1;
3> recover datafile 1;
4> alter database open;
5> }

Starting restore at 23-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_21/o1_mf_nnnd0_FULL_WEEKLY_HOT_8lcsnr7j_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_21/o1_mf_nnnd0_FULL_WEEKLY_HOT_8lcsnr7j_.bkp tag=FULL_WEEKLY_HOT
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:06
Finished restore at 23-MAR-13

Starting recover at 23-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_nnnd1_LVL1C_FULL_DB_8lmjd52r_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_nnnd1_LVL1C_FULL_DB_8lmjd52r_.bkp tag=LVL1C_FULL_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=360
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=361
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=362
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=363
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp tag=TAG20130224T034327
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_360_8nx83bfk_.arc thread=1 sequence=360
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_360_8nx83bfk_.arc RECID=22 STAMP=810862973
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_361_8nx83bjo_.arc thread=1 sequence=361
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_361_8nx83bjo_.arc RECID=19 STAMP=810862971
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_362_8nx83cvw_.arc thread=1 sequence=362
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_362_8nx83cvw_.arc RECID=20 STAMP=810862972
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_363_8nx83d7w_.arc thread=1 sequence=363
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_363_8nx83d7w_.arc RECID=21 STAMP=810862972
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
media recovery complete, elapsed time: 00:00:04
Finished recover at 23-MAR-13

database opened

RMAN>

============================= break ===========================

SQL> SELECT file#, name,status FROM v$datafile;

FILE# NAME STATUS
----- ------------------------------------------------------------ ----------
1 /home/oracle/app/oracle/oradata/orcl/system01.dbf SYSTEM
2 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
3 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
4 /home/oracle/app/oracle/oradata/orcl/users01.dbf ONLINE
5 /home/oracle/app/oracle/oradata/orcl/example01.dbf ONLINE
6 /home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.d ONLINE
bf

7 /home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.d ONLINE
bf

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='SYSTEM';

TABLESPACE_NAME STATUS
------------------------------ ----------
SYSTEM ONLINE

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
[oracle@localhost orcl]$ ls -lrt
total 2593432
-rw-rw---- 1 oracle oracle 20979712 Mar 23 23:25 temp01.dbf
-rw-r----- 1 oracle oracle 52429312 Mar 23 23:43 redo01.log
-rw-rw---- 1 oracle oracle 239869952 Mar 23 23:43 users01.dbf
-rw-r----- 1 oracle oracle 52429312 Mar 23 23:43 redo02.log
-rw-rw---- 1 oracle oracle 85991424 Mar 23 23:43 example01.dbf
-rw-rw---- 1 oracle oracle 8396800 Mar 23 23:43 APEX_1265209995679366.dbf
-rw-rw---- 1 oracle oracle 2105344 Mar 23 23:43 APEX_1246426611663638.dbf
-rw-rw---- 1 oracle oracle 845160448 Mar 23 23:49 system01.dbf
-rw-rw---- 1 oracle oracle 144711680 Mar 23 23:49 undotbs01.dbf
-rw-rw---- 1 oracle oracle 1158684672 Mar 23 23:49 sysaux01.dbf
-rw-r----- 1 oracle oracle 52429312 Mar 23 23:49 redo03.log
-rw-r----- 1 oracle oracle 9748480 Mar 23 23:49 control01.ctl
[oracle@localhost orcl]$ rm users01.dbf
[oracle@localhost orcl]$ sqlplus '/as sysdba'SQL*Plus: Release 11.1.0.7.0 - Production on Sat Mar 23 23:50:30 2013Copyright (c) 1982, 2008, 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 optionsSQL> col file# for 999
SQL> col name for a60
SQL> col status for a10
SQL> set linesize 120
SQL> SELECT file#, name,status FROM v$datafile where tablespace_name='USERS';
SELECT file#, name,status FROM v$datafile where tablespace_name='USERS'
*
ERROR at line 1:
ORA-00904: "TABLESPACE_NAME": invalid identifier

SQL> DESC V$DATAFILE
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
FOREIGN_DBID NUMBER
FOREIGN_CREATION_CHANGE# NUMBER
FOREIGN_CREATION_TIME DATE
PLUGGED_READONLY VARCHAR2(3)
PLUGIN_CHANGE# NUMBER
PLUGIN_RESETLOGS_CHANGE# NUMBER
PLUGIN_RESETLOGS_TIME DATE

SQL> desc dba_data_files
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)

SQL> select file_name,file_id,tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME
---------- ------------------------------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name='USERS';

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME STATUS
---------- ------------------------------ ----------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,TS#,status fro v$datafile where file#=4;
select file#,TS#,status fro v$datafile where file#=4
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
----- ---------- ----------
4 4 ONLINE

SQL> alter system flush shared_pool;

System altered.

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

SQL>
SQL> select name,user ,open_mode from v$database;

NAME USER OPEN_MODE
------------------------------------------------------------ ------------------------------ --------------------
ORCL SYS READ WRITE

SQL>

======================== break =============================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Mar 23 23:55:32 2013

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

connected to target database: ORCL (DBID=1229390655)
connected to recovery catalog database

RMAN> run {
2> restore datafile 4;
3> recover datafile 4;
4> }

Starting restore at 23-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/users01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/23/2013 23:56:44
ORA-19573: cannot obtain exclusive enqueue for datafile 4
ORA-19600: input file is datafile-copy 2 (/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf)
ORA-19601: output file is datafile 4 (/home/oracle/app/oracle/oradata/orcl/users01.dbf)

RMAN>

=========================== break =============================

SQL> ALTER DATABASE DATAFILE 4 OFFLINE;

Database altered.

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name='USERS';

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME STATUS
---------- ------------------------------ ----------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
----- ---------- ----------
4 4 RECOVER

SQL> select name,user ,open_mode from v$database;

NAME USER OPEN_MODE
------------------------------------------------------------ ------------------------------ --------------------
ORCL SYS READ WRITE

SQL>

============================== break =========================================

RMAN> run {
2> restore datafile 4;
3> recover datafile 4;
4> sql 'atler database datafile 4 online';
5> }

Starting restore at 24-MAR-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0
Finished restore at 24-MAR-13

Starting recover at 24-MAR-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
archived log for thread 1 with sequence 368 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc thread=1 sequence=366
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-MAR-13
starting full resync of recovery catalog
full resync complete

sql statement: atler database datafile 4 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/24/2013 00:01:08
RMAN-11003: failure during parse/execution of SQL statement: atler database datafile 4 online
ORA-00900: invalid SQL statement

RMAN>

RMAN> run {
2> sql 'alter database datafile 4 online';
3> }

sql statement: alter database datafile 4 online

RMAN>

========================== break =============================

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name='USERS';

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME STATUS
---------- ------------------------------ ----------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
----- ---------- ----------
4 4 ONLINE

SQL> select count(1) from scott.emp;

COUNT(1)
----------
14

SQL> create table test (sno number) tablespace users;

Table created.

SQL>

============================================================================================

Lost non system datafile :( Online method )

[oracle@localhost orcl]$ ls -lrt
total 2593488
-rw-r----- 1 oracle oracle 52429312 Mar 23 23:43 redo01.log
-rw-r----- 1 oracle oracle 52429312 Mar 23 23:43 redo02.log
-rw-rw---- 1 oracle oracle 85991424 Mar 23 23:43 example01.dbf
-rw-rw---- 1 oracle oracle 8396800 Mar 23 23:43 APEX_1265209995679366.dbf
-rw-rw---- 1 oracle oracle 2105344 Mar 23 23:43 APEX_1246426611663638.dbf
-rw-rw---- 1 oracle oracle 20979712 Mar 24 00:00 temp01.dbf
-rw-rw---- 1 oracle oracle 239869952 Mar 24 00:06 users01.dbf
-rw-rw---- 1 oracle oracle 1158684672 Mar 24 00:10 sysaux01.dbf
-rw-rw---- 1 oracle oracle 144711680 Mar 24 00:11 undotbs01.dbf
-rw-rw---- 1 oracle oracle 845160448 Mar 24 00:11 system01.dbf
-rw-r----- 1 oracle oracle 52429312 Mar 24 00:11 redo03.log
-rw-r----- 1 oracle oracle 9748480 Mar 24 00:12 control01.ctl
[oracle@localhost orcl]$ rm users01.dbf
[oracle@localhost orcl]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 24 00:12:39 2013

Copyright (c) 1982, 2008, 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> col file# for 999
col name for a60
col status for a10
set linesize 120
select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name='USERS';SQL> SQL> SQL> SQL>

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME STATUS
---------- ------------------------------ ----------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
----- ---------- ----------
4 4 ONLINE

SQL> alter system flush shared_pool;

System altered.

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
----- ---------- ----------
4 4 ONLINE

SQL> select count(1) from scott.emp;

COUNT(1)
----------
14

SQL> alter system flush shared_pool;

System altered.

SQL> select count(1) from scott.emp;

COUNT(1)
----------
14

SQL> drop table test;

Table dropped.

SQL> create table test (sno number) tablespace users;
create table test (sno number) tablespace users
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> select name,user ,open_mode from v$database;

NAME USER OPEN_MODE
------------------------------------------------------------ ------------------------------ --------------------
ORCL SYS READ WRITE

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
----- ---------- ----------
4 4 ONLINE

SQL> shut immediate
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
----- ---------- ----------
4 4 ONLINE

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name='USERS';
select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name='USERS'
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
----- ---------- ----------
4 4 ONLINE

SQL>

====================== break ==============================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Mar 24 00:18:11 2013

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

connected to target database: ORCL (DBID=1229390655, not open)
connected to recovery catalog database

RMAN> run {
2> restore datafile 4;
3> recover datafile 4;
4> sql 'alter database datafile 4 online';
5> }

Starting restore at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0
Finished restore at 24-MAR-13

Starting recover at 24-MAR-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
archived log for thread 1 with sequence 368 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc thread=1 sequence=366
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-MAR-13
starting full resync of recovery catalog
full resync complete

sql statement: alter database datafile 4 online

RMAN>

========================================== break ===================================================

SQL> alter database open;

Database altered.

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name='USERS';

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME STATUS
---------- ------------------------------ ----------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,TS#,status from v$datafile where file#=4;

FILE# TS# STATUS
----- ---------- ----------
4 4 ONLINE

SQL> select count(1) from scott.emp;

COUNT(1)
----------
14

SQL> create table test (sno number) tablespace users;

Table created.

SQL> drop table test;

Table dropped.

SQL> sho parameter recycle

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on
SQL> purge recyclebin;

Recyclebin purged.

SQL>

Restoring a tablespace : (open mode)[oracle@localhost orcl]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 24 00:32:07 2013

Copyright (c) 1982, 2008, 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> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME STATUS
------------------------------ ---------
USERS ONLINE

SQL> desc dba_tablespaces
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)

SQL> desc v$tablespace
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)

SQL> select ts#,name from v$tablespace where name='USERS';

TS# NAME
---------- ------------------------------
4 USERS

SQL> desc dba_data_files
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME STATUS
---------- ------------------------------ ---------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> desc v$datafile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
FOREIGN_DBID NUMBER
FOREIGN_CREATION_CHANGE# NUMBER
FOREIGN_CREATION_TIME DATE
PLUGGED_READONLY VARCHAR2(3)
PLUGIN_CHANGE# NUMBER
PLUGIN_RESETLOGS_CHANGE# NUMBER
PLUGIN_RESETLOGS_TIME DATE

SQL> select file#,ts#,status from v$datafile where ts#=4;

FILE# TS# STATUS
---------- ---------- -------
4 4 ONLINE

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
[oracle@localhost orcl]$ rm users01.dbf
[oracle@localhost orcl]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 24 00:35:42 2013

Copyright (c) 1982, 2008, 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> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME STATUS
------------------------------ ---------
USERS ONLINE

SQL> select ts#,name from v$tablespace where name='USERS';

TS# NAME
---------- ------------------------------
4 USERS

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME STATUS
---------- ------------------------------ ---------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,ts#,status from v$datafile where ts#=4;

FILE# TS# STATUS
---------- ---------- -------
4 4 ONLINE

SQL> create table test (sno number) tablespace users;
create table test (sno number) tablespace users
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> alter tablespace users offline force;
alter tablespace users offline force
*
ERROR at line 1:
ORA-02141: invalid OFFLINE option

SQL> alter tablespace users force offline;
alter tablespace users force offline
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

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

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01109: database not open

SQL>

================================= break ===========================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Mar 24 00:41:20 2013

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

connected to target database: ORCL (DBID=1229390655, not open)
connected to recovery catalog database

RMAN> run {
2> restore tablespace users;
3> recover tablespace users;
4> sql 'alter database open';
5> }

Starting restore at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0
Finished restore at 24-MAR-13

Starting recover at 24-MAR-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
archived log for thread 1 with sequence 368 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
archived log for thread 1 with sequence 369 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc thread=1 sequence=366
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc thread=1 sequence=367
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-MAR-13
starting full resync of recovery catalog
full resync complete

sql statement: alter database open

RMAN>

=================================== Break ==============================================

SQL> select name,user ,open_mode from v$database;

NAME USER OPEN_MODE
--------- ------------------------------ --------------------
ORCL SYS READ WRITE

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME STATUS
------------------------------ ---------
USERS ONLINE

SQL> select ts#,name from v$tablespace where name='USERS';

TS# NAME
---------- ------------------------------
4 USERS

SQL> select file_name,file_id,tablespace_name,status from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME STATUS
---------- ------------------------------ ---------
/home/oracle/app/oracle/oradata/orcl/users01.dbf
4 USERS AVAILABLE

SQL> select file#,ts#,status from v$datafile where ts#=4;

FILE# TS# STATUS
---------- ---------- -------
4 4 ONLINE

SQL> create table test (sno number) tablespace users;

Table created.

SQL> drop table test;

Table dropped.

SQL> purge recyclebin;

Recyclebin purged.

SQL>

Restoring a datafile if no backups[oracle@localhost orcl]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 24 01:52:39 2013

Copyright (c) 1982, 2008, 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> create tablespace test_ts datafile '/home/oracle/app/oracle/oradata/orcl/test_ts01.dbf' size 5m;

Tablespace created.

SQL> create table test tablespace test_ts as select * from scott.emp;

Table created.

SQL> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

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
[oracle@localhost orcl]$ rm test_ts01.dbf
[oracle@localhost orcl]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 24 01:54:06 2013

Copyright (c) 1982, 2008, 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> select count(1) from test;

COUNT(1)
----------
14

SQL> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> alter system flush shared_pool;

System altered.

SQL> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> select segment_name,tablespace_name from dba_segments where segment_name='TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
TEST
TEST_TS

SQL> shut immediate
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/home/oracle/app/oracle/oradata/orcl/test_ts01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL>

=============================================== Break =========================================================

RMAN> run {
2> sql 'alter database datafile 8 offline';
3> restore datafile 8;
4> recover datafile 8;
5> sql 'alter database datafile 8 online';
6> }

starting full resync of recovery catalog
full resync complete
sql statement: alter database datafile 8 offline

Starting restore at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK

creating datafile file number=8 name=/home/oracle/app/oracle/oradata/orcl/test_ts01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 24-MAR-13

Starting recover at 24-MAR-13
using channel ORA_DISK_1

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

Finished recover at 24-MAR-13

sql statement: alter database datafile 8 online

RMAN>

============================================== Break ==========================================

SQL> alter database open;

Database altered.

SQL> create table test tablespace test_ts as select * from scott.emp;
create table test tablespace test_ts as select * from scott.emp
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> create table test1 tablespace test_ts as select * from scott.emp;

Table created.

SQL>
SQL> drop table test;

Table dropped.

SQL> drop table test1;

Table dropped.

SQL>

SQL> drop tablespace test_ts including contents and datafiles;

Tablespace dropped.

SQL>

Restoring a datafile to different location[oracle@localhost orcl]$ clear
[oracle@localhost orcl]$ ls -lrt user*
-rw-rw---- 1 oracle oracle 239869952 Mar 24 02:01 users01.dbf
[oracle@localhost orcl]$ rm users01.dbf
[oracle@localhost orcl]$ sqlplus '/as sysdba'SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 24 02:10:36 2013Copyright (c) 1982, 2008, 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> create table scott.test tablespace uses as select * from scott.emp;
create table scott.test tablespace uses as select * from scott.emp
*
ERROR at line 1:
ORA-00959: tablespace 'USES' does not exist

SQL> create table scott.test tablespace users as select * from scott.emp;
create table scott.test tablespace users as select * from scott.emp
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> alter database datafile 4 offline;

Database altered.

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
[oracle@localhost orcl]$ mkdir askm
[oracle@localhost orcl]$ cd askm
[oracle@localhost askm]$ pwd
/home/oracle/app/oracle/oradata/orcl/askm
[oracle@localhost askm]$ ls
[oracle@localhost askm]$

=============================================== Break ==========================================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Mar 24 02:12:20 2013

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

connected to target database: ORCL (DBID=1229390655)
connected to recovery catalog database

RMAN> run {
2> set newname for datafile 4 to '/home/oracle/app/oracle/oradata/orcl/askm/users01.dbf';
3> restore datafile 4;
4> switch datafile 4;
5> recover datafile 4;
6> sql 'alter database datafile 4 online';
7> }

executing command: SET NEWNAME
starting full resync of recovery catalog
full resync complete

Starting restore at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=808202600 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
destination for restore of datafile 00004: /home/oracle/app/oracle/oradata/orcl/askm/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/app/oracle/oradata/orcl/askm/users01.dbf RECID=10 STAMP=810872051
Finished restore at 24-MAR-13

datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=810872055 file name=/home/oracle/app/oracle/oradata/orcl/askm/users01.dbf
starting full resync of recovery catalog
full resync complete

Starting recover at 24-MAR-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
archived log for thread 1 with sequence 368 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
archived log for thread 1 with sequence 369 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc
archived log for thread 1 with sequence 370 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_370_8nxclvch_.arc
archived log for thread 1 with sequence 371 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc thread=1 sequence=366
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc thread=1 sequence=367
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc thread=1 sequence=368
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc thread=1 sequence=369
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-MAR-13
starting full resync of recovery catalog
full resync complete

sql statement: alter database datafile 4 online

RMAN>

================================= Break ======================================================

[oracle@localhost askm]$ pwd
/home/oracle/app/oracle/oradata/orcl/askm
[oracle@localhost askm]$ ls -lrt
total 234484
-rw-rw---- 1 oracle oracle 239869952 Mar 24 02:14 users01.dbf
[oracle@localhost askm]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 24 02:15:49 2013

Copyright (c) 1982, 2008, 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> create table scott.test tablespace users as select * from scott.emp;

Table created.

SQL> drop table scott.test;

Table dropped.

SQL>

Restoring controlfile
=======================SQL> desc v$controlfile
Name Null? Type
----------------------------------------- -------- ----------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBERSQL> select name,status from v$controlfile;NAME
--------------------------------------------------------------------------------
STATUS
-------
/home/oracle/app/oracle/oradata/orcl/control01.ctl/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

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
[oracle@localhost askm]$ rm /home/oracle/app/oracle/oradata/orcl/control01.ctl /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
[oracle@localhost askm]$ ps -ef | grep -i pmon
oracle 5502 1 0 01:56 ? 00:00:04 ora_pmon_orcl
[oracle@localhost askm]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 24 02:25:15 2013

Copyright (c) 1982, 2008, 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> create table scott.test tablespace users as select * from scott.emp;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> shut immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/home/oracle/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL>

================================== Break =================================================

[oracle@localhost ~]$ rman target / catalog rman/oracle@rmandb

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Mar 24 02:29:14 2013

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

connected to target database: ORCL (not mounted)
connected to recovery catalog database

RMAN> set dbid=1229390655

executing command: SET DBID
database name is "ORCL" and DBID is 1229390655

RMAN> restore controlfile;

Starting restore at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_ncsn1_LVL1C_FULL_DB_8lmjhknt_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_ncsn1_LVL1C_FULL_DB_8lmjhknt_.bkp tag=LVL1C_FULL_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 24-MAR-13

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/24/2013 02:30:27
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> recover database;

Starting recover at 24-MAR-13
Starting implicit crosscheck backup at 24-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 24-MAR-13

Starting implicit crosscheck copy at 24-MAR-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-MAR-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_372_8nxkowqt_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_370_8nxclvch_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_8ln2ptb0_.dbf
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_23/o1_mf_ncsn1_LVL1C_FULL_DB_8lmjhknt_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T033253_8lmym81j_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp

using channel ORA_DISK_1
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=810873044 file name=/home/oracle/app/oracle/oradata/orcl/askm/users01.dbf

starting media recovery

archived log for thread 1 with sequence 364 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc
archived log for thread 1 with sequence 365 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc
archived log for thread 1 with sequence 366 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc
archived log for thread 1 with sequence 367 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc
archived log for thread 1 with sequence 368 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc
archived log for thread 1 with sequence 369 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc
archived log for thread 1 with sequence 370 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_370_8nxclvch_.arc
archived log for thread 1 with sequence 371 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc
archived log for thread 1 with sequence 372 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_372_8nxkowqt_.arc
archived log for thread 1 with sequence 373 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo01.log
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=360
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=361
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=362
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=363
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_24/o1_mf_annnn_TAG20130224T034327_8lmz7150_.bkp tag=TAG20130224T034327
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_360_8nxky72y_.arc thread=1 sequence=360
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_360_8nxky72y_.arc RECID=22 STAMP=810873048
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_361_8nxky74t_.arc thread=1 sequence=361
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_361_8nxky74t_.arc RECID=19 STAMP=810873048
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_362_8nxky831_.arc thread=1 sequence=362
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_362_8nxky831_.arc RECID=20 STAMP=810873048
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_363_8nxky876_.arc thread=1 sequence=363
channel default: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_363_8nxky876_.arc RECID=21 STAMP=810873048
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_364_8ln4t99n_.arc thread=1 sequence=364
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_365_8ln4tbcr_.arc thread=1 sequence=365
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_02_24/o1_mf_1_366_8ln4td5w_.arc thread=1 sequence=366
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_367_8nx72rtt_.arc thread=1 sequence=367
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_23/o1_mf_1_368_8nx83s2t_.arc thread=1 sequence=368
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_369_8nxbcp4n_.arc thread=1 sequence=369
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_370_8nxclvch_.arc thread=1 sequence=370
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc thread=1 sequence=371
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc thread=1 sequence=371
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_371_8nxj70f0_.arc thread=1 sequence=371
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_24/o1_mf_1_372_8nxkowqt_.arc thread=1 sequence=372
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=373
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-MAR-13

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/24/2013 02:31:09
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "resetlogs, ;"
RMAN-01008: the bad identifier was: resetlog
RMAN-01007: at line 1 column 21 file: standard input

RMAN>

============================================ Break ========================================================

SQL> alter database open resetlogs;

Database altered.

SQL> select name,user,open_mode from v$database;

NAME USER OPEN_MODE
--------- ------------------------------ --------------------
ORCL SYS READ WRITE

SQL> archive list logs;
SP2-0734: unknown command beginning "archive li..." - rest of line ignored.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>

============================================== Break ==========================================

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 18 ORCL 1229390655 PARENT 1 13-AUG-09
1 2 ORCL 1229390655 CURRENT 754488 30-OCT-09

RMAN>

============================================== Break ===========================================

1. Create a directory pointing to any physical directory on the server 
2. Create a loopback database link. It means, you have to create a database link pointing to the same database.  

NOTE : If your requirement is to copy schema to a different schema, create a database link pointing to target database. 

1. Use the impdp to copy schema to another schema.

SQL> create a directory TEST_DIR as ‘/xxxx/exp-dir/’;
SQL> create a database link SCOTT_DB_LINK connect to SYSTEM identified by  xxxxxxx using ‘DB-A’;

If you are trying to copy schema to a different database , say DB-B , create database link as follows.

SQL> create a database link SCOTT_DB_LINK connect to system identified by xxxxx using ‘DB-B’;

(NOTE : We should be able to resolve the alias DB-A to database A and DB-B to database B.)

Verify the database link

SQL> select * from dual@SCOTT_DB_LINK;

Use the following syntax to copy schema,

impdp system/xxxxxx schemas=SCOTT directory=TEST_DIR network_link=SCOTT_DB_LINK  remap_schema=SCOTT:SCOTT_NEW logfile=TEST_DIR:SCOTT_NEW.log

Verify the new schema with the source schema with following commands.

SQL> select object_type,count(1) from dba_objects where owner='SCOTT' and status='INVALID' group by object_type;
SQL> select object_type,count(1) from dba_objects where owner='SCOTT_NEW' and status='INVALID' group by object_type;
SQL> select object_name,object_type from dba_objects where owner='SCOTT' and status='INVALID';
SQL> select object_name,object_type from dba_objects where owner='SCOTT_NEW' and status='INVALID';

Observations:
We are eliminating the creation of dump file, by using database link with the parameter network_link

Hope it helps
SRI 

Once you have selected the agents, click continue

It runs the plugin pre-requisites and click next

Click Deploy, to deploy the plug-in on the agents

Oracle VM Manager Registration Page Title	Entry Selections and Entry Syntax
Name	The name entered in the "Name" text box is displayed on the Infrastructure Cloud page.
EM Agent URL	The EM Agent URL is selected by clicking the magnifying glass icon. A pop-up window will appear, select the Oracle VM Manager agent from the list. The EM Agent URL syntax is: "https://<AGENT-FQDN>:3872/emd/main/" Note: The Oracle VM Manager host can be selected as long as the host is managed target by OEM with the Oracle Virtualization plug-in.
Oracle VM Manager Core	Enter "http://<ORACLE VM MANAGER HOST FQDN>:7001" in the Oracle VM Manager Core URL text box.
Automatic Synchronization	Select the Automatic Synchronization check box to enable automated synchronization between OEM and the Oracle VM API Core.
Monitoring Credentials for Oracle VM Manager	Enter the Oracle VM Manager admin user name and the admin password in the Monitoring Credentials for Oracle VM Manager text fields.
Administration Credentials for Oracle VM Manager / Use Administration Credentials	Enter the Oracle VM Manager admin user name and the admin password in the Administration Credentials for Oracle VM Manager text fields. Select the Use Administration Credentials check box.
Oracle VM Manager Console URL	Enter the non-SSL Oracle VM Manager URL, i.e.http://<ORACLE VM MANAGER HOST FQDN>:7001/ovm/console/faces/login.jspx in the Oracle VM Manager Console URL text box.
Submit	Once all of the information has been entered, click the Submit button to start the registration job.

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      orcl                           PRIMARY          TO STANDBY

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence     524
Next log sequence to archive   526
Current log sequence           526
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      sbyorcl                        PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence     524
Next log sequence to archive   0
Current log sequence           526
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED
---------- ---------
517 YES
518 YES
519 YES
520 YES
521 YES
522 YES
523 YES
524 YES
525 IN-MEMORY

9 rows selected.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL>

SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf

SQL> alter tablespace example offline;

Tablespace altered.

[oracle@dgaskmpri01 OPatch]$ mv /home/oracle/app/oracle/oradata/orcl/example01.dbf  /tmp/askm/example01_temp.dbf
[oracle@dgaskmpri01 OPatch]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:47:17 2011

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 tablespace example rename datafile '/home/oracle/app/oracle/oradata/orcl/example01.dbf' to '/tmp/askm/example01_temp.dbf';

Tablespace altered.

SQL> alter tablespace example online;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
/tmp/askm/example01_temp.dbf

SQL>

SQL> select ts#,name from v$tablespace where name='EXAMPLE';

TS# NAME
---------- ------------------------------
6 EXAMPLE

SQL> select name from v$datafile where ts#=6;

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>

[oracle@dgaskmsby01 askm]$ mv /home/oracle/app/oracle/oradata/sbyorcl/example01.dbf /tmp/askm/example01_temp.dbf
[oracle@dgaskmsby01 askm]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:53:49 2011

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             343935672 bytes
Database Buffers          104857600 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> alter database rename file '/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf' to '/tmp/askm/example01_temp.dbf';

Database altered.

SQL> select name from v$datafile where ts#=6;

NAME
--------------------------------------------------------------------------------
/tmp/askm/example01_temp.dbf

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      orcl                           PRIMARY          TO STANDBY

SQL> arcvhive log list
SP2-0734: unknown command beginning "arcvhive l..." - rest of line ignored.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence     522
Next log sequence to archive   524
Current log sequence           524
SQL>

SQL> select name,user,database_role,switchover_status from v$database;

NAME      USER                           DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      SYS                            PHYSICAL STANDBY NOT ALLOWED

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      sbyorcl                        PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence     522
Next log sequence to archive   0
Current log sequence           524
SQL>

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
sbyorcl          MOUNTED

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
sbyorcl          OPEN

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL>

[oracle@dgaskmpri01 ~]$ mkdir /tmp/askm
[oracle@dgaskmpri01 ~]$ chmod 777 /tmp/askm
[oracle@dgaskmpri01 ~]$

[oracle@dgaskmpri01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 04:25:18 2011

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> conn system/xxxxx
Connected.
SQL> create database link db_sbyorcl connect to system identified by oracle using 'sbyorcl';

Database link created.

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
------------------------------
orcl

SQL> select db_unique_name from v$database@db_sbyorcl;

DB_UNIQUE_NAME
------------------------------
sbyorcl

SQL> create directory sby_dump_dir as '/tmp/askm';

Directory created.

SQL>

[oracle@dgaskmpri01 askm]$ expdp system/oracle full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log

Export: Release 11.2.0.2.0 - Production on Tue Nov 22 04:50:05 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 183.1 MB
Processing object type DATABASE_EXPORT/TABLESPACE

.....
.....
....

ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 7564
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP('OE',0,1,'11.02.00.00.00',newblock)
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_CUBE_EXP
ORA-16000: database open for read-only access
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_CUBE_EXP"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 7564
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PROCACT_SCHEMA:"OE"]
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_METADATA", line 5466

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358

----- PL/SQL Call Stack -----
object      line  object
handle    number  name
0x34f7b2c0     19208  package body SYS.KUPW$WORKER
0x34f7b2c0      8385  package body SYS.KUPW$WORKER
0x34f7b2c0      2935  package body SYS.KUPW$WORKER
0x34f7b2c0      9054  package body SYS.KUPW$WORKER
0x34f7b2c0      1688  package body SYS.KUPW$WORKER
0x34d28650         2  anonymous block

Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 04:55:56

[oracle@dgaskmpri01 askm]$ expdp system/oracle attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.2.0 - Production on Tue Nov 22 05:03:32 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: B2533E2386512016E040A8C022010D27
Start Time: Tuesday, 22 November, 2011 5:03:43
Mode: FULL
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        system/******** full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /tmp/askm/sby_db.dmp
bytes written: 950,272

Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Object Schema: OE
Object Type: DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Completed Objects: 36
Worker Parallelism: 1

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

[oracle@dgaskmpri01 askm]$ clear
[oracle@dgaskmpri01 askm]$