Monday, May 30, 2011

Interview questions from Oracle High Availability

Hi,
Based on my personal experience,Here I'm sharing interview question on Oracle
High availability topics like RAC,Apps & Other crucial questions,As a DBA and Apps DBA.Hope these questions are very much vital.
As a experience DBA and Apps DBA I have attended many interviews.Below are the questions which I have faced and find them really useful to put in this post.Many times we know the answer for the questions but unintentionally we may commit mistakes
which should be avoided to get selected for your ideol Role/Position.


1) Tell me about yourself?
Ans: Looks to be a simple question as there is no thinking require.But,This is the question which may decides your selection sometimes.Here You have speak about your experience and why you are the best person for this particular position and what you have done in past,what you are doing now and what you will be
doing in future for your growth,Team growth and Organization growth.

In brief about myself: This is Rafi here.I'm working as Oracle DBA and Apps DBAs since 4 yrs. I've been involved in Handling Database and Applications.As a part of DBA and Apps DBA team I have done Oracle RAC implentation on Linux and Solaris platform using ASM,OCFS and RAW DEVICE Storage mechanism,I've involved in migration of Database and Upgradation from Oracle 9i to Oracle 11g rel2 and Upgradation of Oracle Applications from Oracle 11i to Oracle R12 .I have worked on 4 Unix based platformsi.e Linux,Solaris,HP-UX and IBM-AIX including windows server. I've handled Oracle Database 9i,10g & 11g and I've Handled Oracle Applications 11i & R12 Administration.I've been involved in Clonning & Patching of Oracle Applications.
I've completed Oracle 9i OCP,10g OCP,11g OCP,RAC Expert,Oracle E-businness Suite R12 OCP and ITIL v3 foundation Certification.
Right now I'm focussing on Oracle SOA,Oracle Data Integrator,Oracle JDeveloper,Weblogic and Fusion middleware components.

Note:Please take about your skillsets and experience while answering the above question so that interview can ask questions
sometime based on your experience and skillsets and not necessary what skillset he wants.


2)How you stop Your RAC Database configue with ASM?

Ans: This is a bit tricky.But a simple logic is Database depends on storage and storage depends on node.Hence We can tell like this,Important is to remember that node application and cluster is started and stopped only with 'root'
user or the user having sudo privilege to do this in /etc/sudoers file.One more point to remember always asm instance
should be started first.

There are actually 2 commands to do it:

1) crsctl stop crs: This command will stop all the node applications(i.e gsd, VIP, ons, listener) and crs daemons(crsd,evmd,ocsd)
However /etc/init.d/init.crs {start|stop|enable|disable} can be used to start,stop,enable and disable these crs daemons
or services.

Login as root or a user with sudo permission to run the crsctl command:
#cd ORA_CRS_HOME/bin
#crsctl start crs
=> Run as root or you should have SUDO permissing in /etc/sudoers file.
#ssh testnode_1n1 /home/app/crs/bin/crsctl check crs
(or)
#ssh testnode_1n1 /home/app/crs/bin/crsctl stop crs

Note:This command will prevent CRS from starting on a reboot.There is no return output from the command.

[root@TEST_NODE1 oracle]# crsctl disable crs

For checking this use 'crsstat' command:

[root@TEST_NODE1 oracle]# crsstat

HA Resource Target State
----------- ------ -----
ora.TEST_NODE1.ASM1.asm OFFLINE OFFLINE
ora.TEST_NODE1.LISTENER_TEST_NODE1.lsnr OFFLINE OFFLINE
ora.TEST_NODE1.gsd OFFLINE OFFLINE
ora.TEST_NODE1.ons OFFLINE OFFLINE
ora.TEST_NODE1.vip OFFLINE OFFLINE
ora.orcl.RAC.cs OFFLINE OFFLINE
ora.orcl.RAC.orcl1.srv OFFLINE OFFLINE
ora.orcl.RAC.orcl2.srv OFFLINE OFFLINE
ora.orcl.db OFFLINE OFFLINE
ora.orcl.orcl1.inst OFFLINE OFFLINE
ora.orcl.orcl2.inst OFFLINE OFFLINE
ora.TEST_NODE2.ASM2.asm OFFLINE OFFLINE
ora.TEST_NODE2.LISTENER_TEST_NODE2.lsnr OFFLINE OFFLINE
ora.TEST_NODE2.gsd OFFLINE OFFLINE
ora.TEST_NODE2.ons OFFLINE OFFLINE
ora.TEST_NODE2.vip OFFLINE OFFLINE
Let’s not forget to enable CRS on reboot:

[root@TEST_NODE1 oracle]# crsctl enable crs

2)srvctl stop:
--------------

To stop a RAC Database configure system with ASM step by step:
--------------------------------------------------------------------------

srvctl stop service -d test =>To stop services like TAF(Transparent Application failover
srvctl stop database -d test =>To stop Database which may include 'n' number of instances
srvctl stop asm -n node1-pub1 =>To stop asm instance on node1
srvctl stop asm -n node2-pub2 =>To stop asm instance on node2
srvctl stop nodeapps -n node1-pub1,node2-pub2 =>To stop all node apps(listeners,GSD,ONS,VIP)

(or)

To start a RAC Database configure with ASM:
---------------------------------------------------------------------------

We have to first start the nodeapps,then the ASM instances, followed by the database instances, and lastly the services for TAF and load balancing.

srvctl start nodeapps -n node1-pub
srvctl start nodeapps -n node2-pub
srvctl start asm -n node1-pub
srvctl start asm -n node2-pub
srvctl start database -d test


Note 1: When we use crsctl to stop clusterware,It will stop nodeapps,asm instances and Database instance.

Note 2:Starting and Stopping of Cluster in Oracle 11g is changed because of 1)Separate home for clusterware
i.e Grid home.
2) OCR is in diskgroup inside ASM, then no way to stop ASM using srvctl, you have to shutdown the cluster to stop ASM.
We can start or start cluster or other services as follows:

Starting / Stopping the Cluster in Oracle 11g rel2:
--------------------------------------------------------------------


Stopping Cluster:
-----------------------

We can Use the "crsctl stop cluster" command on node1 to stop the Oracle Clusterware stack:
root@node1 ~]# /u01/app/11.2.0/grid/bin/crsctl stop cluster

Starting Cluster:
---------------------------------

[root@node1 ~]# /u01/app/11.2.0/grid/bin/crsctl start cluster

We can start/stop all the clusterware nodes by using below command:
[root@node1 ~]# /u01/app/11.2.0/grid/bin/crsctl start cluster all
[root@node1 ~]# /u01/app/11.2.0/grid/bin/crsctl stop cluster all


3)How you check the health of Your RAC Database?
Ans: 'crsctl' command from root or oracle user can be used to check the clusterware health But for starting or stopping we have to use root user or any privilege user.

