Changing SYSADMIN default password

Changing SYSADMIN default password
Hi All,This article is about "Reset of SYSADMIN password".Steps:1. Keep all the services running.Do not shutdown appsTier Or dbTier application2. Login to OS level via applmgr manager userLogin to application server via Operating System User.3. Run environment scriptsa. cd $APPL_TOPrun APPLORA.env$ . ./APPLORA.envb. cd admin (folder)run adovars.env$. ./adovars.envc. cd $FND_TOP/bin (folder)run FNDCPASS to change the SYSADMIN password(IMPORTANT: Change ONLY the SYSADMIN password)eg:FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN {new password}4. Restart the Apache,Forms,Reports, Listener & Concurrent Manager servicesIn short: Restart the dbTier Or appsTier services.5. To test, login into the application as the SYSADMIN user and the new password.6. Verify that a concurrent program such as Active Users runs.eg: Login to application user and run the any concurrent reports.

http://onlineappsdba.blogspot.com/2008/01/r12-collecting-configuration.html

http://onlineappsdba.blogspot.com/2008/01/r12-collecting-configuration.html

Oracle Applications 11i Hot Backup Cloning with Rapid Clone

Source System (PROD):

(a) P4 3.0 GHz System with 2GB RAM and 200 GB HDD (Redhat Linux AS 4)

/d01 ——- 40 GB (Application Tier Files)

/d02 ——- 10 GB (10g Oracle Home)

/d03 ——- 80 GB (Data Files)

/backup —- 100 GB (NFS mount point Shared on TEST Server)

Hostname: prodserver

Application Version: 11.5.10.2

Database Version: 10.2.0.2 Target System (TEST):

(b) P4 2.6 GHz system with 1.5 GB RAM with 300 GB HDD (Redhat Linux AS 4)

/d01 ——- 40 GB (Application Tier Files)

/d02 ——- 10 GB (10g Oracle Home)

/d03 ——- 80 GB (Data Files)

/backup —- 100GB (NFS Share Directory)

Hostname: testserver

Application Version: 11.5.10.2

Database Version: 10.2.0.2

Note: This target System was previously cloned with cold backup. This is second time cloning with Hot Backup from PRODSERVER.

Stage1: Prerequisites:

 ========> Apply OUI22 Patch, 5035661 to every IAS Oracle Home

          and RDBMS Oracle Home to be cloned. 

          If you are having 10g Oracle Home,

          there is no need of applying this patch. 

          You need to apply this patch on IAS Oracle Home 

         (if Database is not 10g)

A. Applying the patch on the iAS $ORACLE_HOME:

   ====================================================

   (a)  Unzip the patch into the  directory:

       $unzip -od /d01/prodora/iAS p5035661_11i_LINUX.zip

   (b)   Source the Apps environment file :

         $. $APPL_TOP/APPSORA.env

   (c)   Change directory to the /appsoui/setup

        $cd $IAS_ORACLE_HOME/appsoui/setup

   (d)  Execute the perl script OUIsetup.pl:

       $perl OUIsetup.pl

  NOTE:

  In the case of a Multi-Node instance, the above process

  should be repeated on the  of each Node.

(B) Applying the patch on the RDBMS $ORACLE_HOME: 

     (This step is not required for my current setup, 

    because my database version is 10g R2)

  (a)  Unzip the patch into the  directory:

       $unzip -od /u01/proddb/9.2.0 p5035661_11i_LINUX.zip

  (b)  Source the DB environment file :

       $. $ORACLE_HOME/PROD_prodserver.env

  (c) Change directory to the /appsoui/setup

      $cd $ORACLE_HOME/appsoui/setup

  (d) Execute the perl script OUIsetup.pl:

      $perl OUIsetup.pl

======> Check all other Requirements as Perl, JRE, JDK, ZIP utilities on Source and Target Nodes as per

        document “Cloning Oracle Applications Release 11i with Rapid Clone”

=======> Apply the Latest AD Minipack on Application Tier (Latest One is AD.I.5)

=======è Apply the Latest Autoconfig Template Patch and Latest Rapidclone Patches to Application Tier (Check Metalink for These Patches)

