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.
Tuesday, December 7, 2010
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.
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.
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 :
Before this, Additionally to be in safer side :
- We had the rman backup of Database and Spfile and controlfile.
- We also had a backup of control file by trace(ie: in text format).
- 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
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=
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 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
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 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
Subscribe to:
Posts (Atom)