New Blog About Oracle on Our Community

by David Ramishvili 15. November 2010 13:18

ახალი ბლოგი ორაკლის მონაცემთა ბაზის ადმინისტრატორებისთვის!

აი კიდევ ერთი სასიამოვნო სიახლე ჩვენს კომუნიტიში. ჩვენს ბლოგებს შეემატა ახალი ბლოგი http://ocp.community.ge/ (Oracle Certified Professional OCP, Super DBA BLOG!)

ბლოგის იდეა ეკუთვნის ვაჟა მანტუას, რომელიც არის ორაკლის სერთიფიცირებული პროფესიონალი და აქვს დიდი გამოცდილება მონაცემთა ბაზების ადმინისტრირებაში, იგი მოღვაწეობს საქართველოში ერთ-ერთ ლიდერ ბანკში.

ვაჟას მინდა დიდი მახდლობა მოვუხადო, რომ აქვს სურვილი გაგვიზიარებს თავის დიდ გამოცდილება და ხელი შეუწყოს საქართველოში თემატური IT ბლოგების განვითარებას.

 

ხოლო მათთვის ვისაც აქვს სურვილი რომ გაუზიაროს თავის გამოცდილება პუბლიკას, კიდევ ერთხელ შეგახსენებთ რა არის საჭირო იმისთვის რომ გქონდეთ თქვენი საკუთარი ბლოგი ჩვენს რესურსზე:

1. მოიფიქრე თემა, თუ რის დაპოსტვას ისურვებ ბლოგზე (აუცილებელია თემა ეხებოდეს IT სფეროს)

2. მოითხოვე! (ანუ გამოაგზავე წერილი mcp-club@hts.ge -ზე თემით: Blog Idea)

3. MCP-Club Tbilisi და Hight-Tech Solutions განიხილავს თქვენს მოთხოვნას და შეგატყობინებთ პასუხს :)

Tags: , , , , ,

APEX 4.0 Installation Guide

by Vazha Mantua 12. November 2010 15:14

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle APEX is a fully supported, no cost option of the Oracle database

Let’s start!

1.Oracle Application Express Installation Requirements

1.1 Oracle Database Requirement
Oracle Application Express version 4.0 requires an Oracle database (Enterprise Edition, Standard Edition or Standard Edition One) that is release 10.2.0.3 or higher. Application Express 4.0 can also be used with Oracle Database 10g Express.

1.1.1 Checking the shared_pool_size of the Target Database

Note:Ignore this requirement if your configuration uses non-null values for the database initialization parameters SGA_TARGET (in Oracle Database 10g and 11g) or MEMORY_TARGET (in Oracle Database 11g).
Oracle Application Express requires the shared_pool_size of the target database to be at least 100 MB.
To check the shared_pool_size of the target database:
1. Start the database:
SQL> STARTUP
2. If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;
3. Determine the current values of the shared_pool_size parameter:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
4. If the system is using a server parameter file, set the value of the SHARED_POOL_SIZE initialization parameter to at least 100 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=spfile;
5. Shut down the database:
SQL> SHUTDOWN
6. Restart the database:
SQL> STARTUP

1.2 Browser Requirements

To view or develop Oracle Application Express applications, Web browsers must support Java Script and the HTML 4.0 and CSS 1.0 standards. The following browsers are required to develop applications in Oracle Application Express:
· Microsoft Internet Explorer 7.0 or later version
· Mozilla Firefox 3.5 or later version
· Google Chrome 4.0 or later version
· Apple Safari 4.0 or later version
Application Express applications can be developed that support earlier Web browser versions, including Microsoft Explorer 6.0.

1.3 Disk Space Requirement

Oracle Application Express disk space requirements are as follows:
· Free space for Oracle Application Express software files on the file system: 450 MB if using English only download (apex_4_0_en.zip) and 1 GB if using full download (apex_4_0.zip).
· Free space in Oracle Application Express tablespace: 185 MB
· Free space in SYSTEM tablespace: 100 MB
· Free space in Oracle Application Express tablespace for each additional language (other than English) installed: 75 MB