Stage2: Prepare the Source System (PRODSERVER)

      (a) Login into Database Tier as ORACLE user and run the preclone

           $cd $ORACLE_HOME/appsutil/scripts/PROD_prodserver

           $perl adpreclone.pl dbTier

      (b) Login into the Application Tier as APPLMGR User and run the  preclone

           $cd $COMMON_TOP/admin/scripts/PROD_prodserver

           $perl adpreclone.pl appsTier

Stage3: Put the Database in Begin Backup Mode and copy the Database Files 

       (a)   Login into database as sysdba user

             $sqlplus “/as sysdba”

             Sql> alter database begin backup;

       (b)   Copy Archive log files created during hot backup to /backup directory.

       (c)   Copy the All Data files to /backup directory.

       (d)   Backup the control file to trace.

             Sql> alter database backup control file to trace;

             Copy this trace file to /backup directory

       (e)   Copy the current init.ora file to /backup directory

       (f)   End the Begin Backup Mode.

             Sql> alter database end backup.

 Stage4: Copy the Application Tier File System Files

         (a)Login into the Application Tier as APPLMGR user and copy the APPL_TOP, COMMON_TOP, 

             IAS ORACLE HOME and 8.0.6 Oracle Home to /backup directory

 Stage5: Copy the Source Database files and Application Files to Target server

     Copy the parameter file, backup control file and archive log files from /backup directory  

          to /d01, /d02 and /d03 in target server.

 Stage 6: Configure the Target Database (TESTSERVER)

Log on to the target system as the ORACLE user

(1) Configure the

cd /appsutil/clone/bin

perl adcfgclone.pl dbTechStack

(2) Create the target database control file manually

Open the backed up control file

a. remove all lines before the startup nomount statement

b. Modify the REUSE to SET

c. Modify Source DB SID to Target SID (Here PROD to TEST)

d. Modify NORESETLOGS TO RESETLOGS

e. delete all lines after the CHARACTER SET statement

————————————

CREATE CONTROLFILE SET DATABASE “TEST” NORESETLOGS ARCHIVELOG…

LOGFILEGROUP 1

‘/d03/log01.log’ SIZE 50M,

…

DATAFILE

‘/d03/system01.dbf’,

‘/d03/undotbs01.dbf’,

…

CHARACTER SET UTF8;

—————————————————–

On the target system, modify the init.ora to have the target SID and location of the control file and also make sure that init.ora parameters are set for archive log mode On the target system,

starup the database in nomount stage

sql> startup nomount pfile=<>

sql> @clone.ctl ( here clone.ctl is the control file which we have modified above)

Once control file is created, database will be in mount stage

execute recover command using backup control file after the database is mounted

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

After the last archive log has been applied, issue the following command

SQL> alter database open resetlogs;

After opening the database, add temp files to target database

(3) Run the library update script against the database

cd /appsutil/install/

Where is “sl” for HP-UX, “so” for any other UNIX platform and not required for Windows.

(4)Configure the target database (the database must be open)

cd /appsutil/clone/bin

perl adcfgclone.pl dbconfig

where target context file is: /appsutil/.xml

Stage 7 : Configure the Target Application Tier

Logon to the target system as the APPLMGR user and type the following commands

$Cd $COMMON_TOP/clone/bin

$Perl adcfgclone.pl appsTier

Finishing tasks:

(1) Update Profile options

(2) Update Printer Settings

(3) Update the workflow configuration settings

(4) Verify the APPLCSF variable setting

(5) Update the session_cookie_domain value in icx_parameters

For the finishing tasks, check the finishing tasks section of the following document

Cloning Oracle Applications Release 11i with rapid clone Note: 230672.1

