Archive for
Dec 29
Can we use Flashback database If noarchivelog exists
Can we use flashback database log after switching from archivelog mode to noarchivelog mode and back to archivelog mode?
According to this below document reference
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV583
We cannot, because when we turn off flashback database, you lose the flashback logs unless they are guaranteed
If they guaranteed, We can’t alter no archivelog mode
Let us see some example :
Step 1: Ensure Flashback ON
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES
Step 2:
SQL> conn flashown/flashown; Connected. SQL> create table employee as select * from scott.emp; Table created. SQL> update employee set sal=sal+50; 14 rows updated. SQL> commit; Commit complete. SQL> create restore point azar; Restore point created. SQL> update employee set sal=sal+50; 14 rows updated. SQL> commit; SQL>select current_timestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 19-DEC-10 09.53.55.726000 AM +03:00
Step 3: alter noarchivelog mode
SQL> shutdown immediate ORACLE instance shut down. SQL> startup mount Database mounted. SQL> alter database noarchivelog; alter database noarchivelog * ERROR at line 1: ORA-38774: cannot disable media recovery - flashback database is enabled SQL> alter database flashback off; Database altered. SQL> alter database noarchivelog; Database altered. SQL> alter database open; Database altered. SQL> update flashown.employee set sal=sal+50; 14 rows updated. SQL> commit; Commit complete.
Step 4: Again back to archivelog mode & Recover flashback database But we can’t.
SQL> shutdown immediate ORACLE instance shut down. SQL> startup mount Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database flashback on; Database altered. SQL> flashback database to restore point azar; flashback database to restore point azar * ERROR at line 1: ORA-38729: Not enough flashback database log data to do FLASHBACK. SQL> flashback database to timestamp to_date('19-DEC-10 09.54.00','DD-MM-YY HH24:MI:SS'); flashback database to timestamp to_date('19-DEC-10 09.54.00','DD-MM-YY HH24:MI:SS') * ERROR at line 1: ORA-38729: Not enough flashback database log data to do FLASHBACK.
Step 5: Create Guarantee Restore point
SQL> show parameter db_flashback_retention_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 SQL> create restore point azardb guarantee flashback database; Restore point created. SQL> alter database open; Database altered. SQL> select sal from flashown.employee; SAL ---------- 955 1755 1405 3130 1405 3005 2605 3255 5155 1655 1255 SAL ---------- 1105 3155 1455 14 rows selected. SQL> select current_timestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 19-DEC-10 10.11.43.716000 AM +03:00 SQL> update flashown.employee set sal=sal-5; 14 rows updated. SQL> commit; Commit complete. SQL> shutdown immediate ORACLE instance shut down. SQL> startup mount Database mounted. SQL> alter database flashback off; Database altered. SQL> alter database noarchivelog; alter database noarchivelog * ERROR at line 1: ORA-38781: cannot disable media recovery - have guaranteed restore points SQL> alter database flashback on; Database altered. SQL> flashback database to timestamp to_date('19-DEC-10 10.11.44','DD-MM-YY HH24:MI:SS'); Flashback complete. SQL> alter database open resetlogs; Database altered. SQL> select sal from flashown.employee; SAL ---------- 955 1755 1405 3130 1405 3005 2605 3255 5155 1655 1255 SAL ---------- 1105 3155 1455 14 rows selected. SQL>
So we can’t alter noarchivelog mode While Guarantee restore point exists in DB.
And the If flashback database off, we can’t recover flashback database using flashback logs.
Dec 18
Flashback Data Archive
Flashback Data archive is one of the Oracle 11g feature (Oracle Total Recall).
A Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.
How to Enable Flashback Data Archive;
Step 1: Conn AS sys user & create tablespace
SQL> select flashback_on from v$database; FLASHBACK_ON SQL> create tablespace flashbacktbs datafile 'D:\backup\flbtbs01.dbf' size 50m autoextend on; Tablespace created. ------------------ YES
Step 2: Create Flashback Data Archive with retention period
SQL> create flashback archive fla1 tablespace flashbacktbs retention 1 year; Flashback archive created.
Step 3:
SQL> conn scott/tiger; Connected. SQL> alter table emp flashback archive fla1; alter table emp flashback archive fla1 * ERROR at line 1: ORA-55620: No privilege to use Flashback Archive So Need to give privilege for scott user
Step 4: Grant privilege
SQL> conn sys/Admin123 as sysdba Connected. SQL> grant flashback archive on fla1 to scott; Grant succeeded.
Step 5:
SQL> conn scott/tiger; Connected. SQL> alter table emp flashback archive fla1; Table altered.
Step 6:
SQL> select empno,ename,sal from emp where empno=7788; EMPNO ENAME SAL ---------- ---------- ---------- 7788 SCOTT 3000 SQL> select current_timestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 18-DEC-10 10.53.41.521000 AM +03:00 SQL> update emp set sal=sal+100 where empno=7788; 1 row updated. SQL> select sal from emp as of timestamp to_timestamp('18-DEC-10 10:53:41','DD-MM-YY HH24:MI:SS') WHERE EMPNO=7788; SAL ---------- 3000 SQL> select empno,ename,sal from emp where empno=7788; EMPNO ENAME SAL ---------- ---------- ---------- 7788 SCOTT 3100
Some Other operations:
Modify Retention Period:
SQL> alter flashback archive FLA1 modify retention 2 year; Flashback archive altered.
Purge all data
SQL> alter flashback archive FLA1 purge all; Flashback archive altered.
Disable Falshback data archive for table
SQL> alter table scott.emp no flashback archive; Table altered.
ADD/MODIFY/REMOVE tablespace
SQL> alter flashback archive fla1 add tablespace testdb; Flashback archive altered.
Ref :
https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#BJFIEEII
Dec 17
Duplicate database from ASM to non ASM
Download PDF Document from here
Duplicate Database from ASM to Non pdf Right click on thir URL, Save Target as & then Download it.
Step 1:
Determine how much disk space will be required.
SQL> select name from v$database; NAME --------- AZARDB
Calculate total space for all datafiles within database
SQL> select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ; DataFile Size Mb Redo Log Size Mb ---------------- ---------------- Control File Size Mb Total Size Mb -------------------- ------------- 1310 150 18.59375 1478.59375
Calculate space for list of datafiles within primary database
SQL> select DF.TOTAL/1048576 "DataFile Size Mb", LOG.TOTAL/1048576 "Redo Log Size Mb", CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual, (select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in ('SYSTEM','SYSAUX','UNDO','USERS')) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ; DataFile Size Mb Redo Log Size Mb ---------------- ---------------- Control File Size Mb Total Size Mb -------------------- ------------- 1165 150 18.59375 1333.59375
Step 2: backup Source database
RMAN> run{ 2> allocate channel d1 type disk; 3> backup format '/backups/df_t%t_s%s_p%p' database; 4> sql 'alter system archive log current'; 5> backup format '/backups/al_t%t_s%s_p%p' archivelog all; 6> release channel d1; 7> } released channel: ORA_DISK_1 allocated channel: d1 channel d1: SID=44 device type=DISK Starting backup at 17-DEC-10 channel d1: starting full datafile backup set channel d1: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/azardb/datafile/system.256.736179685 input datafile file number=00002 name=+DATA/azardb/datafile/sysaux.257.736179685 input datafile file number=00005 name=+DATA/azardb/datafile/example.269.736179971 input datafile file number=00003 name=+DATA/azardb/datafile/undotbs1.258.736179685 input datafile file number=00004 name=+SPTEST/azardb/datafile/users.256.737964169 channel d1: starting piece 1 at 17-DEC-10 channel d1: finished piece 1 at 17-DEC-10 piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635 comment=NONE channel d1: backup set complete, elapsed time: 00:02:27 channel d1: starting full datafile backup set channel d1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel d1: starting piece 1 at 17-DEC-10 channel d1: finished piece 1 at 17-DEC-10 piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635 comment=NONE channel d1: backup set complete, elapsed time: 00:00:01 Finished backup at 17-DEC-10 sql statement: alter system archive log current Starting backup at 17-DEC-10 current log archived channel d1: starting archived log backup set channel d1: specifying archived log(s) in backup set input archived log thread=1 sequence=3 RECID=1 STAMP=736268182 input archived log thread=1 sequence=4 RECID=2 STAMP=736345263 input archived log thread=1 sequence=5 RECID=3 STAMP=736438711 input archived log thread=1 sequence=6 RECID=4 STAMP=737551583 input archived log thread=1 sequence=7 RECID=5 STAMP=737880523 input archived log thread=1 sequence=8 RECID=6 STAMP=737897929 input archived log thread=1 sequence=9 RECID=7 STAMP=737962069 input archived log thread=1 sequence=10 RECID=8 STAMP=737969217 input archived log thread=1 sequence=11 RECID=9 STAMP=737969220 input archived log thread=1 sequence=12 RECID=10 STAMP=737969948 input archived log thread=1 sequence=13 RECID=11 STAMP=737969948 channel d1: starting piece 1 at 17-DEC-10 channel d1: finished piece 1 at 17-DEC-10 piece handle=/backups/al_t737969948_s8_p1 tag=TAG20101217T073908 comment=NONE channel d1: backup set complete, elapsed time: 00:00:15 Finished backup at 17-DEC-10 released channel: d1 RMAN>
Step 3: Create pfile & edit some required parameters
SQL> create pfile='/backups/initAUX.ora' from spfile; File created. SQL> Edit& Save *.audit_file_dest =/u01/app/oracle/AUX/adump *.core_dump_dest =/u01/app/oracle/AUX/cdump *.db_name ="AUX" *.instance_name =AUX *.control_files =/u01/app/oracle/AUX/control01.ctl *.db_file_name_convert =("+DATA","/u01/app/oracle/AUX","+SPTEST","/u01/app/oracle/AUX") *.log_file_name_convert =("+DATA","/u01/app/oracle/AUX") *.undo_management =AUTO *.undo_retention =10800 *.undo_tablespace='UNDOTBS1' *.db_block_size=8192 *.compatible='11.2.0.0.0'
Step 4: startup nomount for AUX database
[oracle@localhost ~]$ export ORACLE_SID=AUX [oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:14:09 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> startup nomount pfile='/backups/initAUX.ora'; ORACLE instance started. Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes SQL>
Step 5:Create duplicate database from here
[oracle@localhost ~]$ export ORACLE_SID=AUX [oracle@localhost ~]$ rman target sys/Admin123@azardb auxiliary / Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 17 09:18:34 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: AZARDB (DBID=1655311948) connected to auxiliary database: AUX (not mounted) RMAN> run{ 2> allocate auxiliary channel C1 device type disk; 3> duplicate target database to AUX; 4> } using target database control file instead of recovery catalog allocated channel: C1 channel C1: SID=20 device type=DISK Starting Duplicate Db at 17-DEC-10 contents of Memory Script: { sql clone "create spfile from memory"; } executing Memory Script sql statement: create spfile from memory contents of Memory Script: { shutdown clone immediate; startup clone nomount; } executing Memory Script Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes allocated channel: C1 channel C1: SID=18 device type=DISK contents of Memory Script: { sql clone "alter system set db_name = ''AZARDB'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone primary controlfile; alter clone database mount; } executing Memory Script sql statement: alter system set db_name = ''AZARDB'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes allocated channel: C1 channel C1: SID=18 device type=DISK Starting restore at 17-DEC-10 channel C1: starting datafile backup set restore channel C1: restoring control file channel C1: reading from backup piece /backups/df_t737969943_s7_p1 channel C1: piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635 channel C1: restored backup piece 1 channel C1: restore complete, elapsed time: 00:00:03 output file name=/u01/app/oracle/AUX/control01.ctl Finished restore at 17-DEC-10 database mounted contents of Memory Script: { set until scn 971599; set newname for datafile 1 to "/u01/app/oracle/AUX/azardb/datafile/system.256.736179685"; set newname for datafile 2 to "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685"; set newname for datafile 3 to "/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685"; set newname for datafile 4 to "/u01/app/oracle/AUX/azardb/datafile/users.256.737964169"; set newname for datafile 5 to "/u01/app/oracle/AUX/azardb/datafile/example.269.736179971"; restore clone database ; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 17-DEC-10 channel C1: starting datafile backup set restore channel C1: specifying datafile(s) to restore from backup set channel C1: restoring datafile 00001 to /u01/app/oracle/AUX/azardb/datafile/system.256.736179685 channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256.737964169 channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/datafile/example.269.736179971 channel C1: reading from backup piece /backups/df_t737969795_s6_p1 channel C1: piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635 channel C1: restored backup piece 1 channel C1: restore complete, elapsed time: 00:01:27 Finished restore at 17-DEC-10 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=10 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/system.256.736179685 datafile 2 switched to datafile copy input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 datafile 3 switched to datafile copy input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 datafile 4 switched to datafile copy input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 datafile 5 switched to datafile copy input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 contents of Memory Script: { set until scn 971599; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 17-DEC-10 starting media recovery archived log for thread 1 with sequence 12 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 archived log for thread 1 with sequence 13 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12 archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 thread=1 sequence=13 media recovery complete, elapsed time: 00:00:00 Finished recover at 17-DEC-10 contents of Memory Script: { shutdown clone immediate; startup clone nomount; sql clone "alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile"; sql clone "alter system reset db_unique_name scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script database dismounted Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes allocated channel: C1 channel C1: SID=18 device type=DISK sql statement: alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile sql statement: alter system reset db_unique_name scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes allocated channel: C1 channel C1: SID=18 device type=DISK sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/azardb/onlinelog/group_1.263.736179919' ) SIZE 50 M REUSE, GROUP 2 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/azardb/onlinelog/group_2.265.736179925' ) SIZE 50 M REUSE, GROUP 3 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/azardb/onlinelog/group_3.267.736179929' ) SIZE 50 M REUSE DATAFILE '/u01/app/oracle/AUX/azardb/datafile/system.256.736179685' CHARACTER SET WE8MSWIN1252 contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955"; switch clone tempfile all; catalog clone datafilecopy "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685", "/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685", "/u01/app/oracle/AUX/azardb/datafile/users.256.737964169", "/u01/app/oracle/AUX/azardb/datafile/example.269.736179971"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955 in control file cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112 cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112 cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 RECID=3 STAMP=737976112 cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 RECID=4 STAMP=737976113 datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 [oracle@localhost ~]$ export ORACLE_SID=AUX [oracle@localhost ~]$ rman target sys/Admin123@azardb auxiliary / Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 17 09:18:34 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: AZARDB (DBID=1655311948) connected to auxiliary database: AUX (not mounted) RMAN> run{ 2> allocate auxiliary channel C1 device type disk; 3> duplicate target database to AUX; 4> } using target database control file instead of recovery catalog allocated channel: C1 channel C1: SID=20 device type=DISK Starting Duplicate Db at 17-DEC-10 contents of Memory Script: { sql clone "create spfile from memory"; } executing Memory Script sql statement: create spfile from memory contents of Memory Script: { shutdown clone immediate; startup clone nomount; } executing Memory Script Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes allocated channel: C1 channel C1: SID=18 device type=DISK contents of Memory Script: { sql clone "alter system set db_name = ''AZARDB'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone primary controlfile; alter clone database mount; } executing Memory Script sql statement: alter system set db_name = ''AZARDB'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes allocated channel: C1 channel C1: SID=18 device type=DISK Starting restore at 17-DEC-10 channel C1: starting datafile backup set restore channel C1: restoring control file channel C1: reading from backup piece /backups/df_t737969943_s7_p1 channel C1: piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635 channel C1: restored backup piece 1 channel C1: restore complete, elapsed time: 00:00:03 output file name=/u01/app/oracle/AUX/control01.ctl Finished restore at 17-DEC-10 database mounted contents of Memory Script: { set until scn 971599; set newname for datafile 1 to "/u01/app/oracle/AUX/azardb/datafile/system.256.736179685"; set newname for datafile 2 to "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685"; set newname for datafile 3 to "/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685"; set newname for datafile 4 to "/u01/app/oracle/AUX/azardb/datafile/users.256.737964169"; set newname for datafile 5 to "/u01/app/oracle/AUX/azardb/datafile/example.269.736179971"; restore clone database ; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 17-DEC-10 channel C1: starting datafile backup set restore channel C1: specifying datafile(s) to restore from backup set channel C1: restoring datafile 00001 to /u01/app/oracle/AUX/azardb/datafile/system.256.736179685 channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256.737964169 channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/datafile/example.269.736179971 channel C1: reading from backup piece /backups/df_t737969795_s6_p1 channel C1: piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635 channel C1: restored backup piece 1 channel C1: restore complete, elapsed time: 00:01:27 Finished restore at 17-DEC-10 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=10 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/system.256.736179685 datafile 2 switched to datafile copy input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 datafile 3 switched to datafile copy input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 datafile 4 switched to datafile copy input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 datafile 5 switched to datafile copy input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 contents of Memory Script: { set until scn 971599; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 17-DEC-10 starting media recovery archived log for thread 1 with sequence 12 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 archived log for thread 1 with sequence 13 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12 archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 thread=1 sequence=13 media recovery complete, elapsed time: 00:00:00 Finished recover at 17-DEC-10 contents of Memory Script: { shutdown clone immediate; startup clone nomount; sql clone "alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile"; sql clone "alter system reset db_unique_name scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script database dismounted Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes allocated channel: C1 channel C1: SID=18 device type=DISK sql statement: alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile sql statement: alter system reset db_unique_name scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes allocated channel: C1 channel C1: SID=18 device type=DISK sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/azardb/onlinelog/group_1.263.736179919' ) SIZE 50 M REUSE, GROUP 2 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/azardb/onlinelog/group_2.265.736179925' ) SIZE 50 M REUSE, GROUP 3 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/azardb/onlinelog/group_3.267.736179929' ) SIZE 50 M REUSE DATAFILE '/u01/app/oracle/AUX/azardb/datafile/system.256.736179685' CHARACTER SET WE8MSWIN1252 contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955"; switch clone tempfile all; catalog clone datafilecopy "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685", "/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685", "/u01/app/oracle/AUX/azardb/datafile/users.256.737964169", "/u01/app/oracle/AUX/azardb/datafile/example.269.736179971"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955 in control file cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112 cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112 cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 RECID=3 STAMP=737976112 cataloged datafile copy datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 RECID=4 STAMP=737976113 datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Duplicate database was successfully created
Step 6: Check Duplicate Database
[oracle@localhost ~]$ export ORACLE_SID=AUX [oracle@localhost ~]$ sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:25:45 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$database; NAME --------- AUX SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/AUX/azardb/datafile/system.256.736179685 /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 /u01/app/oracle/AUX/azardb/datafile/users.256.737964169 /u01/app/oracle/AUX/azardb/datafile/example.269.736179971 SQL>
Possible Errors:
You can get some errors , cann’t restore datafile & onlineredo log files, in this case you need to create directory ,It should be indicate.
azardb/datafile
azardb/onlinelog
Dec 16
How to move ASM spfile from one diskgroup to another diskgroup
Here , I just showed you ,How to move ASM spfile from one diskgroup to another diskgroup.
before I have currently using one diskgroup, so i need to create another diskgroup using ASMCA, I just formated disk using fdisk command.
After created new diskgroup, follow the steps
[root@localhost ~]# su - oracle [oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid [oracle@localhost ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin [oracle@localhost ~]$ export ORACLE_SID=+ASM [oracle@localhost ~]$ sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 16 11:46:32 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter user-name: sys/Admin123 as sysasm Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Automatic Storage Management option SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/asm/asmparameterfile/reg istry.253.736177541 SQL> create pfile='/install/initASM.ora' from spfile; File created. SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/asm/asmparameterfile/reg istry.253.736177541 SQL> create spfile='+SPTEST' from pfile='/install/initASM.ora'; File created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Automatic Storage Management option
[oracle@localhost ~]$ crsctl stop has CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'localhost' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'localhost' CRS-2673: Attempting to stop 'ora.SPTEST.dg' on 'localhost' CRS-2673: Attempting to stop 'ora.azardb.db' on 'localhost' CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'localhost' succeeded CRS-2677: Stop of 'ora.SPTEST.dg' on 'localhost' succeeded CRS-2677: Stop of 'ora.azardb.db' on 'localhost' succeeded CRS-2673: Attempting to stop 'ora.DATA.dg' on 'localhost' CRS-2677: Stop of 'ora.DATA.dg' on 'localhost' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'localhost' CRS-2677: Stop of 'ora.asm' on 'localhost' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'localhost' CRS-2677: Stop of 'ora.cssd' on 'localhost' succeeded CRS-2673: Attempting to stop 'ora.diskmon' on 'localhost' CRS-2677: Stop of 'ora.diskmon' on 'localhost' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'localhost' has completed CRS-4133: Oracle High Availability Services has been stopped. [oracle@localhost ~]$
[oracle@localhost ~]$ crsctl start has CRS-4123: Oracle High Availability Services has been started.
[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid [oracle@localhost ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin [oracle@localhost ~]$ export ORACLE_SID=+ASM [oracle@localhost ~]$ sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 16 11:57:30 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter user-name: sys/Admin123 as sysasm Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Automatic Storage Management option SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +SPTEST/asm/asmparameterfile/r egistry.253.737898615 SQL> select name,state from v$asm_diskgroup; NAME STATE ------------------------------ ----------- SPTEST MOUNTED DATA MOUNTED SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Automatic Storage Management option
you can verify using asmcmd command
[oracle@localhost ~]$ asmcmd spget +SPTEST/asm/asmparameterfile/registry.253.737898615
just remove old spfile from here
[[oracle@localhost ~]$ asmcmd rm +DATA/asm/asmparameterfile/registry.253.736177541
[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid [oracle@localhost ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin [oracle@localhost ~]$ export ORACLE_SID=+ASM [oracle@localhost ~]$ sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 16 12:00:08 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter user-name: sys/Admin123 as sysasm Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Automatic Storage Management option SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +SPTEST/asm/asmparameterfile/r egistry.253.737898615 SQL>
Datapump REUSE_DUMPFILES parameter
REUSE_DUMPFILES parameter is using for overwriting preexisting dump file.
It default parameter is NO.
Data Pump Export will return an error if you specify a dump file name that already exists. The REUSE_DUMPFILES
parameter allows you to override that behavior and reuse a dump file name.
This is compatabile for Oracle 11g Version, Not in Oracle 10g version.
see below Example :
C:\Users\mazar>expdp scott/tiger@azardb dumpfile=reusedump.dmp directory=data_pump_dir Export: Release 11.2.0.1.0 - Production on Sat Dec 11 12:17:12 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@azardb dumpfile=reusedump.dmp directory=data_pump_dir Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.945 KB 4 rows . . exported "SCOTT"."EMP" 8.578 KB 14 rows . . exported "SCOTT"."MYTEST" 5.429 KB 1 rows . . exported "SCOTT"."SALGRADE" 5.875 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\REUSEDUMP.DMP Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:18:05
C:\Users\mazar>expdp scott/tiger@azardb dumpfile=reusedump.dmp directory=data_pump_dir Export: Release 11.2.0.1.0 - Production on Sat Dec 11 12:18:17 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31641: unable to create dump file "C:\app\oracle\mazar\admin\azardb\dpdump\reusedump.dmp" ORA-27038: created file already exists OSD-04010: <create> option specified, file already exists
C:\Users\mazar>expdp scott/tiger@azardb dumpfile=reusedump.dmp directory=data_pump_dir reuse_dumpfiles=y Export: Release 11.2.0.1.0 - Production on Sat Dec 11 12:18:31 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@azardb dumpfile=reusedump.dmp directory=data_pump_dir reuse_dumpfiles=y Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.945 KB 4 rows . . exported "SCOTT"."EMP" 8.578 KB 14 rows . . exported "SCOTT"."MYTEST" 5.429 KB 1 rows . . exported "SCOTT"."SALGRADE" 5.875 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\REUSEDUMP.DMP Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:19:10
Ref :
http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm
Dec 11
Datapump compression parameter
Here I just like to show How compression datapump parameter working in Oracle 11g R2 ( see Below Screenshot How size vary from others.)
Default: METADATA_ONLY
Specifies which data to compress before writing to the dump file set
COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}
ALL
enables compression for the entire export operation. TheALL
option requires that the Oracle Advanced Compression option be enabled.DATA_ONLY
results in all data being written to the dump file in compressed format. TheDATA_ONLY
option requires that the Oracle Advanced Compression option be enabled.METADATA_ONLY
results in all metadata being written to the dump file in compressed format. This is the default.NONE
disables compression for the entire export operation.
- To make full use of all these compression options, the
COMPATIBLE
initialization parameter must be set to at least 11.0.0. - The
METADATA_ONLY
option can be used even if theCOMPATIBLE
initialization parameter is set to 10.2. - Compression of data (using values
ALL
orDATA_ONLY
) is valid only in the Enterprise Edition of Oracle Database 11g
For Example , See Below Screenshot:
Compression =METADATA_ONLY
C:\Users\mazar>set oracle_sid=azardb C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressmeta.dmp directory=data_pump_dir compression=metadata_only Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:19:38 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@azardb dumpfile=compressmeta.dmp directory=data_pump_dir compression=metadata_only Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.945 KB 4 rows . . exported "SCOTT"."EMP" 8.578 KB 14 rows . . exported "SCOTT"."MYTEST" 5.429 KB 1 rows . . exported "SCOTT"."SALGRADE" 5.875 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSMETA.DMP Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:21:05
Compression =ALL
C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressall.dmp directory=data_pump_dir compression=all Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:21:59 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@azardb dumpfile=compressall.dmp directory=data_pump_dir compression=all Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 4.984 KB 4 rows . . exported "SCOTT"."EMP" 5.625 KB 14 rows . . exported "SCOTT"."MYTEST" 4.789 KB 1 rows . . exported "SCOTT"."SALGRADE" 4.898 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSALL.DMP Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:22:41
Compression =DATA_ONLY
C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressdata.dmp directory=data_pump_dir compression=data_only Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:23:23 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@azardb dumpfile=compressdata.dmp directory=data_pump_dir compression=data_only Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 4.984 KB 4 rows . . exported "SCOTT"."EMP" 5.625 KB 14 rows . . exported "SCOTT"."MYTEST" 4.789 KB 1 rows . . exported "SCOTT"."SALGRADE" 4.898 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSDATA.DMP Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:24:02
Compression =NONE
C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressnone.dmp directory=data_pump_dir compression=none Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:24:28 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@azardb dumpfile=compressnone.dmp directory=data_pump_dir compression=none Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.945 KB 4 rows . . exported "SCOTT"."EMP" 8.578 KB 14 rows . . exported "SCOTT"."MYTEST" 5.429 KB 1 rows . . exported "SCOTT"."SALGRADE" 5.875 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSNONE.DMP Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:25:09
Screenshot, How size vary from every compression parameter.
Ref :
http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm
Can I rename Export Dumpfile?
One of User Asked me, Can I rename my dumpfle, Yes You can
Just see Example :
SQL> conn scott/tiger; Connected. SQL> create table mytest(empname varchar2(20),city varchar2(20)); Table created. SQL> insert into mytest values('azar','riyadh'); 1 row created. SQL> commit; Commit complete. SQL> conn / as sysdba Connected. SQL> grant read,write on directory data_pump_dir to scott; Grant succeeded.
I just Do Export
SQL> $expdp scott/tiger directory=data_pump_dir dumpfile=expscott.dmp tables=mytest Export: Release 11.2.0.1.0 - Production on Wed Dec 8 10:50:32 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=data_pump_dir dumpfile=expscott.dmp tables=mytest Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."MYTEST" 5.429 KB 1 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\EXPSCOTT.DMP Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 10:52:25
SQL> conn scott/tiger Connected. SQL> drop table mytest; Table dropped. SQL> commit; Commit complete. SQL> select * from mytest; select * from mytest * ERROR at line 1: ORA-00942: table or view does not exist
I just renamed TESTSCOTT.DMP FOR EXPSCOTT.DMP
SQL> $impdp scott/tiger directory=data_pump_dir dumpfile=testscott.dmp tables=mytest Import: Release 11.2.0.1.0 - Production on Wed Dec 8 10:56:03 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=data_pump_dir dumpfile=testscott.dmp tables=mytest Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."MYTEST" 5.429 KB 1 rows Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 10:56:14
SQL> conn scott/tiger; Connected. SQL> select * from mytest; EMPNAME CITY -------------------- -------------------- azar riyadh