1.4 Oracle XML DB Requirement

Oracle XML DB must be installed in the Oracle database that you want to use. If you are using a preconfigured database created either during an installation or by Database Configuration Assistant (DBCA), Oracle XML DB is already installed and configured.
See Also: Oracle XML DB Developer's Guide for more information about manually adding Oracle XML DB to an existing database
Tip: The installer does a prerequisite check for Oracle XML DB and will exit if it is not installed.
Tip: The installation of Oracle XML DB creates the user ANONYMOUS. In order for Oracle Application Express workspace provisioning to work properly, the ANONYMOUS user must not be dropped from the database.

1.5 PL/SQL Web Toolkit

Oracle Application Express requires the PL/SQL Web Toolkit version 10.1.2.0.6 or later. For instructions on determining the current version of the PL/SQL Web Toolkit, and for instructions on installing version 10.1.2.0.6, please review the README.txt file contained in the directory apex/owa.

2. install Oracle Application Express

2.1 Recommended Pre-installation Tasks

Before installing Oracle Application Express, Oracle recommends that you complete the following steps:

1. Shut down with normal or immediate priority the Oracle Database instances where you plan to install Oracle Application Express. On Oracle Real Application Clusters (Oracle RAC) systems, shut down all instances on each node.

2. Back up the Oracle Database installation. Oracle recommends that you create a backup of the current Oracle Database installation before you install Oracle Application Express. You can use Oracle Database Recovery Manager, which is included the Oracle Database installation, to perform the backup.
3. Start the Oracle Database instance that contains the target database. After backing up the system, you must start the Oracle instance that contains the target Oracle database. Do not start other processes such as the listener or Oracle HTTP Server. However, if you are performing a remote installation, make sure the database listener for the remote database has started.
2.2 Download and Install Oracle Application Express
To install Oracle Application Express:
1. For installations where the development will be in English only, download the file apex_4.0_en.zip from the Oracle Application Express download page. Where the development will include languages other than English, download apex_4.0.zip from the Oracle Application Express download page. See:
http://www.oracle.com/technology/products/database/application_express/download.html
Note that the actual file name may differ if a more recent release has shipped since this document was published.
2. Unzip downloaded zip file:
· UNIX and Linux: Unzip apex_4.0.zip
· Windows: Double click the file apex_4.0.zip in Windows Explorer
3. Change your working directory to apex.
4. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
And run the command:
     SQL>@apexins SYSAUX SYSAUX TEMP /i/
