Wednesday, 12 March 2014

IO calibrate statistics are missing in oracle explain plan

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,
    actual_latency     => l_actual_latency);
   DBMS_OUTPUT.put_line ('l_max_iops       = ' || l_max_iops);
  DBMS_OUTPUT.put_line ('l_max_mbps       = ' || l_max_mbps);
  DBMS_OUTPUT.put_line ('l_actual_latency = ' || l_actual_latency);
END;

Note :- Here num_physical_disks should be confirmed from system admin and This should be set to the                     defined response time SLA for your application;
e.g., your 95th percentile response time SLA is 10secs.

6. then find the status of the calibration runs:-  select * from v$io_calibration_status;


   

Thursday, 6 March 2014

change oracle database name using nid

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=1305795399)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/orcl/control01.ctl
    /u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database ID and database name ORCL to ORCL4? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1305795399 to 766881041
Changing database name from ORCL to ORCL4
    Control File /u01/app/oracle/oradata/orcl/control01.ctl - modified
    Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/orcl/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/example01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/tracetbs.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/tracetbs3.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/temp01.db - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/orcl/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to ORCL4.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL4 changed to 766881041.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

[oracle@host01 dbs]$ ls -slhrt
total 36K
4.0K -rw-r--r-- 1 oracle oinstall 2.8K May 15  2009 init.ora
4.0K drwx------ 2 oracle oinstall 4.0K Mar 15  2012 peshm_DBUA0_0
4.0K -rw-rw---- 1 oracle oinstall 1.6K Mar 15  2012 hc_DBUA0.dat
4.0K drwx------ 2 oracle oinstall 4.0K Mar 15  2012 peshm_orcl_0
4.0K -rw-rw---- 1 oracle oinstall 1.6K Mar 15  2012 hc_orcl.dat
4.0K -rw-r----- 1 oracle oinstall   24 Mar 15  2012 lkORCL
4.0K -rw-r----- 1 oracle oinstall 2.0K Mar  2 17:17 orapworcl
4.0K -rw-r----- 1 oracle oinstall 2.5K Mar  7 12:05 spfileorcl.ora
4.0K -rw-r----- 1 oracle oinstall  947 Mar  7 12:06 initorcl.ora
[oracle@host01 dbs]$ rm -f orapworcl
[oracle@host01 dbs]$ cp initorcl.ora initorcl4.ora
[oracle@host01 dbs]$ rm -f spfileorcl.ora
[oracle@host01 dbs]$ vi initorcl4.ora ( Here change the dbname in pfile)
[oracle@host01 dbs]$ orapwd file=orapworcl4 entries=4 password=sys(create new password file)
[oracle@host01 dbs]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 7 12:16:28 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount pfile=initorcl4.ora
ORACLE instance started.

Total System Global Area 1062965248 bytes
Fixed Size                  1341252 bytes
Variable Size             683673788 bytes
Database Buffers          373293056 bytes
Redo Buffers                4657152 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

Alternatively , You can change the database name using alter database backup controlfile to trace command .For this step ,I will post again . 

Sunday, 2 March 2014

Things to consider before moving table

 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 rebuild .

5. You can not move those table which LONG Rows .

6. Always take expdp of those tables which you going to move, so in the middle of operation , this activity failed , you can rolled back .

7. Always enable row movement for the table which is going to be move.

8. Move the table.

9. Disable row movement for the table .

configuring archive and backup

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_size+archivelog+20%);

SQL>shutdown immediate;

SQL> startup mount;

SQL>alter database archivelog;

SQL> alter database open;

check , your database is in archive log mode.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled

4.     Configure auto backup through crontab.