Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Monday, July 26, 2010

Find Oracle Binaries PATH and Oracle bit version

If the below output shows 64-bit then the installed oracle bit version is 64 else it will say 32-bit for 32-bit install

Host>file $ORACLE_HOME/bin/oracl*
/adm02/u0001/oracle/product/10.1.4/apps_1/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped


what if I don't know where oracle is installed
1)
>ps -aef| grep tns
this is will you the path from where listener is running
ex:
>ps -aef| grep tns
oracle 7932 1 0 Oct 30 ? 11795:52 /adm02/u0001/oracle/product/10.2.0/db_1/bin/tnslsnr adm1p -inherit

so my ORACLE_HOME is /adm02/u0001/oracle/product/10.2.0/db_1

2) SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';

ex:
SQL> SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';
2
ORACLE_HOME
--------------------------------------------------------------------------------
/adm02/u0001/oracle/product/10.2.0/db_1

Wednesday, July 21, 2010

RMAN Full DISK BACKUP

labrman01(RCAT) /ora_backup/datafile
>rman

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jul 21 10:38:33 2010

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

RMAN> connect target sys/syspwd@HYPP

connected to target database: HYPP (DBID=2563884143)

RMAN> connect catalog rcat10g/rcat10g@RCAT

connected to recovery catalog database

RMAN> run{
allocate channel ch01 type disk format '/ora_backup/backups/HYPP/HYPP_Full_%u_%s_%U';
backup database include current controlfile;
sql 'alter system archive log current';
backup archivelog all delete input;
}

released channel: ORA_DISK_1
allocated channel: ch01
channel ch01: sid=1079 devtype=DISK

Starting backup at 21-JUL-10
channel ch01: starting full datafile backupset
channel ch01: specifying datafile(s) in backupset
input datafile fno=00006 name=/adm02/u0001/oracle/datafile/HYPP/hypp_data_01.dbf
input datafile fno=00002 name=/adm02/u0001/oracle/datafile/HYPP/undo_01.dbf
input datafile fno=00008 name=/adm02/u0001/oracle/datafile/HYPP/hypp_bi_01.dbf
input datafile fno=00001 name=/adm02/u0001/oracle/datafile/HYPP/system01.dbf
input datafile fno=00003 name=/adm02/u0001/oracle/datafile/HYPP/sysaux01.dbf
input datafile fno=00009 name=/adm02/u0001/oracle/datafile/HYPP/hypp_apps_01.dbf
input datafile fno=00004 name=/adm02/u0001/oracle/datafile/HYPP/users01.dbf
input datafile fno=00005 name=/adm02/u0001/oracle/datafile/HYPP/tools_01.dbf
input datafile fno=00010 name=/adm02/u0001/oracle/datafile/HYPP/hyp_hal_data_01.dbf
input datafile fno=00007 name=/adm02/u0001/oracle/datafile/HYPP/hypp_idx_01.dbf
channel ch01: starting piece 1 at 21-JUL-10
hannel ch01: finished piece 1 at 21-JUL-10
piece handle=/ora_backup/backups/HYPP/HYPP_Full_tnljb89m_6071_tnljb89m_1_1 tag=TAG20100721T104925 comment=NONE
channel ch01: backup set complete, elapsed time: 00:01:05
channel ch01: starting full datafile backupset
channel ch01: specifying datafile(s) in backupset
including current control file in backupset
channel ch01: starting piece 1 at 21-JUL-10
channel ch01: finished piece 1 at 21-JUL-10
piece handle=/ora_backup/backups/HYPP/HYPP_Full_toljb8bn_6072_toljb8bn_1_1 tag=TAG20100721T104925 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:02
Finished backup at 21-JUL-10

Starting Control File Autobackup at 21-JUL-10
piece handle=/adm02/u0001/oracle/product/10.2.0/db_1/dbs/c-2563884143-20100721-01 comment=NONE
Finished Control File Autobackup at 21-JUL-10

sql statement: alter system archive log current