When Oracle Application Express installs it creates three new database accounts:
· APEX_040000 - The account that owns the Oracle Application Express schema and metadata.
· FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
· APEX_PUBLIC_USER - The minimally privileged account used for Oracle Application Express configuration with Oracle HTTP Server and mod_plsql or Oracle Application Express Listener.
2.3 Change the Password for the ADMIN Account
To change the password for the ADMIN account:
1. Change your working directory to the apex directory where you unzipped the installation software.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
3. Run apxchpwd.sql. For example:
SQL>@apxchpwd
When prompted enter a password for the ADMIN account.
2.4 Restart Processes
After you install Oracle Application Express, you must restart the processes that you stopped before you began the installation, such as listener and other processes.
2.5 Configure the Embedded PL/SQL Gateway
2.5 .1 Running the apex_epg_config.sql Configuration Script
The embedded PL/SQL gateway installs with the Oracle Database 11g. However, you must configure it before you can use it with Oracle Application Express. To accomplish this, you run a configuration file and unlock the ANONYMOUS account.
To run the apex_epg_config.sql configuration script:
1. Change your working directory to the apex directory where you unzipped the Oracle Application Express software.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
3. Run apex_epg_config.sql passing the file system path to the base directory where the Oracle Application Express software was unzipped as shown in the following example:
On Windows:
@apex_epg_config SYSTEM_DRIVE:\TEMP
On UNIX and Linux:
@apex_epg_config /tmp
4. Enter the following statement to unlock the ANONYMOUS account:
ALTER USER ANONYMOUS ACCOUNT UNLOCK;
2.5 .2 Verifying the Oracle XML DB HTTP Server Port
The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database. You can determine if the Oracle XML DB HTTP server is enabled by verifying the associated port number.
To verify the port number where the Oracle XML DB HTTP Server is running:
1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
2. Enter the following statement to verify the port number:
SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
If the port number returns 0, the Oracle XML DB HTTP Server is disabled.
3. To enable it, follow the instructions in 2.5 .3 Enabling Oracle XML DB HTTP Server.
2.5 .3 Enabling Oracle XML DB HTTP Server
The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database.
To enable Oracle XML DB HTTP server:
1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
2. Enter a statement similar to the following:
EXEC DBMS_XDB.SETHTTPPORT(port);
For example:
EXEC DBMS_XDB.SETHTTPPORT(8080);
2.6 Enable Network Services in Oracle Database 11g
By default, the ability to interact with network services is disabled in Oracle Database 11g release 1 or 2. Therefore, if you are running Oracle Application Express with Oracle Database 11g release 1 or 2, you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_040000 database user.
2. 6.1 Granting Connect Privileges
The following example demonstrates how to grant connect privileges to any host for the APEX_040000 database user. This example assumes you connected to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role.
DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_040000
  -- the "connect" privilege if APEX_040000 does not have the privilege yet.
 
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
 
  -- Before checking the privilege, ensure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;
 
  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_040000', TRUE, 'connect'); 
  END IF;
 
EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040000', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
The following example demonstrates how to provide less privileged access to local network resources. This example would enable indexing the Oracle Application Express Online Help and could possibly enable email and PDF printing if those servers were also on the local host.
DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to 'localhost' and give APEX_040000
  -- the "connect" privilege if APEX_040000 does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
 
  -- Before checking the privilege, ensure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
 
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;
 
  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_040000', TRUE, 'connect'); 
  END IF;
 
EXCEPTION
  -- When no ACL has been assigned to 'localhost'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040000', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;
2.7 About Managing JOB_QUEUE_PROCESSES

JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs. In Oracle Application Express release 4.0, transactional support and SQL scripts require jobs.
If JOB_QUEUE_PROCESSES is not enabled and working properly, you cannot successfully execute a script.
2.7.1 Viewing JOB_QUEUE_PROCESSES from SQL*Plus

SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
(We had 128)
2.7.2 Changing the Number of JOB_QUEUE_PROCESSES
You can change the number of JOB_QUEUE_PROCESSES by running a SQL statement in SQL*Plus:
To update the number of JOB_QUEUE_PROCESSES:
1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
2. In SQL*Plus run the following SQL statement:
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = <number>
For example, running the statement ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20 sets JOB_QUEUE_PROCESSES to 20.
2.8 Configuring the SHARED_SERVERS Parameter
The embedded PL/SQL gateway uses the shared server architecture of the Oracle Database. To achieve acceptable performance when using the embedded PL/SQL gateway, ensure the SHARED_SERVERS database initialization parameter is set to a reasonable value (that is, not 0 or 1). For a small group of concurrent users, Oracle recommends a value of 5 for SHARED_SERVERS.
Consider the following example:
1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
On UNIX and Linux:
$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
2. Run the following statement:
ALTER SYSTEM SET SHARED_SERVERS = 5 SCOPE=BOTH;
 
Note: If database is not started by spfile shutdown and start it by spfile.
Go to the link to check if it works
 
http://hostname:port/apex/apex_admin

Tags: ,

Huge Space Consumption by $ORACLE_HOME/.patch_storage

by Vazha Mantua 8. November 2010 12:21