[oracle@TEST_NODE1 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

4)How you check the services in RAC Node?
Ans: We can check the service or start the services with 'srvctl' command.load balanced/TAF service named RAC online.

[oracle@TEST_NODE1 ~]$ srvctl start service -d orcl -s RAC
[oracle@TEST_NODE1 ~]$ crsstat
HA Resource Target State
----------- ------ -----
ora.TEST_NODE1.ASM1.asm ONLINE ONLINE on TEST_NODE1
ora.TEST_NODE1.LISTENER_TEST_NODE1.lsnr ONLINE ONLINE on TEST_NODE1
ora.TEST_NODE1.gsd ONLINE ONLINE on TEST_NODE1
ora.TEST_NODE1.ons ONLINE ONLINE on TEST_NODE1
ora.TEST_NODE1.vip ONLINE ONLINE on TEST_NODE1
ora.orcl.RAC.cs ONLINE ONLINE on TEST_NODE2
ora.orcl.RAC.orcl1.srv ONLINE ONLINE on TEST_NODE1
ora.orcl.RAC.orcl2.srv ONLINE ONLINE on TEST_NODE2
ora.orcl.db ONLINE ONLINE on TEST_NODE2
ora.orcl.orcl1.inst ONLINE ONLINE on TEST_NODE1
ora.orcl.orcl2.inst ONLINE ONLINE on TEST_NODE2
ora.TEST_NODE2.ASM2.asm ONLINE ONLINE on TEST_NODE2
ora.TEST_NODE2.LISTENER_TEST_NODE2.lsnr ONLINE ONLINE on TEST_NODE2
ora.TEST_NODE2.gsd ONLINE ONLINE on TEST_NODE2
ora.TEST_NODE2.ons ONLINE ONLINE on TEST_NODE2
ora.TEST_NODE2.vip ONLINE ONLINE on TEST_NODE2
[oracle@TEST_NODE1 ~]$


4)If there is some issue with virtual IP how will you troubleshoot it?How will you change virtual ip?
Ans: To change the VIP (virtual IP) on a RAC node, use the command

[oracle@testnode oracle]$ srvctl modify nodeapps -A new_address

5)What kind of backup stratergy you follow for your Databases?
Ans:We follow different backup strategy for our Databases depends on type of Database.We use different kind of Backup stratergy for Production,Test,Performance,Demo,Development Databases.But the main
aim is to recover the Database with minimal or no Data loss:

1)Production Databases:
-----------------------------

Backup stratergy for Production Database is as follows:
RMAN BACKUP:
---------------------

incremental level 0 =>Weekly Basis at 6am -- Full backup of Database with archive logs and copy of Current control file
incremental level 1 =>Mon,Tues,Thurs,Friday at 6am --Changes from recent back to a particular day
cummulative backup =>Wed,Saturday at 6am -- changes from the lowest level i.e mon-wed and Thur-Sat.

While deciding the backup stratergy for our production system of 300GB we had in our mind the following points:
1)Backup should be schedule at less peak hours.
2)With no loss we should recover Database in case of any Disaster.

For scripts and scheduling details refer my below link:

http://rafioracledba.blogspot.com/search/label/RMAN

expdp Backup:
----------------

Export Datapump backup on daily basis at 9pm.
We should have one datapump backup which should be most recent to recover the lost of Table or any Data.Below points are same:
1)Backup should be schedule at less peak hours.
2)With no loss we should recover Database in case of any Disaster.

For scripts and scheduling details refer my below link:

http://rafioracledba.blogspot.com/search/label/expdp%2Fimpdp

2)Test Databases:
---------------------

Usually Test Database is almost same as production in terms of Data.However whenever we want to test some
patch or any script before applying to production we can apply in test and than apply in production.I usually
prefer to have same backup stratergy as production for Test Databases.

3)Development Database:
---------------------------

In a development Database.We can go for below backup statergy,However if you have space and enough infrastructure you can repeat the same backup stratergy as above.

Expdp full backup:
In a Development environment,We should have full Database logical
Backup up to date and should be schedule on daily basis,so that whenever there
Is some table drop or table backup is requested by developer you can restore
That table from your Logical backup.


COLD RMAN BACKUP:
-------------------

We can schedule cold rman backup on every sundays at 9am(any time which you feel is convinient without affecting much to developers and end users).Below is the link for implementing RMAN cold Backup:

http://rafioracledba.blogspot.com/2011/06/scheduling-rman-cold-backup-and-copy.html

6)How you will backup your RAC Database?
Ans: Backup strategy of RAC Database:
An RAC Database consists of
1)OCR
2)Voting disk &
3)Database files,controlfiles,redolog files & Archive log files


1)OCR BACKUP:
-------------


Oracle performs physical backup of OCR devices every 4 hours under the default backup direcory $ORA_CRS_HOME/cdata/
and then it rolls that forward to Daily, weekly and monthly backup. You can get the backup information by executing below command.
Login with help of 'root' user and fire the below command.

ocrconfig -showbackup

[root@testnode -pub ~]# ocrconfig -showbackup

testnode-pub 2011/05/03 17:50:47 /u01/app/crs/cdata/test-crs/backup00.ocr

testnode-pub 2011/05/03 13:46:45 /u01/app/crs/cdata/test-crs/backup01.ocr

Manually backing up the OCR we can do with the help of same command 'ocrconfig

ocrconfig -manualbackup -->Physical Backup of OCR

The above command backs up OCR under the default Backup directory.We can export the contents of the OCR using below command (Logical backup).

ocrconfig -export /tmp/ocr_exp.dat -s online <<-- data-blogger-escaped-backup="" data-blogger-escaped-br="" data-blogger-escaped-logical="" data-blogger-escaped-ocr="" data-blogger-escaped-of="">
2)Voting Disk Backup:
----------------------------

Backup the existing votedisks as below as oracle:

dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0

[root@node1-pub ~]# su - oracle
[oracle@node1-pub ~]$ dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0
41024+0 records in
41024+0 records out
[oracle@node1-pub ~]$

Note: If we want to add a voting disk we can do with the help of 'crsctl' as given below:

[root@node1-pub ~]# su - oracle
[oracle@node1-pub ~]$ touch /u02/ocfs2/vote/VDFile_3
[oracle@node1-pub ~]$ crsctl add css votedisk /u02/ocfs2/vote/VDFile_3
Now formatting voting disk: /u02/ocfs2/vote/VDFile_3.
Successful addition of voting disk /u02/ocfs2/vote/VDFile_3.

Note:Please ask the interview about which version ,this is because,In Oracle 11g rel2,The way we take backup of Voting Disk and OCR is totally different,Because now we can use ASM for storing OCR and Voting Disk.Another point to keep in mind is Voting File is no longer supported to take a manual backup of it with 'dd' command
Instead,the Voting File gets backed up automatically into the OCR. As a New Feature,we can now do a manual backup of the OCR any time you like, without having to wait until that is done automatically – which is also still done.

