SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38713: Flashback Database logging is already turned on.
SQL> alter database open;
Database altered.
SQL> create tablespace tbs datafile '/11g_install/app/sonujha/
oradata/Airtel/temp01.sonu' size 10m;
Tablespace created.
SQL> create user sujeet identified by sujeet;
User created.
SQL> grant sysdba to sujeet;
Grant succeeded.
SQL> alter user sujeet default tablespace tbs;
User altered.
SQL> create table tbs (id number);
Table created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
744951
SQL> drop tablespace tbs including contents and datafile;
Enter value for datafile:
old 1: drop tablespace tbs including contents and datafile
new 1: drop tablespace tbs including contents
Tablespace dropped.
SQL> desc v$datafile;
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/11g_install/app/sonujha/oradata/Airtel/system01.dbf
/11g_install/app/sonujha/oradata/Airtel/sysaux01.dbf
/11g_install/app/sonujha/oradata/Airtel/undotbs01.dbf
/11g_install/app/sonujha/oradata/Airtel/users01.dbf
/11g_install/app/sonujha/oradata/Airtel/example01.dbf
SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
SQL> flashback database to scn 744951;
Flashback complete.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/11g_install/app/sonujha/oradata/Airtel/system01.dbf
/11g_install/app/sonujha/oradata/Airtel/sysaux01.dbf
/11g_install/app/sonujha/oradata/Airtel/undotbs01.dbf
/11g_install/app/sonujha/oradata/Airtel/users01.dbf
/11g_install/app/sonujha/oradata/Airtel/example01.dbf
/11g_install/app/sonujha/product/11.1.0/db_1/dbs/UNNAMED00006
6 rows selected.
SQL> select file_id,file_name from dba_data_files;
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
4
/11g_install/app/sonujha/oradata/Airtel/users01.dbf
3
/11g_install/app/sonujha/oradata/Airtel/undotbs01.dbf
2
/11g_install/app/sonujha/oradata/Airtel/sysaux01.dbf
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
1
/11g_install/app/sonujha/oradata/Airtel/system01.dbf
5
/11g_install/app/sonujha/oradata/Airtel/example01.dbf
6
/11g_install/app/sonujha/product/11.1.0/db_1/dbs/UNNAMED00006
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
7
/11g_install/app/sonujha/oradata/Airtel/sona.dbf
7 rows selected.
SQL> select status from dba_data_files;
STATUS
---------
AVAILABLE
AVAILABLE
AVAILABLE
AVAILABLE
AVAILABLE
AVAILABLE
AVAILABLE
7 rows selected.
SQL> create table t1(t number);
Table created.
SQL> select file_name,tablespace_name,bytes/1024/1024 from
dba_data_files where tablespace_name='TBS';
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/11g_install/app/sonujha/product/11.1.0/db_1/dbs/UNNAMED00006
TBS
SQL> alter database rename file '/11g_install/app/sonujha/
product/11.1.0/db_1/dbs/UNNAMED00006' to '/11g_install/app/
sonujha/oradata/Airtel/temp01.sonu';
Database altered.
SQL> alter database create datafile '/11g_install/app/sonujha/
oradata/Airtel/temp01.sonu';
Database altered.
SQL> recover datafile 6;
ORA-00279: change 744647 generated at 09/23/2011 14:49:51
needed for thread 1
ORA-00289: suggestion :
/11g_install/app/sonujha/flash_recovery_area/AIRTEL/archivelog/
2011_09_23/o1_mf_
1_8_77ryhwcf_.arc
ORA-00280: change 744647 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
SQL> select bytes,TABLESPACE_NAME from dba_data_files;
No comments:
Post a Comment