Category Archives: Oracle

What is Swap?

Linux divides its physical RAM (random access memory) into chucks of memory called pages. Swapping is the process whereby a page of memory is copied to the preconfigured space on the hard disk, called swap space, to free up that page of memory. The combined sizes of the physical memory and the swap space is the amount of virtual memory available.

Swapping is necessary for two important reasons. First, when the system requires more memory than is physically available, the kernel swaps out less used pages and gives memory to the current application (process) that needs the memory immediately. Second, a significant number of the pages used by an application during its startup phase may only be used for initialization and then never used again. The system can swap out those pages and free the memory for other applications or even for the disk cache.

However, swapping does have a downside. Compared to memory, disks are very slow. Memory speeds can be measured in nanoseconds, while disks are measured in milliseconds, so accessing the disk can be tens of thousands times slower than accessing physical memory. The more swapping that occurs, the slower your system will be. Sometimes excessive swapping or thrashing occurs where a page is swapped out and then very soon swapped in and then swapped out again and so on. In such situations the system is struggling to find free memory and keep applications running at the same time. In this case only adding more RAM will help.

There is an old thumb rule for sizing swap, i.e: Swap space should be twice in the size of the RAM. From above you can understand that more swap will not solve your performance problem as SWAP space is based on your DISK’s rather than fast memory RAM, hence modern systems are no more using old thumb rule of twice the size. Rather sizing swap as per the use.

How much swap space is currently used by the system?

Free command displays the swap space. free -h shows the output according to the size.

[root@psserver ~]# free -h
       total   used  free  shared  buff/cache   available
Mem:     51G   579M   50G    8.3M        140M         50G
Swap:     9G     0B    9G

Swapon command with option -s, displays the current swap space in KB.

[root@psserver ~]# swapon -s
Filename            Type            Size    Used    Priority
/root/myswapfile    file    10485756        0       -1
/dev/sda2           partition       1020    0       -2

You can also verify the above details from /proc/swaps

[root@psserver ~]# cat /proc/swaps 
Filename            Type            Size    Used    Priority
/root/myswapfile    file            10485756        0       -1
/dev/sda2           partition       1020    0       -2

In the following post I will show two options which can be used to create/increase swap space on the server.

Option – A: Using Hard Drive Partition

If you have an additional hard disk, (or space available in an existing disk), create a partition using fdisk command. Let us assume that this partition is called /dev/sda2

Now setup this newly created partition as swap area using the mkswap command as shown below.

[root@psserver ~]# mkswap /dev/sda2
mkswap: /dev/sda2: warning: wiping old swap signature.
Setting up swapspace version 1, size = 1020 KiB
no label, UUID=cb5b3c11-5a63-4c57-9963-f5f45270c885

Enable the swap partition for usage using swapon command as shown below.

[root@psserver ~]# swapon /dev/sda2

To make this swap space partition available even after the reboot, add the following line to the /etc/fstab file.

[root@psserver ~]# cat /etc/fstab|grep swap
/dev/sda2        swap            swap    defaults        0 0

Verify whether the newly created swap area is available for your use.

[root@psserver ~]# swapon -s
Filename           Type            Size    Used    Priority
/dev/sda2          partition       1020    0       -1
/dev/sdb1          partition       10200    0       -2
[root@psserver ~]# free -h
       total  used free shared  buff/cache   available
Mem:     51G  613M  50G   8.3M        152M         50G
Swap:    11G    0B  11G

Note: In the output of swapon -s command, the Type column will say “partition” if the swap space is created from a disk partition.

Option – B: Use a File for Additional Swap Space

If you don’t have any additional disks, you can create a file somewhere on your filesystem, and use that file for swap space.

The following dd command example creates a swap file with the name “swapfile” under /root directory with a size of 10240MB (10GB).

[root@psserver ~]# dd if=/dev/zero of=/root/swapfile bs=1M count=10240
10240+0 records in
10240+0 records out
10737418240 bytes (11 GB) copied, 8.2161 s, 1.3 GB/s
[root@psserver ~]# ls -ltr /root/swapfile 
-rw-r--r--. 1 root root 10737418240 Oct 11 03:08 /root/swapfile

Change the permission of the swap file so that only root can access it.

[root@psserver ~]# chmod 600 /root/swapfile

Make this file as a swap file using mkswap command.