Hello All ,

When we applied Patchset 10.2.0.4 and some critical oracle patches, we got space problem on server.

We found large directories in solaris with command :du -ko /u0|sort -n | tail -10.

$ORACLE_HOME/.patch_storage was largest directory in oracle_home.

In .patch_storage is keeping your system up to date with Patchsets, Patch Bundles, Merge Label Requests (MLR) or Critical Patch Updates (CPU) for restore old versions.

for clearing this directory we can use MetaLink Note 550522.1
or simple: opatch util Cleanup

Tags:

The Real Application testing, Database Replay process

by Vazha Mantua 8. November 2010 12:20

The Real Application testing, Database Replay process

The Real Application testing feature, which consists of two separate tools, Database Replay and the SQL performance Analyzer, is arguably the most significant new feature in the Oracle Database 11.1 Release. With this process we can simulate a real production workload on a test system; we can apply some patches, change data storage and see how our performance changes on test system.
In this post we step by step review Database replay process

1. Restart prod DB and startup restrict mode (this in optional)
2. We can use optional workload filters to restrict the workload capture to only a part of the actual production workload:
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER(FNAME => 'FILTER_NAME',FATTRIBUTE => 'USER',FVALUE => 'SCOTT');

END;
We can find filter’s in view DBA_WORKLOAD_FILTERS;
3. Start Workload capture, means start process which collect workload to the some files, which then will apply on test DB:
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(NAME => '2010OCT',DIR => 'DATA_PUMP_DIR',DURATION => 1200);
END;

Duration is number of seconds for which workload will be captured this is optional parameter.
DIR is oracle directory which must be empty!
We can manually stop capture process with this statement:
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();
END;

We can see status of process in view DBA_WORKLOAD_CAPTURES
4. Preprocess capture data means that capture data will procedure the metadata for captured workload and transform the captured workload datafiles into reply streams called reply files that you can now replay on the test system:
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(CAPTURE_DIR => 'DATA_PUMP_DIR');
END;

5. Now we must create test DB which should have same data structure as prod DB, we can use rman scripts(backup, restore), snapshot standby DB, Data pump utilities or simple cold backup for creating test DB
6. Check that oracle directory in test DB link on directory where replay files are located, in this scenario we create directory TEST_DIR
7. Initializing the replay Data means initialize the data, which loads the metadata into tables required by workload process:
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(REPLAY_NAME => 'TEST_REPLAY',REPLAY_DIR => 'TEST_DIR');
END;

8. Then we mush remap connections with statement:
BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(CONNECTION_ID => 111);
END;

9. Prepare the Workload for Replay with statement :
BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();
END;

There we run procedure with default parameters, for example we can use parameter synchronization, by default true, meaning that the commit order of captured workload will be preserved during the workload replay
10. Set up Reply Clients. The reply driver is a special application that consumes the captured workload by sending replay requests to the test DB. The reply client in essence simulates the production system on the test DB. To Find optimal number of clients we must run this statement on test server in terminal window up to directory where is test_dir
wrc system/a mode=calibrate replay_dir=./test_dir
if we should run with 2 or more clients we must open new terminals and run process in other windows.
Run client with this statement:
wrc system/a mode=replay replaydir=./test_dir
11. Now we can start replay process with statement:
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY();
END:

You must start minimum of one wrc client before you can start the workload replay.
You can manually stop Replay process by statement, this is optional, because replay process will finished itself:
BEGIN
DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY();
END;

Status and progress of replay process you can see in view : DBA_WORKLOAD_REPLAYS
12. Report and result of replay process you can see with this statement :