Now the manual backup:
[root@node1-pub ~]# /u01/app/11.2.0/grid/bin/ocrconfig -manualbackup

We can take backup on a separate shared location also:
[root@node1-pub ~]#/u01/app/11.2.0/grid/bin/ocrconfig -backuploc /u02/ocr_backup

To see the above backup we can use the same command 'ocrconfig':
[root@node1-pub ~]# /u01/app/11.2.0/grid/bin/ocrconfig -showbackup

Note:Oracle’s solution to this problem is to combines the clusterware and ASM into a single Grid Infrastructure home and comes up a procedure with a complex start up sequence which mixes the different components of clusterware and ASM instance in order

3)Datafiles Backup:
--------------------

For Backing up your Database files,Redolog files,control files,Archive log files and other Database files,Please use 'rman' as it has many advantages over other
user managed backup.RMAN gives you the flexibility of backing up Database in archive log mode(hot backup)
and unarchive log mode(Cold backup).

http://forums.oracle.com/forums/thread.jspa?messageID=9624568#9624568

7)How to recover your RAC Database?
Ans: If you have valid backup of OCR,Voting Disk and Database files than RAC Database recovery can be done without much lost or Damages.Below are some steps to do these:
1)OCR can be restore and recover using 'ocrconfig' command if you have a valid export backup of ocr.
2)Voting disk can be restore and recovered using 'dd' command if you have got the backup which you took from 'dd' command.

Detail process we can get from below link:
http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_65.shtml#Recover the Voting Disk

3)Restore and Recover the Database
First, take the database out of cluster mode,This is very important step, via:

# sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sat May 24 17:02:17 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 1261300 bytes
Variable Size 251658508 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

Then restore the database via RMAN:

# rman target=/

Recovery Manager: Release 10.2.0.3.0 - Production on Sat May 24 17:04:10 2008

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 268435456 bytes

Fixed Size 1261300 bytes
Variable Size 251658508 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes

RMAN> restore database;

Starting restore at 24-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oracle/ora1/TESTDB/systTESTDB01.dbf
restoring datafile 00002 to /u02/oracle/ora1/TESTDB/undotbs01.dbf
restoring datafile 00003 to /u02/oracle/ora1/TESTDB/sysaux01.dbf
restoring datafile 00004 to /u02/oracle/ora1/TESTDB/users01.dbf
restoring datafile 00005 to /u02/oracle/ora1/TESTDB/example01.dbf
restoring datafile 00006 to /u02/oracle/ora1/TESTDB/undotbs02.dbf
restoring datafile 00007 to /u02/oracle/ora2/TESTDB/mgmt.dbf
restoring datafile 00008 to /u02/oracle/ora1/TESTDB/mgmt_ecm_depot1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oracle/ora3/TESTDB/backups/0ijh6j4t_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oracle/ora3/TESTDB/backups/0ijh6j4t_1_1 tag=FHB_EM1200805241630
channel ORA_DISK_1: restore complete, elapsed time: 00:06:36
Finished restore at 24-MAY-08

Since my database is so small, I will simply restore the entire thing, however, since you know which datafile is missing, you could simply restore it and then recover the database as necessary.

Recover the Database

RMAN> recover database;

Starting recover at 24-MAY-08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 24-MAY-08

RMAN> alter database open;

database opened
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Afterwards, place the database back into cluster mode and startup both instances:

# sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sat May 24 17:16:36 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


# srvctl start database -d TESTDB
[oracle@rac1 bdump]$ srvctl status database -d TESTDB
Instance TESTDB1 is running on node rac1
Instance TESTDB2 is running on node rac2


8)What kind of backup strategy you are following for application server?
Ans;Complete Oracle Application Server Environment Backup can be done using the below techniques,

A complete Oracle Application Server environment backup includes:

* A full backup of all files in the middle-tier Oracle homes (this includes Oracle software files and configuration files)

* A full backup of all files in the Infrastructure Oracle home (this includes Oracle software files and configuration files)

* A complete cold backup of the Metadata Repository

* A full backup of the Oracle system files on each host in your environment.

OracleAS Backup and Recovery Tool can be used for taking Oracle Application server backup.For more details and configuration of OracleAS Backup and Recovery tool refer the below link:

http://download.oracle.com/docs/cd/B14099_19/core.1012/b13995/br_tool.htm


9)How your Add node to your RAC Database?
Ans: To add a new node(server) to your RAC Database use the command 'srvctl' with the option given below.

[oracle@testnode oracle]$ srvctl add nodeapps -n newserver_name -o $ORACLE_HOME -A 149.181.220.1/255.255.255.0/eth1

Note: The -A flag precedes an address specification.

Howerver there is one more method:

Run the addNode.sh script

On an existing node from the Oracle_home/oui/bin directory,run the addNode.sh script

Note:For above question if it is asked in detail than there are lot of steps,Which is
explained in Q11.

10)For a Database created with ASM on RAC How you would add one more asm configuration?
Ans: We can use DBCA in Silent Mode to Add ASM and Database Instances to Target Nodes

We can use the DBCA in silent mode to add instances to nodes on which you have extended an Oracle Clusterware home and an Oracle Database home. Use the following syntax where password is the password as given below:

$dbca -silent -addInstance -nodeList node -gdbName gdbname [-instanceName instname]
-sysDBAUserName sysdba -sysDBAPassword password


Note:We can use Oracle Enterprise Manager grid control also to do the same task.


11)How you add node for a RAC cluster?Step by step?
Ans:Below are the v steps for adding non-rac node to an RAC Database:

I)Prerequisite Steps for Extending Oracle RAC to Target Nodes:
The following steps describe how to set up target nodes to be part of your cluster:

Step 1, "Make physical connections"

Step 2, "Install the operating system"

Step 3, "Create Oracle users"

Step 4, "Verify the installation" =>use cluvfy ustility for verification of clusterware installation
Eg: cluvfy stage -post hwos -n node_list|all [-verbose]

II)Extend Oracle Clusterware to Target Nodes
In the above step you have to stop clusterware services with 'crsctl' and create a clone environment by copying file and making identical copy of clusterware hope.

III)Configure Shared Storage on Target Nodes
Depending on the environment existing whether it is having asm,ocfs2,raw or any vendor shared storage make the environment same as the source.If the ASM HOME and ORACLE RAC database HOME exists in oracle than you don't need to do any thing as ASM home to a node will happen implicitly if it is not so the case,you must first extend the Oracle Clusterware home (CRS_home),ASM home, and then the Oracle home (in that order), in order to add the new node to the cluster.

IV)Add the Oracle Real Application Clusters Database Homes to Target Nodes
We can add the Oracle RAC database home to target nodes using either of the following methods:

1)Extending the Database Home to Target Nodes Using Oracle Universal Installer in Interactive Mode
(OR)
2)Extending the Database Home to Target Nodes Using Oracle Universal Installer in Silent Mode

Let us see the 2nd method which doesn't involve user interaction:

We can optionally run addNode.sh in silent mode,
replacing steps 1 through 6, as follows where nodeI, nodeI+1,
and so on are the target nodes to which you are adding the Oracle RAC database home.

*Ensure that you have successfully installed the Oracle Database with the Oracle RAC software on at least one node
in your cluster environment.

*Ensure that the $ORACLE_HOME environment variable identifies the successfully installed Oracle home.

Go to Oracle_home/oui/bin and run the addNode.sh script.
In the following example, nodeI, nodeI+1 (and so on) are the nodes that you are adding:

addNode.sh -silent "CLUSTER_NEW_NODES={nodeI, nodeI+1, … nodeI+n}"
You can also specify the variable=value entries in a response file, known as filename, and you can run the addNode script as follows:

addNode.sh -silent -responseFile filename
Command-line values always override response file values.

v)Add ASM and Oracle RAC Database Instances to Target Nodes
We can add ASM and RAC Database Instances with the help of DBCA.
After you terminate your DBCA session, run the following command to verify the administrative privileges on the target node and obtain detailed information about these privileges where nodelist consists of the target nodes:

cluvfy comp admprv -o db_config -d oracle_home -n nodelist [-verbose]

Above the steps in brief so that we can crack the interview.The actual steps might be in detailed which we have to plan and do to avoid issues.

12)Have you done upgrade/migration from Oracle 11i Application to Oracle R12 Applications and What are the steps followed in brief?
Ans: Upgradation/migration tasks from Oracle 11i to R12 can be divided into 3 steps in broad:

1: Prerequisites: Must be completed prior to using the migration utility.
2: Migration Tasks : Tasks to migrate an application tier to a new platform.
3: Finishing Tasks: Tasks that need to be performed after the migration.

Application Tier Platform Migration with Oracle E-Business Suite Release 12 [ID 438086.1] explain these steps in best way.Other
metalink notes from My oracle support which can be really helpful in performing upgradation/migration are:
Export/import process for R12 using 11gR1 or 11gR2 [ID 741818.1]
Using Transportable Database to migrate E-Business Suite R12 Using Oracle Database 10gR2 or 11g [ID 734763.1]
Oracle EBS R12 with Database Tier Only Platform on Oracle Database 11.2.0 [ID 456347.1]

13)What are the difference between Oracle 12.0.4 and 12.1.1 applications?
Ans: The metalink note 566521.1 Oracle Application Object Library Release Notes, Release 12.1.1 explain the difference
in the best way.Link is given below:

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=README&id=566521.1

14)What is the difference in java for Oracle 11i and Oracle R12 Application?
Ans: There are 3 key technical differences when we compare Oracle 11i and R12,They are:
1) Jserv in apps 11i(mod_jserv) is replaced by OC4J (mod_oc4j) in apps R12.
2) JDBC version is changed from version 9 in apps 11i to version 10.2.0 in Apps R12 and
3) Java processes use JDK version 1.3.1 or 1.4.2 in Apps 11i,where as R12 uses JDK/JRE version 1.5.0.

15)What are the difference between Oracle 11i and R12?
Ans:
Database:
Database Version in 11i (11.5.9 & 11.5.10) was 9i Rel 2 where as in Release 12 its 10g R2 (10.2.0.2)

Application Tier:
Tech Stack in Application Tier consist of iAS(1.0.2.2.2) & Developer 6i (Forms & Reports 8.0.6) but in Applications R12 it is build on Fusion Middleware (10g Web Server and 10g Forms & Reports)

Sub component in Application Tier
A) HTTP Server or Web Server in R12 is Version 10.1.3 which is built on Apache version 1.3.34. In apps 11i it is Version 1.0.2.2.2 built on Apache Version 1.3.19
B) Jserv in apps 11i is replaced by OC4J (mod_jserv is replaced by mod_oc4j)
C) Forms Version 6i in Apps 11i is replaced by Forms 10.1.2.0.2 in R12
D) Reports Version 6i in Apps 11i is replaced by Reports 10.1.2.0.2 in R12
E) JDBC version is changed from version 9 in apps 11i to version 10.2.0 in Apps R12
F) modplsql or mod_pls is removed from Apps R12 (What will happen to my mod plsql applications- coming soon* )
G) Java processes use JDK/JRE version 1.5.0 in R12 against JDK version 1.3.1 or 1.4.2 in Apps 11i
H) For various environment variable changes check below picture
I) New top INSTANCE_TOP added in Release 12 for configuration and log files

16)What is the major use of INST_TOP?
Ans:New TOP $INST_TOP(INSTANCE_TOP) in Application Tier.If you are familiar with Oracle Apps 11i ,The file system for 11i with various TOP's consist of APPL_TOP, ORA_TOP, COMMON_TOP, DB_TOP and DATA_TOP.
With Multiple Middle Tier configuration came shared APPL_TOP (only APPL_TOP used to be shared ).
After shared APPL_TOP came shared Application Tier (All three TOP's on Application Tier, APPL_TOP, ORA_TOP and COMMON_TOP were shared across servers).
With this came challenge of configuration files which are specific to a server mainly iAS & 806 configuration file.
If we have configured Shared Application Tier (sharing ORA_TOP as well) in 11i then this problem was overcome by creating conf directory in COMMON_TOP.

In Oracle Apps R12,We can see new TOP called INSTANCE_TOP($INST_TOP) in Application Tier.
This TOP will now hold any configuration files (specific to that server) ,
Certificates (If SSL is enabled SSL Certificates & any other certificates specific to that server) and related log files or pid files (for Apache or any other process).
In diagram tops with <>ST can be shared across servers (Shared Tier) and
INSTANCE_TOP will be specific to Server.

17)What is OID?
Ans:OID which stands for Oracle Internet Directory is part of Identity Management in Infrastructure Tier of Fusion Middleware.
OID is part of infrastructure tier in 10g Application Server (Identity Mangement from 10.1.4 onwards).
Oracle Internet Directory (OID) is Oracle’s Implementation of LDAP (Light weight Directory Access Protocol)which is ldap version 3 compliant. OID is special kind of database repository in which information is stored in Tree structure also called DIT (Directory Information Tree).

18)How to do Oracle Application single-signon configuration?
Ans:We can use OID (Oracle Internet Directory) For configuring Single-Signon configuration.For implementation details the best link is the below one:

http://www.appsdba.info/docs/oracle_apps/advance/Integrate_AppsR12_with_OID_SSO.pdf

(OR)