[root@psserver ~]# mkswap /root/swapfile
Setting up swapspace version 1, size = 10485756 KiB
no label, UUID=9b22a18e-59ab-45f0-a94c-6d60e3f19b1a

Enable the newly created swapfile.

[root@psserver ~]# swapon /root/swapfile

To make this swap file available as a swap area even after the reboot, add the following line to the /etc/fstab file.

[root@psserver ~]#  cat /etc/fstab|grep swap
/root/myswapfile        swap            swap    defaults        0 0

Verify whether the newly created swap area is available for your use.

[root@psserver ~]# swapon -s
Filename            Type            Size    Used    Priority
/root/swapfile    file    10485756        0       -1
[root@psserver ~]# free -h
       total  used free shared  buff/cache   available
Mem:     51G  613M  50G   8.3M        152M         50G
Swap:    10G    0B  10G

Note: In the output of swapon -s command, the Type column will say “file” if the swap space is created from a swap file.

If you don’t want to reboot to verify whether the system takes all the swap space mentioned in the /etc/fstab, you can do the following, which will disable and enable all the swap partition mentioned in the /etc/fstab

Manual Creation of SQL Profile

SQL Profiling is required when there are multiple execution plan available in the database and there is a need to use or better performance in one of the execution plan. Now, it is not guaranteed that CBO will always use the same plan hence there is a need of SQL Profiling. In SQL Profiling we lock the execution plan which we and CBO feels the optimal performance can be achieved.

For Test, I will be taking following query from PeopleSoft Application.

SELECT DISTINCT BUSINESS_UNIT,
VOUCHER_ID,
INVOICE_ID,
GROSS_AMT,
PAY_AMT,
INVOICE_DT,
TO_CHAR (INVOICE_DT, ‘YYYY-MM-DD’),
VENDOR_NAME_SHORT,
VENDOR_ID,
NAME1,
VOUCHER_STYLE,
VOUCHER_ID_RELATED,
ENTRY_STATUS_SRH,
VCHR_SRC
FROM PS_VOUCHER_SRCH_VW
WHERE BUSINESS_UNIT = ‘COMP1’ AND VOUCHER_ID LIKE ‘123456%’
ORDER BY BUSINESS_UNIT, VOUCHER_ID DESC ;

Lets Begin:

First, We have to create tuning task.

For Tuning task we need to have the sql id for the sql and then create tuning task, to get sqlid we can use following:

select * from v$sql where sql_text like “SELECT DISTINCT BUSINESS_UNIT,
VOUCHER_ID,
INVOICE_ID,
GROSS_AMT,
PAY_AMT,
INVOICE_DT,%”

Once, we have the sqlid we can create tuning task as follow:

declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => ‘7u4t0k75bavcp’);
DBMS_OUTPUT.put_line(‘task_id: ‘ || stmt_task );
end;
/

This will give a task ID, in my case it was “TASK_87241”

Next, We execute the task as follow:

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘TASK_87241’);
end;
/

Once task is executed, we have the recommendation from CBO. Which can be queried as follow:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name => ‘TASK_87241’) from dual;

 

If you are satisfied with the sql profile recommended by the CBO then you can accept it as follow:

DECLARE
l_sql_tune_task_id  VARCHAR2(200);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => ‘TASK_87204’,
name      => ‘TEST_PROFILE’,
force_match  => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
END;
/

If you are not satisfied with the SQL profile then try changing the session parameter and try running the above steps again, optimizer should provide different results.

 

Leave a comment if you found anything which can be changed or not up to the mark. There are multiple ways to do this, this is just one way. In other blog I will be taking through the process of locking down the SQL profile in one database and then copy the profile to other database.

 

Thanks.

Upgrade Oracle Database

These steps are for Upgrading Oracle Document, I have used 11.2.0.4 as target and 11.1.0.7 as source. You might have different versions while upgrading but the step remains same if the path you are following is certified from Oracle.

Always good to check the Oracle Support for certified path.

Task A: Installing new Oracle Software on UNIX box.

1.    Go to Binary location

  1. Will find the 11gR2 release under: “/oracle_cds/staging/64bit/11_2_release”
  2. Installing 11.2.0.4  [This is just an example, in your case database version may vary]:
    “/oracle_cds/staging/64bit/Oracle11.2.0.4/database”