DECLARE
CAP_ID NUMBER;
REP_ID NUMBER;
REP_RPT CLOB;
BUFFER VARCHAR2(32767);
BUFFER_SIZE CONSTANT BINARY_INTEGER := 32767;
AMOUNT BINARY_INTEGER;
OFFSET NUMBER(38);
FILE_HANDLE UTL_FILE.FILE_TYPE;
DIRECTORY_NAME CONSTANT VARCHAR2(80) := 'TEST_DIR';
V_FILENAME CONSTANT VARCHAR2(80) := 'REPLAY.HTML';
BEGIN
CAP_ID := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(DIR => 'TEST_DIR');
SELECT MAX(ID)
INTO REP_ID
FROM DBA_WORKLOAD_REPLAYS
WHERE CAPTURE_ID = CAP_ID ;
REP_RPT := DBMS_WORKLOAD_REPLAY.REPORT(REPLAY_ID => REP_ID,
FORMAT => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
-- WRITE REPORT TO FILE
DBMS_OUTPUT.ENABLE(100000);
-- --------------------------------
-- OPEN NEW XML FILE IN WRITE MODE
-- --------------------------------
FILE_HANDLE := UTL_FILE.FOPEN(LOCATION => 'TEST_DIR',
FILENAME => V_FILENAME,
OPEN_MODE => 'W',
MAX_LINESIZE => BUFFER_SIZE);
AMOUNT := BUFFER_SIZE;
OFFSET := 1;
WHILE AMOUNT >= BUFFER_SIZE
LOOP
DBMS_LOB.READ(LOB_LOC => REP_RPT,
AMOUNT => AMOUNT,
OFFSET => OFFSET,
BUFFER => BUFFER);
OFFSET := OFFSET + AMOUNT;
UTL_FILE.PUT(FILE => FILE_HANDLE,
BUFFER => BUFFER);
UTL_FILE.FFLUSH(FILE => FILE_HANDLE);
END LOOP;
UTL_FILE.FCLOSE(FILE => FILE_HANDLE);
END;







Tags: , ,

increase session_cached_cursors

by Vazha Mantua 8. November 2010 12:16

Hello all,

We have performance problem on prod DB, and Oracle ADDM advice us increase size of session_cached_cursors parameter.

We decide to find reason of problem.

Here you can find statement which you should run where problem appears.

create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(3000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);



now we can see result on statement



select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2 desc
/

First Results of selects is reason of performance problem.

In these selects please use bind variables, for decrease parsing sql statment

Tags:

Gather schema statistic exluding some tables

by Vazha Mantua 8. November 2010 12:12

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

* Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.
* Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.

If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired. The mechanisms and issues relating to maintenance of internal statistics are explained below:


Using dbms_stat procedure:

dbms_stat procedure gather statistics for DB object, after gathering them, oracle build right sql_plans and optimize SQL statement.

Example gathering statistic for schema, this statement built SQL text.

select 'execute dbms_stats.gather_table_stats (ownname=>''' || upper(owner)
|| ''', tabname=> ''' || upper(table_name) || ''', degree=>10, cascade=> true);' from DBA_TABLES
where upper(owner) in ('SCOTT') and table_name not in
('TABLE1','TABLE2') order by owner, table_name;

note:
if you have very large tables you can exclude it for quickly gathering statistics

ownname means schema name
degree means that 10 oracle process will start gather information
cascade means that statistics gather for all indexes on tables

Tags:

Installing Oracle Management Agent 11g with response file

by Vaja Mantua 8. November 2010 12:11
Hello Again,

As you know, we installed Oracle EM Grid control 11g in Linux server 2 day ago, now we are trying install management agent in server where is our DB's

First of all download install packages from:
http://www.oracle.com/technology/software/products/oem/htdocs/agentsoft.html

Our example is for :
Agent Software for 64-bit Platforms,Linux x86-64

1. copy zip files in server

2. unzip Linux_x86_64_Grid_Control_agent_download_11_1_0_1_0.zip file(for example /0 folder)

3. make changes on file additional_agent.rsp located linux_x64/response sub folder.

We should set some init parameters

SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
DECLINE_SECURITY_UPDATES=TRUE
ORACLE_AGENT_HOME_LOCATION=/u1/app/oracle/agent11g(you can change as you wish)
OMS_HOST=OEMserver (host where is installed EM grid 11g)
OMS_PORT=4889
AGENT_REGISTRATION_PASSWORD=****** (Agent registration password, if you set it during Oracle EM installation )

4. execute runinstaller with specified parameters
./runInstaller -silent -responseFile /0/linux_x64/response/additional_agent.rsp


If you have some question please contact me by e-mail: vmantua@gmail.com

Tags: , ,

Install Grid Control 11g on linux CentOS(32 bit)

by Vaja Mantua 8. November 2010 11:56

Hi All,
I'm writing manual of installation grid control 11g in Linux (32 bit ). Installation of this product not like early versions of grid control, cause of this is that grid control instead of oracle application server is use oracle web logic server now.


Let's Start!

First of all we should install oracle DBMS and create database for repository OEM.

A. Installation DBMS

1. Install rpm’s

Login on server with user root
 
1.1 run and install rpm’s with command rpm -Uvh

libaio-devel-0.3.106-3.2.i386.rpm

unixODBC-2.2.11-7.1.i386.rpm

unixODBC-devel-2.2.11-7.1.i386.rpm

unixODBC-devel-2.2.11-7.1.x86_64.rpm

1.2 delete old version of rmp ksh

rpm -e ksh-20060214-1.7.i386

1.3 Install new version of ksh: rpm –Uvh pdksh-5.2.14-36.el5.i386.rpm

2. Configuring Linux for Oracle

mkdir -p /u0/app/oracle

groupadd oinstall
groupadd dba
useradd –s /bin/bash –d /u0/app/oracle –g oinstall –G dba oracle

passwd oracle
chown -R oracle:oinstall /u0/app
chmod -R 777 /u0/app 
 

change /etc/sysctl.conf, add lines:
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586



Reboot system

3. Install Oracle software

3.1 Download from http://www.oracle.com/technology/software/products/database/index.html

Installation packages

Oracle Database 11g Release 2 (11.2.0.1.0) for Linux x86

<!--[if !supportLists]--> <!--[endif]-->Linux_11gR2_database_1of2.zip (1,285,396,902 bytes) (cksum - 2237015228)

<!--[endif]-->Linux_11gR2_database_2of2.zip (995,359,177 bytes) (cksum - 2649514514)

3.2 login on server with user oracle , unzip zip files and run runinstaller

B. Install Oracle Web Logic Server And Grid Control


1. <!--[endif]-->Install Oracle RDMS 11g
Please see part A


2.
<!--[endif]-->Install Oracle Web Logic server


2.1
<!--[endif]-->download installation package http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html Oracle WebLogic Server 11gR1 (10.3.2) - Package Installer

2.2 Copy wls1032_linux32.bin file in server and run command chmod a+x *.bin

2.3 <!--[endif]-->
Run ./ wls1032_linux32.bin for installation web logic server

3. Create DB , configure listener.ora file and tnsnames.ora file for connecting DB(You can use dbca utility)

4.Change Oracle instance parameters and increase size of UNDO TBS: 
  

ALTER system SET session_cached_cursors = 200 scope=spfile;
ALTER system SET log_buffer = 10485760 scope=spfile;
ALTER system SET processes = 500 scope=spfile;
ALTER system SET open_cursors = 300 scope=spfile;
   Restart DB and run command :
   ALTER DATABASE DATAFILE ‘/u1/oradata/OEMREP/undotbs01.dbf’ RESIZE 250M;



5. Download Grig control soft from
       

http://www.oracle.com/technology/software/products/oem/htdocs/linuxsoft.html
      
<!--[endif]-->GridControl_11.1.0.1.0_Linux_1of3.zip (1,346,827,162 bytes) (cksum - 4036510904)   
<!--[if !supportLists]-->           <!--[endif]--><!--[endif]-->GridControl_11.1.0.1.0_Linux_2of3.zip (1,504,676,900 bytes) (cksum - 2185370223)   
<!--[if !supportLists]-->       <!--[endif]-->GridControl_11.1.0.1.0_Linux_3of3.zip (1,336,577,399 bytes) (cksum - 4010010869)
  
Copy these files in server unzip and run runinstaller

6. After install

Navigate to the Oracle home of the OMS and run the following command to see a message that confirms that OMS is up and running.

$<OMS_HOME>/bin/emctl status oms

Go to the Oracle home of the Management Agent and run the following command to see a message that confirms that the Management Agent is up and running.

$<AGENT_HOME>/bin/emctl status agent

Run the following command to see a message that confirms that EMD upload completed successfully.

[$<AGENT_HOME>/bin/emctl upload


6.1. Adding ports to firewall rules.

  • Don’t forget to open ports on your firewall:

Default Ports to be opened:

Enterprise Manager Upload Http Port

4889

Enterprise Manager Upload Http SSL Port

4900

Enterprise Manager Central Console Http SSL Port

7799

Enterprise Manager Central Console Http Port

7202

Enterprise Manager Central Console Http Port

7788

Node Manager Http SSL Port

7403

Managed Server Http SSL Port

7301

Oracle Management Agent Port

3872

Admin Server Http SSL Port

7101


Login to EM Grid Control console (sysman/password)

SSL – standard approach:
https://hostname.local:7799/em

It’s also possible to avoid SSL connection. To do that, first you must unlock http access:

[oracle@hostname bin]$ ./emctl secure unlock -console
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :******
OMS Console is unlocked. HTTP ports too can be used to access console.
http://hostname.local:7202/em/console/home


Login without SSL (default port 7202):
http:// hostname.local:7202/em

7. Start/stop the environment: On OMS Server

  • to start Oracle Enterprise Manager Grid Control services:

$<OMS_HOME>/bin/ emctl start oms –all

  • to stop Oracle Enterprise Manager Grid Control services:

$<OMS_HOME>/bin/ emctl stop oms –all



Tags:

Veritas Netbackup problem for Oracle 11gR2

by Vaja Mantua 8. November 2010 11:52

 

Problem:

After migration DB in Oracle 11g, we found out problem with backuping DB over Veritas Netbackup.
In Alert log we saw message likes this :
ORA-07445: exception encountered: core dump [strcpy()+16] [SIGSEGV] [ADDR:0x12][PC:0x3E16079190] [Address not mapped to object] []

On oracle metalink we found that:

The latest version of Netbackup is not compatible with 11gR2
we have client version 6.5.2 which was not supported for 11gR2
also you can read it in this article :
Document ID: 337527
RMAN-10038 error when attempting to backup Oracle version 11.2 using NetBackup 6.x.
URL: http://support.veritas.com/docs/337527


Solution:

We download Veritas Netbackup client patch 6.5.5 , for Linux Red hat. you need to download this patches: (You should extract these patches in a same location for avoiding install problem)

NB_CLT_6.5.5_337786.tar
NB_6.5.5.linuxR_x86_337780.tar
NB_JAV_6.5.5_337809.tar

Also you should download this patch:

NB_6.5.5_ET1940073_1_346033.zip(Bug fixing patch for oracle , netbackup version 6.5.5)
install instruction for this bug : http://www.symantec.com/business/support/index?page=content&id=TECH64620
After applying this patch problem resolved

Tags:

library cache pin

by Vaja Mantua 8. November 2010 11:50
When you try compile package on Oracle DB 10g , compile process may be hang, reason of this is any session which using this package, on this scenario you should see wait event like that library cache pin.
The library cache pin Oracle metric takes place if the process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. The library cache pin wait usually happens when you are compiling or parsing a PL/SQL object or a view.

use this select to see which session lock your compile process. run it with sys user!

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'

Tags: , ,