Statistics are EXACT measurements of the data itself,how large a table is and how useful an index is.Remember out dated statistics cause optimizer to not functioning realistically.Out-of-date statistics would have the same effect on all types of databases. statistics contain number of rows in table,blocks used by table,empty blocks (i.e HWM),average row length,indexes leaf blocks its blevel,number of buckets (i.e histogram). i made a little observation regarding histograms stats that how statisitics affect optimizer. SQL> create table t as select rownum rn,'A' status from all_objects 2 / Table created. SQL> select count(*) 2 from t 3 group by status 4 / COUNT(*) ---------- 38959 SQL> select status,count(*) 2 from t 3 group by status 4 / S COUNT(*) - ---------- A 38959 SQL> update t set status='B' 2 where rn>=19479 3 / 19481 rows updated. SQL> select status,count(*) 2 from t 3 group by status 4 / S COUNT(*) - ---------- A 19478 B 19481 SQL> update t set status='C' where rn>=29218 2 / 9742 rows updated. SQL> select status,count(*) 2 from t 3 group by status 4 / S COUNT(*) - ---------- A 19478 B 9739 C 9742 SQL> commit 2 / Commit complete. SQL> create index t_status_ndx on t (status) 2 / Index created. SQL> select status,count(*) 2 from t 3 group by status 4 / S COUNT(*) - ---------- A 19478 B 9739 C 9742 SQL> delete plan_table 2 / 2 rows deleted. SQL> commit 2 / Commit complete. SQL> exec dbms_stats.delete_table_stats('SCOTT','T') PL/SQL procedure successfully completed. SQL> explain plan for 2 select * 3 from t 4 where status='A' 5 / Explained. SQL> select * from table(dbms_xplan.display()) 2 / PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 749696591 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 21272 | 332K| 19 (11)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 21272 | 332K| 19 (11)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- 1 - filter("STATUS"='A') Note ----- - dynamic sampling used for this statement<-- Dynamic sampling reads a small number of number of blocks in a table to make a best guess at statistics. 17 rows selected. SQL> exec dbms_stats.gather_table_stats('SCOTT','T') PL/SQL procedure successfully completed. SQL> delete plan_table 2 / 2 rows deleted. SQL> commit 2 / Commit complete. SQL> explain plan for 2 select * 3 from t 4 where status='A' 5 / Explained. SQL> select * from table(dbms_xplan.display()) 2 / PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 749696591 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19257 | 112K| 19 (11)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 19257 | 112K| 19 (11)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- 1 - filter("STATUS"='A') 13 rows selected. SQL> delete plan_table 2 / 2 rows deleted. SQL> commit 2 / Commit complete. SQL> exec dbms_stats.gather_index_stats('SCOTT','T_STATUS_NDX') PL/SQL procedure successfully completed. SQL> explain plan for 2 select * 3 from t 4 where status='A' 5 / Explained. SQL> select * from table(dbms_xplan.display()) 2 / PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 749696591 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19257 | 112K| 19 (11)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 19257 | 112K| 19 (11)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- 1 - filter("STATUS"='A') 13 rows selected. SQL> exec dbms_stats.gather_table_stats('SCOTT','T',method_opt=>'for all columns size auto') PL/SQL procedure successfully completed. SQL> delete plan_table 2 / 2 rows deleted. SQL> commit 2 / Commit complete. SQL> explain plan for 2 select * 3 from t 4 where status='A' 5 / Explained. SQL> select * from table(dbms_xplan.display()) 2 / PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 749696591 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19478 | 114K| 19 (11)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 19478 | 114K| 19 (11)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- 1 - filter("STATUS"='A') 13 rows selected. You can see 19478 is an exact row in table for status 'A' SQL> select status,count(*) 2 from t 3 group by status 4 / S COUNT(*) - ---------- A 19478 B 9739 C 9742
Thursday, January 31, 2008
How statistics affect optimizer
how retention policy affects archive log
RMAN> show retention policy 2> ; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 2; RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 no obsolete backups found RMAN> list backup 2> ; AS documentation saysBesides affecting datafile and control file backups, the retention policy affects archived logs and archived log backups.First,RMAN decides which datafile and control file backups are obsolete. Then, RMAN considers as obsolete all archived log backups that are older than the oldest datafile or control file backup that must be retainedNow i connect to SQL and create two big table for filling the redo log file and then get it archived.Please consider it here that i am getting archived before any taking backup i.e backup database in order to make archive older then the oldest datafile. C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17 Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17 03/17/2008 03:44 PM <DIR> . 03/17/2008 03:44 PM <DIR> .. 03/17/2008 03:44 PM 9,750,528 O1_MF_1_15_3XWLVK6T_.ARC 1 File(s) 9,750,528 bytes 2 Dir(s) 62,714,875,904 bytes free Now i take backup RMAN> backup database 2> ; Starting backup at 17-MAR-08 using channel ORA_DISK_1 RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 no obsolete backups found RMAN> backup database 2> ; Starting backup at 17-MAR-08 using channel ORA_DISK_1 RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Archive Log 402 17-MAR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RE COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC You can see the obsolete archived files which is the older then the oldest file which has been obsolete.It also does'nt make sense to keep the archived log files which is older then the oldest file cause it will no longer be useful for recovery process. now this time what i did i created the archived log files after first backup in order to not to make it older then the oldest datafile backup. RMAN> delete obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 using channel ORA_DISK_1 Deleting the following obsolete backups and copies: Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Archive Log 402 17-MAR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RE COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC Do you really want to delete the above objects (enter YES or NO)? yes deleted archive log archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO G\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC recid=402 stamp=649611842 Deleted 1 objects RMAN> delete backup 2> ; using channel ORA_DISK_1 RMAN> list backup 2> ; C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17 Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_ 03_17 03/17/2008 03:59 PM <DIR> . 03/17/2008 03:59 PM <DIR> .. 0 File(s) 0 bytes 2 Dir(s) 62,724,440,064 bytes free RMAN> backup database 2> ; Starting backup at 17-MAR-08 using channel ORA_DISK_1 RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 no obsolete backups found now i come to at SQL and make redo log file filled by creating 2 big tables in order to get it archived. SQL> create table a3 as select * from all_objects 2 / Table created. SQL> create table a4 as select * from all_objects 2 / Table created. C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17 Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_ 03_17 03/17/2008 04:09 PM <DIR> . 03/17/2008 04:09 PM <DIR> .. 03/17/2008 04:09 PM 9,751,552 O1_MF_1_16_3XWNCGRS_.ARC 1 File(s) 9,751,552 bytes 2 Dir(s) 62,563,205,120 bytes free RMAN> backup database 2> ; Starting backup at 17-MAR-08 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 no obsolete backups found You can see no archived logs get obsolete yet cause the archivelog O1_MF_1_16_3 XWNCGRS_.ARC is not older then oldest datafile backup. But this archivelog file will get obsolete if its beyond the retention policy,lets see how ,just take one more backup ,as i have already taken two time backup and the moment i go to take third backup it will cross ours retetnion policy from the period of 2. RMAN> backup database 2> ; RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 240 17-MAR-08 Backup Piece 231 17-MAR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RE COVERY_AREA\ORCL\BACKUPSET\2008_03_17\O1_MF_NNNDF_TAG20080317T160604_3XWN4WTB_.B KP Backup Set 241 17-MAR-08 Backup Piece 232 17-MAR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RE COVERY_AREA\ORCL\BACKUPSET\2008_03_17\O1_MF_NCSNF_TAG20080317T160604_3XWN6Z95_.B KP Archive Log 403 17-MAR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RE COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_16_3XWNCGRS_.ARC
How to clone database within same server
SQL> select instance_name from v$instance 2 / INSTANCE_NAME ---------------- orcl SQL> select * from tab 2 / TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE TEST1 TABLE TEST TABLE TEST2 TABLE 7 rows selected. SQL> create table primary_table as select * from all_objects 2 . SQL> / Table created. SQL> ================================================================== Backup production database with archivelog ================================================================== Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. RMAN> connect target sys/sys@orcl connected to target database: ORCL (DBID=1171886541) RMAN> backup database plus archivelog delete input 2> ; Starting backup at 29-JAN-08 current log archived using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=151 devtype=DISK channel ORA_DISK_1: starting compressed archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=25 recid=82 stamp=645234594 input archive log thread=1 sequence=26 recid=83 stamp=645234661 input archive log thread=1 sequence=27 recid=84 stamp=645234727 input archive log thread=1 sequence=28 recid=85 stamp=645274841 input archive log thread=1 sequence=29 recid=86 stamp=645274906 input archive log thread=1 sequence=30 recid=87 stamp=645284174 input archive log thread=1 sequence=31 recid=88 stamp=645284257 channel ORA_DISK_1: starting piece 1 at 29-JAN-08 channel ORA_DISK_1: finished piece 1 at 29-JAN-08 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_ANNNN_TAG20080129T133739_3SXSGO8V_.BKP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08 channel ORA_DISK_1: deleting archive log(s) archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_28\O1_MF_1_25_3SW8YJTC_.ARC recid=82 stamp=645234594 archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_28\O1_MF_1_26_3SW90NPY_.ARC recid=83 stamp=645234661 archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_28\O1_MF_1_27_3SW92Q8S_.ARC recid=84 stamp=645234727 archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_28_3SXJ857Y_.ARC recid=85 stamp=645274841 archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_29_3SXJB85Y_.ARC recid=86 stamp=645274906 archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_30_3SXSCX8F_.ARC recid=87 stamp=645284174 archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_31_3SXSGKNL_.ARC recid=88 stamp=645284257 Finished backup at 29-JAN-08 Starting backup at 29-JAN-08 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF input datafile fno=00005 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF channel ORA_DISK_1: starting piece 1 at 29-JAN-08 channel ORA_DISK_1: finished piece 1 at 29-JAN-08 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_NNNDF_TAG20080129T133749_3SXSGY3H_.BKP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting compressed full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current controlfile in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 29-JAN-08 channel ORA_DISK_1: finished piece 1 at 29-JAN-08 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_NCSNF_TAG20080129T133749_3SXSJPO0_.BKP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09 Finished backup at 29-JAN-08 Starting backup at 29-JAN-08 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=32 recid=89 stamp=645284335 channel ORA_DISK_1: starting piece 1 at 29-JAN-08 channel ORA_DISK_1: finished piece 1 at 29-JAN-08 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_ANNNN_TAG20080129T133855_3SXSK0L2_.BKP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 channel ORA_DISK_1: deleting archive log(s) archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_32_3SXSJZ1Y_.ARC recid=89 stamp=645284335 Finished backup at 29-JAN-08 Create duplicate database hirarchy with DBCA as same file structure with production database hirarchy =================================== Add Listener in listener.ora file =================================== # listener.ora Network Configuration File: C:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL) (ORACLE_HOME = %ORACLE_HOME%) (SID_NAME = ORCL) ) (SID_DESC = (GLOBAL_DBNAME = orcl1) (ORACLE_HOME = %oracle_home%) (SID_NAME = ORCL1) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pz-khurrams)(PORT = 1521)) ) ========================================================= Add tnames string in tnsnames.ora for duplicate database ========================================================= ORCL1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pz-khurrams)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1) ) ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pz-khurrams)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ============================================================================================================= Edit the pfile of duplicate database ORCL1 and make some changes and after all changes just save not save as ============================================================================================================= DB_FILE_NAME_CONVERT=("C:\oracle\product\10.1.0\oradata\orcl\","C:\oracle\product\10.1.0\oradata\orcl1\orcl1") LOG_FILE_NAME_CONVERT=("c:\oracle\product\10.1.0\oradata\orcl","C:\oracle\product\10.1.0\oradata\orcl1\orcl1") ============================================================================================================= Connect to duplicate database ORCL1 and create spfile from edited pfile ============================================================================================================= SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> create spfile from pfile='C:\oracle\product\10.1.0\admin\orcl1\pfile\init.ora.0292008145243' 2 / File created. SQL> startup nomount ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes SQL> select instance_name from v$instance 2 / INSTANCE_NAME ---------------- orcl1 ============================================================================================================= Connect to target production database ORCL and mount the database ============================================================================================================= SQL> conn sys/sys@orcl as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. SQL> select instance_name from v$instance 2 / INSTANCE_NAME ---------------- orcl ============================================================================================================= Go to dos prompt ============================================================================================================= C:\>set oracle_sid=orcl C:\>rman target / auxiliary sys/sys@orcl1 Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. connected to target database: ORCL (DBID=1171886541) connected to auxiliary database: orcl1 (not mounted) RMAN> run 2> { 3> allocate auxiliary channel aux1 device type disk; 4> duplicate target database to 'orcl1'; 5> } using target database controlfile instead of recovery catalog allocated channel: aux1 channel aux1: sid=160 devtype=DISK Starting Duplicate Db at 29-JAN-08 contents of Memory Script: { set until scn 1104818; set newname for datafile 1 to "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF"; set newname for datafile 2 to "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF"; set newname for datafile 3 to "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF"; set newname for datafile 4 to "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF"; set newname for datafile 5 to "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF"; restore check readonly 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 29-JAN-08 channel aux1: starting datafile backupset restore channel aux1: specifying datafile(s) to restore from backup set restoring datafile 00001 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF restoring datafile 00002 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF restoring datafile 00003 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF restoring datafile 00004 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF restoring datafile 00005 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF channel aux1: restored backup piece 1 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_NNNDF_TAG20080129T133749_3SXSGY3H_.BKP tag=TAG20080129T133749 channel aux1: restore complete Finished restore at 29-JAN-08 sql statement: CREATE CONTROLFILE REUSE SET DATABASE "orcl1" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 454 LOGFILE GROUP 1 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO01.LOG' ) SIZE 10 M REUSE, GROUP 2 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO02.LOG' ) SIZE 10 M REUSE, GROUP 3 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO03.LOG' ) SIZE 10 M REUSE DATAFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF' CHARACTER SET WE8MSWIN1252 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 2 switched to datafile copy input datafilecopy recid=1 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF datafile 3 switched to datafile copy input datafilecopy recid=2 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF datafile 4 switched to datafile copy input datafilecopy recid=3 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF datafile 5 switched to datafile copy input datafilecopy recid=4 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF contents of Memory Script: { set until scn 1104818; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 29-JAN-08 starting media recovery channel aux1: starting archive log restore to default destination channel aux1: restoring archive log archive log thread=1 sequence=32 channel aux1: restored backup piece 1 piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_ANNNN_TAG20080129T133855_3SXSK0L2_.BKP tag=TAG20080129T133855 channel aux1: restore complete archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2008_01_29\O1_MF_1_32_3SXZVRPT_.ARC thread=1 sequence=32 channel clone_default: deleting archive log(s) archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2008_01_29\O1_MF_1_32_3SXZVRPT_.ARC recid=1 stamp=645290824 media recovery complete Finished recover at 29-JAN-08 contents of Memory Script: { shutdown clone; 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 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "orcl1" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 454 LOGFILE GROUP 1 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO01.LOG' ) SIZE 10 M REUSE, GROUP 2 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO02.LOG' ) SIZE 10 M REUSE, GROUP 3 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO03.LOG' ) SIZE 10 M REUSE DATAFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF' CHARACTER SET WE8MSWIN1252 contents of Memory Script: { catalog clone datafilecopy "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF"; catalog clone datafilecopy "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF"; catalog clone datafilecopy "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF"; catalog clone datafilecopy "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF"; switch clone datafile all; } executing Memory Script cataloged datafile copy datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF recid=1 stamp=645290915 cataloged datafile copy datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF recid=2 stamp=645290916 cataloged datafile copy datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF recid=3 stamp=645290916 cataloged datafile copy datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF recid=4 stamp=645290916 datafile 2 switched to datafile copy input datafilecopy recid=1 stamp=645290915 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF datafile 3 switched to datafile copy input datafilecopy recid=2 stamp=645290916 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF datafile 4 switched to datafile copy input datafilecopy recid=3 stamp=645290916 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF datafile 5 switched to datafile copy input datafilecopy recid=4 stamp=645290916 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 29-JAN-08 RMAN> SQL> conn scott/tiger@orcl1 Connected. SQL> select * from tab 2 / TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE TEST1 TABLE TEST TABLE TEST2 TABLE PRIMARY_TABLE TABLE 8 rows selected. SQL>
Subscribe to:
Posts (Atom)