Starting backup at 21-JUL-10
current log archived
channel ch01: starting archive log backupset
channel ch01: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=1673 stamp=724874462
input archive log thread=1 sequence=2 recid=1674 stamp=724934781
input archive log thread=1 sequence=3 recid=1675 stamp=724934785
input archive log thread=1 sequence=4 recid=1676 stamp=724935048
input archive log thread=1 sequence=5 recid=1677 stamp=724935052
channel ch01: starting piece 1 at 21-JUL-10
channel ch01: finished piece 1 at 21-JUL-10
piece handle=/ora_backup/backups/HYPP/HYPP_Full_tqljb8cg_6074_tqljb8cg_1_1 tag=TAG20100721T105055 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:08
channel ch01: deleting archive log(s)
archive log filename=/adm02/u8001/HYPP/arch/arch_HYPQ_0001_0000000001_0724872404.arc recid=1673 stamp=724874462
archive log filename=/adm02/u8001/HYPP/arch/arch_HYPQ_0001_0000000002_0724872404.arc recid=1674 stamp=724934781
archive log filename=/adm02/u8001/HYPP/arch/arch_HYPQ_0001_0000000003_0724872404.arc recid=1675 stamp=724934785
archive log filename=/adm02/u8001/HYPP/arch/arch_HYPQ_0001_0000000004_0724872404.arc recid=1676 stamp=724935048
archive log filename=/adm02/u8001/HYPP/arch/arch_HYPQ_0001_0000000005_0724872404.arc recid=1677 stamp=724935052
Finished backup at 21-JUL-10

Starting Control File Autobackup at 21-JUL-10
piece handle=/adm02/u0001/oracle/product/10.2.0/db_1/dbs/c-2563884143-20100721-02 comment=NONE
Finished Control File Autobackup at 21-JUL-10
released channel: ch01

RMAN>

Create RMAN recovery catalog

labrman01(RCAT) /ora_backup/datafile
>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jul 21 10:31:55 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user RCAT10G identified by rcat10g default tablespace RCAT;

User created.


SQL> grant connect,resource,recovery_catalog_owner to rcat10g;

Grant succeeded.

SQL>exit

labrman01(RCAT) /ora_backup/datafile
>rman

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jul 21 10:36:20 2010

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


RMAN> connect catalog rcat10g/rcat10g@RCAT

connected to recovery catalog database

RMAN> create catalog

recovery catalog created

RMAN> exit


Recovery Manager complete.
labrman01(RCAT) /ora_backup/datafile
>rman

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jul 21 10:38:33 2010

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

RMAN> connect target sys/syspwd@HYPP

connected to target database: HYPP (DBID=2563884143)

RMAN> connect catalog rcat10g/rcat10g@RCAT

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>exit

Thursday, July 8, 2010

install 11g 11.2.0 client Ubuntu

It is not straight forward anything with Linux/Unix, so I will share my experience with installing 11g client on Ubuntu.

ser1@user1-desktop:~$ uname -a
Linux user1-desktop 2.6.31-22-generic #60-Ubuntu SMP Thu May 27 00:22:23 UTC 2010 i686 GNU/Linux

downloaded 11gR2 client for Linux-x86
user1@user1-desktop:~/Downloads/linux1$ ls -lrt
drwxr-xr-x 6 user1 user1 4096 2009-08-18 15:51 client
-rw-r--r-- 1 user1 user1 642016988 2010-07-07 15:23 linux_11gR2_client.zip

Unzip it and goto client dir

Before you start the install prep the PC with necessary Kernel parameters
copy the below contents into the sysctl.conf file as root.

# vi /etc/sysctl.conf

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

set the kernel parameters or reboot
# /sbin/sysctl -p

set security limits
# vi /etc/security/limits.conf
append the following values to the end of the file
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536

create symbolic links
# ln -s /usr/bin/awk /bin/awk
# ln -s /usr/bin/rpm /bin/rpm
# ln -s /lib/libgcc_s.so.1 /lib/libgcc_s.so
# ln -s /usr/bin/basename /bin/basename

Now

start runInstaller
opted administrator install
install went on for a while then boom "started throwing linking errors"

If a dialog pops up indicating:

Error in invoking target install of makefile
/app/user1/product/11.2.0/client_1/rdbms/lib/ins_rdbms.mk

edit user1@user1-desktop:~/app/user1/product/11.2.0/client_1/rdbms/lib/env_rdbms.mk and change:

LINK=$(PURECMDS) gcc $(LDFLAGS) $(COMPSOBJS)

to:

LINK=$(PURECMDS) gcc296 $(LDFLAGS) $(COMPSOBJS)

and change:

ORACLE_LINKER=$(ADE_DEL_FILE_CMD) $(PURECMDS) gcc $(OLAPPRELINKOPTS) $(LDFLAGS) $(COMPSOBJS)

to:

ORACLE_LINKER=$(ADE_DEL_FILE_CMD) $(PURECMDS) gcc296 $(OLAPPRELINKOPTS) $(LDFLAGS) $(COMPSOBJS)

then click Retry or just say ignore

and the install went fine like a charm and asked me to run the root.sh scripts which I did run
and added the environmental variables like ORACLE_HOME in .bashrc file

and invoked sqlplus

user1@user1-desktop:~$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 8 10:25:38 2010

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

