Database Charachter set change from WE8MSWIN1252 to utf8

The case is we have a database with characterset WE8MSWIN1252, we need to migrate the schemas to different database which has different characterset.
What we did is we ran CSSCAN utility against the source database to check whether the characterset migration is possible or not.
step 1:
CSSCAN 'SYS/********@SourceDB as sysdba' user=schemaname fromchar=WE8MSWIN1252 TOCHAR=utf8 log=D:\CSS_SCAN_DETAILS\Schemaname_log
Step 2:
The above command will create three output files
1.Err output file.
2.Log file.
3.Text file
like Schemaname_log.err,schemaname_log.txt.schemaname_log.log
The text file contains the below output.

Database Scan Summary Report
Time Started : 2010-08-03 18:52:44
Time Completed: 2010-08-03 18:53:16
Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2010-08-03 18:53:14 2010-08-03 18:53:15
---------- -------------------- --------------------
[Database Size]
Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 1,062.25M 7.75M 1,070.00M .00K
UNDOTBS1 17.94M 7,822.06M 7,840.00M .00K
SYSAUX 749.38M 50.63M 800.00M .00K
TEMP .00K .00K .00K .00K
USERS 2,392.50M 147.50M 2,540.00M .00K
EMP 28.88M 271.13M 300.00M .00K
EMPTEST 79.75M 220.25M 300.00M .00K
EMPPROD 78.00M 222.00M 300.00M .00K
------------------------- --------------- --------------- --------------- ---------------
Total 26,868.00M 19,685.06M 46,553.06M .00K
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name dbdatabase
Database Version 10.2.0.4.0
Scan type User tables
User name TRAN
Scan CHAR data? YES
Database character set WE8MSWIN1252
FROMCHAR WE8MSWIN1252
TOCHAR utf8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 1
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Scan Summary]
Some character type application data are not convertible to the new character set
[Data Dictionary Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 0 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 0 0 0 0
Total in percentage 0.000% 0.000% 0.000% 0.000%

[Application Data Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 4,499 29 2 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 4,499 29 2 0
Total in percentage 99.316% 0.640% 0.044% 0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
TRAN.TBL_FILE_STORAGE 2 1 0
TRAN.TBL_TASK_DETAILS 3 0 0
TRAN.TBL_TASK_DOCUMENTS 1 1 0
TRAN.TBL_TRANSLATIONS 22 0 0
TRAN.TBL_USER_MASTER 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
USER.TABLECOLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
TRAN.TBL_FILE_STORAGEFIST_FILE_NAME 2 1 0
TRAN.TBL_TASK_DETAILSTADE_CUSTOMER_NAME 1 0 0
TRAN.TBL_TASK_DETAILSTADE_END_CUSTOMER_NA 1 0 0
TRAN.TBL_TASK_DETAILSTADE_INVOICE_SEND_TO 1 0 0
TRAN.TBL_TASK_DOCUMENTSTADO_SOURCE_FILENA 1 1 0
TRAN.TBL_TRANSLATIONSTRAN_VALUE 22 0 0
TRAN.TBL_USER_MASTERUSMA_PASSWORD 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

The error output file will contain the tables list that wich we need to work on when doing the migration like below
Database Scan Individual Exception Report
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name dbdatabase
Database Version 10.2.0.4.0
Scan type User tables
User name TRAN
Scan CHAR data? YES
Database character set WE8MSWIN1252
FROMCHAR WE8MSWIN1252
TOCHAR utf8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 1
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Data Dictionary individual exceptions]

[Application data individual exceptions]
User : TRAN
Table : TBL_FILE_STORAGE
Column: FIST_FILE_NAME
Type : VARCHAR2(50)
Number of Exceptions : 1
Max Post Conversion Data Size: 51
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAY62AAVAAAAHWAAU exceed column size 51 P3-FRA2028FR -Contrôle des DE
------------------ ------------------ ----- ------------------------------
User : TRAN
Table : TBL_TASK_DOCUMENTS
Column: TADO_SOURCE_FILENAME
Type : VARCHAR2(50)
Number of Exceptions : 1
Max Post Conversion Data Size: 51
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAY64AAVAAAAJuAAF exceed column size 51 P3-FRA2028FR -Contrôle des DE
------------------ ------------------ ----- ------------------------------
The logfile will contain the below output
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Tue Aug 3 18:52:43 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enumerating tables to scan...
. process 1 scanning TRAN.TBL_EMAIL_LOG[AAAY6wAAVAAAAGhAAA]
. process 1 scanning TRAN.TBL_ROLE_ACCESS[AAAY5JAAVAAAAIZAAA]
. process 1 scanning TRAN.TBL_TASK_STATUS[AAAY5UAAVAAAAJ5AAA]
. process 1 scanning TRAN.TBL_SYSTEM_VARIABLES[AAAY5MAAVAAAAIxAAA]
. process 1 scanning TRAN.TBL_USER_MASTER[AAAY5gAAVAAAALZAAA]
. process 1 scanning TRAN.TBL_TRANSLATION_CODES[AAAY5aAAVAAAAKpAAA]
. process 1 scanning TRAN.TBL_TASK_DETAILS[AAAY5PAAVAAAAJJAAA]
. process 1 scanning TRAN.TBL_TASK_DOCUMENTS[AAAY64AAVAAAAJpAAA]
. process 1 scanning TRAN.TBL_TRANSLATIONS[AAAY5XAAVAAAAKRAAA]
. process 1 scanning TRAN.TBL_USER_ROLES[AAAY5nAAVAAAANZAAA]
. process 1 scanning TRAN.TBL_FORMAT_CODES[AAAZiFAAkAAAAAZAAA]
. process 1 scanning TRAN.TBL_LIST_DETAILS[AAAY5DAAVAAAAHpAAA]
. process 1 scanning TRAN.TBL_LIST_MASTER[AAAY5GAAVAAAAIBAAA]
. process 1 scanning TRAN.TBL_FILE_STORAGE[AAAY62AAVAAAAHRAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.

From the above reports,the error file says we need to alter the column of the couple of the tables for the migration to be sucessfull.
So based on the report we altered the column size according to the report and did the migration sucessfully.

Table level recovery using RMAN by restoring in a different server

The below scenario describes that a table has been accedentialy dropped from a database and recovering using the RMAN backup by restoring only the specific datafiles that are related to that specific Schema in a new different server and taking the export of the dropped table and importing into the production server.
in production server:
SQL> drop table TBL_RESULT_MAIN;
Table dropped.

SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_REPORTCONFIG_PEEARS TS_COMET_SPC
T_MENU_PEEARS TS_COMET_SPC
MICROSOFTDTPROPERTIES TS_COMET_SPC
T_ADDL_SPEC TS_COMET_SPC
T_CORR_MP TS_COMET_SPC
T_CUSTOMER TS_COMET_SPC
T_CUSTOMER_TYPES TS_COMET_SPC
T_DEPARTMENT TS_COMET_SPC
T_ERROR TS_COMET_SPC
T_FILE_ACESS TS_COMET_SPC
T_GENERAL_MP TS_COMET_SPC
T_GENERAL_SPEC TS_COMET_SPC
T_LINES TS_COMET_SPC
T_LINE_ITEMS TS_COMET_SPC
T_MAIN TS_COMET_SPC
T_MENUS TS_COMET_SPC
T_MP_LOOKUP TS_COMET_SPC
T_REPORTCONFIG TS_COMET_SPC
T_REPORT_SEL_MP TS_COMET_SPC
T_REPORT_SRF TS_COMET_SPC
T_RESDUP_MAIN TS_COMET_SPC
T_RESDUP_MP TS_COMET_SPC
T_RESULT_CORR_MP TS_COMET_SPC
T_RESULT_MP TS_COMET_SPC
T_ROLES TS_COMET_SPC
T_SPEC_CHANGE TS_COMET_SPC
T_TESTPRG_HIST TS_COMET_SPC
T_TEST_PRG TS_COMET_SPC
T_USER TS_COMET_SPC
T_USERS_CPR TS_COMET_SPC
30 rows selected.

1* select file#,name from v$datafile
SQL> /
FILE# NAME
---------- --------------------------------------------------------------------------------------
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\UNDOTBS02.DBF
5 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\USERS01.DBF
6 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\PHASER01.DBF
7 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\RMSUSR01.DBF
8 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\COMET01.DBF
9 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\COMET02.DBF
10 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\COMET03.DBF
11 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\COMET04.DBF
12 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\EDITOR_TBS01.DBF
13 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\SCADA_TBS01.DBF
14 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\LCS_TBS_01
15 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\PE_TRACE01.DBF
16 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\HDK_TBS_01.DBF
17 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\INDX_PHASE_01.DBF
18 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\INDX_EDITOR_01.DBF
19 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\INDX_COMET_01.DBF
20 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\AUTOCALPF.DBF
21 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\AUTOCALPF_INDEX.DBF


SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
15 INDX_PHASE YES NO YES
4 UNDOTBS2 YES NO YES
5 USERS YES NO YES
6 TS_PHASER YES NO YES
7 TS_RMSUSR YES NO YES
8 TS_COMET_SPC YES NO YES
9 EDITOR_TBS01 YES NO YES
10 SCADA_TBS YES NO YES
11 LCS_TBS_01 YES NO YES
12 PE_TRACE YES NO YES
13 HDK_TBS_01 YES NO YES
14 TEMP1 NO NO YES
16 INDX_EDITOR YES NO YES
17 INDX_COMET YES NO YES
20 AUTOCALPF YES NO YES
21 AUTOCALPF_INDEX YES NO YES
3 TEMP NO NO YES

Move the backup files to the test server in the same location where the RMAN has taken backup and start restoring as below.In this case the test server already has a dummy database. So we are restoring in it.
in test server:
>RMAN target 'sys/******* as sysdba'
RMAN> run
2> {
3> shutdown immediate;
4> startup nomount;
5> restore controlfile from 'D:\ADB_BACKUP\ADB_CTRL_7KKLTCPL_20090805';
6> startup mount;
7> }

RMAN> catalog start with 'D:\ADB_backup\ADB';
searching for all files that match the pattern D:\ADB_backup\ADB
List of Files Unknown to the Database
=====================================
File Name: D:\ADB_BACKUP\ADB_ARCH_7FKLT9F1_1_1_20090805
File Name: D:\ADB_BACKUP\ADB_ARCH_7GKLTABC_1_1_20090805
File Name: D:\ADB_BACKUP\ADB_ARCH_7HKLTBE3_1_1_20090805
File Name: D:\ADB_BACKUP\ADB_ARCH_7IKLTCN8_1_1_20090805
File Name: D:\ADB_BACKUP\ADB_CTRL_7KKLTCPL_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7CKLSQ4D_2_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7CKLSQ4D_3_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7CKLSQ4D_4_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7CKLSQ4D_5_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7DKLSQ4E_1_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7DKLSQ4E_2_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7DKLSQ4E_3_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7DKLSQ4E_4_1_20090805
Do you really want to catalog the above files (enter YES or NO)? y
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: D:\ADB_BACKUP\ADB_ARCH_7FKLT9F1_1_1_20090805
File Name: D:\ADB_BACKUP\ADB_ARCH_7GKLTABC_1_1_20090805
File Name: D:\ADB_BACKUP\ADB_ARCH_7HKLTBE3_1_1_20090805
File Name: D:\ADB_BACKUP\ADB_ARCH_7IKLTCN8_1_1_20090805
File Name: D:\ADB_BACKUP\ADB_CTRL_7KKLTCPL_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7CKLSQ4D_2_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7CKLSQ4D_3_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7CKLSQ4D_4_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7CKLSQ4D_5_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7DKLSQ4E_1_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7DKLSQ4E_2_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7DKLSQ4E_3_1_20090805
File Name: D:\ADB_BACKUP\ADB_DATA_7DKLSQ4E_4_1_20090805

RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> restore datafile 1,2,3,4,6;
6> }
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1291845632 bytes
Fixed Size 1292276 bytes
Variable Size 494929932 bytes
Database Buffers 788529152 bytes
Redo Buffers 7094272 bytes
Starting restore at 05-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=268 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\UNDOTBS01.DBF
restoring datafile 00006 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\PHASER01.DBF
channel ORA_DISK_1: reading from backup piece D:\ADB_BACKUP\ADB_DATA_7DKLSQ4E_1_1_20090805
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ADB_BACKUP\ADB_DATA_7DKLSQ4E_1_1_20090805 tag=TAG20090805T003004
channel ORA_DISK_1: restore complete, elapsed time: 00:23:06
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\SYSTEM01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ADB\UNDOTBS02.DBF
channel ORA_DISK_1: reading from backup piece D:\ADB_BACKUP\ADB_DATA_7CKLSQ4D_1_1_20090805
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ADB_BACKUP\ADB_DATA_7CKLSQ4D_1_1_20090805 tag=TAG20090805T003004
channel ORA_DISK_1: restore complete, elapsed time: 00:19:56
Finished restore at 05-OCT-09
sql>conn sys/****** as sysdba
SQL> alter database datafile 5 offline drop;
Database altered.
SQL> alter database datafile 7 offline drop;
Database altered.
SQL>
SQL> alter database datafile 8 offline drop;
Database altered.
SQL>
SQL> alter database datafile 9 offline drop;
Database altered.
SQL>
SQL> alter database datafile 10 offline drop;
Database altered.
SQL>
SQL> alter database datafile 11 offline drop;
Database altered.
SQL>
SQL> alter database datafile 12 offline drop;
Database altered.
SQL>
SQL> alter database datafile 13 offline drop;
Database altered.
SQL>
SQL> alter database datafile 14 offline drop;
Database altered.
SQL>
SQL> alter database datafile 15 offline drop;
Database altered.
SQL>
SQL> alter database datafile 16 offline drop;
Database altered.
SQL>
SQL> alter database datafile 17 offline drop;
Database altered.
SQL>
SQL> alter database datafile 18 offline drop;
Database altered.
SQL>
SQL> alter database datafile 19 offline drop;
Database altered.
SQL>
SQL> alter database datafile 20 offline drop;
Database altered.
SQL>
SQL> alter database datafile 21 offline drop;
Database altered.
SQL>
RMAN> recover database until sequence 64;
Starting recover at 05-OCT-09
using channel ORA_DISK_1
starting media recovery
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00001_0695490270.001 thread=1 sequence=1
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00001_0695490270.002 thread=2 sequence=1
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00002_0695490270.001 thread=1 sequence=2
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00003_0695490270.001 thread=1 sequence=3
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00002_0695490270.002 thread=2 sequence=2
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00004_0695490270.001 thread=1 sequence=4
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00005_0695490270.001 thread=1 sequence=5
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00006_0695490270.001 thread=1 sequence=6
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00003_0695490270.002 thread=2 sequence=3
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00007_0695490270.001 thread=1 sequence=7
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00008_0695490270.001 thread=1 sequence=8
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00009_0695490270.001 thread=1 sequence=9
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00004_0695490270.002 thread=2 sequence=4
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00010_0695490270.001 thread=1 sequence=10
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00011_0695490270.001 thread=1 sequence=11
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00012_0695490270.001 thread=1 sequence=12
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00005_0695490270.002 thread=2 sequence=5
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00013_0695490270.001 thread=1 sequence=13
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00014_0695490270.001 thread=1 sequence=14
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00015_0695490270.001 thread=1 sequence=15
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00006_0695490270.002 thread=2 sequence=6
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00016_0695490270.001 thread=1 sequence=16
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00017_0695490270.001 thread=1 sequence=17
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00018_0695490270.001 thread=1 sequence=18
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00007_0695490270.002 thread=2 sequence=7
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00019_0695490270.001 thread=1 sequence=19
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00020_0695490270.001 thread=1 sequence=20
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00008_0695490270.002 thread=2 sequence=8
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00021_0695490270.001 thread=1 sequence=21
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00022_0695490270.001 thread=1 sequence=22
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00009_0695490270.002 thread=2 sequence=9
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00023_0695490270.001 thread=1 sequence=23
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00024_0695490270.001 thread=1 sequence=24
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00025_0695490270.001 thread=1 sequence=25
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00010_0695490270.002 thread=2 sequence=10
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00026_0695490270.001 thread=1 sequence=26
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00027_0695490270.001 thread=1 sequence=27
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00028_0695490270.001 thread=1 sequence=28
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00011_0695490270.002 thread=2 sequence=11
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00029_0695490270.001 thread=1 sequence=29
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00030_0695490270.001 thread=1 sequence=30
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00031_0695490270.001 thread=1 sequence=31
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00012_0695490270.002 thread=2 sequence=12
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00032_0695490270.001 thread=1 sequence=32
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00033_0695490270.001 thread=1 sequence=33
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00034_0695490270.001 thread=1 sequence=34
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00013_0695490270.002 thread=2 sequence=13
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00035_0695490270.001 thread=1 sequence=35
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00036_0695490270.001 thread=1 sequence=36
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00037_0695490270.001 thread=1 sequence=37
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00014_0695490270.002 thread=2 sequence=14
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00038_0695490270.001 thread=1 sequence=38
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00039_0695490270.001 thread=1 sequence=39
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00040_0695490270.001 thread=1 sequence=40
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00015_0695490270.002 thread=2 sequence=15
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00041_0695490270.001 thread=1 sequence=41
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00042_0695490270.001 thread=1 sequence=42
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00043_0695490270.001 thread=1 sequence=43
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00016_0695490270.002 thread=2 sequence=16
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00044_0695490270.001 thread=1 sequence=44
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00045_0695490270.001 thread=1 sequence=45
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00046_0695490270.001 thread=1 sequence=46
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00017_0695490270.002 thread=2 sequence=17
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00047_0695490270.001 thread=1 sequence=47
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00048_0695490270.001 thread=1 sequence=48
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00049_0695490270.001 thread=1 sequence=49
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00018_0695490270.002 thread=2 sequence=18
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00050_0695490270.001 thread=1 sequence=50
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00051_0695490270.001 thread=1 sequence=51
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00052_0695490270.001 thread=1 sequence=52
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00019_0695490270.002 thread=2 sequence=19
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00053_0695490270.001 thread=1 sequence=53
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00054_0695490270.001 thread=1 sequence=54
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00055_0695490270.001 thread=1 sequence=55
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00020_0695490270.002 thread=2 sequence=20
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00056_0695490270.001 thread=1 sequence=56
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00057_0695490270.001 thread=1 sequence=57
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00058_0695490270.001 thread=1 sequence=58
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00021_0695490270.002 thread=2 sequence=21
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00059_0695490270.001 thread=1 sequence=59
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00060_0695490270.001 thread=1 sequence=60
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00061_0695490270.001 thread=1 sequence=61
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00022_0695490270.002 thread=2 sequence=22
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00062_0695490270.001 thread=1 sequence=62
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\ARC00063_0695490270.001 thread=1 sequence=63
unable to find archive log
archive log thread=1 sequence=64
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/05/2009 18:49:07
RMAN-06054: media recovery requesting unknown log: thread 1 seq 64 lowscn 294192954

RMAN> alter database open resetlogs;
database opened

SQL> conn phase/phase
Connected.
SQL>
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DPS2003 TABLE
DROPLET TABLE
FMNREPAIR TABLE
FMNREPAIRNEW TABLE
FMNVALUE TABLE
FMNVALUENEW TABLE
TSTAMM TABLE
TVOREINSTELLUNG TABLE
T_DEFECTCODE TABLE
T_PASSWORD TABLE
T_PHASING_RESULT TABLE
T_SPEC TABLE
T_TESTPROGRAM TABLE
A VIEW
V_SPEC VIEW
44 rows selected.
C:\Documents and Settings\q4ban>exp phase/***** file='E:\test\TBL_PHASING_RESULT.dmp' statistics=none buffer=314572800
=TBL_PHASING_RESULT
Export: Release 10.2.0.3.0 - Production on Mon Oct 5 18:55:18 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TBL_PHASING_RESULT 2250433 rows exported
Export terminated successfully without warnings.
C:\Documents and Settings\q4ban>

After this transfer the dump file to the production server and import the table.

OS upgradation and Production DB Recovered (size 320 Gigs)

Implemented steps
Scenario is
The server OS has been upgraded from Windows 2000 standard edition to Windows 2003 standard edition, Oracle 10.2.0.3 is installed in C: drive where the OS has been installed.
Apart from this we have additional D: drive and E: drive mounted in the server.
Note: All the database files are present in the D: drive and E: drive.
Client wants to upgrade the OS in this server, So what he did is he removed the mounted drives (D: and F:) and upgraded the OS to Windows 2003 standard edition.
And after installing the OS Ms 2003 he again remounted the drive, since both the OS versions has the same filesystem no need of formating the remounted drives.

So all the Datafiles were present in the drives as it was before.
Before this, Additionally to be in safer side :

1. We had the rman backup of Database and Spfile and controlfile.
2. We also had a backup of control file by trace(ie: in text format).
3. We also had a backup of updated spfile and pfile in text format.

So in this case what we did is,
Step 1 : We installed the Oracle 10.2.0.1 version in C: drive.
Step 2 : Apply patch 10.2.0.3 to the 10.2.0.1 software home in C: drive.
While patching we got an error as below

Workaround is to go to services.msc and stop the Distributed Transaction Coordinator services and retry.
After that it worked like charm. !
Step 3: After Applying the patch, Manually create the Oracle ADUMP,BDUMP,CDUMP,PFILE,DPDUMP folders in the “C:\oracle\admin\shat8\ “ location and copy the pfile (which we have a backup already) to the pfile directory in Oracle location “ c:\oracle\admin\shat8\pfile\ “.

Note:before going to step 4 update the pfile according to the changes in the new installation if any.

Step 4: Go to command prompt and create the service using ORADIM utility by using the upated backup pfile which we already taken.
C:\> Oradim –new –sid shat8 –pfile ‘c:\oracle\admin\shat8\pfile\init.ora’
Step 5: Create password file using orapwd like ORADIM.
C:\> set ORACLE_SID=shat8
C:\> orapwd file= password= entries=
Step 6: Since we have all the data files in the mounted drive itself don’t need to restore the datafiles from Rman Backup.

But Client has mistakenly remounted drives with different Drive letters (D: drive as E: drive <> E: drive as F: drive). So as per the Database controlfile records most of the datafiles are in E: drive and some of the datafiles are present in F: drive.
So what we did is we recreated the controlfile with the trace (text ) backup which we have already taken by updating the script with the changes (ie D: drive as E: drive and E: drive as F: drive) as below

C:\>Set Oracle_sid=shat8
C:\>Set Oracle_home=c:\oracle\product\10.2.0\db_1\
C:\>Cd %ORACLE_HOME%
C:\oracle\product\10.2.0\db_1\>sqlplus sys/****** as sysdba

///
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SHAT8" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 10278
LOGFILE
GROUP 1 'E:\ORACLE10G\SHAT8\ADMIN\REDO01A.LOG' SIZE 100M,
GROUP 2 'E:\ORACLE10G\SHAT8\ADMIN\REDO02A.LOG' SIZE 100M,
GROUP 3 'E:\ORACLE10G\SHAT8\ADMIN\REDO03A.LOG' SIZE 100M,
GROUP 4 'E:\ORACLE10G\SHAT8\ADMIN\REDO04A.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'F:\ORACLE10G\SHAT8\ORADATA\SYSTEM01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\UNDOTBS01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\SYSAUX01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\USERS01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_BACH_A_01.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_BACH_A_01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2005.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2006.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2007_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2007_Q2.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2007_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2007_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2008_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2008_Q2.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2008_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2008_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2005.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2006.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2007_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2007_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2007_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2007_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2008_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2008_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2008_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2008_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2005.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_BACH_DE_01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2006.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2007_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2007_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_BACH_DE_01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2007_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2008_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2007_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2008_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2005.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2008_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2008_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2006.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2007_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2008_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2007_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2008_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2007_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2008_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2007_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2008_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\UNDOTBS02.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_BACH_DE_02.DBF',
'F:\ORACLE\ORADATA\STAT\TS_DE_DATA_FY2009_Q1.ORA',
'F:\ORACLE\ORADATA\STAT\TS_DE_DATA_FY2009_Q2.ORA',
'F:\ORACLE\ORADATA\STAT\TS_DE_DATA_FY2009_Q3.ORA',
'F:\ORACLE\ORADATA\STAT\TS_DE_DATA_FY2009_Q4.ORA',
'F:\ORACLE\ORADATA\STAT\INDX_DE_DATA_FY2009_Q1.ORA',
'F:\ORACLE\ORADATA\STAT\INDX_DE_DATA_FY2009_Q2.ORA',
'F:\ORACLE\ORADATA\STAT\INDX_DE_DATA_FY2009_Q3.ORA',
'F:\ORACLE\ORADATA\STAT\INDX_DE_DATA_FY2009_Q4.ORA',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2009_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2009_Q2.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2009_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2009_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2010_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2010_Q2.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2010_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2010_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2009_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2009_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2009_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2009_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2010_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2010_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2010_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2010_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2010_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2010_Q2.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2010_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2010_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_BACH_DE_03.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\BACH_DE04.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\INDX_BACH_A02.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\INDX_BACH_A03.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\INDX_DE_FY2008_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\INDX_DE_DATA_FY2008_Q2.DBF'
CHARACTER SET JA16SJIS
;

////
Step 7: Recover the database as mentioned in the controlfile trace
File. This step is followed as per the comment in the controlfile trace as below.

-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE
-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

Step 8: Open the database and add the temp file as below.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP01.ORA'
SIZE 10000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP027.DBF'
SIZE 4000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP026.DBF'
SIZE 4000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP025.DBF'
SIZE 4000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP024.DBF'
SIZE 2000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP023.DBF'
SIZE 2000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP022.DBF'
SIZE 2000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP021.DBF'
SIZE 2000M REUSE AUTOEXTEND OFF;
-- End of tempfile additions.

Step 9: create spfile from pfile=’c:\oracle\admin\shat8\pfile\init.ora’;
Step 10: Shutdown immediate
Step 11: Startup
Step 12: Now configure the listener using NETCA and NETMGR utility and configure the database with the listener and test from a client machine.

Finally check the components, number of datafiles,status of datafiles and check the archive log by switching the redo logs and do other basic checks.

Workaorund scripts used including Backup of DB

Own Backup script which i used in linux boxes:

export ORACLE_HOME=/star/app/oracle/product/11.1.0/db_1
DTE=`date +"%m%d%y"`
DIR=/work/BKP_DB
echo [.. starting Backup of NAVADEMO "`date`"] >> $DIR/Bkp_all.txt
export ORACLE_SID=NAVADEMO
dmpfname='expdp_DEMO_'$DTE'.dmp'
logfname='expdp_DEMO_'$DTE'.log'
/star/app/oracle/product/11.1.0/db_1/bin/expdp system/star@NAVADEMO directory=exp_job dumpfile=$dmpfname full=y logfile=$logfname
#/star/app/oracle/product/11.1.0/db_1/bin/expdp system/star@starDEMO directory=exp_job dumpfile=$dmpfname schemas=tiger logfile=$logfname
echo [.zipping dumpfile "`date`"] >> $DIR/Bkp_all.txt
gzip $DIR/$dmpfname >> $DIR/Bkp_all.txt
echo [.. starting Backup of NAVADEV "`date`"] >> $DIR/Bkp_all.txt
export ORACLE_SID=NAVADEV
dmpfname='expdp_DEV_'$DTE'.dmp'
logfname='expdp_DEV_'$DTE'.log'
/star/app/oracle/product/11.1.0/db_1/bin/expdp system/star@NAVADEV directory=exp_job dumpfile=$dmpfname full=y logfile=$logfname
echo [.zipping dumpfile "`date`"] >> $DIR/Bkp_all.txt
gzip $DIR/$dmpfname >> $DIR/Bkp_all.txt
echo [.. starting Backup of NAVATST "`date`"] >> $DIR/Bkp_all.txt
export ORACLE_SID=NAVATST
dmpfname='expdp_TST_'$DTE'.dmp'
logfname='expdp_TST_'$DTE'.log'
/star/app/oracle/product/11.1.0/db_1/bin/expdp system/star@NAVATST directory=exp_job dumpfile=$dmpfname full=y logfile=$logfname
echo [.zipping dumpfile "`date`"] >> $DIR/Bkp_all.txt
gzip $DIR/$dmpfname >> $DIR/Bkp_all.txt
echo [..starting Backup of NAVATMP "`date`"] >> $DIR/Bkp_all.txt
export ORACLE_SID=starTMP
dmpfname='expdp_TMP_'$DTE'.dmp'
logfname='expdp_TMP_'$DTE'.log'
/star/app/oracle/product/11.1.0/db_1/bin/expdp system/star@NAVATMP directory=exp_job dumpfile=$dmpfname full=y logfile=$logfname
echo [.zipping dumpfile "`date`"] >> $DIR/Bkp_all.txt
gzip $DIR/$dmpfname >> $DIR/Bkp_all.txt


Scheduler in Linux for DB backups of NAVADEV,NAVADEMO,NAVATST & NAVATMP
scheduled as per the below scripts:

0 1 * * 1,4 /work/BKP_DB/BKP_SCRIPT_DB.sh


So this backup are scheduled to happen at every Monday and Thursday at 1 am.
Hence NAVADEV,NAVADEMO,NAVATST & NAVATMP are backuped every Monday and Thursday.



Calling procedure to write tracing of default trace file in a file:
---------------------------------------------------------------------

/*
declare
v_sid number;
v_serial number;
v_file varchar2(2000);
begin
-- Call the procedure
select sid,serial# into v_sid,v_serial from v$session where osuser='501200I430' and status='ACTIVE';
sys.dbms_system.set_sql_trace_in_session(sid => v_sid,
serial# => v_serial,
sql_trace => TRUE);
SELECT value into v_file
FROM v$diag_info
WHERE name = 'Default Trace File';
dbms_output.put_line(v_file);
end;

*/


Tablespace critical message when it becomes 85% occupied:
----------------------------------------------------------

#!/bin/ksh

export PATH=/star/app/oracle/product/11.1.0/db_1/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/star/bin
export ORACLE_BASE=/star/app/oracle
export LD_LIBRARY_PATH=/star/app/oracle/product/11.1.0/db_1/lib:/lib:/usr/lib
export LD_LIBRARY_PATH=/ora11g/app/oracle/product/11.1.0.7/lib:/usr/lib:/usr/ccs/bin:/usr/dt/lib
export ORACLE_HOME=/star/app/oracle/product/11.1.0/db_1


MAILLIST=`cat /home/star/scripts/email.lst`
LOGFILE=/tmp/chk_ts_$1.log

echo $1

CHKUP=`ps -ef | grep pmon | grep $1 | wc -l`

if [ "${CHKUP}" -eq 1 ]
then
echo "The Pmon Process is running on host"
else
echo " ****************************************************************" >> $LOGFILE
echo " " `date` " Pmon Proces is not found" >> $LOGFILE
echo " ****************************************************************" >> $LOGFILE
exit 0
fi

export ORACLE_SID=$1

sqlplus -s "/ as sysdba" set echo off
set trimspool on
set trimout on
set verify off
set feedback off
column TABLESPACE_NAME format a18
column "%ocup" format a6
set lines 200
spool /tmp/chk_ts_$1.spl
SELECT total.tablespace_name tablespace_name,
ROUND (tot / 1024 / 1024) total,
ROUND ((tot - tot_l) / 1024 / 1024) occupied,
ROUND (tot_l / 1024 / 1024) remain,
ROUND (max_l / 1024 / 1024) max_extent,
ROUND ((tot - tot_l) * 100 / tot) || '%' AS "%ocup"
FROM (SELECT tablespace_name, SUM (BYTES) tot_l, MAX (BYTES) max_l
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT d.tablespace_name,
f.bytes_free + f.bytes_used - NVL (p.bytes_used, 0) tot_l,
0 max_l
FROM SYS.v_\$temp_space_header f,
dba_temp_files d,
SYS.v_\$temp_extent_pool p
WHERE f.tablespace_name(+) = d.tablespace_name AND f.file_id(+) = d.file_id
AND p.file_id(+) = d.file_id) libre,
(SELECT tablespace_name, SUM (BYTES) tot
FROM dba_data_files
GROUP BY tablespace_name
UNION
(SELECT tablespace_name, SUM (BYTES) tot
FROM dba_temp_files
GROUP BY tablespace_name)) total,
dba_tablespaces dba_t
WHERE total.tablespace_name = libre.tablespace_name(+)
AND total.tablespace_name = dba_t.tablespace_name
AND ROUND (tot_l / 1024 / 1024) < 1024
AND ROUND ((tot - tot_l) * 100 / tot) > 85
AND dba_t.tablespace_name NOT IN ('RBS')
ORDER BY ROUND ((tot - tot_l) * 100 / tot) DESC;
spo off
EOF
if [ -s /tmp/chk_ts_$1.spl ]; then
cat /tmp/chk_ts_$1.spl | mailx -s "Critical : ($1 on insiscdev02.sony.com.sg) Missing free space on tablespace of the database $1 -- Immediate Action" $MAILLIST
echo "---------------------------------------------------------------------------" >> $LOGFILE
echo `date` >> $LOGFILE
cat /tmp/chk_ts_$1.spl >> $LOGFILE
echo "---------------------------------------------------------------------------" >> $LOGFILE
rm -fr /tmp/chk_ts_$1.spl
fi


STATISTICS COLLECTED FOR NAVAREP SCHEMA:

Step 1:Execute the DBMS_STATS.gather_schema_stats package with the below options.

BEGIN
DBMS_STATS.gather_schema_stats (
ownname => 'NAVATXN',
estimate_percent => null,
block_sample => false,
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => null,
granularity => 'ALL',
cascade => true,
options => 'GATHER');
END;
/

Step 2:Check the statistics gathered with the help of DBA_TABLES dictionary view.

SELECT table_name, num_rows, blocks, avg_row_len
FROM DBA_tables where OWNER='NAVAREP';

SQL> SELECT table_name, num_rows, blocks, avg_row_len
FROM DBA_tables where OWNER='NAVAREP'; 2

STATISTICS COLLECTED FOR NAVAREP SCHEMA:

Step 1:Execute the DBMS_STATS.gather_schema_stats package with the below options.

BEGIN
DBMS_STATS.gather_schema_stats (
ownname => 'NAVATXN',
estimate_percent => null,
block_sample => false,
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => null,
granularity => 'ALL',
cascade => true,
options => 'GATHER');
END;
/

Step 2:Check the statistics gathered with the help of DBA_TABLES dictionary view.


SELECT table_name, num_rows, blocks, avg_row_len
FROM DBA_tables where OWNER='NAVAREP';

SQL> SELECT table_name, num_rows, blocks, avg_row_len
FROM DBA_tables where OWNER='NAVAREP'; 2

Configure Active standby database 11g

run {
allocate channel ch1 type disk;

allocate channel ch2 type disk;

allocate channel ch3 type disk;

allocate channel ch4 type disk;

allocate auxiliary channel stndby type disk;


duplicate target database for standby from active database

spfile

parameter_value_convert 'orcl','orclstdby'

set db_unique_name='orclstdby'

set db_file_name_convert='/orcl/','/orclstdby/'

set log_file_name_convert='/orcl/','/orclstdby/'

set control_files='/u01/app/oracle/oradata/orclstdby.ctl'

set log_archive_max_processes='5'

set fal_client='orclstdby'

set fal_server='orcl'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(orcl,orclstdby)'

set log_archive_dest_1='service=orcl ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl' ;
}

alter database add standby logfile
('/u01/app/oracle/oradata/orcl/sredol01.log',
'/u01/app/oracle/oradata/orcl/sredol02.log',
'/u01/app/oracle/oradata/orcl/sredol03.log',
'/u01/app/oracle/oradata/orcl/sredol04.log')
size 52428800;



SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;

select sequence#,archived,applied,deleted from v$archived_log where completion_time like '01-JUL-10'%;

Auditing in Oracle

Auditing DML operations on table level

Audit update,delete,insert on scott.emp by access;

Auditing DDL operations

AUDIT create table,alter table,drop table By scott whenever sucessful;

To turn off all the auditing the statement is.
NOAUDIT ALL;
NOAUDIT ALL ON SCOTT.EMP;

To Check the auditing table details and the session information use below
select * from dba_audit_trail where username='SCOTT' and timestamp like '28-MAY-10%'
select * from aud$
select * from DBA_AUDIT_OBJECT where username ='SCOTT'


The Data dictionary views related auditing are
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL

Manual recovery of a standby in case of structural changes in Primary

This scenario applicable if you have done any structural changes in primary database and there is a problem in replicating the changes in standby site.

shutdown standby

startup nomount

alter database mount standby database

recover standby database until cancel

AUTO

http://dbaspot.com/forums/oracle-server/71881-physical-standby-database-cannot-close-gap-if-any-gap-exists.html

'ORA-00280: change 3220290269 for thread 1 is in sequence #17219

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17218.ARC' no longer needed for this recovery

ORA-00279: change 3220290274 generated at 04/11/2010 19:03:32 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17220.ARC

ORA-00280: change 3220290274 for thread 1 is in sequence #17220

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17219.ARC' no longer needed for this recovery

ORA-00279: change 3220300520 generated at 04/11/2010 23:27:08 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17221.ARC

ORA-00280: change 3220300520 for thread 1 is in sequence #17221

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17220.ARC' no longer needed for this recovery

ORA-00279: change 3220300533 generated at 04/11/2010 23:27:16 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17222.ARC

ORA-00280: change 3220300533 for thread 1 is in sequence #17222

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17221.ARC' no longer needed for this recovery

ORA-00279: change 3220304453 generated at 04/12/2010 01:05:52 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17223.ARC

ORA-00280: change 3220304453 for thread 1 is in sequence #17223

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17222.ARC' no longer needed for this recovery

ORA-00279: change 3220304456 generated at 04/12/2010 01:05:53 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17224.ARC

ORA-00280: change 3220304456 for thread 1 is in sequence #17224

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17223.ARC' no longer needed for this recovery

ORA-00279: change 3220429248 generated at 04/12/2010 09:49:53 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17225.ARC

ORA-00280: change 3220429248 for thread 1 is in sequence #17225

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17224.ARC' no longer needed for this recovery

ORA-00308: cannot open archived log 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17225.ARC'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.

SQL>

SQL> recover standby database until cancel;

ORA-00279: change 3220429248 generated at 04/12/2010 09:49:53 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17225.ARC

ORA-00280: change 3220429248 for thread 1 is in sequence #17225

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 3220572194 generated at 04/12/2010 10:46:06 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17226.ARC
ORA-00280: change 3220572194 for thread 1 is in sequence #17226
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17225.ARC' no longer needed for this recovery


ORA-00279: change 3220627014 generated at 04/12/2010 11:06:36 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17227.ARC
ORA-00280: change 3220627014 for thread 1 is in sequence #17227
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17226.ARC' no longer needed for this recovery


ORA-00279: change 3220627289 generated at 04/12/2010 11:06:51 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17228.ARC
ORA-00280: change 3220627289 for thread 1 is in sequence #17228
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17227.ARC' no longer needed for this recovery


ORA-00279: change 3220763002 generated at 04/12/2010 12:27:59 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17229.ARC
ORA-00280: change 3220763002 for thread 1 is in sequence #17229
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17228.ARC' no longer needed for this recovery


ORA-00279: change 3220899620 generated at 04/12/2010 13:24:57 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17230.ARC
ORA-00280: change 3220899620 for thread 1 is in sequence #17230
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17229.ARC' no longer needed for this recovery


ORA-00279: change 3221036980 generated at 04/12/2010 14:44:50 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17231.ARC
ORA-00280: change 3221036980 for thread 1 is in sequence #17231
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17230.ARC' no longer needed for this recovery


ORA-00279: change 3221165247 generated at 04/12/2010 16:11:07 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17232.ARC
ORA-00280: change 3221165247 for thread 1 is in sequence #17232
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17231.ARC' no longer needed for this recovery


ORA-00279: change 3221302585 generated at 04/12/2010 17:30:15 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17233.ARC
ORA-00280: change 3221302585 for thread 1 is in sequence #17233
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17232.ARC' no longer needed for this recovery


ORA-00279: change 3221437356 generated at 04/12/2010 18:37:11 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17234.ARC
ORA-00280: change 3221437356 for thread 1 is in sequence #17234
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17233.ARC' no longer needed for this recovery


ORA-00279: change 3221499410 generated at 04/12/2010 19:02:44 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17235.ARC
ORA-00280: change 3221499410 for thread 1 is in sequence #17235
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17234.ARC' no longer needed for this recovery


ORA-00279: change 3221499630 generated at 04/12/2010 19:03:02 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17236.ARC
ORA-00280: change 3221499630 for thread 1 is in sequence #17236
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17235.ARC' no longer needed for this recovery


ORA-00279: change 3221663610 generated at 04/12/2010 20:36:12 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17237.ARC
ORA-00280: change 3221663610 for thread 1 is in sequence #17237
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17236.ARC' no longer needed for this recovery


ORA-00279: change 3221809532 generated at 04/12/2010 21:37:13 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17238.ARC
ORA-00280: change 3221809532 for thread 1 is in sequence #17238
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17237.ARC' no longer needed for this recovery


ORA-00279: change 3221951748 generated at 04/12/2010 22:41:44 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17239.ARC
ORA-00280: change 3221951748 for thread 1 is in sequence #17239
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17238.ARC' no longer needed for this recovery


ORA-00279: change 3222098577 generated at 04/12/2010 23:39:32 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17240.ARC
ORA-00280: change 3222098577 for thread 1 is in sequence #17240
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17239.ARC' no longer needed for this recovery


ORA-00279: change 3222248037 generated at 04/13/2010 00:55:46 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17241.ARC
ORA-00280: change 3222248037 for thread 1 is in sequence #17241
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17240.ARC' no longer needed for this recovery


ORA-00279: change 3222264966 generated at 04/13/2010 01:05:47 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17242.ARC
ORA-00280: change 3222264966 for thread 1 is in sequence #17242
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17241.ARC' no longer needed for this recovery


ORA-00279: change 3222265468 generated at 04/13/2010 01:06:02 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17243.ARC
ORA-00280: change 3222265468 for thread 1 is in sequence #17243
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17242.ARC' no longer needed for this recovery


ORA-00279: change 3222405780 generated at 04/13/2010 02:19:18 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17244.ARC
ORA-00280: change 3222405780 for thread 1 is in sequence #17244
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17243.ARC' no longer needed for this recovery


ORA-00308: cannot open archived log 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17244.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL> disc

SQL> conn ****/***** as sysdba
Connected.
SQL>
SQL> set pages 100
SQL> set lines 1000
SQL>
SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '12-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17222 YES YES NO
17223 YES YES NO
17224 YES YES NO
17225 YES YES NO
17226 YES YES NO
17227 YES YES NO
17228 YES YES NO
17229 YES YES NO
17230 YES YES NO
17231 YES YES NO
17232 YES YES NO
17233 YES YES NO
17234 YES YES NO
17235 YES YES NO
17236 YES YES NO
17237 YES YES NO
17238 YES YES NO
17239 YES YES NO

18 rows selected.

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES NO NO

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES NO NO
17245 YES NO NO

6 rows selected.

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES NO NO
17245 YES NO NO

6 rows selected.

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES NO NO
17245 YES NO NO

6 rows selected.

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES YES NO
17245 YES YES NO

6 rows selected.

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES YES NO
17245 YES YES NO

6 rows selected.

SQL> exit

Flashback Drop scenario

The tested scenario for the flashback drop feature.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
D TABLE
SYS_TEMP_FBT TABLE
C TABLE
N TABLE
B TABLE
A1 TABLE

6 rows selected.

SQL> drop table c;

Table dropped.

SQL> select * from c;
select * from c
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME
------------------------------ -------------------------------- --------- ------------------------- ----------
BIN$PRtTP+PTTlqWRcW9NnHSRg==$0 C DROP TABLE USERS

SQL> select autoextensible from dba_data_files;

AUT
---
YES
YES
YES
YES

SQL> alter database datafile 4 autoextend off;

Database altered.

SQL> select file_name,autoextensible from dba_data_files;

FILE_NAME AUT
---------------------------------------------------------------------- ---
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF NO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF YES
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF YES
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF YES


SQL> create table flashbk (no number);

Table created.

SQL> insert into flashbk select object_id from dba_objects;

49836 rows created.

SQL> /
insert into flashbk select object_id from dba_objects
*
ERROR at line 1:
ORA-01653: unable to extend table NV.FLASHBK by 128 in tablespace USERS


SQL> select * from recyclebin;

no rows selected