(The major defferenc is there between offline and "begin backup" mode.

Offline: If you put tablespace in offline you can't do any transaction on that perticular tablespace.
Begin backup: You can do transaction on the objects pertaining to this tablespace. only thing is the datafle header will be in freeze state. Once you take out of the backup mode your datafile header will be updated with new SCN no's. )

RMAN - Duplicate Database on the same host

RMAN - Duplicate Database on the same host

Primary DB : ORCL

Clone DB : AUX

Production Database should be archive enabled.

Startup mount;

alter database archivelog;

alter database open;

archive log list;

Recovery catalog for RMAN

Creating the Recovery Catalog Owner

Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant

it the recovery_catalog_owner role. Look at this example:

% sqlplus '/ as sysdba'

SQL> CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE tools
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON tools;

SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman

Creating the Recovery Catalog

% rman catalog rman/rman@ORCL

RMAN> CREATE CATALOG;

Registering the target database

% rman TARGET / CATALOG rman/rman@ORCL

RMAN> REGISTER DATABASE;

Reference : RMAN: How to Query the RMAN Recovery Catalog ( Note:98342.1 )

Example Source Listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /newpart//product/10.2.0/)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = ORCL)

(ORACLE_HOME = /newpart//product/10.2.0)

(SID_NAME = ORCL)

)

(SID_DESC =

(GLOBAL_DBNAME = AUX)

(ORACLE_HOME = /newpart//product/10.2.0)

(SID_NAME = AUX)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

Example Source tnsnames.ora

AUX =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = AUX)(UR=A)

)

)

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

Note : Make use of netca and netmgr to configure listener and tnsnames

# Find Production Database Files:

SQL> select name from v$datafile;

Create the Auxiliary Database directories needed

cd $ORACLE_HOME/dbs

create parameter file initAUX.ora

db_file_name_convert = ('/old/path1', '/new/path1',

'/old/path2', '/new/path2',

'/old/path3', '/new/path3')

log_file_name_convert = ('/old/path1', '/new/path1',

'/old/path2', '/new/path2',

'/old/path3', '/new/path3')

eg:-

db_name = aux

db_block_size = 8192

compatible = 10.2.0.1.0

remote_login_passwordfile = exclusive

control_files = ('/newpart/oradata/aux/control01.ctl',

'/newpart/oradata/aux/control02.ctl')

db_file_name_convert = ('/newpart/oradata/orcl',

'/newpart/oradata/aux')

log_file_name_convert = ('/newpart/oradata/orcl',

'/newpart/oradata/aux')

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

# create a passwordfile for remote connections as sysdba

% orapwd password= file=orapwAUX

% sqlplus /nolog

SQL> connect / as sysdba

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora

SQL> exit

Start the Duplication

ORACLE_SID=AUX; export ORACLE_SID # ksh

sqlplus /nolog

SQL> connect / as sysdba

Connected to an idle instance

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora

SQL> exit

# Set your SID back to the TARGET for duplication.

> rman trace.log

Recovery Manager: Release 10.2.0.1.0 - Production

Copyright (c) Oracle. All rights reserved.

RMAN> connect target

connected to target database: V10GREL4 (DBID=2510891965)

RMAN>backup database;

RMAN>sql 'alter system switch logfile';

RMAN> connect auxiliary sys/pwd@AUX

connected to auxiliary database: AUX (not mounted)

RMAN> duplicate target database to AUX device type disk;

Once this is done, login to duplicate database with alter database open resetlogs.

Configuring Load Balancer and Shared Application Tier for multiple Forms/Web Tier nodes

Configuring Load Balancer and Shared Application Tier for multiple Forms/Web Tier nodes




In this post I will describe the procedure to implement Http Layer load balancer for forms/web tier nodes. This also covers the method to setup your second forms/web tier node from the already running one forms/web node. The following picture describes what we will achieve:

Shared Application Tier

Assumptions:

1. There is already a Forms/web node running. This node will be called primary node.
2. The second node has already been setup and it is exactly similar in configuration to primary node i.e. OS version e.g. Red hat 4.0, OS kernel Parameters, OS patches, OS packages. This node will be called secondary node.
3. The secondary node is in the same network and has all the network related settings already done i.e. Assigned name, had an entry in DNS servers already. There should be ability to NFS mount application tier filesystems from primary node to secondary node.
4. You have setup your hardware level HTTP load balancer (e.g. Cisco, Big IP etc) and it is setup in your network (DNS entries, Ip adress setting have been done already etc..)

Steps:

1. Sharing your existing application file system with secondary node.