Enter user-name:


looked great...So good luck guys

Wednesday, July 7, 2010

RMAN DEBUG & TRACING

Turn debug on

$ rman target / catalog user/pwd@db debug trace rman.trc log rman.log
RMAN>


Additional Tracing at the channel level

RMAN> run {
2> allocate channel ch01 type sbt trace=5;
3> send 'NB_ORA_CLIENT=hypprdbv1-bn';
4> RESTORE SPFILE to pfile '/export/home/oracle/sshaik/initHYPP.ora' from autobackup maxdays 20;
5> }

allocated channel: ch01
channel ch01: sid=34 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

sent command to channel: ch01

Starting restore at 07-JUL-10

channel ch01: looking for autobackup on day: 20100707

Thursday, July 1, 2010

Known RMAN Performance Problems [ID 247611.1]

Anyone who wants to improve the RMAN backup/recovery performance problems I strongly recommend to read the below Metalink ID: This is very useful and simple. "ALL known issues documented at one place"


Known RMAN Performance Problems [ID 247611.1]

gather stats on fixed database objects

Latching on large fixed objects is expensive, so without proper statistics, performance degradation is expected when sub-optimal plans happen.(as in some cases, there are instance wide effects, such as effectively serializing access to the shared pool) This is more prevalent in very large, or very busy systems, as the number of times the latch is requested, and/or the length of time the latch is held, will increase with load and volume in the X$ being queried through the views.

Fixed object statistics will need to be gathered under representative load and you may experience performance degradation while the statistics are gathering.

There are some cases where having no statistics on a fixed object may produce the best plans, but in general, better plans are achieved by gathering statistics on these tables than by not gathering statistics.

There are some fixed tables that are simply very volatile by nature and it will be extremely hard to get accurate statistics on. In general, in the case of these volatile fixed tables, better plans are achieved by gathering statistics on these tables than by not gathering statistics.

SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';

OWNER TABLE_NAME LAST_ANAL
------------------------------ ------------------------------ ---------
SYS X$KGLDP

SQL> exec dbms_stats.gather_fixed_objects_stats();

PL/SQL procedure successfully completed.


SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';

OWNER TABLE_NAME LAST_ANALYZED
------------------------------ ------------------------------ -------------------
SYS X$KGLDP 07/1/2010 11:09:54

Size of the local individual index partitions

If you know the index name then go to PART-2 if not use PART-1 to get the index name using table name

PART-1
First find the index names for the given table and partition names:
select DISTINCT ''''||A.INDEX_NAME||''''||',' FROM dba_ind_partitions A,
BA_PART_INDEXES B
WHERE
A.INDEX_OWNER=B.OWNER
AND A.INDEX_NAME=B.INDEX_NAME
AND A.INDEX_OWNER='ISA' AND
B.TABLE_NAME IN
('ADJUSTMENT_REASON_CODE_PRE')
AND A.PARTITION_NAME IN
('P_CHARLOTTE')

PART-2:

select SEGMENT_NAME,PARTITION_NAME,sum(bytes)/1024/1024 size_in_mb
FROM DBA_SEGMENTS
WHERE owner='USER1'
AND SEGMENT_name in ('INDEX1')
AND SEGMENT_TYPE='INDEX PARTITION'
AND PARTITION_NAME IN
('P_CHARLOTTE')
GROUP BY SEGMENT_NAME,PARTITION_NAME
ORDER BY 1,2

Restore older version db on a newer version binaries

Oracle Says yes:

1) Can I restore or duplicate my previous version database using a later version of Oracle?

For example, is it possible to restore a 9i backup while using the 10g executables?

It is possible to use the 10.2 RMAN executable to restore a 9.2 database even if the restored datafiles will be stored in ASM. RMAN is configured so that a higher release is able to restore a lower release, but it is strongly suggested you use only the same version. In this scenario for a restore (not RMAN duplicate) you will be using the restored 9.2 database against 10.2 binaries. If you intend to run the restored 9.2 database against the 10.2 binary files you need to run the upgrade steps to make the restored 9.2 database a 10.2 database, but understand that the pre-upgrade checklist which you may have submitted against the source 9.2 database does NOT apply to this restored 9.2 database.

Once the restore / recovery is completed you will need to open the database with a combination of the resetlogs option and the upgrade option. For example:

SQL> alter database open resetlogs upgrade;
After the database is opened in this fashion run the upgrade steps for the higher version. If you have any problems with any part of this process please open a service request. If you are restoring using backups created by an earlier release, some time after you have upgraded the database, then media recovery will re-play the changes that were made during the upgrade, and you do not need to run the upgrade scripts again.