1.PURPOSE AND SCOPE
In some cases we accidentally forget and create datafiles on local disk while using.In this article i am going to explain how to move datafile local disk to ASM.
2.PRACTICE
2.1. I use 2 node RAC system for this practice. And create 2 tablespace on node 1 local disk.
1.PURPOSE AND SCOPE
When using RMAN for taking backup, database records to backup informations to controlfile. Oracle suggest to use RMAN catalog option because of probability to losing controlfile.With help of RMAN catalog we can save rman informations at catalog database.Also it can use for saving rman scripts and information about rman backups.For to use this product we also need another database which act like a catalog database.
In this artichle i am going to explain how to install oracle RMAN catalog,how to register your database to catalog database and how to control backups.
1.AMAÇ VE KAPSAM
Veritabanındaki corrupt blockları bir kaç yöntemle kurtarmamız/temizlememiz mümkündür. Corrupt duruma düşmesinin sebebi genellikle veritabanın çok büyümesi veya yapılan update işlemlerinden sonra başımıza gelmektedir.
2.UYGULAMA
2.1. Corruption hataları veritabanı alert loguna aşağıdaki gibi yansır, eğer çok fazla corruption varsa grep awk 2 lisini kullanıp ayırmanız rman scriptini oluşturmanıza yardım edecektir.
ORA-01578:
ORACLE data block corrupted (file # string, block # string)
Whenever we encounter above error message mean we have BLOCK CORRUPTION.
1.AMAÇ VE KAPSAM
DATA1 VE DATA2 olmak üzere 2 tane disk groupumuz var. Datafileların coğunlugu DATA1 üstünde bulunmakta, arcive log ,backup ve 1 tane datafileda DATA2 diskgroupun ustunde bulunmakta.
Senaryomuza göre DATA2 diskgroupunu mount edemiyoruz , backup ve archiveloglar o dizinde oldugundan dolayı recover yapamıyoruz ve tek datafile orda bulundugundan ötürü veritabanını açamıyoruz.AMDU yu kullanıp datafilelı dışarı alıp veritabanını açmayı deniyecez.
1.PURPOSE AND SCOPE
When there is no hope to restore a database, i can suggest to use a internal program of oracle which named DUL (Data UnLoader). With this tool’s help we can export tables while database closed/crashed.
2.PRACTICE
2.1. We need to put dul.sh , init.dul and control.dul to same folder. We need to set datafile names to control.dul and then we are going to set some necessery parameters to init.dul. For test enviroment i corrupt datafiles, and database only allow me to open mount position.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jul 31 10:30:58 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> startup mount; ORACLE instance started. Total System Global Area 322240512 bytes Fixed Size 1299652 bytes Variable Size 209718076 bytes Database Buffers 104857600 bytes Redo Buffers 6365184 bytes Database mounted. |
2.2. Dul need to create database dictionary thats the reason we need to se datafiles to control.dul .
|
1 2 3 4 5 6 7 8 9 10 |
SQL> set trimspool on pagesize 0 linesize 256 feedback off SQL> column name format a200 SQL> spool control.dul SQL> select ts#, rfile#, name from v$datafile; 0 1 /u01/app/oracle/oradata/bugra/system01.dbf 1 2 /u01/app/oracle/oradata/bugra/sysaux01.dbf 2 3 /u01/app/oracle/oradata/bugra/undotbs01.dbf 4 4 /u01/app/oracle/oradata/bugra/users01.dbf 6 5 /u01/app/oracle/oradata/bugra/example01.dbf SQL> exit |
2.3. Now lets edit control.dul and edit it like this.
|
1 2 3 4 5 6 |
[oracle@oracle ~]$ cat control.dul 0 1 /u01/app/oracle/oradata/bugra/system01.dbf 1 2 /u01/app/oracle/oradata/bugra/sysaux01.dbf 2 3 /u01/app/oracle/oradata/bugra/undotbs01.dbf 4 4 /u01/app/oracle/oradata/bugra/users01.dbf 6 5 /u01/app/oracle/oradata/bugra/example01.dbf |
2.4. Now time to create init.dul
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[oracle@oracle dul]$ vi init.dul osd_big_endian_flag=false osd_dba_file_bits=10 osd_c_struct_alignment=32 osd_file_leader_size=1 osd_word_size = 32 dc_columns=2000000 dc_tables=10000 dc_objects=1000000 dc_users=400 dc_segments=100000 db_block_size=8192 export_mode=true COMPATIBLE=10 LDR_PHYS_REC_SIZE =0 |
PS: There are couple of different parameters also which can be used.
ALLOW_TRAILER_MISMATCH
BOOLEAN
Strongly discouraged to use, will seldom produce more rows. Use only if you fully understand what it means and why you want it. skips the check for correct block trailer. The blocks failing this test are split of corrupt. But it saves you the trouble to patch some blocks.
ALLOW_DBA_MISMATCH
BOOLEAN
Strongly discouraged to use, will seldom produce more rows. Use only if you fully understand what it means and why you want it. Skips the check for correct block address. The blocks failing this test are probably corrupt. But it saves you the trouble to patch some blocks.
ALLOW_OTHER_OBJNO
BOOLEAN
If your dictionary is older than your datafiles then the data object id’s can differ for truncated tables. With this parameter set to true it will issue a warning but use the value from segment header. All other blocks are fully checked. This is for special cases only.
ASCII2EBCDIC
BOOLEAN
Must (var)char fields be translated from EBCDIC to ASCII. (For unloading MVS database on a ASCII host)
BUFFER
NUMBER (bytes)
row output buffer size used in both export and SQL*Loader mode. In each row is first stored in this buffer. Only complete rows without errors are written to the output file.
COMPATIBLE
NUMBER
Database version , valid values are 6, 7, 8 or 9. This parameter must be specified
CONTROL_FILE
TEXT
Name of the DUL control file (default: “control.dul”).
DB_BLOCK_SIZE
NUMBER
Oracle block size in bytes (Maximum 32 K)
DC_COLUMNS
NUMBER
DC_OBJECTS
NUMBER
DC_TABLES
NUMBER
DC_USERS
NUMBER
Sizes of dul dictionary caches. If one of these is too low the cache will be automatically resized.
EXPORT_MODE
BOOLEAN
EXPort like output mode or SQL*Loader format
FILE
TEXT
Base for (dump or data) file name generation. Use this on 8.3 DOS like file systems
FILE_SIZE_IN_MB
NUMBER (Megabytes)
Maximum dump file size. Dump files are split into multiple parts. Each file has a complete header and can be loaded individually.
LDR_ENCLOSE_CHAR
TEXT
The character to enclose fields in SQL*Loader mode.
LDR_PHYS_REC_SIZE
NUMBER
Physical record size for the generated loader datafile.
LDR_PHYS_REC_SIZE = 0 No fixed records, each record is terminated with a newline.
LDR_PHYS_REC_SIZE > 2: Fixed record size.
MAX_OPEN_FILES
Maximum # of database files that are concurrently kept open at the OS level.
OSD_BIG_ENDIAN_FLAG
Byte order in machine word. Big Endian is also known as MSB first. DUL sets the default according to the machine it is running on. For an explanation why this is called Big Endian, you should read Gullivers Travels.
OSD_DBA_FILE_BITS
File Number Size in DBA in bits. Or to be more precise the size of the low order part of the file number.
OSD_FILE_LEADER_SIZE
bytes/blocks added before the real oracle file header block
OSD_C_STRUCT_ALIGNMENT
C Structure member alignment (0,16 or 32). The default of 32 is correct for most ports.
OSD_WORD_SIZE
Size of a machine word always 32, except for MS/DOS(16)
PARSE_HEX_ESCAPES
Boolean default FALSE
Use \\xhh hex escape sequences in strings while parsing. If set to true then strange characters can be specified using escape sequences. This feature is also for specifying multi-byte characters.
USE_SCANNED_EXTENT_MAP
BOOLEAN
Use the scanned extent map in ext.dat when unloading a table. The normal algorithme uses the extent map in the segment header. This parameter is only useful if some segment headers are missing or incorrect.
WARN_RECREATE_FILES
BOOLEAN (TRUE)
Set to FALSE to suppress the warning message if an existing file is overwritten.
WRITABLE_DATAFILES
BOOLEAN (FALSE)
Normal use of DUL will only read the database files. However the UPDATE and the SCAN RAW DEVICE will write as well. The parameter is there to prevent accidental damage.
2.5. Now we are ready to use dul program.With help of bootstrap command it is going to analyze good segments and its going to create map for unload process.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 |
[oracle@oracle dul]$ ./dul Data UnLoader: 10.2.0.5.3 - Internal Only - on Sun Jul 31 10:32:58 2011 with 64-bit io functions Copyright (c) 1994 2011 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only DUL: Warning: Creating file "dul.log" Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries Reading SEG.dat 0 entries loaded Reading EXT.dat 0 entries loaded and sorted 0 entries Reading COMPATSEG.dat 0 entries loaded Found db_id = 810046301 Found db_name = BUGRA DUL> bootstrap; Probing file = 1, block = 401 . unloading table BOOTSTRAP$ DUL: Warning: block number is non zero but marked deferred trying to process it anyhow 60 rows unloaded DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty Reading BOOTSTRAP.dat 60 entries loaded Parsing Bootstrap$ contents Generating dict.ddl for version 10 OBJ$: segobjno 18, file 1 block 121 TAB$: segobjno 2, tabno 1, file 1 block 25 COL$: segobjno 2, tabno 5, file 1 block 25 USER$: segobjno 10, tabno 1, file 1 block 89 Running generated file "@dict.ddl" to unload the dictionary tables . unloading table OBJ$ 69665 rows unloaded . unloading table TAB$ 2585 rows unloaded . unloading table COL$ 78741 rows unloaded . unloading table USER$ 90 rows unloaded Reading USER.dat 90 entries loaded Reading OBJ.dat 69665 entries loaded and sorted 69665 entries Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries Reading TAB.dat 2585 entries loaded Reading COL.dat 78741 entries loaded and sorted 78741 entries Reading SEG.dat 0 entries loaded Reading EXT.dat 0 entries loaded and sorted 0 entries Reading COMPATSEG.dat 0 entries loaded Reading BOOTSTRAP.dat 60 entries loaded DUL: Warning: Recreating file "dict.ddl" Generating dict.ddl for version 10 OBJ$: segobjno 18, file 1 block 121 TAB$: segobjno 2, tabno 1, file 1 block 25 COL$: segobjno 2, tabno 5, file 1 block 25 USER$: segobjno 10, tabno 1, file 1 block 89 TABPART$: segobjno 563, file 1 block 3601 INDPART$: segobjno 568, file 1 block 3641 TABCOMPART$: segobjno 585, file 1 block 3785 INDCOMPART$: segobjno 590, file 1 block 3833 TABSUBPART$: segobjno 575, file 1 block 3697 INDSUBPART$: segobjno 580, file 1 block 3745 IND$: segobjno 2, tabno 3, file 1 block 25 ICOL$: segobjno 2, tabno 4, file 1 block 25 LOB$: segobjno 2, tabno 6, file 1 block 25 COLTYPE$: segobjno 2, tabno 7, file 1 block 25 TYPE$: segobjno 490, tabno 1, file 1 block 3073 COLLECTION$: segobjno 490, tabno 2, file 1 block 3073 ATTRIBUTE$: segobjno 490, tabno 3, file 1 block 3073 LOBFRAG$: segobjno 596, file 1 block 3881 LOBCOMPPART$: segobjno 599, file 1 block 3905 UNDO$: segobjno 15, file 1 block 105 TS$: segobjno 6, tabno 2, file 1 block 57 PROPS$: segobjno 97, file 1 block 673 Running generated file "@dict.ddl" to unload the dictionary tables . unloading table OBJ$ DUL: Warning: Recreating file "OBJ.ctl" 69665 rows unloaded . unloading table TAB$ DUL: Warning: Recreating file "TAB.ctl" 2585 rows unloaded . unloading table COL$ DUL: Warning: Recreating file "COL.ctl" 78741 rows unloaded . unloading table USER$ DUL: Warning: Recreating file "USER.ctl" 90 rows unloaded . unloading table TABPART$ 145 rows unloaded . unloading table INDPART$ 304 rows unloaded . unloading table TABCOMPART$ 0 rows unloaded . unloading table INDCOMPART$ 0 rows unloaded . unloading table TABSUBPART$ 0 rows unloaded . unloading table INDSUBPART$ 0 rows unloaded . unloading table IND$ 4061 rows unloaded . unloading table ICOL$ 6750 rows unloaded . unloading table LOB$ 785 rows unloaded . unloading table COLTYPE$ 2077 rows unloaded . unloading table TYPE$ 2657 rows unloaded . unloading table COLLECTION$ 924 rows unloaded . unloading table ATTRIBUTE$ 10149 rows unloaded . unloading table LOBFRAG$ 7 rows unloaded . unloading table LOBCOMPPART$ 0 rows unloaded . unloading table UNDO$ 21 rows unloaded . unloading table TS$ 7 rows unloaded . unloading table PROPS$ 32 rows unloaded Reading USER.dat 90 entries loaded Reading OBJ.dat 69665 entries loaded and sorted 69665 entries Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries Reading TAB.dat 2585 entries loaded Reading COL.dat 78741 entries loaded and sorted 78741 entries Reading SEG.dat 0 entries loaded Reading EXT.dat 0 entries loaded and sorted 0 entries Reading TABPART.dat 145 entries loaded and sorted 145 entries Reading TABCOMPART.dat 0 entries loaded and sorted 0 entries Reading TABSUBPART.dat 0 entries loaded and sorted 0 entries Reading INDPART.dat 304 entries loaded and sorted 304 entries Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries Reading IND.dat 4061 entries loaded Reading LOB.dat 785 entries loaded Reading ICOL.dat 6750 entries loaded Reading COLTYPE.dat 2077 entries loaded Reading TYPE.dat 2657 entries loaded Reading ATTRIBUTE.dat 10149 entries loaded Reading COLLECTION.dat 924 entries loaded Reading COMPATSEG.dat 0 entries loaded Reading BOOTSTRAP.dat 60 entries loaded Reading LOBFRAG.dat 7 entries loaded and sorted 7 entries Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries Reading UNDO.dat 21 entries loaded Reading TS.dat 7 entries loaded Reading PROPS.dat 32 entries loaded Database character set is WE8MSWIN1252 Database national character set is AL16UTF16 |
2.6. We had got dictionary of database now we can scan it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DUL> scan database; DUL: Warning: Recreating file "EXT.dat" DUL: Warning: Recreating file "SEG.dat" DUL: Warning: Recreating file "COMPATSEG.dat" DUL: Warning: Recreating file "SCANNEDLOBPAGE.dat" Scanning tablespace 0, data file 1 ... 1332 segment header and 74491 data blocks tablespace 0, data file 1: 88319 blocks scanned Scanning tablespace 1, data file 2 ... 5506 segment header and 23687 data blocks tablespace 1, data file 2: 72143 blocks scanned Scanning tablespace 2, data file 3 ... 0 segment header and 0 data blocks tablespace 2, data file 3: 6399 blocks scanned Scanning tablespace 4, data file 4 ... 41 segment header and 124 data blocks tablespace 4, data file 4: 639 blocks scanned Scanning tablespace 6, data file 5 ... 429 segment header and 6767 data blocks tablespace 6, data file 5: 12799 blocks scanned Reading EXT.dat 7812 entries loaded and sorted 7812 entries Reading SEG.dat 7308 entries loaded Reading COMPATSEG.dat 0 entries loaded Reading SCANNEDLOBPAGE.dat 8057 entries loaded and sorted 8057 entries |
2.7. Now we are ready to use export in this section we can use 3 different command.
UNLOAD TABLE owner.table; //For table export
UNLOAD USER user name; //For schema export
UNLOAD DATABASE; //For database export
2.8. I am going to try HR schema export.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DUL> unload user HR; About to unload HR's tables ... . unloading table REGIONS 4 rows unloaded . unloading (index organized) table COUNTRIES 25 rows unloaded . unloading table LOCATIONS 23 rows unloaded . unloading table DEPARTMENTS 27 rows unloaded . unloading table JOBS 19 rows unloaded . unloading table EMPLOYEES 107 rows unloaded . unloading table JOB_HISTORY 10 rows unloaded DUL> exit |
2.9. I was lucky that HR wasnt on any bad segments but sometimes it can happen and you need to sacrífice those rows. Lets check exports are really exist.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 |
[oracle@oracle dul]$ ls -ltrh total 13M -rwxrwxrwx 1 oracle oinstall 626K Jul 5 17:55 dul -rw-r--r-- 1 oracle oinstall 385 Jul 31 10:31 control.dul -rw-r--r-- 1 oracle oinstall 19K Jul 31 10:33 BOOTSTRAP.dat -rw-r--r-- 1 oracle oinstall 332 Jul 31 10:33 BOOTSTRAP.ctl -rw-r--r-- 1 oracle oinstall 6.3K Jul 31 10:33 dict.ddl -rw-r--r-- 1 oracle oinstall 1.7K Jul 31 10:33 USER.dat -rw-r--r-- 1 oracle oinstall 252 Jul 31 10:33 USER.ctl -rw-r--r-- 1 oracle oinstall 0 Jul 31 10:33 TABSUBPART.dat -rw-r--r-- 1 oracle oinstall 684 Jul 31 10:33 TABSUBPART.ctl -rw-r--r-- 1 oracle oinstall 6.9K Jul 31 10:33 TABPART.dat -rw-r--r-- 1 oracle oinstall 678 Jul 31 10:33 TABPART.ctl -rw-r--r-- 1 oracle oinstall 149K Jul 31 10:33 TAB.dat -rw-r--r-- 1 oracle oinstall 880 Jul 31 10:33 TAB.ctl -rw-r--r-- 1 oracle oinstall 0 Jul 31 10:33 TABCOMPART.dat -rw-r--r-- 1 oracle oinstall 334 Jul 31 10:33 TABCOMPART.ctl -rw-r--r-- 1 oracle oinstall 3.6M Jul 31 10:33 OBJ.dat -rw-r--r-- 1 oracle oinstall 600 Jul 31 10:33 OBJ.ctl -rw-r--r-- 1 oracle oinstall 44K Jul 31 10:33 LOB.dat -rw-r--r-- 1 oracle oinstall 810 Jul 31 10:33 LOB.ctl -rw-r--r-- 1 oracle oinstall 0 Jul 31 10:33 INDSUBPART.dat -rw-r--r-- 1 oracle oinstall 684 Jul 31 10:33 INDSUBPART.ctl -rw-r--r-- 1 oracle oinstall 15K Jul 31 10:33 INDPART.dat -rw-r--r-- 1 oracle oinstall 678 Jul 31 10:33 INDPART.ctl -rw-r--r-- 1 oracle oinstall 230K Jul 31 10:33 IND.dat -rw-r--r-- 1 oracle oinstall 810 Jul 31 10:33 IND.ctl -rw-r--r-- 1 oracle oinstall 0 Jul 31 10:33 INDCOMPART.dat -rw-r--r-- 1 oracle oinstall 334 Jul 31 10:33 INDCOMPART.ctl -rw-r--r-- 1 oracle oinstall 153K Jul 31 10:33 ICOL.dat -rw-r--r-- 1 oracle oinstall 392 Jul 31 10:33 ICOL.ctl -rw-r--r-- 1 oracle oinstall 129K Jul 31 10:33 COLTYPE.dat -rw-r--r-- 1 oracle oinstall 608 Jul 31 10:33 COLTYPE.ctl -rw-r--r-- 1 oracle oinstall 4.8M Jul 31 10:33 COL.dat -rw-r--r-- 1 oracle oinstall 950 Jul 31 10:33 COL.ctl -rw-r--r-- 1 oracle oinstall 208K Jul 31 10:33 TYPE.dat -rw-r--r-- 1 oracle oinstall 392 Jul 31 10:33 TYPE.ctl -rw-r--r-- 1 oracle oinstall 115K Jul 31 10:33 COLLECTION.dat -rw-r--r-- 1 oracle oinstall 754 Jul 31 10:33 COLLECTION.ctl -rw-r--r-- 1 oracle oinstall 1.1M Jul 31 10:33 ATTRIBUTE.dat -rw-r--r-- 1 oracle oinstall 752 Jul 31 10:33 ATTRIBUTE.ctl -rw-r--r-- 1 oracle oinstall 937 Jul 31 10:33 UNDO.dat -rw-r--r-- 1 oracle oinstall 532 Jul 31 10:33 UNDO.ctl -rw-r--r-- 1 oracle oinstall 142 Jul 31 10:33 TS.dat -rw-r--r-- 1 oracle oinstall 318 Jul 31 10:33 TS.ctl -rw-r--r-- 1 oracle oinstall 964 Jul 31 10:33 PROPS.dat -rw-r--r-- 1 oracle oinstall 254 Jul 31 10:33 PROPS.ctl -rw-r--r-- 1 oracle oinstall 286 Jul 31 10:33 LOBFRAG.dat -rw-r--r-- 1 oracle oinstall 608 Jul 31 10:33 LOBFRAG.ctl -rw-r--r-- 1 oracle oinstall 0 Jul 31 10:33 LOBCOMPPART.dat -rw-r--r-- 1 oracle oinstall 336 Jul 31 10:33 LOBCOMPPART.ctl -rwxrw-rw- 1 oracle oinstall 0 Jul 31 10:34 COMPATSEG.dat -rwxrw-rw- 1 oracle oinstall 166K Jul 31 10:34 SEG.dat -rw-r--r-- 1 oracle oinstall 452K Jul 31 10:34 SCANNEDLOBPAGE.dat -rwxrw-rw- 1 oracle oinstall 349K Jul 31 10:34 EXT.dat -rwxrw-rw- 1 oracle oinstall 8.0K Jul 31 10:36 seen_tab.dat -rwxrw-rw- 1 oracle oinstall 120K Jul 31 10:36 seen_col.dat -rwxrw-rw- 1 oracle oinstall 399 Jul 31 10:36 init.dul -rw-r--r-- 1 oracle oinstall 1.4K Jul 31 10:41 HR_REGIONS.dmp -rw-r--r-- 1 oracle oinstall 2.8K Jul 31 10:41 HR_LOCATIONS.dmp -rw-r--r-- 1 oracle oinstall 2.1K Jul 31 10:41 HR_JOBS.dmp -rw-r--r-- 1 oracle oinstall 1.8K Jul 31 10:41 HR_JOB_HISTORY.dmp -rw-r--r-- 1 oracle oinstall 10K Jul 31 10:41 HR_EMPLOYEES.dmp -rw-r--r-- 1 oracle oinstall 2.1K Jul 31 10:41 HR_DEPARTMENTS.dmp -rw-r--r-- 1 oracle oinstall 1.8K Jul 31 10:41 HR_COUNTRIES.dmp -rwxrw-rw- 1 oracle oinstall 3.7K Jul 31 10:42 dul.log |
2.10. Seems like its okey. Now lets try our export to import other database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
[oracle@oracle dul]$ imp canbakal/canbakal file=HR_REGIONS.dmp full=y Import: Release 11.1.0.6.0 - Production on Sun Jul 31 10:52:36 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V07.00.07 via conventional path Warning: the objects were exported by Bernard's DUL, not by you . importing Bernard's DUL's objects into CANBAKAL . importing Bernard's DUL's objects into CANBAKAL . . importing table "REGIONS" 4 rows imported Import terminated successfully without warnings. [oracle@oracle dul]$ imp canbakal/canbakal file=HR_COUNTRIES.dmp full=y Import: Release 11.1.0.6.0 - Production on Sun Jul 31 11:34:01 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V07.00.07 via conventional path Warning: the objects were exported by Bernard's DUL, not by you . importing Bernard's DUL's objects into CANBAKAL . importing Bernard's DUL's objects into CANBAKAL . . importing table "COUNTRIES" 25 rows imported Import terminated successfully without warnings. |
2.11. 2 import finished without any problem lets try to query them.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
[oracle@oracle dul]$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jul 31 10:52:42 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from canbakal.regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa |
2.11. Its hard to find information about DUL thats the reason when you receive any error it can make you crazy. I am sharing some fixes related some problems.
WRONG osd_dba_file_bits size
This can generate output similar to below. Normally this should not happen since you should create a demo database and check this via the DUL documented (in html page) query.
The mismatch in DBA’s is only in the file number (first number in brackets) part. The second number, the block number, is correct.
Data UnLoader: Release 3.2.0.1 – Internal Use Only – on Wed Sep 3 10:40:33 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
DUL: Warning: Block[1][2] DBA in block mismatch [4][2]
DUL: Warning: Bad cache layer header file#=1, block#=2
DUL: Warning: Block[1][3] DBA in block mismatch [4][3]
DUL: Warning: Bad cache layer header file#=1, block#=3
………..and etc……….
WRONG osd_file_leader_size
This may create output similar to below, but many other flavours are possible. In this case we are a fixed number of blocks off. The file number is correct. The difference in the block numbers is constant.:
Data UnLoader: Release 3.2.0.1 – Internal Use Only – on Wed Sep 3 10:44:23 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
DUL: Warning: Block[1][2] DBA in block mismatch [1][3]
DUL: Warning: Bad cache layer header file#=1, block#=2
DUL: Warning: Block[1][3] DBA in block mismatch [1][4]
DUL: Warning: Bad cache layer header file#=1, block#=3
………..and etc……….
WRONG osd_c_struct_alignment
This may generate output similar to the following:
Data UnLoader: Release 3.2.0.1 – Internal Use Only – on Wed Sep 3 10:46:10 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
. unloading table OBJ$
DUL: Warning: file# 0 is out of range
DUL: Warning: Cannot read data block file#=0, block# = 262145
OS error 2: No such file or directory
DUL: Warning: file# 0 is out of range
DUL: Warning: Cannot read data block file#=0, block# = 262146
OS error 2: No such file or directory
………..and etc……….
WRONG db_block_size
The following output was generated when the db_block_size was set too
small. The correct value was 4096 and it was set to 2048. Normally, the
value for this parameter should be taken from the Oracle instances’s init.ora
file and will not be correctly set.
Data UnLoader: Release 3.2.0.1 – Internal Use Only – on Thu Sep 4 12:38:25 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
DUL: Warning: Block[1][2] DBA in block mismatch [513][1159680]
DUL: Warning: File=1, block 2: illegal block version 2
DUL: Warning: Block[1][2] Illegal block type[0]
DUL: Warning: Bad cache layer header file#=1, block#=2
DUL: Warning: Block[1][4] DBA in block mismatch [1][2]
DUL: Warning: File[1]Block[4]INCSEQ mismatch[90268!=0]
DUL: Warning: Bad cache layer header file#=1, block#=4
DUL: Warning: Block[1][6] DBA in block mismatch [1][3]
DUL: Warning: File[1]Block[6]INCSEQ mismatch[139591710!=86360346]
DUL: Warning: Bad cache layer header file#=1, block#=6
………..and etc……….
QUOTE MISSING
If you get the following error it is caused by the data dictionary tables
“USER$, OBJ$, TAB$ and COL$” not being correctly generated. To
fix this error simply delete all dictv6.ddl or dictv7.ddl created .dat
and .ctl files and restart.
Data UnLoader: Release 3.2.0.1 – Internal Use Only – on Wed Sep 3 10:49:30 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
DUL: Error: Quote missing
1.AMAÇ VE KAPSAM
Verilerimiz büyük ve backup alırken sorun yaşıyorsak Block Change Tracking (BCT) dosyasını oluşturup incremental backup alma işlemimizi hızlandırabiliriz.
1.AMAÇ VE KAPSAM
Restore işleminde datafileların pathlerinin yerlerinin değiştirilmesi
2.UYGULAMA
2.1. Database’i mount posizyonunda açıyoruz.
[oracle@oracle dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Sat Oct 23 17:35:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 251660416 bytes
Database Buffers 163577856 bytes
Redo Buffers 6131712 bytes
Database mounted.
[Read more…]