Mar 26 2009

Command Line Scripts for Database Replay

Category: 11g,Database — ittichai @ 10:47 am

One of new exciting features of Oracle 11g is the Real Application Testing (RAT). The RAT has two solutions – Database Replay and SQL Performance Analyzer (SPA) to address two different issues. Both have the same concept (capture then replay), but are scoped differently. Database Replay applies at database-level workload for all activities (exclusion is possible), but SPA is more granular at a specific SQL statement or its set.

Oracle extends the capture capability to the earlier versions. Note that the replay capability can only be done on Oracle 11g or higher. The minimum requirement to make 9i and 10g capable of capturing is stated in the Metalink note 560977.1 – Real Application Testing Now Available for Earlier Releases.

In our case, we would like to use the Database Replay and we do have Oracle 9.2.0.8 on Solaris which is the minimum required database version for capture, so the only patch needed is one-off patch number 6973309 (for non-Windows system).

Note that after patch, you may need to run catwrr.sql to create needed tables, views and package for workload capture.

Oracle provides sample of the command line interface scripts for Database Replay. Search Metalink for note 742645.1 – Database Replay: Command Line Interface (CLI) usage examples/scripts. I find it very useful because it is categorized based on execution tasks in order. This collection of scripts gives more flexibility and control especially when you want to automate tasks or where there is no Enterprise Manager interface for database replay (in 9.2.0.8/10.2.0.2 and 10.2.0.3).

Seven scripts provided in the db_replay_cli.zip file are self-explanatory and customizable based on your environments.

The first two scripts are executed on the capture system (in this case is 9i), and the rest on the replay system (11g).

1_start_capture.sql – Set of commands to create capture directory, create capture filters and start workload capture

2_finish_capture.sql – Set of commands to stop workload capture and export AWR

3_prepare_replay.sql – Set of commands to initialize replay, re-map connections and install replay parameters

4_start_replay_client.sql – Set of OS commands to calibrate and start replay clients

5_start_replay.sql – Command to start replay

6_reports.sql – Set of commands to import AWR, input arguments/data for capture/replay/ASH/AWR/Compare Period reports and generate these reports. In this script you can find text of PL/SQL procedure, which help you create reports easily with minimal input.

x_cancel_replay.sql – Command to cancel wokload replay in progress.

Tags: , ,

Dec 19 2008

11g OCP upgrade exam

Category: 11g,OCP — ittichai @ 3:38 am

I just passed the 11g OCP upgrade after many excuses to postpone it.

I have to admit I was nervous a little bit because the last Oracle exam I’ve taken was more than one year ago. I was struggling a little bit at first. I tend to read too quickly (= careless). So at the beginning I missed couple phrases in question especially those saying please select two (or more) answers. I just answered one and then clicked Next. Note that system won’t prompt you if you answer less than what it is looking for, but it will prompt if your answer more. :-(   After about 30 minutes, I realized something was not right. Fortunately I still had just enough time to go back and fix it. Whew!

Tags: ,

Dec 07 2008

A new extended partition syntax in 11g

Category: 11g,Partition — ittichai @ 5:30 am

A new extended partition syntax can be used to designate a partition without knowing its name. The syntax must refer to a possible value for the partition. This syntax works for all cases when you have to reference a partition, whether it be range, list, interval, or hash. It supports all operations such as drop, merge, split, and so on.

Some samples are shown below -



create table SALES (
  id              number,
  order_date      date
)
partition by range (order_date)
(
  partition p1 values less than
     (to_date('01/01/2008','mm/dd/yyyy')),
  partition p2 values less than
     (to_date('02/01/2008','mm/dd/yyyy')),
  partition p3 values less than
     (to_date('03/01/2008','mm/dd/yyyy'))
);


Generally when you want to merge partitions, the syntax will have to refer to the partition names.




alter table SALES merge partitions p2, p3
into partition p2_3;


However, in 11g, the same can be accomplished by referring to a possible value for the partition with use of “for” syntax.




alter table SALES merge partitions
   for(to_date('01/12/2008','mm/dd/yyyy')),
   for(to_date('02/15/2008','mm/dd/yyyy'))
into partition p2_3;




SQL> select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'SALES';

TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ------------------------------
SALES      P1         TO_DATE(' 2008-01-01 00:00:00'
                      , 'SYYYY-MM-DD HH24:MI:SS', 'N
                      LS_CALENDAR=GREGORIA

SALES      P2_3       TO_DATE(' 2008-03-01 00:00:00'
                      , 'SYYYY-MM-DD HH24:MI:SS', 'N
                      LS_CALENDAR=GREGORIA


Or with dropping a partition -




SQL> alter table SALES drop partition
for(to_date('02/15/2008','mm/dd/yyyy'));

Table altered.




SQL> select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'SALES';

TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ------------------------------
SALES      P1         TO_DATE(' 2008-01-01 00:00:00'
                      , 'SYYYY-MM-DD HH24:MI:SS', 'N
                      LS_CALENDAR=GREGORIA



		    
Tags:  , 

			

	








	  

    

         Mar
         16
         2008
    

    

	 
11g SQL*Plus

	
Category: SQLPlus — ittichai @ 2:08 am 


	

		


In 11g SQL*Plus, when session is terminated, somehow it displays the process ID, SID and Serial#. Not quite sure what is really for? I guess it is for debugging purpose.


10g Client


user @db10w1> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on


11g Client


user @db10w1> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 135 Serial number: 29158





		    
Tags:  , 

			

	








	  

    

         Nov
         14
         2007
    

    

	 
Oracle APEX in 11g Installation

	
Category: 11g,APEX,SQL Developer — ittichai @ 8:55 pm 


	

		


Today I installed Oracle 11g (11.1.0.6) on my machine. I did not realize that Oracle APEX is a part of the standard database components.




So after the 11g installation, I just follow simple steps (shown later below) for the post-installation. In order to access the APEX application, either the embedded PL/SQL gateway or Oracle HTTP server with mod_plsql is needed. For simplicity, I’ve decided to go with the former. By using the embedded PL/SQL gateway, it will run using the Oracle XML DB HTTP server which is already in Oracle database, so there is no need to install a separate HTTP server. The Oracle’s document here explains about this as well as provides the detailed information on the post-installation.


To configure the embedded PL/SQL gateway:


1. Go to the $ORACLE_HOME/apex directory.


2. Use SQL/Plus to connect as SYS to 11g database where APEX is installed.


SYS AS SYSDBA@db11r1> @apxconf

PORT
----------8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.

Enter a password for the ADMIN user              []admin_password
Enter a port for the XDB HTTP listener [      8080]
...changing HTTP Port

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.


3. Unlock the ANONYMOUS account.


SYS AS SYSDBA@db11r1> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.


4. Enable Oracle XML DB HTTP server


SYS AS SYSDBA@db11r1> EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SYS AS SYSDBA@db11r1> COMMIT;

Commit complete.


5. We’re now ready to access APEX.


http://host:port/apex


http://host:port/apex/apex_admin    — for admin page


Port in this case is 8080 which is the default.




Note that the format of URL is a little bit different from when using HTTP server with mod_plsql -


http://host:port/pls/apex


http://host:port/pls/apex/apex_admin    — for admin page


Also the SQL Developer 1.1.3 is included under “sqldeveloper” directory of ORACLE HOME. So just double-click at sqldeveloper.exe to launch application.








		    
Tags:  , 

			

	







« Previous Page