The below MY ORACLE SUPPORT ID can be useful in doing single-signon configuration using Progress Reporter.
Configuring Oracle Application Server Single Sign-On (OSSO) with P6 [ID 1110098.1]

We can configure Oracle Application server single sign-on(OSSO) with P6(progress reporter 6).

Configuring Oracle Application Server Single Sign-On (OSSO) with P6

Part 1 - Configure P6 Authentication Scheme

In the P6 v7.0 Client Applications media download, there is a tool called LDAPCfgWiz.exe (\P6_70_Client_Applications\install\database\ldap-config). This tool configures the authentication mode and specifies the LDAP store information as well as steps for provisioning users from the LDAP store to the database.

In the Admin Guide, located in the Documentation medial download (P6_70_Documentation\language\Product Manuals) or http://download.oracle.com/docs/cd/E16281_01/index.htm, follow pages 313-323 for detailed steps to setting authentication mode, specifying LDAP store information, and provisioning users.

Note:
For Web Authentication you will be setting Web Single Sign On.Provisioning the users to the database is a required step. When the users are provisioned, these users still require licensing to Primavera modules. This is accomplished by logging into the P6 Project Management application.

Part 2 - Configure P6 Web Access

The P6 Web Administration Application has to be configured for authentication to Oracle Application Server Single Sign-On.

Launch the Administration Application by going to the P6 Web Home directory and launching. adminpv.cmd (windows) or adminpv.sh (unix). You can also launch the Administration Application through the browser (http://servername:port/primaveraweb/admin.jsp).

We will need to log into the P6 Web Administration Application using your privileged database user account (ex. PRIVUSER).

Under Custom, select the plus-sign (+) next to the configuration you are using for P6 Web Access (ex. Primavera Configuration)
Expand Authentication and set the Mode equal to WebSSO
Under Web Single Sign-On, set the following values:

Setting
Value
User Name Header Key
The name of the http header you specified in the policy server
Context Path Override
The path used to pass web requests from the Single Sign-On
Web server to the server of P6 Web Access.
Server and Port Override
The fully qualified domain name and port for the Web server
that the Single Sign-On server is controlling.

Save your configuration and restart the P6 Web Application server

Part 3 - Configure P6 Progress Reporter

The P6 Progress Reporter Configuration Application has to be configured for authentication to Oracle Application Server Single Sign-On
Launch the Configuration Application through the browser by going to http://servername:port/pr/admin/configuration
Click the Authentication link at the top of the screen
Enter the User Header being used with your SSO configuration (ex. Proxy-Remote-User)
Save your configuration and restart the P6 Progress Reporter server

19)Do you have any idea of load balancing in application?How load balancing is done?
Ans: I will not be doing justice to myself and others if I don't share this link with you all.Navdeep had explained the load balancing in application in a really beautiful way.Check the below link:
http://practicalappsdba.wordpress.com/category/for-master-apps-dbas/

If we want more basic stuffs we can get from below MY ORACLE SUPPORT ID(metalink note 380489.1):
Using Load-Balancers with Oracle E-Business Suite Release 12
The most current version of this document can be obtained in Oracle Metalink Note 380489.1

20)What is demililitarized setup?
Ans:DMZ stands for Demilitarized Zone. It is a network segment that is separate from your internal network, where publicly accessible servers reside, (ie: an FTP server). This separation provides an additional layer of security for your internal network. If one of these DMZ servers is compromised, intruders will not be allowed direct access to your internal network as well.To provide segregation from your internal network, the DMZ should be created on an interface of the SonicWall that is different from the one used for your internal LAN.

The above question is basically dealing with how you protect your network and security of the Network.Ofcourse as a DBA networking awareness and security details related to networking is very crucial so that we can guide network team to maintain security at network layer also.

21)Do you have any questions?
Ans: In this question you have to express your eagerness to know about company,team size and the role you will be assigned after getting this postition.Be carefull don't show your eagerness to get this job at any cost by begging,remember one thing
"If the company needs you they will take you by paying any cost but,if they don't need than you don't get that job even after reducing
your cost.
Below are few questions we can ask:
1)Gather some information about company if you come to know before giving interview or else ask For which kind of sector
the company is progressing.
2)How many memembers are going to work on this project?
3)What will be my role if I get selected?

I tried to share my knowledge in a best way.I used
Few links and reference to get the best out of the oracle technology and understand in a best way.

Hope it helps.

Happy INTERVIEWS

Best regards,

Rafi.


Note:Check www.orasols.com for the services we offer.

Resolving ORA-24247 and ORA-06512

Resolving ORA-24247 and ORA-06512:
-----------------------------------

When you start working with Oracle 11g rel2 environment.You might face some issues related to ACL
access control list(extra security layer).One of my developer complained he is not able to send mails from stored procedure
and getting the error as given below:

Connecting to the database TEST_USER_DEV 11g.
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "TEST_USER.SP_GM_INIT_MAIL", line 43
ORA-06512: at line 15

I resolve this error as follows:


Step 1: connect to the Database and create a procedure as given below:
--------


SQL> select name from v$database;

NAME
---------
TEST_DB

SQL> set serveroutput on
SQL> create or replace procedure mailserver_acl(
aacl varchar2,
acomment varchar2,
aprincipal varchar2,
aisgrant boolean,
aprivilege varchar2,
aserver varchar2,
aport number)
is
begin
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
dbms_output.put_line('ACL dropped.....');
exception
when others then
dbms_output.put_line('Error dropping ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
dbms_output.put_line('ACL created.....');
exception
when others then
dbms_output.put_line('Error creating ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
dbms_output.put_line('ACL assigned.....');
exception
when others then
dbms_output.put_line('Error assigning ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
commit;
dbms_output.put_line('ACL commited.....');
end;
/

Procedure created.


SQL> show errors
No errors.

Step 2: create an ACL as given below:
--------


SQL> begin
mailserver_acl(
'mailserver_acl.xml',
'ACL for used Email Server to connect',
'TEST_USER',
TRUE,
'connect',
'rb-smtp-int.MAIL_SERVER.com',
25);
end;
/
Error dropping ACL: mailserver_acl.xml
ORA-31001: Invalid resource handle or path name "/sys/acls/mailserver_acl.xml"
ACL created.....
ACL assigned.....
ACL commited.....

PL/SQL procedure successfully completed.

Now tell the developer to try and send mail it should work.It is just a short and sweet process
to solve ORA-24247 along with ORA-06512.



Best regards,

Rafi.

Tuesday, May 17, 2011

How to apply Database Patches

Hi,
Database patches are of various kinds like,But in broad there are II types:
I)Patchset: To upgrade to higher version we use Database patchset.i.e For Upgrading from 10.2.0.1.0 to 10.2.0.4.0 we have to use
Patchset.

II)Patchset Updates:
Patchset Updates are mainly divided into two types:

1)CPU(Critical Patch Update):
Critical Patch Update, quarterly delivered by Oracle to fix security issues.