2.    Start X server at your Windows Workstation

  1. Set Display environmental variable
    1. “export DISPLAY=<your_IP>.0:0

3.    Installation Wizard – 30 min

  1. Start the “runInstaller” from -20 min
    “/oracle_cds/ibm/64bit/11_2_release/Oracle11.2.0.4/database” as oracle user.
  2. Execute root.sh script as a root user – 2 min

 

Task B : Upgrading the Database from 11gR1 to 11.2.0.4

Task B-1: Pre-upgrade steps

1.    Collecting Pre-upgrade report

  • Copy the “utlu112i.sql” from new ORACLE_HOME/rdbms/admin to a temp location
    “/opt/oracle/product/11.2.0/db4/rdbms/admin”

2.    Executing the pre-upgrade tool
time – 10 mins

  • Change the location to that temporary directory
  • Start the sqlplus and login as sysdba into the target database (old version)
  • Execute the script which was copied at temporary location with spooling enabled
  • Read the spool log to get the further steps.

3.    Analyze the log for recommended changes.

 

Task B-2: Preparing for the Upgrade

1.    Check the integrity of database — used for lower versions [Prior to 11gR1]

  • Download “DBUPGDIAG.SQL” from Oracle support
  • Execute the script to check the integrity issues
  • Ask for the log location, give the temporary directory used above as the temporary log directory, all logs will be generated under that directory.

2.    Optimizer Statistics

Gather stats on SYS Schema

4.    Disable Oracle Database Vault

  • SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;
  • Follow Oracle Support Document 453903.1
  • Link

5.    Backing up Enterprise Manager Database Control Data – Skip

  • Initiate the oraenv for the old version of oracle
  • Change the current directory to new ORACLE_HOME
    • $ emdwgrd -save -sid old_SID -path save_directory
  • where old_SID is the SID of the database being upgraded and save_directory is the path to the storage place you have chosen for your Database Control files and data.

7.    Ensure the users SYS and SYSTEM have ‘SYSTEM’ as their default tablespace.

  • Check using:
  • SELECT username, default_tablespace
    FROM dba_users
    WHERE username in (‘SYS’,’SYSTEM’);
  • Alter by this code:
    SQL> ALTER user SYS default tablespace SYSTEM;
    SQL> ALTER user SYSTEM default tablespace SYSTEM;

8.    Note down the location of datafiles, redo logs and control files

  • SQL> SELECT name FROM v$controlfile;
    SQL> SELECT file_name FROM dba_data_files;
    SQL> SELECT group#, member FROM v$logfile;.
  • Take a backup of all files

9.    Stop the listener

  • lsnrctl stop testdb_lsnr
  • initiate the netca from previous version of ORACLE_HOME
  • delete the existing listener using netca
  • stop the dbconsole also

10. Shutdown the Database

11. Take a cold backup/rman backup

  • backup database format ‘/oracle/tempdb/andy/%u’ tag ‘before_upgrade’
  • BACKUP CURRENT CONTROLFILE TO ‘/oracle/tempdb/andy’;
  • Backup the whole db and pfile/spfile/control file

 

 

12. Modify the init parameters

  • 11gR1 parameters are similar to 11gR2
  • We done need to modify the parameters in our scenario but may vary on your database, best thing is to refer pre-upgrade report generated earlier

13. Set Environment Variables at OS

  • Set Oracle_HOME to new HOME [ 11gR2 home]
  • Set Oracle_BASE
  • Set Oracle_SID
  • Update the oratab entried according to the database which you are upgrading
  • Copy the .profile from user home to a new file and edit the oracle home value

 

Task B-3: Upgrading steps

 

  • Go to $ORACLE_HOME/rdbms/admin
  • Start the database in upgrade mode
  • Run the following: – 40 min
    1. SQL> set echo on
      SQL> SPOOL upgrade.log
      SQL> @catupgrd.sql
      SQL> spool off

 

 

TASK B-4: Post Upgrade Steps

 

  • Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql
  • Run utlrp.sql to compile all system objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

  • select * from v$version;
  • Create the listener entry in the listener.ora
  • Start the listener
  • Set the listener parameter in the database
  • Gather the statistics on the database
    1. Not gathering statistics can cause performance issues.

 

 

ISSUES

  1. RMAN Catalog

Your rman catalog might be at the lower version, whereas rman catalog have to be at upper version of the database. Hence, upgrade your catalog version.