a. Verify Software versions: Before doing anything make sure you have the following softwares/patches
i. AD minipack H or higher (patch 2673262)
ii. ADX should be 11i.ADX.E.1 Feb 2005 Consolidated Update. (patch 4175764)
iii. iAS should be Oracle HTTP Server Rollup 4 (patch 3072811)
iv. Developer 6i patchset 16 or higher
v. Oracle Universal Installer should be 2.2.0.19: Apply patch 4017155 to each iAS ORACLE_HOME to have OUI version 2.2
vi. Apply patch 4104924 to each APPL_TOP in the application tier to have TXK AutoConfig Templates to RUP K.
vii. Apply patch 3219567 to each APPL_TOP in the application tier. This patch is included in 11.5.10.
viii. Zip version 2.3

b. Implement AutoConfig if you have not done yet : See metalink note:165195.1

c. Rename the Applications context file: If the Applications system was created with Rapid Install version 11.5.8 or earlier, you must regenerate the Applications context file:
i. cd /bin
ii. perl adclonectx.pl sharedappltop contextfile= Context file name
iii. Running adclonectx.pl creates a new Applications context file named SID_Hostname.xml

d. Run AutoConfig to enable shared file system
i. Run these commands on the primary node where you are enabling shared file system support. After performing this step, re-load your environment settings.
ii. cd /admin/scripts/CONTEXT_NAME
iii. adstpall.sh apps/apps pass
iv. cd FND_TOP/patch/115/bin
v. perl -I AU_TOP/perl txkSOHM.pl

The script prompts for the following information:

Script Prompt

Important thing to note here is Configuration_top. There is an application environment variable called IAS_CONFIG_HOME which will be set to value of Configuration_top that you will provide. For non shared nodes this variable is set to IAS_ORACLE_HOME value. The perl script txkSOHM.pl that you ran above will create the following directories in IAS_CONFIG_HOME:
a. network (all tnsnames.ora etc)
b. Apache (apache config files)
c. oem_webstage (for oem conf files)
d. soap (Jserv soap conf files)
Please note that now your Apache configuration and log directories are under IAS_CONFIG_HOME and not IAS_ORACLE_HOME. This way oracle has placed IAS related configurations to separate directories for the nodes that will be sharing one IAS_ORACLE_HOME, this avoiding any conflict that may arise out of running same executable from two separate machines.

Adding the secondary node to a shared file system:

a. Execute Rapid clone on existing node.
i. cd COMMON_TOP/admin/scripts/CONTEXT_NAME
ii. perl adpreclone.pl appsTier

b. NFS mount the application tier files from primary node to secondary node.
Please note there is a specific way to NFS mount. Please see my blog post in wierdos sections:
NFS mounts with Shared Application Tier
c. Configure the node you want to add: On the secondary node login as applmgr and do the following.
i. cd AD_TOP/bin
ii. perl adclonectx.pl sharedappltop contextfile= Application Context file for existing node
iv. cd FND_TOP/patch/115/bin
v. perl -I AU_TOP/perl txkSOHM.pl

The script will prompt you same question as mentioned above. The only difference is that this time you have to mention “Type of Instance” as “secondary”

At this point your second node is added and ready to run. You can test it by bringing up services on it. Please note that since load balancer is not yet configured, you can only use one node at a time.

Setup HTTP layer Load Balancer the for two nodes
1. Create a web entry point : When you setup your hardware level load balancer there is an option to specify for which URL this load balancer is configured. For example: http://navdeep.practicalappsdba.com. This URL is your “web entry point”. Also make sure that suitable option should be chosen to ensure that the load-balancer sends all the requests from one client session to the same Web Server Node, this is also called as session persistency or “stickyness”. At present oracle supports “Cookie based” and “IP address based” stickyness. Please consult your sysadmins/harware fols for Loadbalancer to know more about which stickyness is available for you.
2. On each Web server node, run the AutoConfig Context Editor : In the Context Detail screen, set the following configuration values:
a. “Web entry point Host” to the HTTP load-balancer machine name.
b. “Web entry point Domain” to the HTTP load-balancer domain name
c. “Web entry protocol” to the HTTP load-balancer protocol e.g. “http” or “https”
d. “Active Web Port” to the value of the HTTP load-balancer’s external port
e. “Login Page” to include “Web entry protocol”://“Web Host entry point”.“Web domain entry point”:“Active Web Port”

