How to Export and Import Statistics.

How to Export and Import Statistics. 


A critical application suddenly seems to hang, wait events show long table scans running on the OLTP environment.
It comes out that the DBA in charge of this system did run statistics on the tables of the user that owns the application.
The gather statistics got stuck and the process was killed. Since this moment the application started to perform extremely slowly.


The production database has several clones; we decide to export back statistics from one of these clones, to the production database.


This document will descripe the steps to perform the export and import statistics. 


1. Create the stat table 


SQL> exec DBMS_STATS.CREATE_STAT_TABLE('SCOTT','OLD_STATS');


PL/SQL procedure successfully completed.


SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON SCOTT.OLD_STATS TO PUBLIC;


Grant succeeded.


SQL>


SQL> select 'exec dbms_stats.export_table_stats('||chr(39)||owner||chr(39)||','||chr(39)||table_name||chr(39)||',nu
  2  ||chr(39)||',null,true,'||chr(39)||'SCOTT'||chr(39)||')'
  3  from dba_tables where owner ='&usrname'
  4
SQL> /
Enter value for usrname: SCOTT
old   3: from dba_tables where owner ='&usrname'
new   3: from dba_tables where owner ='SCOTT'
exec dbms_stats.export_table_stats('SCOTT','DEPT',null,'DEPT',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','EMP',null,'EMP',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','BONUS',null,'BONUS',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','SALGRADE',null,'SALGRADE',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','OLD_STATS',null,'OLD_STATS',null,true,'INV')


SQL>
SQL> select num_rows,last_analyzed from dba_tables where owner='SCOTT';


  NUM_ROWS LAST_ANAL
---------- ---------
         4 25-DEC-10
        14 25-DEC-10
         0 25-DEC-10
         5 25-DEC-10




SQL> exec DBMS_STATS.EXPORT_SCHEMA_STATS('&username','&stattablename');
Enter value for username: SCOTT
Enter value for stattablename: OLD_STATS


PL/SQL procedure successfully completed.


SQL> COMMIT;


Commit complete.


SQL>


SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.


E:\>exp system/sys file=soctt_stat.dmp tables=scott.old_stats


Export: Release 10.2.0.1.0 - Production on Sat Dec 25 14:58:20 2010


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




Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Product
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set


About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table                      OLD_STATS         24 rows exported
Export terminated successfully without warnings.


E:\>




I'm deleting statistics by using the below method.


SQL> exec DBMS_STATS.delete_SCHEMA_STATS('&username');
Enter value for username: SCOTT


PL/SQL procedure successfully completed.


SQL> commit;


Commit complete.


SQL>






SQL> seLEct stale_stats ,num_rows from dba_tab_statistics where owner='SCOTT' and stale_stats is not null;


no rows selected




Here, Im not importing existing statistics by using export dump. Im trying to generate the new stats


SQL> exec dbms_stats.gather_schema_stats  -
>     (ownname=> 'SCOTT', -
>       cascade=>TRUE,   -
>       degree => 4, -
>     estimate_percent=>dbms_stats.auto_sample_size,-
>       options =>'GATHER');


PL/SQL procedure successfully completed.


SQL> commit;


Commit complete.


SQL> seLEct stale_stats ,num_rows from dba_tab_statistics where owner='SCOTT' and stale_stats is not null;
NO           4
NO          14
NO           0
NO           5
NO          24


SQL>


Thanks

ORA-01157: cannot identify/lock data file 37 - see DBWR trace file

Greeting!!! One of our RAC environment getting the below error

ORA-01157: cannot identify/lock data file 37 - see DBWR trace file
ORA-01110: data file 37: '+D02/datafile/01_data.306

This error means only one instance mounted to ASM others are DISMOUNTED. Even when we try to mount the ASM Instance getting the above message.

All the RAC services up but still getting same error .

Work Around:

ALTER SYSTEM CHECK DATAFILES.

After execute this command the one of cluster sync process released from hung stage. We’re able to connect the instance.

Welcome your comments and suggestion, Thanks

Daylight Saving Time Problem