2)PSU(Patch Set Update):
PatchSet Update, also quarterly delivered by Oracle,it includes CPU and a bunch of other one-off patches.

PSUs have been introduced for the first time on Oct-2009,We can use them, and stop to apply CPUs. PSUs, as CPUs, are cumulatives, no need to apply all of them, just apply the last one should be enough. One-off patch is single fix, to fix a particular issue.

We need to have metalink(My Oracle Support)Account to download this patch and applying instructions will be there in readme file of Patch.

1)How to apply Patchsets:
-------------------------------------------------


Patchsets are applied via OUI(Oracle Universal Installer).Patchsets are usually applied to upgrade oracle version
Eg: When we want to upgrade from Base release 10.2.0.1.0 to 10.2.0.4.0 for more useful feature and to avoid bugs,We have to use Patchset 10.2.0.4.0.So Patchsets change the version number.
Details of this process is there in the below mentioned link:

http://rafioracledba.blogspot.com/search/label/Database%20Upgrade
i.e Database Upgrade Label

2)How to apply Patch set updates or Critical Patch Updates:
--------------------------------------------------------------------------


PSUs(PatchSet Updates) or CPUs(Critical Patch Updates)are applied via opatch utility.
opatch is an interim utility for applying Database interim patches which will fix the bug in the release. For making use of the opatch utility you have to download the opatch recent version and apply as follow:


Downloading and installing the latesh Opatch version:
------------------------------------------------------

Below are the steps for downloading and installing the latest opatch version.opatch is very much useful for applying the database patches to fix various bugs and it is very much important to have the latest version.

1) Please download the latest OPatch version from My Oracle Support (MOS)

a) Click on the "Patches & Updates" tab

b) In the "Patch Name or Number" field type 6880880

c) In the "Platform" field select the relevant platform

d) Click the Search button.

e) Select the patch that corresponds to the Oracle release installed:

6880880 Universal Installer: Patch OPatch 11.2

f) Click the Download button
Once the above task is done copy the patch to $ORACLE_HOME directory and move the previous OPatch directory to separate directory in OS.We can use winscp or ftp for copying this patch from MOS to windows and
than windows to linux Box.

Now,unzip this patch zip file.The files will be extracting as shown below:

Eg:
-----
$ cd $ORACLE_HOME
(If an OPatch directory already exist then move it)
$ mv Opatch /TEST/OPatch_100057
$cd $ORACLE_HOME
$ unzip p6880880_101000_LINUX64.zip
Archive: p6880880_101000_LINUX64.zip
creating: OPatch/
creating: OPatch/docs/
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/README.txt
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/docs/bt1.txt
inflating: OPatch/docs/bt2.txt
inflating: OPatch/docs/tmp
inflating: OPatch/emdpatch.pl
creating: OPatch/jlib/
inflating: OPatch/jlib/opatch.jar
inflating: OPatch/opatch
inflating: OPatch/opatch.bat
inflating: OPatch/opatch.pl

[oracle@kor300949lx1 OPatch]$ opatch version
Invoking OPatch 11.2.0.1.5

OPatch Version: 11.2.0.1.5

OPatch succeeded.

[oracle@kor300949lx1 OPatch]$ opatch apply /u01/app/8264365
Invoking OPatch 11.2.0.1.5

Oracle Interim Patch Installer version 11.2.0.1.5
Copyright (c) 2010, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.5
OUI version : 11.2.0.1.0
OUI location : /u01/app/oracle/product/11.1.0/db_1/oui
Log file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2011-05-16_17-18-01PM.log

Patch history file: /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Here is your opatch latest version ready,Let us what all it consists of:

Go to the below mentioned path for all opatch commands description.

$ cd $ORACLE_HOME/OPatch/docs/
$ vi Users_Guide.txt

In general for applying any patch,We have to use the below command:
$cd $ORACLE_HOME/OPatch
$opatch apply /u01/Patch_dir/

Where :/u01/Patch_dir: The path where you have kept your Patch in the OS.

Hope the above post helps in understanding Database patches completely.

Best regards,

Rafi.

Wednesday, May 11, 2011

Detecting new hard disk ,creating partition,formating a partition,creating mount point point for storage purpose on linux platform

Detecting new hard disk ,creating partition,formating a partition,creating mount point point for storage,Using the mount point for storage on linux platform :
-------------------------------------------------------------------------------------
For becoming a best DBA we should have sound knowledge of operating system.The below experiment will be very useful for creating partition on linux ,formating the partition and mounting the disk for our storage purpose.

I got the 50 GB hard disk from my system admin,Now I want to use this hard disk for
storing backup of Database files.before doing I need to do the below steps:

Pre-requistics step:
--------------------

For detecting hard disk in linux server you need to reboot.We can do as follows:
[root@testNODE ~]# shutdown -r now
Broadcast message from root (pts/0) (Wed Apr 20 01:23:45 2009).

The system is going down for reboot NOW!

Note: 'reboot' command is also used.
----
Once ther server is up you can detect your hard disk as follows:

[root@testNODE ~]# fdisk -l =>This is for checking disk in linux.

Disk /dev/sda: 64.4 GB, 64424509440 bytes
255 heads, 63 sectors/track, 7832 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 64 514048+ 83 Linux
/dev/sda2 65 4633 36700492+ 83 Linux
/dev/sda3 4634 5482 6819592+ 83 Linux
/dev/sda4 5483 7832 18876375 5 Extended
/dev/sda5 5483 6265 6289416 82 Linux swap / Solaris
/dev/sda6 6266 7030 6144831 83 Linux
/dev/sda7 7031 7683 5245191 83 Linux

Disk /dev/sdb: 42.9 GB, 42949672960 bytes
255 heads, 63 sectors/track, 5221 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 4981 40009851 83 Linux
/dev/sdb2 4982 5221 1927800 83 Linux

Disk /dev/sdc: 53.6 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table

So here is my new hard disk '/dev/sdc'.I will create a valid partition for this disk.For creating a valid partition we need to follow below steps:

Step 1: Use the command 'fdisk' as follows:
-------


[root@testNODE ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 6527.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Step 2: To get help for various partition options use 'm' option
-------


Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)

Step 3:As seen above 'n' is used to create a new partition,'e' for extended
---------
partition,'1' is the primary partition used 'w' is for writing to disk


Command (m for help): n =>For creating new partition
Command action
e extended
p primary partition (1-4)
p =>For primary partition
Partition number (1-4): 1
First cylinder (1-6527, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-6527, default 6527):
=>Above you can specify size as +5G,+10G,...if you want to create 2 or more partition,I want to create only 1 partition of size 50G(default value of cylinder)
so I have not given any values.

Command (m for help): p =>for print of partitions which are created.

Disk /dev/sdc: 53.6 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdc1 1 6080 48837568+ 83 Linux