a. Here is an example values you will want to give:
i. Web entry point Host = navdeep
ii. Web entry point domain = practicalappsdba.com
iii. Web entry protocol = http
iv. Active Web Port = 8001
v. Login Page = http://navdeep.practicalappsdba.com:8001
3. Run Autoconfig on both the nodes.
4. Bring up the services on both the nodes and change your login URL to the one mentioned above e.g. http://navdeep.practicalappsdba.com:8001, try to login and see if everything is working.

At this point you have implemented shared application tier between your two nodes and placed Http based Hardware load balancer in front of them. If your implementation includes configurator then you will have to enable load balancing at the Apache JServ layer even if your hardware load balancer is configured to maintain session persistency. I will talk about Apache Jserv load balancer in my future post, till then keep reading my posts and good luck ……….

------------------------------------------------------------------------------------
Source :- http://practicalappsdba.wordpress.com/category/for-master-apps-dbas/
------------------------------------------------------------------------------------

Latest FAQ'S

xxxxxxxxxxxxxxxxxxx

Tell me some situations are when database get hang. How you find the problem and solve them.

1. When your online redo log files are missed
2. If all online redo log files are in ACTIVE state
3. If it is a resumable enabled
4. If DDL_WAIT_LOCK_TIME is set
5. If your standby database

is in MRM(Managed Recovery

Mode) in 9i

ii

Database gets hang in following situations:

1. Temp tablespace is full and sorting requires temp tablespace.
2. Undo tablespace is full and some undo is getting generated to be stored in undo datafiles.
3. Database is in archivelog mode and redo is transferring the log member data to archive and yet not finished the transfer. At the same time redo buffers are generated too much and that requires to write in redo log which is still in the process of transferring the log to archive.
4. When long running scripts are running, which can be queried using v$session_longo

Xxxxxxxxxxxxxxxxxxxxxxxxxxxx

What is the crucial situation you got in last two years of carrier while handling production database?

We face the problem in Database in Production environment.
We have two database
1) Primary Database
2) Secondary Database

The Secondary Database which was Physical Standby Database.

We create a new tablespace in Primary Database, for that we create folder for Database file in Disk but we forget to create same folder in Secondary Database(Physical Standby Database). With Result our logfile move to the Secondary (Standby)Database but never apply to the database(Secondary).

It is mandatory for Standby Database is folder structure in terms of Database should be same in Primary & Secondary Database.

we can transfer physical files from producation database

to standby database through ftp.it is do manually.if any matheod to do this automatically?

Yes,
By using rsync we can transfer the files from Production to standby server.

"rsync -av filename stbyserver_location" and schedule this in cronjob

xxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxx

what are the 10g new features in oracle DBA?

Some of the new features in Oracle 10g for DBAs are:
1. Oracle 10g Utility : Data Pump

2. Automatic Shared Memory Management
3. Automatic Workload Repository
4. Tuning Advisors
5. Automatic Database Diagnostic Monitor
6. SQL Tuning Advisor
7. Server

generated alerts
8. Automatic Optimizer Statistics
9. OEM Enhancements
10. RBO desupported

SysAux Tablespace
Default tablespace for whole DB
Bigfile tablespace

xxxxxxxxxxxxxxxxxxxxxxxxxx

what is diff between mount and nomount stage of a databse?

Oracle database

startup belongs three mode,such as:
Nomount:
> In nomount stage instance has been started using the initialization parameter file plus and background process.

Mount:
> In mount stage oracle read controlfile and it's contents. follow up the controlfile oracle database goes to startup

ii

Three modes of startup of a database.
1. Nomount - Reads init parameter. Memory[SGA] and background processes are started. On unix prompt do ps -ef | grep pmon to see process monitor started, one of the background process in Oracle.

2. Mount - Reads control file. Location of datafile, redolog file is understood to Oracle.

3. Open - Opens the datafiles and Redolog files for read and write operations.

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

how to see alert log file for errors?

goto altert lig file address and open yext format alert log file(bdump file have alert log file).

Ii

goto altert lig file address and open yext format alert log file (bdump folder have alert log file).

Iii

Background dump destination [bdump] has alert_SID.log file.
To see background dump destination do this:

SQL> show parameter backg;
NAME TYPE VALUE
-------------------- ----------- ------------------------------
background_dump_dest string C:\ORACLE\PRODUCT\10.1.0\ADMIN
\ORCL\BDUMP
Go to BDUMP directory and do ls -ltr alert_SID.log on unix prompt.
do tail -f to see the latest information in the alertlog.

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

