shivenracdba

IO calibrate statistics are missing  in explain plan, then below mentioned steps should be executed one by one :- set these parameters 1. alter system set filesystemio_options=asynch scope=spfile 2. alter system set disk_asynch_io = true scope=spfile; 3. bounce the db 4. Ensure asynchronous I/O is enabled on all datafiles and tempfiles. The following query can be used to            verify asynchronous I/O for these files. col name format a50 select name,asynch_io from v$datafile f,v$iostat_file i where f.file#=i.file_no and (filetype_name='Data File' or filetype_name='Temp File'); / 5. run the calibration procedure :- SET SERVEROUTPUT ON DECLARE   l_max_iops        PLS_INTEGER;   l_max_mbps        PLS_INTEGER;   l_actual_latency  PLS_INTEGER; BEGIN   DBMS_RESOURCE_MANAGER.calibrate_io (     num_physical_disks => 1,     max_latency        => 20,     max_iops           => l_max_iops,     max_mbps           => l_max_mbps,     a

First of all start your database in mount state . Here I am changing the database name from orcl to orcl4. Then perform below steps :- [oracle@host01 dbs]$ sqlplus sys/sys as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 7 12:09:36 2014 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> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@host01 dbs]$ nid target=sys/sys DBNAME=orcl4 DBNEWID: Release 11.2.0.1.0 - Production on Fri Mar 7 12:14:08 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Connected to database ORCL (DBID=130579539

 The following thing can be potential problem for big size table move . 1. No transaction should access those tables which you are going to move otherwise there will be ORA error on application side .So better to use dbms_redifnation in this case.( dbms_redifnation is available only EE edition) . 2. Time estimation can be go wrong .It may be due to different storage type used ( some times in some scenario the moving table reside on attached hard disk level file system and target reside on NAS or SAN ). I have seen that even 100GB of table takes time around 4 hours although target tablesapce and source tablespace were on different mount point but on same storage ( it was SAN ) . 3. Always use DBverify command to check block level corruption on target datafile before moving large tables .Otherwise it stuck in the middle of operation and some times it gets failed . 4.you can not use index rebuild on BITMAP index on partition table , if it is invalid state , you have to drop and r

1. Log on the target server and find out database is in archive log or not :- Export ORACLE_SID=<database_name> Sqlplus / as sysdba SQL> archive log list; Database log mode              No Archive Mode Automatic archival             Disabled 2. Create respective directories for backup in the name of database on the backup location and grant permission on it . mkdir –P /backup/$ORACLE_SID/flash_recovery_area chmod –R 777 /backup/$ORACLE_SID/flash_recovery_area chown –R oracle:oinstall /backup/$ORACLE_SID/flash_recovery_area 3. Configure database in archive log, recovery area ,recovery location  :- SQL>create pfile from spfile; (take backup of pfile) SQL> select * from v$recovery_file; (Here none of the datafile should be there) SQL> show parameter recovery_file_dest; SQL> alter system set log_archive_dest_1='LOCATION=/backup/$ORACLE_SID/flash_recovery_area' scope = both; SQL> alter system set db_recovery_file_dest_size=(db