Daylight Saving Time Problem

Interesting topic for daylight saving time problem in oracle database. Many of DBA's thinking for daylight saving time changes database timestamp changes require. 

Just wants to share you all. Daylight saving time is related to application setup not database related. If your using > oracle 9i no changes require from database level. 

As  per my understanding there might be some EMC tool available for daylight saving time changes for application or operating system. 

Refer: http://www.orafaq.com/node/1395

Thanks, Babu

Step by Step to create physical standby database using RMAN

Dear blog readers and friends, In my busy schedule do not have time to write oracle tech..,  This week and next week planing to write something in oracle hints query's , data guard switch over etc.. 

Today I have done physical standby database using recovery manager and sharing my experience with you., Please let me know your feedback / comments

Step by Step to create physical standby database using RMAN

RMAN will take care the following procedure for creating standby database

a. Standby controlfile restore

b. Primary data files backup restore 

c. After restore standby controlfile, Rman recover the standby database by specify time or latest archive log generated. 

c. Finally, RMAN leave the standby database once it's mounted. If needed manually we can start MRP process. 

According to oracle documentation, RMAN cannot full automate for create standby database, there are some manual process require. 

Manual Process:

1. Starting database with out mount stage. 

2. Configuring INIT parameter in primary & standby database.  ( In windows env., manually need to create password file)

3. And we must have backup of database including current standby controlfile.

1. How to Configure NET Service (INIT) parameter in primary & standby database. 

Refer : http://babudba.blogspot.com/2008/03/data-guard.html

2. Once completed net service configuration in primary database, copy same init parameter for standby database & needed some necessary changes like FAL_SERVER, FAL_CLIENT, LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST2

3. In standby database manually create password file & start the database with out mount stage

4. As i said, In production need to take complete backup with current standby controlfile. I used the below command for taking complete backup. 

Execute the below script in Target database:-

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

backup incremental level 0 format 'e:\DBbackup\i001_%T_%u_s%s_p%p' database;

backup format 'e:\DBbackup\i001_arch_%T_%u_s%s_p%p' archivelog all;

backup current controlfile for standby format'e:\DBbackup\i001_CF_%T_%u_s%s_p%p' ; 

}

released channel: ORA_DISK_1

....

...

..

5. Once completed your full backup, EXIT your session, We need to connect AUXILIARY by using TARGET as well Catalog / NoCatalog

Ex:

C:\>rman target / nocatalog auxiliary sys/sys@dg_i001

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Aug 23 02:56:06 2010

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

connected to target database: I001 (DBID=2720927431)

using target database control file instead of recovery catalog

connected to auxiliary database: I001 (DBID=2720927431, not open)

Script for Standby Database creation by using Duplicate RMAN::-

run

{

allocate auxiliary channel c1 device type disk;

allocate auxiliary channel c2 device type disk;

duplicate target database for standby

nofilenamecheck;

#dorecover;

}

Duplicate Standby RMAN Logs::-

RMAN> run

2> {

3> allocate auxiliary channel c1 device type disk;

4> allocate auxiliary channel c2 device type disk;

5> duplicate target database for standby

6> nofilenamecheck;

7> #dorecover;

8> }

allocated channel: c1

channel c1: sid=155 devtype=DISK

allocated channel: c2

channel c2: sid=158 devtype=DISK

Starting Duplicate Db at 23-AUG-10

contents of Memory Script:

{

   restore clone standby controlfile;

   sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 23-AUG-10

channel c1: starting datafile backupset restore

channel c1: restoring control file

channel c1: reading from backup piece E:\DBBACKUP\I001_CF_20100823_0ULM19RE_S30_

P1

channel c1: restored backup piece 1

piece handle=E:\DBBACKUP\I001_CF_20100823_0ULM19RE_S30_P1 tag=TAG20100823T020325

channel c1: restore complete, elapsed time: 00:00:03

output filename=E:\ORADATA\I001\CONTROL01.CTL

output filename=E:\ORADATA\I001\CONTROL02.CTL

output filename=E:\ORADATA\I001\CONTROL03.CTL

Finished restore at 23-AUG-10

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 "E:\ORADATA\I001\TEMP01.DBF";

   switch clone tempfile all;

   set newname for datafile  1 to

 "E:\ORADATA\I001\SYSTEM01.DBF";

   set newname for datafile  2 to

 "E:\ORADATA\I001\UNDOTBS01.DBF";

   set newname for datafile  3 to

 "E:\ORADATA\I001\SYSAUX01.DBF";

   set newname for datafile  4 to

 "E:\ORADATA\I001\USERS01.DBF";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to E:\ORADATA\I001\TEMP01.DBF in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 23-AUG-10

channel c1: starting datafile backupset restore

channel c1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to E:\ORADATA\I001\UNDOTBS01.DBF

restoring datafile 00003 to E:\ORADATA\I001\SYSAUX01.DBF

channel c1: reading from backup piece E:\DBBACKUP\I001_20100823_0OLM199H_S24_P1

channel c2: starting datafile backupset restore

channel c2: specifying datafile(s) to restore from backup set

restoring datafile 00001 to E:\ORADATA\I001\SYSTEM01.DBF

restoring datafile 00004 to E:\ORADATA\I001\USERS01.DBF

channel c2: reading from backup piece E:\DBBACKUP\I001_20100823_0NLM199G_S23_P1

channel c1: restored backup piece 1

piece handle=E:\DBBACKUP\I001_20100823_0OLM199H_S24_P1 tag=TAG20100823T015352

channel c1: restore complete, elapsed time: 00:01:05

channel c2: restored backup piece 1

piece handle=E:\DBBACKUP\I001_20100823_0NLM199G_S23_P1 tag=TAG20100823T015352

channel c2: restore complete, elapsed time: 00:01:40

Finished restore at 23-AUG-10

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=6 stamp=727710709 filename=E:\ORADATA\I001\SYSTEM01.DB

F

datafile 2 switched to datafile copy

input datafile copy recid=7 stamp=727710709 filename=E:\ORADATA\I001\UNDOTBS01.D

BF

datafile 3 switched to datafile copy

input datafile copy recid=8 stamp=727710709 filename=E:\ORADATA\I001\SYSAUX01.DB

F

datafile 4 switched to datafile copy

input datafile copy recid=9 stamp=727710710 filename=E:\ORADATA\I001\USERS01.DBF

Finished Duplicate Db at 23-AUG-10

released channel: c1

released channel: c2

RMAN>

Successfully completed standby database creating by using RMAN & both database are in Sync.,

Sync Checking:

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 23 03:00:21 2010

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            D:\ArchiveLog\i001

Oldest online log sequence     21

Next log sequence to archive   23

Current log sequence           23

SQL>

SQL> exit

C:\>sqlplus sys/sys@dg_i001 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 23 03:00:40 2010

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            E:\ArchiveLog\i001

Oldest online log sequence     21

Next log sequence to archive   23

Current log sequence           23

SQL>

Thanks., 

Modifying the VIP in 10g Oracle Cluster Node

Hello,
I got one request to change public, private & vip address in my two nodes oracle cluster. I was thinking this may possible or it will create any other issues. 


In test instance just tried to change VIP IP address in one nodes it's working fine. Now i am looking for change public & private IP address change using Oracle Interface Configuration Tool (oifcfg) & I will post this later when i got free time. 


I would like to share the below changes with you; If you have any query or suggestion you can reach me any time.  Thanks ...


How to change VIP - IP address in Oracle Cluster Node


There are few steps to change VIP IP Address. 


1. Check current configuration
E:\oracle\product\10.2.0\crs\BIN>srvctl config nodeapps -h

Usage: srvctl config nodeapps -n [-a] [-g] [-o] [-s] [-l]

    -n           Node name

    -a                  Display VIP configuration

    -g                  Display GSD configuration

    -s                  Display ONS daemon configuration

    -l                  Display listener configuration

    -h                  Print usage

E:\oracle\product\10.2.0\crs\BIN>srvctl config nodeapps -n babu-node1 -a

VIP exists.: /babu-node1-vip/192.168.200.34/255.255.255.0/public

2. Stop your asm, db instance and other services on node 1

E:\oracle\product\10.2.0\crs\BIN>srvctl stop asm -n babu-node1


E:\oracle\product\10.2.0\crs\BIN>srvctl stop instance -d devdb -i devdb1


E:\oracle\product\10.2.0\crs\BIN>srvctl stop nodeapps -n babu-node1


E:\oracle\product\10.2.0\crs\BIN>crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....SM1.asm application    OFFLINE   OFFLINE
ora....E1.lsnr application    OFFLINE   OFFLINE
ora....de1.gsd application    OFFLINE   OFFLINE
ora....de1.ons application    OFFLINE   OFFLINE
ora....de1.vip application    OFFLINE   OFFLINE
ora....SM2.asm application    ONLINE    ONLINE    babu-node2
ora....E2.lsnr application    ONLINE    ONLINE    babu-node2
ora....de2.gsd application    ONLINE    ONLINE    babu-node2
ora....de2.ons application    ONLINE    ONLINE    babu-node2
ora....de2.vip application    ONLINE    ONLINE    babu-node2
ora.devdb.db   application    ONLINE    ONLINE    babu-node2
ora....b1.inst application    OFFLINE   OFFLINE
ora....b2.inst application    ONLINE    ONLINE    babu-node2


3. Modify your new VIP - IP Address or VIP Host Name in /etc/hosts or C:\WINDOWS\system32\drivers\etc\hosts file ( you should update your new ip on both nodes )


4. Now modify new ip address on node1

E:\oracle\product\10.2.0\crs\BIN>srvctl config nodeapps -n babu-node1 -a

VIP exists.: /babu-node1-vip/192.168.200.34/255.255.255.0/public

E:\oracle\product\10.2.0\crs\BIN>srvctl modify nodeapps -n babu-node1 -A 192.168.200.36/255.255.255.0/"public"


E:\oracle\product\10.2.0\crs\BIN>srvctl config nodeapps -n babu-node1 -a
VIP exists.: /babu-node1-vip/192.168.200.36/255.255.255.0/public


5. Double check VIP modified or not using the above command. If everything fine then start all services & check your new VIP - IP address. 


E:\oracle\product\10.2.0\crs\BIN>srvctl start nodeapps -n babu-node1


E:\oracle\product\10.2.0\crs\BIN>srvctl start asm -n babu-node1


E:\oracle\product\10.2.0\crs\BIN>srvctl start instance -d devdb -i devdb1


E:\oracle\product\10.2.0\crs\BIN>ipconfig


Windows IP Configuration




Ethernet adapter public:


   Connection-specific DNS Suffix  . :
   IP Address. . . . . . . . . . . . : 192.168.200.36
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   IP Address. . . . . . . . . . . . : 192.168.200.33
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . : 192.168.200.1


Ethernet adapter private:


   Connection-specific DNS Suffix  . :
   IP Address. . . . . . . . . . . . : 10.1.1.1
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . :


E:\oracle\product\10.2.0\crs\BIN>crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    babu-node1
ora....E1.lsnr application    ONLINE    ONLINE    babu-node1
ora....de1.gsd application    ONLINE    ONLINE    babu-node1
ora....de1.ons application    ONLINE    ONLINE    babu-node1
ora....de1.vip application    ONLINE    ONLINE    babu-node1
ora....SM2.asm application    ONLINE    ONLINE    babu-node2
ora....E2.lsnr application    ONLINE    ONLINE    babu-node2
ora....de2.gsd application    ONLINE    ONLINE    babu-node2
ora....de2.ons application    ONLINE    ONLINE    babu-node2
ora....de2.vip application    ONLINE    ONLINE    babu-node2
ora.devdb.db   application    ONLINE    ONLINE    babu-node2
ora....b1.inst application    ONLINE    ONLINE    babu-node1
ora....b2.inst application    ONLINE    ONLINE    babu-node2