What are the real time issues faced by DBA class=preloadImg v:shapes="_x0000_i1026">

S?

The Real Time issue faced by DBA is
1. Performance

issue
2. Space Problem
3. Access of Object
4. Cloning of database
5. Export of Database

Ii

1. Resize Datafiles
2. backup

class=preloadImg v:shapes="_x0000_i1029">

3. Upgrade
4. Clone / Duplicate etc

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

What are the New features of 11g?

1)flashback archivelog has been introduces in 11g
2)bdump,cdump,udump replaced with diagnostic_dest="location"
3)snapshot standby database introduced in 11g
4)we can make the indexes invisible from 11g.(optimizer will not use these invisible indesxes)
5)11g rman enhancements------data recovery adviser,proactive health check,zlib compression,parallel backup of a file,virtual private catalog
we have some more...............

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

if my database

size 100gb what is the size of sga?

it depends on the no users are connecting to database;

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

my database

is p and running...i lost my control file.... after that what will happen if i give the command like " alter system backup control file to trace" it will work or not..........?

ORA-00210: cannot open the specified control file

Ii

If the control file is not there, it will give the error as it is not identifying any control file. The command will not work. If you have executed the command alter database backup controlfile to trace; then you can recreate control file from this file.

Iii

alter database backup controlfile to trace; If you have been executed the command before the loss of control file, at that time you can recreate the control file from this file which will be stored in the trace file.

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

what is the meaning of roll forward and roll backward?

Rollforward is related to REDO and Roll backward is related to UNDO.

Roll forward means when instance is failed, Datas will be retrieved from Online redo log files, Roll backward means datas will be retrieved from undo TS

Xxxxxxxxxxxxxxxxxxxxxxxxxxx

what are the types of logs?

The physical architecture

class=preloadImg v:shapes="_x0000_i1032">

comprises of two type of log file
1) Redo Log files
2) Archive Log file(if archive mode is enabled)


Besides there are many logs and trace files which located in the ORACLE_HOME through which a DBA

can asses the status of an database

(single instance or RAC node). The main log is the alert_SID.log located in the ORACLE_HOME/bdump folder which stores useful information regarding checkpoints,startup and shutdown etc.

Xxxxxxxxxxxxxxxxxxxxxxxxxxx

What are the different high availability solutions provided by Oracle?

• Oracle Real Application Clusters
• Oracle Clusterware
• Oracle Data Guard
• Oracle GoldenGate
• Oracle Streams
• Oracle Secure Backup

• Recovery Manager (RMAN)
• Flashback Technologies
• Oracle VM
• Oracle Cloud Computing
• Automatic Storage Management (ASM)
• Cross-Platform Transportable Tablespace
• Online Reorganization

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

i need cold backup script in linux?

Script about cold backup
1: Shutdown the database gracefully(normal, transactional, immediate).
2: Make one back destination directory.
3: Copy all the files to destination directory.
4: Startup

Ii

Go to rman prompt.
RMAN> connect target /
Then write a small piece of code as below.
RMAN> run
( allocate channel ch1 type disk format '%t_%s_%r';
backup database;
alter database open;
)

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

how identify that the perticular table has been exported or not in its lifetime.

Export Information gets updated in the following sys tables
SYS.INCEXP SYS.INCFIL SYS.INCVID

desc SYS.INCVID
EXPID

desc SYS.INCEXP
OWNER#
NAME
TYPE#
CTIME
ITIME
EXPID


desc SYS.INCFIL

EXPID
EXPTYPE
EXPFILE
EXPDATE
EXPUSER
by quering these system table you can find out

Xxxxxxxxxxxxxxxxxxxxx

I created a script in RMAN for incremental level 1 backup. But i forgot that script name. Now i wants to execute that script. How can i find that script name that is created by me? Which view i can use?

RMAN>LIST SCRIPT NAMES

Xxxxxxxxxxxxxxxxxxxxxxxx

What is the difference between whole database backup and full database backup?

Well whole database backup includes backing up the datafile,the archive files(if archiving is enabled), control file also including the parameter file and the password file.
Whereas in a full backup only the total backup of the datafiles is taken.