Command (m for help): w =>for writing and saving the partition
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

So our partition is created with 50GB of space.

Step 4: Check the filesystems types available
-------


[root@testNODE ~]# cat /etc/filesystems =>Gives types of filesystem available in OS
ext3
ext2
nodev proc
nodev devpts
iso9660
vfat
hfs
hfsplus

Step 5:Our attached hard disk is created with extended based filesystem i.e ext3
------


[root@testNODE ~]# fdisk -l /dev/sdc =>For checking the partition

Disk /dev/sdc: 53.6 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdc1 1 6080 48837568+ 83 Linux

So,as you see above we created one partition with size 50GB.

Step 6:Format your partition before mounting
------


a)Check your filesystem with 'e2fsck':
-------------------------------------------


[root@testNODE /]# e2fsck -b 8193 /dev/sdc
e2fsck 1.39 (29-May-2006)
e2fsck: Bad magic number in super-block while trying to open /dev/sdc

The superblock could not be read or does not describe a correct ext2
filesystem. If the device is valid and it really contains an ext2
filesystem (and not swap or ufs or something else), then the superblock
is corrupt, and you might try running e2fsck with an alternate superblock:
e2fsck -b 8193

From the above error I came to know the partition is not formated,Now for formating use 'mke2fs' for formating extended
filesystem.

[root@testNODE /]# e2fsck -b 8193 /dev/sdc1 =>For filesystem check i.e extended here
e2fsck 1.39 (29-May-2006)
e2fsck: Bad magic number in super-block while trying to open /dev/sdc1

The superblock could not be read or does not describe a correct ext2
filesystem. If the device is valid and it really contains an ext2
filesystem (and not swap or ufs or something else), then the superblock
is corrupt, and you might try running e2fsck with an alternate superblock:
e2fsck -b 8193

Note 1:'e2fsck' is used to check a Linux ext2/ext3 file system.
Note 2: 'fsck' is used to check and repair a Linux file system

b)Format your partition with 'mke2fs'
------------------------------------------------


[root@testNODE /]# sudo file -s /dev/sdc1
/dev/sdc1: x86 boot sector
[root@testNODE /]# sudo mke2fs -S /dev/sdc1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
6553600 inodes, 13107024 blocks
655351 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
400 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424

Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 27 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

[root@testNODE /]# sudo mke2fs -j /dev/sdc1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
6553600 inodes, 13107024 blocks
655351 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
400 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 22 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.


Below are very important notes based on above experiment:
-------------------------------------------------------------


Note 1:'mke2fs' is used to format the extended filesystem.
-------


Note 2:In 'mke2fs' -S option is a very good option as it Write superblock and group descriptors only.
-------
This is useful if all of the superblock and backup superblocks are corrupted, and a last-ditch recovery method is desired. It causes mke2fs to
reinitialize the superblock and group descriptors, while not touching the inode table and the
block and inode bitmaps.

Note 3:'mkfs' is used to build a Linux file system on a device, usually a hard disk partition. filesystem is the device name (e.g. /dev/hda1, /dev/sdb2). blocks is the number of blocks to be used for the file system.
The exit code returned by mkfs is 0 on success and 1 on failure.

Note 4=>'mke2f' -j=>Create the filesystem with an ext3 journal (given the size of the filesystem)

=> So our extended filesystem is formated.

Step 7: Make an entry in /etc/fstab to detect a mount point even after re-boot.
--------


vi /etc/fstab
=>Add the below line.

/dev/sdc1 /u05 ext3 defaults 1 2


Step 8: Mount the filesystem.
-------

Create a directory for mounting as follows:
[root@testNODE /]# mkdir /u05

Once the directory is created we can use this for mounting purpose.
We have to use the 'mount' command to mount a disk with the option as given below.

[root@testNODE /]# mount -t ext3 /dev/sdc1 /u05
=>The above command is for mounting the partition
Here: -t=>is used to indicate the file system type
ext3=>is the filesystem type i.e extended as per entry in the /etc/fstab
/dev/sdc1=>raw disk partition used for mounting
/u05=>Directory used for mounting

[root@testNODE /]# df -h =>For checking the mount point you have created and diskspace utilization.
Filesystem Size Used Avail Use% Mounted on
/dev/sda6 5.7G 4.0G 1.5G 73% /
/dev/sda7 4.9G 4.1G 515M 90% /u01
/dev/sda3 6.3G 1.7G 4.3G 29% /u03
/dev/sda2 34G 32G 719M 98% /u02
/dev/sda1 487M 17M 445M 4% /boot
tmpfs 2.0G 1.4G 641M 68% /dev/shm
/dev/sdb1 38G 34G 2.1G 95% /u04
/dev/sdc1 50G 180M 47G 1% /u05

Let us do filesystem check now after mounting:

[root@testNODE /]# e2fsck -b 8193 /dev/sdc
e2fsck 1.39 (29-May-2006)
e2fsck: Device or resource busy while trying to open /dev/sdc
Filesystem mounted or opened exclusively by another program?

As you see above our raw disk mounted and ready to use.

Step 9:Granting ownership and permission to 'Oracle' user
-------

Once the directory is mounted now let us grant the ownership to 'Oracle' user so that this disk can be used
for various purposes by 'Oracle' owner.

[root@testNODE u05]# chown oracle:oinstall /u05
[root@testNODE u05]# chmod -R 775 /u05

Here:
chown:Granting ownership to operating system user 'Oracle'.
chmod:Granting permission to operating system user 'Oracle'.


Step 10: Login to 'Oracle' user to verify:
--------


[oracle@testNODE u05]$ mkdir TEST
[oracle@testNODE u05]$ cd TEST
[oracle@testNODE TEST]$ touch test1
[oracle@testNODE TEST]$ ls -altr
total 8
drwxrwxr-x 4 oracle oinstall 4096 May 11 17:21 ..
-rw-r--r-- 1 oracle dba 0 May 11 17:22 test1
drwxr-xr-x 2 oracle dba 4096 May 11 17:22 .

Note:'touch' command is used to create an empty file in linux(unix) for creating empty file with 0 byte size'
----

So our directory '/u05' is ready for use.My friends keep on saying me they need more on unix from DBA's point of view.This is just a basic experiment.

Hope it helps.

Best regards,

Rafi.

Monday, May 9, 2011

Experiment for auditing USERS login information for a period of time for existing users in Database

Hi,
In our development environment we planned to release the disk space and drop those users who have not login to the database since last 2 months,For this purpose
we decided to create an audit table and enable a trigger to track logon details and the OS users who will login to Database for a period of two months,once after knowing these the users who have not logon will will be dropped and the disk space will be recovered.Below are the steps we followed:

Step 1: Crating user audit table
-------

connect sys/manager;

create table
stats_user_logon
(
user_id varchar2(30),
osuser varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;

Step 2: Designing a logon trigger
--------


create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
declare
cnt number;
us varchar2(30);
BEGIN
select user into us from dual;
select count(*) into cnt from stats_user_logon where user_id=us;
if cnt > 0 then
UPDATE stats_user_logon SET LOGON_TIME=to_char(sysdate,'hh24:mi:ss'),LOGON_DAY=SYSDATE where user_id=us;
ELSE
insert into stats_user_logon values(
user,
sys_context('USERENV','OS_USER'),
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate,'hh24:mi:ss'),
null,
null,
null
);
END IF;
END;
/

Step 3:Enable triggers for log on and log off
---------


Alter trigger logon_audit_trigger enable;

Note: When you create the trigger by default it is enable,the above step is only for
reference,we can check this with the help of the below query also:

SQL> select owner||' '||trigger_name||' '||table_owner||' '||status from dba_triggers
WHERE trigger_name like '%LOGON%';

OWNER||''||TRIGGER_NAME||''||TABLE_OWNER||''||STATUS
--------------------------------------------------------------------------------
SYS LOGON_AUDIT_TRIGGER SYS ENABLED


Step 4:Tracking the users from the below query:
-------


SQL> select user_id||' '||osuser||' '||logon_day||' '||logon_time
from stats_user_logon;
2
USER_ID||''||OSUSER||''||LOGON_DAY||''||LOGON_TIME
--------------------------------------------------------------------------------
T1 oracle 17-DEC-10 18:05:20
T2 oracle 16-DEC-10 18:49:32
TEST_PROF aagppp 16-DEC-10 18:58:41
T3 ARL1MAC 17-DEC-10 18:51:43

The above experiment is used to track the present users.I have implemented one more experiment to track the future users with the help of profile that i will be posting soon.

Hope it helps.


Best regards,

Rafi.

Friday, May 6, 2011

Troubleshooting ORA-39083 & ORA-02270

Hi,
When my checked my impdp job logfile I have seen the error ORA-39083 & ORA-02270,
You see such error when the primary key of table is disabled while doing export.
Below is such scenario and the possible solution:


Error details from logfile of impdp:
------------------------------------


ORA-02270: no matching unique or primary key for this column-list
Failing sql is:
ALTER TABLE "TESTOOL"."TEST_LANDSCAPE_DETAIL_QKIT" ADD CONSTRAINT "PRLD_PRLM_FK" FOREIGN KEY ("PRLD_PRLM_FK") REFERENCES "PI OOL"."TEST_LANDSCAPE_MASTER_QKIT" ("PRLM_PK") DISABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:

Process of troubleshooting:
---------------------------


Process:
--------

Compare the source and target environment,Diagnose the issue and resolve it.

Step 1: Check the tables which are having primary key disabled
-------


SQL> select owner||' '||constraint_name||' '||constraint_type||' '||status from dba_constraints where table_name='TEST_LANDSCAPE_MASTER_QKIT';

OWNER||''||CONSTRAINT_NAME||''||CONSTRAINT_TYPE||''||STATUS
------------------------------------------------------------------------
TESTOOL PRLM_PK P DISABLED
TESTOOL SYS_C0015976 C DISABLED
TESTOOL SYS_C0015975 C DISABLED
TESTOOL SYS_C0015974 C DISABLED
TESTOOL SYS_C0015973 C DISABLED
TESTOOL SYS_C0015972 C DISABLED

6 rows selected.

SQL> select owner||' '||constraint_name||' '||constraint_type||' '||status from dba_constraints where table_name='TEST2_LINE_MASTER_QKIT';

OWNER||''||CONSTRAINT_NAME||''||CONSTRAINT_TYPE||''||STATUS
------------------------------------------------------------------------
TESTOOL PLM_PK P DISABLED
TESTOOL SYS_C0015986 C DISABLED
TESTOOL SYS_C0015985 C DISABLED
TESTOOL SYS_C0015984 C DISABLED
TESTOOL SYS_C0015983 C DISABLED
TESTOOL PLM_FK R DISABLED

6 rows selected.

Step 2:Enable the primary keys
------


SQL> ALTER TABLE TESTOOL.TEST_LANDSCAPE_MASTER_QKIT
2 enable CONSTRAINT PRLM_PK;

Table altered.

SQL> ALTER TABLE TESTOOL.TEST2_LINE_MASTER_QKIT
2 enable CONSTRAINT PLM_PK;

Table altered.

SQL> select owner||' '||constraint_name||' '||constraint_type||' '||status from dba_constraints where table_name='TEST_LANDSCAPE_MASTER_QKIT';

OWNER||''||CONSTRAINT_NAME||''||CONSTRAINT_TYPE||''||STATUS
------------------------------------------------------------------------
TESTOOL PRLM_PK P ENABLED
TESTOOL SYS_C0015976 C DISABLED
TESTOOL SYS_C0015975 C DISABLED
TESTOOL SYS_C0015974 C DISABLED
TESTOOL SYS_C0015973 C DISABLED
TESTOOL SYS_C0015972 C DISABLED

6 rows selected.


Step 3: Now do the export,copy the dump file and import in target Database.Remember to disable the primary keys at both source
and Target side.

In the final step copy the new dump file and import into target Database.This time you won't get ORA-02270.

Hope it helps,


Best regards,

Rafi.

Steps for changing archive log mode and archive log destination

Hi,
The below post seems to be very simple but has it importance when it comes to maintaining archive logs and doing Database recovery.

Below are the steps for changing archive log mode:

For changing the archive log mode we have to shutdown the database and we have to do it only it mount state
as follows:

Step 1:
---------

SQL> select name from v$database;

NAME
---------
ORACLE11

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 25
Current log sequence 27
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2:
--------

SQL> startup mount
ORACLE instance started.

Total System Global Area 431038464 bytes
Fixed Size 1375088 bytes
Variable Size 327156880 bytes
Database Buffers 96468992 bytes
Redo Buffers 6037504 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

Step 3:
--------


SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 25
Next log sequence to archive 27
Current log sequence 27

We can change the archive log destination online which is very much useful when your archive destination is almost going to filled.

Changing archive log destination:
Below is a simple command to do it.Here I'm changing from flash recovery area(DB_RECOVERY_FILE_DEST) path(Default location
of archives) to new location.

SQL> select name from v$database;

NAME
---------
ORACLE11

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> ALTER SYSTEM SET log_archive_dest_1='location=D:\Oracle11gDB\archives';

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\Oracle11gDB\archives
Oldest online log sequence 25
Next log sequence to archive 27
Current log sequence 27
(OR)
In Linux OS below command can be used:
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/archives';

System altered.

In my upcoming posts I will explain how this archives are useful for recovery purpose.Some times small things matters the most.

Hope it helps.


Best regards,

Rafi.