Xxxxxxxxxxxxxxxxxxxxxxx

How do you set up your tablespace on installation?

On Oracle Installation there is no need to setup/create the tablespace. At the time of Database Creation we need to setup/create the Tablespace.

Xxxxxxxxxxxxxxxx

In RMAN can we do complete & incomplete recovery ? how ?

Yes.. We can do the following incomplete recovery in RMAN.
1.Time based
2.SCN based
3.Log Sequence based
It can be done by using the command
"set until time/scn/logseq ...." before restoring the backup.

Xxxxxxxxxxxxxxxxxxxxxxxxx

where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed.?

You can look in the init.ora file for an indication or manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.

Ii

select name,value,isdefault from v$parameter order by isdefault,name;
isdefault = false shows those values has been changed from default

iii

At SQL prompt type this
SQL> SHOW PARAMETER

Xxxxxxxxxxxxxxxxxxxxx

when should you increase copy latches. What parameters control copy latches.?

when you get excessive convention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter log_simultaneous_copies to twice the number of cpu's on your system.

Xxxxxxxxxxxxxxxxxxxx

when looking at v$sysstat you see that sorts(disk) is high. Is this bad or good?

If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort parameter is the sort_area_size parameter.

Ii

this is bad.....you need to increase the sort area by setting the parameter sort_area_size.

Xxxxxxxxxxxxxxxxxxxx

Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?

the tkprof tool is a tuning tool used to determine cpu and execution times for sql statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql-trace parameter or for the session using the alter session command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

sql_trce=true;
timed_statistics=true;

xxxxxxxxxxxxxxxx

what are some indications that you need to increase the shared_pool_size parameter?

Poor data dictionary or library cache hit ratios, getting error ora-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.

Xxxxxxxxxxxxxxx

a tablespace has a table with 30 extents in it. Is this bad? Why or why not.

multiple extents in and of themselves are not bad. However if you also have chained rows this can hurt performance.

Xxxxxxxxxxxxxxxx

how do you generate file output from sql.

By use of the spool command.

Xxxxxxxxxxxxxxx

what is explain plan and how is it used.

the explain_plan command is a tool to tune sql statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the sql statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.

Xxxxxxxxxxxxxxxxx

What is tkprof and how is it used.?

The tkprof tool is a tunning tool used to determine cpu and execution times for sql statements. Use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the alter session command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

Ii

tkproof can give the exact statistics about how long the sql-stmt took to run(in sec) and also what was the access path it has been used in the executin........
you need to set two parameters in parameter file
1)sql_trace=true;
2)timed_statistics=true;
after setting above parameters run sql-stmt
after that goto trace file location...and select brand new trace file.
you need to run the tkproof utility on the top of this trace file....
$tkproof abcd.trc abcd.log explain=scott/tiger;

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

Ii

ORACLE_BASE is the root directory for the oracle products.
ORACLE_HOME is the directory

where the oracle software is located.

Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

How would you go about generating an explain plan?

Create a plan table with utlxplan.sql. Use the explain plan set statement_id='tst1' into plan_table for a SQL statement. Look at the explain plan with utlxplp.sql or utlxpls.sql.

Xxxxxxxxxxxxxxxxxxxxxxxxxx

What command would you use to create a backup control file?

Alter database backup

control file to trace.

Xxxxxxxxxxxxxxxxxxxx

what is the pre-requirement of installing oracle 10g on linux?

1. Checking Memory and Swap Space
2. Checking /tmp Space (OUI requires 400MB)
3. Checking Software Packages (RPMs)
4. Checking Kernel Parameters
5. Sizing Disk Space

for Oracle10g
6. Creating Oracle User Accounts
7. Creating Oracle Directories
8. Setting Oracle

Environments(bash_profile)
9. Installing Oracle10g

Xxxxxxxxxxxxx

ora 600 andora 155 how can you resolve that errors?

The ORA-00600 error is a generic internal error from Oracle, and you must look-up the numeric arguments to understand the nature of the internal error.

Xxxxxxxxxxxxxx

in imports we use ignore=y what is the use of it?

ignore=y used this keyword at the time of imp job,to ignore errors

ii

ignore=y keyword is ignore error for miss match etc.

xxxxxxxxxxxxxxx