ORACLE APPS DBA

Excellent Deal To Developer or Functional Consultant Install setup of Orace EBS 11i or R12 or OFA

2014-09-19T21:47:00.001-07:00

PLEASE NOTE:

All software's listed on this site are only IDES or Vision versions. This software's will only enable you to train as a Oracle professional and also practice as if you are in real time production environment. These are meant strictly for educational/informational use only; these are not suitable to run any commercial applications. Full commercial license can be purchased only from Oracle Corporation .

Dear Sir,
I am a Oracle Consultant,

We deal with All Oracle Software's, Installations

Call Javeed :+91-9160903479
Our services Are :

a.We Do Oracle Installations @ UR Door Step.

b.We provide Pre-Installed Oracle, USB 3.0 External Hard disks.

c.We will do the Remote Installations with Online Support(We do Oracle Installation in all over INDIA, US, UK, GULF, ASIA and all over the World)

We provide Pre-Installed Oracle External Hard disks are Compatible with Windows Xp,Windows7 & Windows 8 Laptops also.

1. Oracle Application 11i Installation
2. Oracle Application R12.1.1 Installation
3. Oracle Application R12.1.3 Installation
4. Oracle Application EBS R12.2 Installation
5. Oracle Fusion Application 11.1.8 Installation
6. Oracle Database (9i,10g,11g,12c) Installation
7. Oracle Rac (10g,11g,12c) Installation
8 Oracle External Hard disk with (9i,10g,11g,11i,R12,12c)
9. Others Oracle Issues

Thanks,

Javeed

Oracle Apps (E-Business Suite) 12.2 installation steps

2014-02-19T10:26:00.002-08:00

Oracle Apps (E-Business Suite) 12.2 installation steps
1. Download software from eDelivery .

1.1 Download following zip files for Linux
V29764-01.zip V35231-01_4of5.zip V35806-01_1of3.zip V37515-01_1of6.zip V29856-01.zip V35231-01_5of5.zip V35806-01_2of3.zip V37515-01_2of6.zip V35215-01_1of3.zip V35802-01.zip V35806-01_3of3.zip V37515-01_3of6.zip V35215-01_2of3.zip V35803-01_1of3.zip V35807-01.zip V37515-01_4of6.zip V35215-01_3of3.zip V35803-01_2of3.zip V35808-01.zip V37515-01_5of6.zip V35230-01_1of2.zip V35803-01_3of3.zip V35809-01.zip V37515-01_6of6.zip V35230-01_2of2.zip V35804-01_1of2.zip V35810-01.zip V39615-01.zip V35231-01_1of5.zip V35804-01_2of2.zip V35811-01.zip V35231-01_2of5.zip V35805-01_1of2.zip V35812-01.zip V35231-01_3of5.zip V35805-01_2of2.zip V35813-01.zip

2. Setup staging area (You can either unzip all files downloaded earlier or follow below process)
2.1 unzip StratHere DVD (for Linux)
unzip V35215-01_1of3.zip
2.2 Start buildStage script
[R12.2_SOFTWARE]/startCD/Disk1/rapidwiz/bin
./buildStage.sh
2.3 select option 1 i.e. Create New Stage
2.3 Select Option 2 Linux x86-64
2.4 Please enter the directory containing the zipped installation media: [Enter directory location where all the zip files are downloaded]
/stage/oracle/r12/12.2/
You should see output like
__________
Unzipping V35810-01.zip
Unzipping V35803-01_3of3.zip
Unzipping V35231-01_5of5.zip
.
.
.
Verifying stage area…
Directory /stage/oracle/r12/12.2/TechInstallMedia is valid
Directory /stage/oracle/r12/12.2/TechPatches/DB is valid
Directory /stage/oracle/r12/12.2/TechPatches/MiddleTier is valid
Directory /stage/oracle/r12/12.2/EBSInstallMedia/AppDB is valid
Directory /stage/oracle/r12/12.2/EBSInstallMedia/Apps is valid
Directory /stage/oracle/r12/12.2/EBSInstallMedia/AS10.1.2 is valid
Directory /stage/oracle/r12/12.2/TechInstallMedia/database is valid
Directory /stage/oracle/r12/12.2/TechInstallMedia/ohs11116 is valid
Directory /stage/oracle/r12/12.2/TechInstallMedia/wls1036_generic is valid
__________

2.5 Staging Area should look like below

3. Create Operating System User depending on Single User or Multi UserInstallation :
You can do
a) Single -User Installation : oracle where both Application Tier and Database Tier are owned by single user
b) Multi-User Installation : oracle & applmgr where Application Tier is owned by one user (applmgr) and Database Tier by another (oracle)
Note: I am doing multi-user installation and using applmgr & oracle both member of group dba
groupadd dba
useradd -g dba applmgr
useradd -g dba oracle

4. Start Installer from root user
cd $R12.2_SOFTWARE/startCD/Disk1/rapidwiz/
./rapidwiz
5. In Wizard Operation screen select : Install Oracle E-Business Suite Release 12.2.0

1. Start EBS 12.2.x Installer from root user as
cd $R12.2_SOFTWARE/startCD/Disk1/rapidwiz/
./rapidwiz

2. Select Port Pool : Select port pool for services as you used to do in previous versions 12.1.x or 11i
Note: Oracle Apps 12.2 uses dual file system . more on dual file system in 12.2 later.

3. Select Database Type
a) Fresh Database : Use this for production or where you don’t need vision environment.
b) Vision Database : Use this option if you need vision data in R12.

Note: Base directory (/d01/oracle/prod) mentioned above should already be created and owned by Database user (oracle:dba) defined above.

4. License Additional Products : If you need any additional products you can license it now .
Note: You can license addtional products after installation as well.

5. If you are planning to use multi-language (language other than american english) then recommendation is to select Databse character set as AL32UTF8

6. Provide Application Tier details
Note: Apps OS user (applmgr:dba) should be able to create directory under Base directory (/d01/oracle/prod) .
Note: I created application tier user applmgr and database tier user oracle using same group i.e. dba

More on dual file system i.e. File System 1 & File System 2 later.
7. Provide Application Tier & database user password .
Note: WLS Admin User is WebLogic Server Administrator user used to login to WebLogic Console . WebLogic Server is newly introduced in Oracle Apps 12.2

8. If you plan to deploy application tier on multiple nodes then click on Add Server button at bottom.
Note: I am doing single node installation i.e. Application Tier and Database Tier on single host/machine.

9. Installer will validate system configuration and if any check fails then click on ! next to check .
Note: In my case Port Availablity and File Space checks failed. After fixing failed check click on Retry button

a) Port Availaibity check : In my case port 8899 was in use
To Identify which process is using port 8899 in linux run “netstat -tulpn | grep [Port_no]“
[atul@innowave21 ~]$ netstat -tulpn | grep 8899
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 :::8899 :::* LISTEN 4536/java
In my case Process ID 4536 was using port 8899, stop the process using 8899 or use different port pool in previous screen.

10. Click Next and then Install when all checks complete successfully.

11. After installation , access Application as http://host:port where port number is 8000+ port pool, as I use port pool 10 URL should be http://host:8010
12.2 comes with the dual file system. One for PROD purpose and one for patching purpose.So there is not need to bring down EBS services for patching.

Overcoming Oracle Apps R12 FRM-92101 Error On Redhat Linux 5.6

2013-07-19T05:37:00.002-07:00

Have you ever got the error, FRM-92101 on with Oracle Applications R12 on 64 bit Redhat Linux 5.5? Here is how to overcome it.

Oracle Forms Runtime Error FRM92101

Here is my environment:
Server: Redhat Linux 5.5
lsb_release -a
LSB Version: :core-3.1-amd64:core-3.1-ia32:core-3.1-noarch:graphics-3.1-amd64:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: EnterpriseEnterpriseServer
Description: Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Release: 5.5
Codename: Carthage
uname -a
Linux black.testrac.com 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
select
APPLICATIONS_SYSTEM_NAME
, ARU_RELEASE_NAME
, release_name
from apps.fnd_product_groups
APPLICATIONS_SYSTEM_NAME ARU_RELEASE_NAME
—————————— ——————-
RELEASE_NAME
————————————————–
VIS R12
12.1.1
———–
Prerequisites:
The java is working in your browser. To test whether Java is working in the web browser, run this in the URL.
http://java.com/en/download/testjava.jsp
You have overcome the error concerning: NPX_PLUGIN_PATH, and oaj2se.exe. See my previous post.
http://rodgersnotes.wordpress.com/2012/01/20/firefox-oracle-apps-java-plugins-on-64-bit-redhat-5-5/
———–
Symptom:
Start the database, listener, and all the Oracle Apps processes. Open a browser. Test an Oracle Apps Form with the forms test webpage. In my case:
file:///oapps/applmgr/VIS/apps/tech_st/10.1.2/tools/web/html/runform.htm
You get a java window open. But you get the error: FRM-92101 There was a failure in the Forms server during startup. This could happen due to an invalid configuration. Please look into the web server log for the details.
Click on Details:
Java exception:
oracle.forms.net.ConnectionException: forms session <1> failed during startup: no response from runtime process
at oracle.forms.net.ConnectionException.createConnectionException(Unknown source)
at oracle.forms.net.HTTPStream.getResponse(Unknown source)
at oracle.forms.net.HTTPStream.doFlush(Unknown source)
at oracle.forms.net.HTTPStream.Flush(Unknown source)
at java.io.DataOutputStream.flush (DataOutputStream.java:106)
at oracle.forms.net.HTTPConnection.connect(Unknown source)
at oracle.forms.engine.FormsDispatcher.initConnection(Unknown source)
at oracle.forms.engine.FormsDispatcher.init(Unknown source)
at oracle.forms.engine.Runform.initConnection(Unknown source)
at oracle.forms.engine.Runform.startRunform(Unknown source)
at oracle.forms.engine.Main.createRunform(Unknown source)
at oracle.forms.engine.Main.start(Unknown source)
at sun.plugin2.applet.Plugin2Manager$AppletExecutionRunnable.run(Plugin2Manager.java:1698)
at Java.lang.Thread.run(Thread.java:662)
————
Check the Oracle Forms Log:
$LOG_HOME/ora/10.1.3/opmn/forms_default_group_1/formsstd.out
In my case:
/oapps/applmgr/VIS/inst/apps/VIS_black/logs/ora/10.1.3/opmn/forms_default_group_1/formsstd.out
tail -100 /oapps/applmgr/VIS/inst/apps/VIS_black/logs/ora/10.1.3/opmn/forms_default_group_1/formsstd.out
…
12/01/21 12:32:08 FormsServlet init():
configFileName: /oapps/applmgr/VIS/inst/apps/VIS_black/ora/10.1.2/forms/server/appsweb.cfg
testMode: false
12/01/21 12:32:08 Oracle Containers for J2EE 10g (10.1.3.4.0) initialized
12/01/21 13:04:27 ListenerServlet init()
12/01/21 13:04:28 Forms session <1> aborted: runtime process failed during startup with errors /oapps/applmgr/VIS/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXm.so.2: cannot open shared object file: No such file or directory
12/01/21 13:19:53 Forms session <2> aborted: runtime process failed during startup with errors /oapps/applmgr/VIS/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXm.so.2: cannot open shared object file: No such file or directory
————–
And Check The File, application.log:
tail -100 /oapps/applmgr/VIS/inst/apps/VIS_black/logs/ora/10.1.3/j2ee/forms/forms_default_group_1/application.log
12/01/21 13:04:27.314 formsweb: ListenerServlet init()
12/01/21 13:04:28.455 formsweb: Forms session <1> aborted: runtime process failed during startup with errors /oapps/applmgr/VIS/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXm.so.2: cannot open shared object file: No such file or directory
12/01/21 13:04:28.455 formsweb: Forms session <1> exception stack trace:
oracle.forms.engine.RunformException: Forms session <1> failed during startup: no response from runtime process
at oracle.forms.servlet.RunformProcess.connect(Unknown Source)
at oracle.forms.servlet.RunformProcess.dataToRunform(Unknown Source)
at oracle.forms.servlet.RunformSession.dataToRunform(Unknown Source)
at oracle.forms.servlet.ListenerServlet.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:619)
————–
Cause:
The cause is not unusual with Linux, or Oracle, or Oracle Apps. It’s that Oracle Forms is looking for a library file. But the file doesn’t exist in the expected place. Nor does a link. Not again.
In this case, libXm.so.2 can’t be found.
———-
Analysis:
Find the files. As root, do lots of linear searches all over the hard drive looking for library files.
find / -type f -ls 2> /dev/null | grep -i “libXm.so”
…
214067513 2692 -rwxr-xr-x 1 root root 2747528 Jun 6 2007 /usr/lib64/libXm.so.3.0.2
214054985 2776 -rwxr-xr-x 1 root root 2836528 Mar 4 2010 /usr/lib64/libXm.so.4.0.1
214067522 2492 -rwxr-xr-x 1 root root 2542828 Jun 6 2007 /usr/lib/libXm.so.3.0.2
1802278 2584 -rwxr-xr-x 1 root root 2637260 Mar 4 2010 /usr/lib/libXm.so.4.0.1
…
I did have more occurences of this file. But they were under different subdirectories.
/oapps/oracle/VIS/db/tech_st/11.1.0/lib32/stubs
/middleware/Oracle_BI1/lib32/stubs
The directories under /usr are the critical ones.
You can find any existing links with:
find / -type l -ls 2> /dev/null | grep -i “libXm.so”
—————
File Types:
file /usr/lib64/libXm.so.4.0.1
/usr/lib64/libXm.so.4.0.1: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), stripped
file /usr/lib/libXm.so.3.0.2
/usr/lib/libXm.so.3.0.2: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), stripped
—————
Find or Verify the Packages Needed For The Library Files:
Reading other webpages (below), these library files are provided by the package: openmotif22-2.2.3-18.
I had installed everything, every last package, when I installed Redhat. It’s to avoid nonsense like this. So, I already the package installed. If you don’t have the package installed, download it, and install it. rpmfind.net is a good place to start.
Another time, I’d stuggled with a similar installation. In my notes, I was concerned with three library files: libMrm.so, libUil.so, and libXm.so. So, I leveraged my notes, and looked for all three. Turns out they are all owned by the same package: openmotif.
—–
rpm -q -f /usr/lib64/libXm.so.4.0.1
openmotif-2.3.1-2.el5_4.1
—–
rpm -q -f /usr/lib/libXm.so.3.0.2
openmotif22-2.2.3-18
—–
What Are All The Files Associated With That Package?
rpm -q openmotif22 -l | sort | more
/usr/lib64/libMrm.so.3
/usr/lib64/libMrm.so.3.0.2
/usr/lib64/libUil.so.3
/usr/lib64/libUil.so.3.0.2
/usr/lib64/libXm.so.3
/usr/lib64/libXm.so.3.0.2
….
/usr/lib/libMrm.so.3
/usr/lib/libMrm.so.3.0.2
/usr/lib/libUil.so.3
/usr/lib/libUil.so.3.0.2
/usr/lib/libXm.so.3
/usr/lib/libXm.so.3.0.2
/usr/share/doc/openmotif22-2.2.3
…
This query obviously finds both the 32 and 64 bit files.
—————–
—————–
Analysis For Links:
cd /usr/X11R6/lib
Original files:
/usr/lib/libMrm.so.3.0.2
/usr/lib/libMrm.so.4.0.1
/usr/lib/libUil.so.3.0.2
/usr/lib/libUil.so.4.0.1
/usr/lib/libXm.so.3.0.2
/usr/lib/libXm.so.4.0.1
—–
The application, is looking for files in the directory: /usr/X11R6/lib
But it does not exist. Create it.
As root:
mkdir -p /usr/X11R6/lib
—–
Make Links To The Original Files:
cd /usr/X11R6/lib
ln -s /usr/lib/libMrm.so.3.0.2 .
ln -s /usr/lib/libMrm.so.4.0.1 .
ln -s /usr/lib/libUil.so.3.0.2 .
ln -s /usr/lib/libUil.so.4.0.1 .
ln -s /usr/lib/libXm.so.3.0.2 .
ln -s /usr/lib/libXm.so.4.0.1 .
The above ^ will have a link, the same name as the original file.
—–
Make Links With An Abbreviated Name:
ln -s /usr/lib/libXm.so.3.0.2 libXm.so.3
ln -s /usr/lib/libXm.so.4.0.1 libXm.so.4
ln -s /usr/lib/libXm.so.4.0.1 libXm.so
ln -s /usr/lib/libUil.so.4.0.1 libUil.so.4
ln -s /usr/lib/libUil.so.3.0.2 libUil.so.3
ln -s /usr/lib/libUil.so.4.0.1 libUil.so
ln -s /usr/lib/libMrm.so.4.0.1 libMrm.so.4
ln -s /usr/lib/libMrm.so.3.0.2 libMrm.so.3
ln -s /usr/lib/libMrm.so.4.0.1 libMrm.so
—–
At this point,I still got the error: FRM-92101
Of course, the software is still looking for libXm.so.2, but I had not yet created the link it.
As root:
cd /usr/X11R6/lib
ln -s /usr/lib/libMrm.so.3.0.2 libMrm.so.2
ln -s /usr/lib/libUil.so.3.0.2 libUil.so.2
ln -s /usr/lib/libXm.so.3.0.2 libXm.so.2
———–
Try again. It works. In the sense that I can get a login form.

Oracle Applications Login

However, when I try logging in as sysadmin, OPERATIONS, or APPS, I now get another error:
APP-FND-01542: The applications servers is not authorized to access this database.
Are these not the right users to login with? If so, when do the errors ever stop? If not, what are the right users to login with? Please leave a comment.
—————
Useful Links:
Test whether Java is working in the web browser.
http://java.com/en/download/testjava.jsp
Installing Oracle E-Business 12.1.1 on Oracle Enterprise Linux 5.4 64-bit
http://www.oracle-developer.com/installing_oracle_r12_rhel5_64bit.html
Installing Oracle E-Business 12.1.1 on Oracle Enterprise Linux 5.4 64-bit
http://www.pythian.com/news/1265/installing-oracle-r12-ebs-in-my-living-room/
Oracle® Fusion Middleware Release Notes 11g Release 1 (11.1.1) for Linux x86 E10133-25 44 Oracle Forms
http://docs.oracle.com/cd/E23549_01/relnotes.1111/e10133/forms.htm

UPGRADE 11.1.0.7 DATABASE TO 11.2.0.3 DATABASE WITH R12.1.3

2013-03-10T16:04:00.000-07:00

11.2.0.3 is the latest release (at the time of writing this post) for the Oracle database. The intent of this guide is to upgrade the current database version from 11.1.0.7 to 11.2.0.3.

The installation procedure for the new Oracle Database 11g Release 2 (11.2) installs the Oracle software into a new Oracle home. This is referred to as an out-of-place upgrade and is different from patch set releases for earlier releases of Oracle Database, where the patch set was always installed in place. Oracle strongly recommends that you follow the steps in this procedure to ensure minimal downtime for the upgrade process and integrity of the new binaries and software libraries.

Also, Manual upgrade method has been used instead of DBUA for better control over the complete upgrade process.

Linux x86-64 is used as platform in the below guide

SOFTWARE SOURCES

You can obtain the 11.2.0.3 software from patch 10404530 on My Oracle Support

Files 1, 2 and 3 are sufficient to cover 11.2.0.3 database (including grid infra)

Part 1: INSTALL ORACLE 11.2.0.3 SOFTWARE

1.1) Check oratab file

Ensure that the oratab file contains an entry for the database to be upgraded

1.2) Check for installed languages

SQL> select LANGUAGE_CODE,LANGUAGE_ID,NLS_LANGUAGE,INSTALLED_FLAG from fnd_languages where INSTALLED_FLAG in (‘I’,'B’);

LANG LANGUAGE_ID NLS_LANGUAGE I

—- ———– —————————— -

US 0 AMERICAN B

CS 30 CZECH I

D 4 GERMAN I

1.3) Install 11g database software

cd <software location>

./runInstaller

Below are the details of OUI screen and actions.

Screen 1: Configure Security updates

Leave blank and press next

Screen 2: Download Software updates

Check ‘Skip software updates’ and press next

Screen 3: Select Installation option

Check ‘Install database software only’ and press next

Screen 4: Grid Installation options

Check ‘Single Instance database installation’ and press next

Screen 5: Select Product Languages

Choose English & all other languages required and press next

NOTE : You may select only ‘English language’ even if on EBS side you have many languages installed. The languages that we select during database install/upgrade through OUI are not related to what languages we install in EBS. If you select languages other than the English language, the Oracle installer will bring over extra *.msb files for the languages selected and the user/developer will be able to see ”core” ora- messages in languages other than English too.

Screen 6: Select database edition

Check ‘EnterpriseEdition’

Screen 7: Specify Installation location

Input values of ‘Oracle base’ and press next

Screen 8: Privileged operating system groups

Take dba (or whatever group you created ) as OSDBA Group and press next

Screen 9: Perform prerequisite checks

Correct if anything is required to (by fix & check again) and press next

Screen 10: Summary

Check and press Install and press next

Screen 11a: Install

Installation will proceed.

Screen 11b: run root.sh scripts as instructed

Screen 12: Finish

This completes the database 11g installation.

Part 2: PRE-UPGRADE STEPS

2.1) Take Invalid Count

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS=’INVALID’;

2.2) Apply mandatory patches

Category a) For customers on E-Business Suite Release 12.1, apply:

Check in ad_bugs and apply whichever patch is not available. All the below patches need to be applied by adpatch

1) 9062910 (11g Release 2 interoperability patch for Release 12.1 )

2) 8919489 (12.1 TXK Delta 3 patch)

3) 9868229

4) 10163753

Category b) ) On 11.2.0.3 home, apply additional 11.2.0.3 RDBMS patches :

Apply the following patches (OPATCH) for all UNIX/Linux platforms:

NOTE: Do not run any of the post install instructions as those will be done after the upgrade.

1) 4247037

There is a Special post patch Instructions (mentioned below), execute ONLY after database upgrade part is done

Post install steps:

cd $ORACLE_HOME/md/admin

connect to the database using sqlplus / as sysdba

sqlplus> @catmgdidcode

2) 9858539

There is a post patch install Instructions (step 3, mentioned below), execute ONLY after database upgrade is done

Post install steps:

Run following files as sysdba

cd $ORACLE_HOME/rdbms/admin

@catnomtt.sql

@catnomta.sql

@dbmsmeta.sql

@dbmsmeti.sql

@dbmsmetu.sql

@dbmsmetb.sql

@dbmsmetd.sql

@dbmsmet2.sql

@catmeta.sql

@prvtmeta.plb

@prvtmeti.plb

@prvtmetu.plb

@prvtmetb.plb

@prvtmetd.plb

@prvtmet2.plb

@catmet2.sql

-- recompile dbms_metadata_int to enable the diffing code

alter package dbms_metadata_int compile plsql_ccflags = 'ku$xml_enabled:true';

-- recompile dbms_metadata_util to enable the xmlschema load code

alter package dbms_metadata_util compile plsql_ccflags = 'ku$xml_enabled:true';

3) 12942119

4) 12960302

5) 12985184

6) 13001379

7) 13004894

8) 13258936

9) 13366268

2.3) Run utlu112i.sql on existing 11.1.0.7 oracle database

Copy the Pre-Upgrade Information Tool (utlu112i.sql) from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle Home, such as the temporary directory(/tmp) on your system.

Connect via existing 11.1.0.7 database and run utlu112i.sql

Below issue were found and were corrected

ISSUE 1

———-

**********************************************************************

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

**********************************************************************

–> plsql_native_library_dir 11.2 OBSOLETE

–> plsql_native_library_subdir_ 11.2 OBSOLETE

SOLUTION 1

—————-

commented out below values in init file

#plsql_native_library_dir = /u01/oracle/<instance_name>/db/tech_st/11.1.0/plsql/nativelib

#plsql_native_library_subdir_count = 149

ISSUE 2

———-

WARNING: –> Database is using a timezone file older than version 14.

…. After the release migration, it is recommended that DBMS_DST package

…. be used to upgrade the 11.1.0.7.0 database timezone version

…. to the latest version which comes with the new release

SOLUTION 2

————–

-ignore

Upgrade to 11.2.0.3. No need to apply DST patches on the 11.1.0.6 or 11.1.0.7 side first. You can skip any DST related upgrade instructions.

The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 11.1.0.6 or 11.1.0.7.

ISSUE 3

———–

WARNING: –> Your recycle bin contains 99 object(s).

…. It is REQUIRED that the recycle bin is empty prior to upgrading

…. your database. The command:

PURGE DBA_RECYCLEBIN

SOLUTION 3

—————

ISSUE 4

———-

Oracle recommends gathering dictionary statistics prior to

upgrading the database.

To gather dictionary statistics execute the following command

while connected as SYSDBA:

SOLUTION 4

—————-

ISSUE 5

———-

Oracle recommends removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command

while connected AS SYSDBA:

SELECT name,description from SYS.V$PARAMETER WHERE name

LIKE ‘\_%’ ESCAPE ‘\’

Changes will need to be made in the init.ora or spfile.

SOLUTION 5

————–

Comment out all hidden variables in init file

2.4) Shut down Applications server processes and database listener

shut down all the application services running on Apps tier and also the database listener ( keep the database running)

2.5) Run dbupgdiag.sql on existing 11.1.0.7 oracle database

Run dbupgdiag.sql to find any duplicate objects in sys or system schema

Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

It will ask for input directory to create log file

See the instructions/warnings/errors in log file produced and correct if anything

2.6) Purge DBA_RECYCLEBIN on existing 11.1.0.7 oracle database

SQL> PURGE DBA_RECYCLEBIN;

Note: Already done as corrective action in Step 2.3

2.7) Check Hidden Parameters on existing 11.1.0.7 oracle database

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\’;

Oracle recommends removing all hidden parameters prior to upgrading.

Note: Already done as corrective action in Step 2.3

2.8) Set the new environment for 11.2.0.3 oracle home

After the software installation of Oracle 11.2.0.3 you must set the environment variables to the new home.

export ORACLE_BASE=/u01/oracle/<instance_name>/

export ORACLE_HOME=/u01/oracle/<instance_name>/db/tech_st/11.2.0

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0

export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata

Make sure that The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])

2.9) Create nls/data/9idata directory on new 11.2.0.3 home

Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 11g Oracle home.

$ perl cr9idata.pl

$ export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata

2.10) Check for TIMESTAMP WITH TIMEZONE Datatype on existing 11.1.0.7 oracle database

The RDBMS DST patching has been greatly improved in 11gR2.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

———-

For Upgrade to 11.2.0.3, there is no need to apply DST patches on the 11.1.0.6 or 11.1.0.7 side first. You can skip any DST related upgrade instructions.

The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 11.1.0.6 or 11.1.0.7.

2.11) Check the National Characterset on existing 11.1.0.7 oracle database

Check that the National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 or AL16UTF16.

select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET’;

If this is UTF8 or AL16UTF16 then no action is needed.

SQL> select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET’;

VALUE

———————-

AL16UTF16

2.12) Run Optimizer Statistics on existing 11.1.0.7 oracle database

When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.

$ sqlplus “/as sysdba”

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

2.13) Drop SYS.ENABLED$INDEXES (conditional)

If the SYS.ENABLED$INDEXES table exists, use SQL*Plus to connect to the database as SYSDBA and running the following command to drop it:

SQL> drop table sys.enabled$indexes;

PL/SQL procedure successfully completed.

2.14) Disable Database Vault (conditional)

If you have Database Vault installed, perform steps 1 to 6 of Part 2 of document 1091083.1 on My Oracle Support to disable Database Vault.

Check>>

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

PARAMETER VALUE

—————————————————————-

Oracle Database Vault FALSE

If the Value is ‘FALSE’ so no action is required.

2.15) Back up Enterprise Manager Database Control Data (conditional)

You may need to backup the EM data if you have installed.

2.16) Check for any logical corruption

To check for corruption in the dictionary, use the following commands in SQL*Plus (connected as sys):

—————————————————————-

Set verify off

Set space 0

Set line 120

Set heading off

Set feedback off

Set pages 1000

Spool analyze.sql

SELECT ‘Analyze cluster “‘||cluster_name||’” validate structure cascade;’

FROM dba_clusters

WHERE owner=’SYS’

UNION

SELECT ‘Analyze table “‘||table_name||’” validate structure cascade;’

FROM dba_tables

WHERE owner=’SYS’

AND partitioned=’NO’

AND (iot_type=’IOT’ OR iot_type is NULL)

UNION

SELECT ‘Analyze table “‘||table_name||’” validate structure cascade into invalid_rows;’

FROM dba_tables

WHERE owner=’SYS’

AND partitioned=’YES’;

spool off

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

This creates a script called analyze.sql.

Now execute the following steps:

$ sqlplus “/ as sysdba”

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

SQL> @analyze.sql

This script (analyze.sql) should not return any errors.

Note:

1. ORA-30657 might occur if there is any external table validated, which can be safely ignored as per Note 209355.1 ORA-30657: Using ANALYZE TABLE for an External Table

2. Errors shown below when executing analyze.sql can be ignored:

SP2-0734: unknown command beginning “SQL> SELEC…” – rest of line ignored.

SP2-0042: unknown command “SQL>” – rest of line ignored.

SP2-0734: unknown command beginning “SQL> spool…” – rest of line ignored.

SQL> @analyze.sql

Analyze table “SYS_TZUV2_AFFECTED_REGIONS” validate structure cascade

ERROR at line 1:

ORA-30657: operation not supported on external organized table

2.17) Ensure that all snapshot refreshes are successfully completed

Ensure that all snapshot refreshes are successfully completed, and that replication is stopped.

SELECT DISTINCT(TRUNC(last_refresh))FROM dba_snapshot_refresh_times;

2.18) Ensure that no files need media recovery and that no files are in backup mode

Ensure that no files need media recovery and that no files are in backup mode.

SELECT * FROM v$recover_file;

SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;

This should return no rows.

2.19) Resolve outstanding distributed transactions

Resolve outstanding distributed transactions prior to the upgrade.

SQL> select * from dba_2pc_pending;

If this returns rows you should do the following:

SQL> SELECT local_tran_id FROM dba_2pc_pending;

SQL> EXECUTE dbms_transaction.purge_lost_db_entry(”);

SQL> COMMIT;

Note: no records were returned

2.20) To check if a standby database exists, issue the following query

To check if a standby database exists, issue the following query:

SELECT SUBSTR(value,INSTR(value,’=',INSTR(UPPER(value),’SERVICE’))+1)

FROM v$parameter WHERE name LIKE ‘log_archive_dest%’ AND UPPER(value) LIKE ‘SERVICE%’;

If this query returns a row, then sync the standby database with the primary database.

1. Make sure all the logs are transported to the standby server after a final log switch in the primary.

2. Start the recovery of the standby database with the NODELAY option.

2.21) Disable all batch and cron jobs

Disable all batch and cron jobs.

About jobs initiated with Oracle the packages DBMS_JOB, DBMS_SCHEDULER can be used , regarding cron jobs (external jobs controlled at the OS level), this is a task for your Unix administrator

Useful Metalinks

2013-03-10T14:59:00.001-07:00

Good Metalink docs

--GenernalTradeoffs when increasing DB_BLOCK_SIZE [ID 65984.1]

--TNS & Listener
TroubleShooting Guide For ORA - 12502 TNS:listener received no CONNECT_DATA from client [ID 453505.1]
**--SQL*PLUSHow To Connect Using A Sqlplus Preliminary Connection [ID 986640.1]
--Log fileWAITEVENT: "log file sync" Reference Note [ID 34592.1]
How to Tune Log File Sync? [ID 857576.1]
-- Performance Diagnostic & Health Check
How to Perform a Health Check on the Database [ID 122669.1]**

Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]

Database Instance Tuning Knowledge Browser Product Page [ID 267886.1]

Full Table Scans On A Table Is Reading 1 Block At A Time. (Due To Chained / Migrated Rows) [ID 554366.1]
WAITEVENT: "db file sequential read" Reference Note [ID 34559.1]
--Backup & Recovery
How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark [ID 130866.1]

Fractured blocks found during crash or instance recovery when using EMC Symmetrix storage [ID 758055.1]How to resolve ORA-00600 [13013], [5001] [ID 816784.1] --corrupted block
Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g [ID 28814.1]
Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231 [ID 33405.1]

--Standby & replication
Cascaded Standby Databases [ID 409013.1]
--Query tuning

Master Note: TROUBLESHOOTING: Advanced Query Tuning [ID 163563.1]

START HERE> Diagnosing Query Tuning Problems [ID 233112.1]

Master Note: Diagnosing Why a Query is Not Using an Index [ID 67522.1]QREF: SQL Statement HINTS [ID 29236.1]

--CBO & Statistics

Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above [ID 754931.1]

Master Note: Restoring table statistics in 10G onwards [ID 452011.1]
--Upgrade & Downgrade

TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance [ID 160089.1]
--OS, Solaris

How To Display Information About Processes on SUN Solaris [ID 70609.1]

--LockHOW TO FIND BLOCKING SESSION FOR MUTEX WAIT EVENT cursor: pin S wait on X [ID 786507.1]

SCRIPT: FULLY DECODED LOCKING [ID 1020008.6]

How to Determine The Lock Type and Mode from an Enqueue Wait [ID 413934.1]

TX Transaction locks - Example wait scenarios [ID 62354.1]-good explain on TX lock

REFERENTIAL INTEGRITY AND LOCKING [ID 33453.1] --good explain on v$lock, TM lockVIEW: "V$LOCK" Reference Note [ID 29787.1]--good explain on v$lock,

WAITEVENT: "enqueue" Reference Note [ID 34566.1]FAQ about Detecting and Resolving Locking Conflicts [ID 15476.1]
--Memory

Init.ora Parameter "CURSOR_SHARING" Reference Note [ID 94036.1]

Fundamentals of the Large Pool [ID 62140.1]

How to Calculate Your Shared Pool Size [ID 1012046.6]

WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK [ID 115656.1]Understanding and Tuning the Shared Pool [ID 62143.1]
Which Views Can Be Used to Monitor PGA Usage in Oracle 10g and Higher? [ID 469521.1]

Automatic PGA Memory Management [ID 223730.1]

--Parallel

Using Parallel Execution [ID 203238.1]Checklist for Performance Problems with Parallel Execution [ID 280939.1]

PARALLEL_EXECUTION_MESSAGE_SIZE Usage [ID 756242.1]

Why didn't my parallel query use the expected number of slaves? [ID 199272.1]

Parallel Query Wait Events [ID 191103.1]

--CPU

Diagnosing High CPU Utilization [ID 164768.1]

*TBR*Database Performance - FAQ [ID 402983.1]
Master Note: Why is a Particular Query Slower on One Machine than Another? [ID 604256.1]

Extent Sizes for Sort, Direct Load and Parallel Operations (PCTAS & PDML) [ID 50592.1]--Partition
Top Partition Performance Issues [ID 166215.1]

How to Split Partition of Heavily Used Table [ID 76769.1]

Fast Split partitioning in 9iR2 [ID 232628.1]

"Update Global Indexes" Allows Automatic Global Index Maintenance During DDL [ID 139707.1]Exchange Partition Having Global Index with Non - Partitioned Table [ID 419505.1]

How to Partition a Non-partitioned Table [ID 1070693.6]

How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]

--Space

10g and above SEGMENT ADVISOR [ID 242736.1]

--Index

Index Rebuild, the Need vs the Implications [ID 989093.1]

How Does the Index Block Splitting Mechanism Work for B*tree Indexes? [ID 183612.1]

How Btree Indexes Are Maintained [ID 30405.1]

How to Find Out How Much Space an Index is Using [ID 33343.1]

Script: To Report Information on Indexes [ID 1019722.6]

--flashback
Flashback Database Best Practices & Performance [ID 565535.1]
Flash Recovery area - Space management Warning & Alerts [ID 305812.1]
Flashback Logs-Space management [ID 369755.1]
How To Disable Flashback Database Without A Bounce [ID 833584.1]
How To Recover From A Drop/Truncate/Delete Table Done On Primary Using Flashback On A Standby Database [ID 958557.1]

--Security Password
All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault [ID 207959.1]
Script to Check for Default Passwords Being Used for Common Usernames [ID 227010.1]
Patch 4926128 Oracle Default Password Scanner
Oracle Created Database Users: Password, Usage and Files References [ID 160861.1]

--WWW documents
--ORA-06512 executing utlu112i.sql
http://davidalejomarcos.wordpress.com/2010/05/07/ora-06512-executing-utlu112i-sql/

--oracle convert between row and column
http://www.itpub.net/viewthread.php?tid=1017026&extra=page%3D1%26amp%3Bfilter%3Ddigest

--How to escapte special characters
http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_escape_special_characters_when_writing_SQL_queries.3F

--oracle default account and password
http://www.petefinnigan.com/default/oracle_default_passwords.htm

E-Business Suite

Autoconfig

165195.1 Using AutoConfig to Manage System Configurations with Oracle Applications 11i
218089.1 Autoconfig FAQ
270519.1 Customizing an AutoConfig Environment

Application Object Library

177183.1 Succesfully Installing NLS/MLS in 11i

Application Server

230688.1 Basic ApacheJServ Troubleshooting with IsItWorking.class With Applications 11i
146468.1 Installing Oracle9i Application Server 1.0.2.2.2 with Oracle Applications 11i
146468.1 Installing Oracle9i Application Server 1.0.2.2.2 with Oracle Applications 11i
237988.1 How to Enable Rotatelog for 9iAS V2 HTTP Server (Log file rotation)

Cloning

135792.1 Cloning Oracle Applications Release 11i
230672.1 Cloning Oracle Applications

Release 11i with Rapid Clone

Concurrent Manager

105133.1 Concurrent Manager Questions and Answers Relating to Generic Platform
213021.1 Concurrent Processing (CP) / APPS Reporting Scripts
185489.1 Setting Up Parallel Concurrent Processing On Unix Server
241370.1 Concurrent Manager Setup and Configuration Requirements in an 11i RAC Environment

Patchset Uitlity to check installed patchsets and Family Packs

2011-10-10T00:55:00.000-07:00

Hello Everyone!!!

Here you have a good tool to know.

patchset.sh is quite a good tool to evaluate the currently installed Oracle Applications' patchsets and Family Packs.

Note 139684.1 provides in depth details about this utility.

Here is the simple example, when you need to use this tool.

1) You want to know what is the current AD level of your system.
2) whether your system is at ATG RUP4 or RUP5 or RUP6(yet to be released!!)

Note: Its advised to download the latest version of this tool from
ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh

If the ftp link is not working, try manual ftp as follows:
NOTE: Put the full path in for the get.

ftp ftp.oracle.com
login as an anonymous user, and then:
get support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh

and finally this script works perfectly for 10.7, 11.0.x, 11.5.x, and 12.0.x !!!

Hope this helps you better in many ways!!

How to Drop Database in Oracle 10g?

2011-04-24T05:13:00.001-07:00

In order to drop the database start the database in restrict mode and bring it in mount state as shown:
sqlplus / as sysdba
SQL> shutdown immediate;
oracle database closed
oracle database dismounted
oracle instance shutdown
SQL> startup restrict mount;
SQL> drop database;
Database dropped
SQL> exit
Thus u will find that all the files associated with the database will be deleted.

Configuring RMAN

2011-03-23T12:58:00.001-07:00

RMAN can invoked from the command line on the database host machine like so:
C:\>rman target sys/sys_password
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Connected to target database: ORCL (DBID=1036216947)
RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'e:\backup\ctl_sp_bak_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'e:\backup\%U.bak' MAXPIECESIZE 4G;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'e:\backup\%U.bak' MAXPIECESIZE 4G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\ORA92\DATABASE\SNCFORCL.ORA'; #
default
RMAN>
Retention Policy:
This instructs RMAN on the backups that are eligible for deletion.
For example: A retention policy with redundancy 2 would mean that two backups - the latest and the one prior to that - should be retained. All other backups are candidates for deletion.
Default Device Type:
This can be "disk" or "sbt" (system backup to tape). We will backup to disk and then have our OS backup utility copy the completed backup, and other supporting files, to tape.
Controlfile Autobackup:
This can be set to "on" or "off". When set to "on", RMAN takes a backup of the controlfile AND server parameter file each time a backup is performed. Note that "off" is the default.
Controlfile Autobackup Format:
This tells RMAN where the controlfile backup is to be stored. The "%F" in the file name instructs RMAN to append the database identifier and backup timestamp to the backup filename. The database identifier, or DBID, is a unique integer identifier for the database.
Parallelism:
This tells RMAN how many server processes you want dedicated to performing the backups.
Device Type Format:
This specifies the location and name of the backup files. We need to specify the
format for each channel. The "%U" ensures that Oracle appends a unique identifier to the backup file name. The MAXPIECESIZE attribute sets a maximum file size for each file in the backup set.
Any of the above parameters can be changed using the commands displayed by the "show all" command.
For example, one can turn off controlfile autobackups by issuing:
RMAN> configure controlfile autobackup off;
using target database controlfile instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
RMAN>

Rman Cloning Oracle DB

2011-03-23T12:55:00.000-07:00

You want to use RMAN to create a duplicate database on the same server by using RMAN backups.

My Source Database Name is MYTEST

Target (duplicate) Database name is MYDB

Step 1: Configure Listener.ora and tnsnames.ora files

tnsnames.ora file

MYTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest)
)
)

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb)
)
)

listener.ora file

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mytest)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mytest)
)
(SID_DESC =
(GLOBAL_DBNAME= mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mydb)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)

Step 2: create pfile for target database from source database

SQL> create pfile from spfile;

File created.

Step 3: modified parameter file for target database (duplicate) here log_file_name_convert and db_file_name_convert and control file location is important, further your choice.

*.audit_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/adump’
*.compatible=’10.2.0.1.0′
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’mydb’
*.control_files=’/home/oracle/oracle/product/10.2.0/oradata/mydb/control01.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/mydb/control02.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/mydb/control03.ctl’
*.db_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,'/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.log_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,'/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.db_recovery_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=mydbXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=285212672
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

Step 4: Create password file

[oracle@localhost ~]$orapwd file=orapwmydb password=oracle entries=5

Step 5: start your target database nomount stage

[oracle@localhost ~]$ export ORACLE_SID=mydb
[oracle@localhost ~]$ rlwrap sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Tue May 25 18:22:09 2010

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

Enter user-name: sys/oracle@mydb as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> startup nomount pfile=/home/oracle/oracle/product/10.2.0/db_1/dbs/initmydb.ora
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> exit

Step 6: From source database

SQL> alter system switch logfile;

System altered.

SQL>

Step 7: Backup database and archivelog Source database

[oracle@localhost root]$ rlwrap rman target sys/azar@mytest

Recovery Manager: Release 10.2.0.1.0 – Production on Tue May 25 19:56:10 2010

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

connected to target database: MYTEST (DBID=2419076664)

RMAN> backup database plus archivelog;

Starting backup at 25-MAY-10
current log archived

input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/sysaux01.dbf
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAY-10
channel ORA_DISK_1: finished piece 1 at 25-MAY-10

(skipped)

Starting Control File and SPFILE Autobackup at 25-MAY-10
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_25/o1_mf_s_719956316_5zr01f85_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAY-10

Step 8: Connect auxiliary database

RMAN> connect auxiliary sys/oracle@mydb;

connected to auxiliary database: MYDB (not mounted)

Step 9: Create duplicate database.

RMAN> duplicate target database to mydb;

Starting Duplicate Db at 25-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
set until scn 470310;
set newname for datafile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf”;
set newname for datafile 2 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
set newname for datafile 3 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
set newname for datafile 4 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 25-MAY-10
using channel ORA_AUX_DISK_1

skipping datafile 1; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
skipping datafile 2; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
skipping datafile 3; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
skipping datafile 4; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 25-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

contents of Memory Script:
{
set until scn 470310;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 25-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-MAY-10

contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1

contents of Memory Script:
{
set newname for tempfile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf recid=1 stamp=719956593

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf recid=2 stamp=719956593

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf recid=3 stamp=719956593

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 25-MAY-10

RMAN>

Step 10: My duplicate database succesfully created.

Enter user-name: sys/oracle@mydb as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select status from v$instance;

STATUS
————
OPEN

SQL> select name from V$database;

NAME
———
MYDB

SQL>

How to Flashback in oracle 10g

2011-03-11T02:24:00.000-08:00

Oracle9i introduced the DBMS_FLASHBACK package to allow queries to reference older versions of the database. Oracle 10g has taken this technology a step further making it simpler to use and much more flexible.

Note: Internally Oracle uses SCNs to track changes so any flashback operation that uses a timestamp must be translated into the nearest SCN which can result in a 3 second error.

Flashback Query
Flashback Version Query
Flashback Transaction Query
Flashback Table
Flashback Drop (Recycle Bin)
Flashback Database
Flashback Query Functions

Flashback Query

Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK functionality or Oracle9i, but in a more convenient form. For example:

CREATE TABLE flashback_query_test (
id NUMBER(10)
);

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
722452 2009-03-29 13:34:12

INSERT INTO flashback_query_test (id) VALUES (1);
COMMIT;
SELECT COUNT(*) FROM flashback_query_test;
COUNT(*)
----------
1

SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2009-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

COUNT(*)
----------
0

SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452;
COUNT(*)
----------

Flashback Version Query
Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause:
CREATE TABLE flashback_version_query_test (
id NUMBER(10),
description VARCHAR2(50)
);

INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');
COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
725202 2009-03-29 14:59:08

UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;
COMMIT;

UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;
COMMIT;

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
725219 2009-03-29 14:59:36
COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200

SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
description
FROM flashback_version_query_test
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2009-03-29 14:59:08', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2009-03-29 14:59:36', 'YYYY-MM-DD HH24:MI:SS')
WHERE id = 1;

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ----------------

725212 29-MAR-09 02.59.16 PM 02001C0043030000 U THREE
725209 29-MAR-09 02.59.16 PM 725212 29-MAR-04 02.59.16 PM 0600030021000000 U TWO
725209 29-MAR-09 02.59.16 PM ONE

SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
description
FROM flashback_version_query_test
VERSIONS BETWEEN SCN 725202 AND 725219
WHERE id = 1;

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
----------------- ------------------------ ------------------ ------------------------ ----------------
725212 29-MAR-09 02.59.16 PM 02001C0043030000 U THREE
725209 29-MAR-09 02.59.16 PM 725212 29-MAR-04 02.59.16 PM 0600030021000000 U TWO
725209 29-MAR-09 02.59.16 PM ONE

The available pseudocolumn meanings are:
VERSIONS_STARTSCN or VERSIONS_STARTTIME - Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN ot TIMESTAMP.

VERSIONS_ENDSCN or VERSIONS_ENDTIME - Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN ot TIMESTAMP.

VERSIONS_XID - ID of the transaction that created the row in it's current state.

VERSIONS_OPERATION - Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete

Flashback Transaction Query

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view like:

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0600030021000000');
XID OPERATION START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
---------------------------------------------------------------------------------------------------
0600030021000000 UPDATE 725208 725209
SCOTT

update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAA
AYAAA';
0600030021000000 BEGIN 725208 725209
SCOTT
XID OPERATION START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------
2 rows selected.

Flashback Table

The FLASHBACK TABLE command allows point in time recovery of individual tables subject to the following requirements:
You must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the table.

You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
There must be enough information in the undo tablespace to complete the operation.
Row movement must be enabled on the table (ALTER TABLE tablename ENABLE ROW MOVEMENT;).

The following example creates a table, inserts some data and flashbacks to a point prior to the data insertion. Finally it flashbacks to the time after the data insertion:
CREATE TABLE flashback_table_test (
id NUMBER(10)
);

ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;
SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
715315

INSERT INTO flashback_table_test (id) VALUES (1);
COMMIT;

SELECT current_scn FROM v$database;
CURRENT_SCN
----------
715340

FLASHBACK TABLE flashback_table_test TO SCN 715315;
SELECT COUNT(*) FROM flashback_table_test;
COUNT(*)
----------
0

FLASHBACK TABLE flashback_table_test TO SCN 715340;
SELECT COUNT(*) FROM flashback_table_test;
COUNT(*)
----------
1

Flashback of tables can also be performed using timestamps:
FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS

Flashback Drop (Recycle Bin)

In Oracle 10g the default action of a DROP TABLE command is to move the table to the recycle bin (or rename it), rather than actually dropping it. The PURGE option can be used to permanently drop a table.

The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP privilege. The contents of the recycle bin can be shown using the SHOW RECYCLEBIN command and purged using the PURGE TABLE command. As a result, a previously dropped table can be recovered from the recycle bin:

CREATE TABLE flashback_drop_test (
id NUMBER(10)
);

INSERT INTO flashback_drop_test (id) VALUES (1);
COMMIT;
DROP TABLE flashback_drop_test;
SHOW RECYCLEBIN

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE 2004-03-29:11:09:07 EST

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;
SELECT * FROM flashback_drop_test;
ID
----------
1
Tables in the recycle bin can be queried like any other table:

DROP TABLE flashback_drop_test;
SHOW RECYCLEBIN

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0 TABLE 2004-03-29:11:18:39 EST
SELECT * FROM "BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";
ID
----------
1

If an object is dropped and recreated multiple times all dropped versions will be kept in the recycle bin, subject to space. Where multiple versions are present it's best to reference the tables via the RECYCLEBIN_NAME. For any references to the ORIGINAL_NAME it is assumed the most recent object is drop version in the referenced question. During the flashback operation the table can be renamed like:
FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;
Several purge options exist:

PURGE TABLE tablename; -- Specific table.
PURGE INDEX indexname; -- Specific index.
PURGE TABLESPACE ts_name; -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username; -- All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN; -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN; -- The whole recycle bin.

Several restrictions apply relating to the recycle bin:
Only available for non-system, locally managed tablespaces.
There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary.
The objects in the recycle bin are restricted to query operations only (no DDL or DML).
Flashback query operations must reference the recycle bin name.

Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
Tables with Fine Grained Access policies aer not protected by the recycle bin.
Partitioned index-organized tables are not protected by the recycle bin.
The recycle bin does not preserve referential integrity.

Flashback DatabaseThe FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery. In order to flashback the database you must have SYSDBA privilege and the flash recovery area must have been prepared in advance.
If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode
CONN sys/password AS SYSDBA

ALTER SYSTEM SET log_archive_dest_1='location=d:\oracle\oradata\DB10G\archive\' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_start=TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ARCHIVE LOG START
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Flashback must be enabled before any flashback operations are performed:
CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
With flashback enabled the database can be switched back to a previous point in time or SCN without the need for a manual incomplete recovery. In the following example a table is created, the database is then flashbacked to a time before the table was created:
-- Create a dummy table.
CONN scott/tiger
CREATE TABLE flashback_database_test (
id NUMBER(10)
);

-- Flashback 5 minutes.

CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

-- Check that the table is gone.
CONN scott/tiger
DESC flashback_database_test

Some other variations of the flashback database command include:
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;

The window of time that is available for flashback is determined by the DB_FLASHBACK_RETENTION_TARGET parameter. The maximum flashback can be determined by querying the V$FLASHBACK_DATABASE_LOG view. It is only possible to flashback to a point in time after flashback was enabled on the database and since the last RESETLOGS command.

Flashback Query Functions

The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations:

SELECT * FROM emp AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
SELECT * FROM emp AS OF TIMESTAMP SCN_TO_TIMESTAMP(993240);
DECLARE
l_scn NUMBER;
l_timestamp TIMESTAMP;
BEGIN
l_scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/

Checking Growth of Oracle Database

2011-03-09T01:04:00.001-08:00

Step : 1 Calculate total Size of tablespace
SELECT sum(bytes)/1024/1024 "TOTAL SIZE (MB)"
FROM dba_Data_files

Step : 2 Calculate Free Space in Tablespace
SELECT sum(bytes)/1024/1024 "FREE SPACE (MB)"
FROM dba_free_space

Step : 3 Calculate total size , free space and used space in tablespace

SELECT t2.total "TOTAL SIZE",
t1.free "FREE SPACE",
(t1.free/t2.total)*100 "FREE (%)" ,
(1-t1.free/t2.total)*100 "USED (%)"
FROM (SELECT sum(bytes)/1024/1024 free FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total FROM dba_Data_files) t2

Step : 4 Create table which is store all free/use space related information of tablespace

CREATE TABLE db_growth
AS
SELECT *
FROM(
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE% "
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)

Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)

INSERT INTO db_growth
SELECT *
FROM (
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE%"
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)

Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)

CREATE VIEW v_db_growth
AS SELECT *
FROM
(
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE%"
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view

INSERT INTO db_growth select *
FROM v_db_growth

Step : 8 Check everything goes fine.

SELECT * FROM db_growth;

Check Result
Step : 9 Execute following SQL for more time stamp information

ALTER SESSION SET nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.

Step : 10 Create a DBMS jobs which execute after 24 hours
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.submit(
jobno, 'BEGIN INSERT INTO db_growth SELECT * FROM v_db_growth;COMMIT:END;', sysdate, 'SYSDATE+ 24', TRUE);
COMMIT;
END;

PL/SQL procedure successfully completed.

Step: 11 View your dbms jobs and it's other information
SELECT * FROM user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
EXEC DBMS_JOB.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 12 Finally all data populated in db_growth table
SELECT * FROM db_growth

How to Drop All Objects in a Schema in Oracle 10g

2011-03-09T01:01:00.001-08:00

Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database.

If you don't have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed.

select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects

Then, I normally purge the recycle bin to really clean things up. To be honest, I don't see a lot of use for oracle's recycle bin, and wish i could disable it... but anyway:

purge recyclebin;

This will produce a list of drop statements. Not all of them will execute - if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:

select * from user_objects

Session Roles

2011-03-09T00:20:00.001-08:00

Viewing Privilege and Role Information in Oracle 10g Database

DBA view describes all column object grants in the database. ALL view describes all column object grants for which the current user or PUBLIC is the object owner, grantor, or grantee. USER view describes column object grants for which the current user is the object owner, grantor, or grantee.

DBA_COL_PRIVS

ALL_COL_PRIVS

USER_COL_PRIVS

ALL view lists column object grants for which the current user is object owner or grantor. USER view describes column object grants for which the current user is the grantor.

ALL_COL_PRIVS_MADE

USER_COL_PRIVS_MADE

ALL view describes column object grants for which the current user or PUBLIC is the grantee. USER view describes column object grants for which the current user is the grantee.

ALL_COL_PRIVS_RECD

USER_COL_PRIVS_RECD

DBA view lists all grants on all objects in the database. ALL view lists the grants on objects where the user or PUBLIC is the grantee. USER view lists grants on all objects where the current user is the grantee.

DBA_TAB_PRIVS

ALL_TAB_PRIVS

USER_TAB_PRIVS

ALL view lists the all object grants made by the current user or made on the objects owned by the current user. USER view lists grants on all objects owned by the current user.

ALL_TAB_PRIVS_MADE

USER_TAB_PRIVS_MADE

ALL view lists object grants for which the user or PUBLIC is the grantee. USER view lists object grants for which the current user is the grantee.

ALL_TAB_PRIVS_RECD

USER_TAB_PRIVS_RECD

This view lists all roles that exist in the database.

DBA_ROLES

DBA view lists roles granted to users and roles. USER view lists roles granted to the current user.

DBA_ROLE_PRIVS

USER_ROLE_PRIVS

DBA view lists system privileges granted to users and roles. USER view lists system privileges granted to the current user.

DBA_SYS_PRIVS

USER_SYS_PRIVS

This view describes roles granted to other roles. Information is provided only about roles to which the user has access.

ROLE_ROLE_PRIVS

This view contains information about system privileges granted to roles. Information is provided only about roles to which the user has access.

ROLE_SYS_PRIVS

This view contains information about object privileges granted to roles. Information is provided only about roles to which the user has access.

ROLE_TAB_PRIVS

This view lists the privileges that are currently enabled for the user.

SESSION_PRIVS

This view lists the roles that are currently enabled to the user.

How to change the Redo Log File size in Oracle Database

2011-02-24T01:05:00.000-08:00

We had 50 MB Redo Log Files, 1 Redo Thread, 2 Redo Log Groups and One member in each group.
We decided to increase the Redo Log size to 100MB.
The Redo Logs must be dropped and recreated for changing the redo log size. It can be done online without shutting down the database. However, you need to make sure that the Redo Group being dropped should be INACTIVE when you do this.
We have the following Log Groups and members:

SQL> select * from v$logfile;

 GROUP# STATUS  TYPE    MEMBER          IS_RECOVE
------ ------- -----      --------- ---------
 2   ONLINE   /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
 1   ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

And the status of the Log Groups is:

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVE STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- --------------
 1     1   12   52428800   1   NO    CURRENT   1156736  27-JAN-11
 2     1   11   52428800   1   YES   ACTIVE    1156732  27-JAN-11

Here we see that the Group# 1 is being used Currently and the Group# 2 though not being used Currently, however is ACTIVE (means if the Database crashes now, you will need this Group for recovery.) We need to make this group Inactive before proceeding ahead:
For this, execute a checkpoint:

SQL> alter system checkpoint;

System altered.

Now again check the status of the Redo Groups:

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- --------
 1 1 12 52428800 1 NO CURRENT 1156736 27-JAN-11
 2 1 11 52428800 1 YES INACTIVE 1156732 27-JAN-11

The group 2 is now Inactive. Hence we can proceed with dropping this group:

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files for instance BOTI (thread 1)
ORA-00312: online log 2 thread 1: '/app01/oratest/oradata/BOTI/BOTI/redo02.log'

What went wrong ???
It’s a basic requirement in Oracle Database that there should always be a minimum 2 Redo Log Groups available with the Database. Hence we can not drop any Redo Group if there are only 2 Groups.
To overcome this issue, we need to add one more Redo group to the database.
Execute the following step:

SQL> alter database add logfile group 3 '/app01/oratest/oradata/BOTI/BOTI/redo03.log' size 100M;

Database altered.

Now check the logfiles:

SQL> select * from v$logfile;

 GROUP# STATUS TYPE MEMBER IS_RECOVE
---------- --------------------- --------------------- -
 3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
 2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
 1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

… and the status of the Groups:

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVE STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------
 1 1 12   52428800 1 NO CURRENT 1156736 27-JAN-11
 2 1 11 52428800 1 YES INACTIVE 1156732 27-JAN-11
 3 1 0 104857600 1 YES UNUSED 0

The status of the new Group is UNUSED because it has never been used.
Now, we have 3 Redo Groups in our database. We can now proceed with Dropping Redo Group# 2.

SQL> alter database drop logfile group 2;

Database altered.

Also, delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo02.log’ from File system also.
Now add the Redo Group 2 back to the database with changed Redo size:

SQL> alter database add logfile group 2 '/app01/oratest/oradata/BOTI/BOTI/redo02.log' size 100M;

Database altered.

SQL> select * from v$logfile;

 GROUP#       STATUS  TYPE   MEMBER  IS_RECOVE
---------- --------------------- --------------------- --------
 3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
 2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
 1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- --------------
 1 1 12 52428800 1 NO CURRENT 1156736 27-JAN-11
 2 1 0 104857600 1 YES UNUSED 0
 3 1 0 104857600 1 YES UNUSED 0

Now we have to drop the 1st Redo Log Group. Before that, we need to change the status of this group:

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---- -----
 1 1 12 52428800 1 YES ACTIVE 1156736 27-JAN-11
 2 1 13 10485760 1 NO CURRENT 1157376 27-JAN-11
 3 1 0 104857600 1 YES UNUSED 0

Still the Grpoup is in Active status. Issue a checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- -------
 1 1 12 52428800 1 YES INACTIVE 1156736 27-JAN-11
 2 1 13 104857600 1 NO CURRENT 1157376 27-JAN-11
 3 1 0 104857600 1 YES UNUSED 0

Now we can drop the Redo Group# 1.

SQL> alter database drop logfile group 1;

Database altered.

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- -----
 2 1 13 104857600 1 NO CURRENT 1157376 27-JAN-11
 3 1 0 104857600 1 YES UNUSED 0

SQL> select * from v$logfile;

 GROUP# STATUS TYPE MEMBEs IS_RECOVE
---------- --------------------- --------------------- 
3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO 
2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO

Delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo01.log’ from OS.
Now we need to add this group back to the database with 100MB Redo Log file:

SQL> alter database add logfile group 1 '/app01/oratest/oradata/BOTI/BOTI/redo01.log' size 100M;

Database altered.

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ----------
 1   1   0   104857600   1    YES    UNUSED    0
 2   1   13  104857600   1     NO   CURRENT     1157376       27-JAN-11
 3   1   0   104857600   1    YES   UNUSED    0

Here we can see that all the Redo Groups are now showing 100MB size (column BYTES).

SQL> select * from v$logfile;

 GROUP# STATUS  TYPE   MEMBER    IS_RECOVE
---------- --------------------- -----------------
 3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
 2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
 1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

Though it’s suggested that you should keep 3 groups, if you want you can drop the Redo Group 3 which we added for this exercise.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select * from v$logfile;

 GROUP# STATUS    TYPE                MEMBER                   IS_RECOVE
------- ------- -------------- ---------------------
 2 ONLINE  /app01/oratest/oradata/BOTI/BOTI/redo02.log      NO
 1 ONLINE  /app01/oratest/oradata/BOTI/BOTI/redo01.log      NO

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- --------- ---------
 1      1       0          104857600     1 YES UNUSED 0
 2      1      13          104857600     1 NO CURRENT 1157376 27-JAN-11

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVE STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------- 
 1        1    14  104857600 1  NO   CURRENT   1157584       27-JAN-11
 2        1    13  104857600 1  YES  ACTIVE     1157376     27-JAN-11

Congratulations ! You have changed the Redo Log file size from 50M to 100M.

Installing Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86

2011-01-20T23:17:00.000-08:00

In this tutorial, you learn how to install Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86.

Time to Complete

Approximately 2.5 hours.

Overview

Earlier releases of Oracle Enterprise Manager Grid Control were installed in a single step using Oracle Universal Installer. With the release of Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) installing Oracle Enterprise Manager Grid Control is a three step process requiring the installation of Oracle WebLogic Server and Oracle Database prior to installing Grid Control.

Scenario

In this tutorial, you install Oracle WebLogic Server 11g Release 1 (10.3.2) Linux x86, Oracle Database 11g Release 2 (11.2.0.1.0) Linux x86, and Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86.

Software Requirements

The following is a list of software requirements:

Download Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86 from OTN at this link
Download Oracle WebLogic Server 11g Release 1 (10.3.2) Linux x86 from OTN at this link
Oracle Database 11g Release 2 (11.2.0.1.0) Linux x86 from OTN at this link

Prerequisites

Before starting this tutorial, you should:

Have a server with 4G or more RAM
Complete a default install of Oracle Enterprise Linux 5.X (this example uses 5.4) with the oracle-validated RPM installed
Have 7G of free disk space for the installation software
Have 14G of free space for installing the software

Note: Screen captures for this tutorial were taken in Oracle Enterprise Linux 5.4 with a Gnome 2.16 Desktop.

Configuring the Operating System

Installing the oracle-validated RPM installs RPMs required for an Oracle installation as well as creates the operating system user oracle. The lesson uses operating system user oracle to install all the software.
Before installing the software the operating system must be configured. To configure the operating system perform the following steps as operating system user root.

Configuring the Operating System

1 .	As root, in a terminal, perform these steps to configure the operating system in preparation for the software installations. Operating system commands:

Staging Software

2 .	The installation software for this lesson is staged in directory /stage.

Installation Information

3 .

The software will be installed using the follow homes:

Database:	/u01/app/oracle/database/11.2.0/dbhome_1
WebLogic:	/u01/app/oracle/middleware
EMGC:	/u01/app/oracle/gc_inst

Use your host name and domain name.
Any valid database SID can be used.
When passwords are required, choose a strong password. Do not use the passwords provided in the lesson.

Installing Oracle WebLogic Server 11g Release 1 (10.3.2) Linux x86

4 .	Connect as operating system user oracle in an X session.
5 .	Execute the installer for Oracle WebLogic Server 11g Release 1 (10.3.2) Linux x86. ls -al /stage/wls cd /stage/wls ./wls1032_linux32.bin
6 .	Click Next.
7 .	Set the value of Middleware Home Directory to: /u01/app/oracle/middleware Click Next.
8 .	Enter your e-mail address and My Oracle Support Password. Click Next. If you choose to leave the fields blank, you will receive a pop up after clicking Next. Click Yes, to continue.
9 .	Click Next.
10 .	Click Next.
11 .	Click Next.
12 .	Wait for the installation to complete.
13 .	After the installation completes, uncheck Run Quickstart. Click Done.

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

14 .	Execute the installer for Oracle Database 11g Release 2 (11.2.0.1.0) Linux x86. ls -al /stage/database cd /stage/database ./runInstaller
15 .	Enter your e-mail address and My Oracle Support Password. Click Next. If you choose to leave the fields blank, you will receive a pop up after clicking Next. Click Yes, to continue.
16 .	Click Next.
17 .	Select Server Class. Click Next.
18 .	Click Next.
19 .	Select Typical. Click Next.
20 .	Enter these values, then click Next. Oracle Base: /u01/app/oracle/database Software location: /u01/app/oracle/database/11.2.0/dbhome_1 Storage Type: File System Database file location: /u01/app/oracle/database/oradata Database Edition: Enterprise Edition OSDBA Group: dba Global database name: emrep.example.com Administrative password: Pick a strong password. Use the domain of your network as the domain for the global database name.
21 .	Inventory Directory: /u01/app/oraInventory oraInventory Group Name: oinstall Click Next.
22 .	Although installing the oracle-validated RPM made several operating system configuration changes, a few more are required. Click Fix & Check Again.
23 .	Follow the on screen instructions to run the fixup script changing the needed operating system settings.
24 .	Open a terminal. Change to the operating system user root. Execute the script as instructed in the pop up window. su - root cd /tmp/CVU* ls ./runfixup.sh
25 .	Click OK.
26 .	Click Finish.
27 .	Wait for the installation to complete.
28 .	Wait for the installation to complete.
29 .	Click OK.
30 .	Follow the instructions in the window.
31 .	Open a terminal. Change to operating system user root. Execute the two scripts. su - root /u01/app/oraInventory/orainstRoot.sh /u01/app/oracle/database/11.2.0/dbhome_1/root.sh
32 .	Click Close.

Removing Enterprise Manager Database Control

Creating a database using a Database Configuration Assistant template, as is done when choosing to create a database in the Oracle Universal Installer, creates a database containing an Enterprise Manger Database Control repository. The repository contains a subset of the Enterprise Manager Grid Control functionality. Before Enterprise Manager Grid Control can use the database, the Enterprise Manager Database Control repository must be dropped and the configuration files removed.

33 .	Set the environment by souring oraenv located in /usr/local/bin. Execute the command to remove Enterprise Manager Database Control. . oraenv emrep emca -deconfig dbcontrol db -repos drop -HOST host01.example.com -PORT 1521 -SID emrep -SYS_PWD oracle_4U -SYSMAN_PWD oracle_4U -DBSNMP_PWD oracle_4U Y

Configuring the Database for an Enterprise Manager Grid Control Installation

Enterprise Manager Grid Control database repository requirements exceed the default configuration from the Database Configuration Assistant template. Perform the following to meet the requirements.

34 .	An Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) database repository requires: session_cached_cursors to be set to 200 or more log_buffer set to 10485760 or more processes set to 500 or more undo tablespace size to be 200M or greater The first three changes require the database to be bounced for the changes to take effect. sqlplus /nolog connect / as sysdba alter system set session_cached_cursors=200 scope=spfile; alter system set log_buffer=10485760 scope=spfile; alter system set processes=500 scope=spfile; alter database datafile '/u01/app/oracle/database/oradata/emrep/undotbs01.dbf' resize 250M; shutdown immediate startup exit

34 .

An Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) database repository requires:

session_cached_cursors to be set to 200 or more
log_buffer set to 10485760 or more
processes set to 500 or more
undo tablespace size to be 200M or greater

The first three changes require the database to be bounced for the changes to take effect.
sqlplus /nolog
connect / as sysdba
alter system set session_cached_cursors=200 scope=spfile;
alter system set log_buffer=10485760 scope=spfile;
alter system set processes=500 scope=spfile;
alter database datafile '/u01/app/oracle/database/oradata/emrep/undotbs01.dbf' resize 250M;
shutdown immediate
startup
exit

Installing Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86

35 .	Execute the installer for Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86. ls -al /stage/em cd /stage/em ./runInstaller
36 .	Enter your e-mail address and My Oracle Support Password. Click Next. If you choose to leave the fields blank, you will receive a pop up after clicking Next. Click Yes, to continue.
37 .	Enter your My Oracle Support information and an install location or choose to Skip Software Updates. Click Next.
38 .	Click Next.
39 .	Click Next.
40 .	The installer will pick up the Middleware home and provide the install location for the OMS base on the Middleware home. Click Next.
41 .	Enter a password for the WebLogic user and Node Manager. Click Next.
42 .	Enter the database connection information used from the database installation lesson Click Next. Database Host Name: host01.example.com Port: 1521 Service/SID: emrep SYS Password: oracle_4U
43 .	Enter a password for SYSMAN. Click Next.
44 .	Enter a password for the agent. Click Next.
45 .	Click Next.
46 .	Click Install.
47 .	Wait for the installation to complete.
48 .	Follow the instructions in the window.
49 .	Open a terminal. Change to operating system user root. Execute the script. Press the Enter key for each of the prompts. su - root /u01/app/oracle/middleware/oms11g/allroot.sh
50 .	Click OK.
51 .	Wait for the installation to complete.
52 .	Click Close.

Accessing Oracle Enterprise Manager Grid Control with a Browser

Accessing Enterprise Manager for the first time requires accepting the security certificate from Enterprise Manager. Use URL: https://host01.example.com:7799/em
This example uses Firefox 3.6.

53 .	Click Or you can add an exception... .
54 .	Click Add Exception... .
55 .	Click Get Certificate .
56 .	Click Confirm Security Exception .
57 .	Enter username sysman and the password you chose for sysman.
58 .	Click I Accept.
59 .	Installation complete.

Summary

In this tutorial, you should have learned how to:

Configure Oracle Enterprise Linux 5 for an Enterprise Manager Grid Control installation.
Install Oracle WebLogic Server 11g Release 1 (10.3.2) Linux x86.
Install Oracle Database 11g Release 2 (11.2.0.1.0) Linux x86.
Configure Oracle Database 11g Release 2 (11.2.0.1.0) Linux x86 for an Enterprise Manager Grid Control installation.
Install Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86.
Access Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86 from a browse

COMPILE INVALID OBJECTS

2010-12-13T06:53:00.001-08:00

Applying Patches can create invalid objects. To get a quick count of the number of existing invalids (if any), use the following select statement :
SELECT COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = ‘INVALID’;

For a more detailed query, use the following script :
SELECT OWNER, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = ‘INVALID’
GROUP BY OWNER, OBJECT_TYPE;

To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and use one of the following depending on the object type :
alter package compile; (package specification)
alter package compile body;(package body)

alter view compile;(view)

if the object compiles with warning ,use either of the following to see the error s that caoused the warnings:

show errors

OR
select * from user_errors where name = ' ';

Another way to correct invalid objects is to run the adadmin utility as follows:

UNIX OPERATING PLATFORM

1. Log in as APPS User : /

2. Start the adadmin-Utility from the Unix prompt with this command :
adadmin

The utility will then ask you a series of questions.

3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)

This task spawns parallel workers to compile invalid database objects in your APPS schema(s). It uses the same parallel phases as AutoInstall.

Also try running $ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba )

Within Applications, there is a script to compile INVALID objects - called ADCOMPSC.pls

Arguments for ADCOMPSC.pls :

1 - Schema to run in
2 - Password for schema
3 - Check errors for objects starting with #3

NOTE: The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schema’s. In case of an ORA-1555 error while running adcompsc.pls, restart the script.

The script can be run as followed :
cd $AD_TOP/sql
sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %

Example : SQL> @adcompsc.pls apps apps %

After the script completes, check for invalid objects again. If the number has decreased, but invalid objects still exist, run adcompsc.pls again. Keep running adcompsc.pls until number of invalid objects stops decreasing.

If there are any objects still left INVALID, verify them by using the script ‘aderrchk.sql’ to record the remaining INVALID objects. ‘Aderrchk.sql’ uses the same syntax as ‘adcompsc.pls’. This script is also supplied with the Applications. Send the aderrchk.sql to a file using the spool command in sqlplus

e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %

For objects which will not compile, try the following :
select text
from user_source
where name = ‘OBJECTNAME’
and text like ‘%Header%’;

This script will provide the script that creates the packages/recreates the packages.
SQL>@packageheader
SQL>@packagebody

If recreating the package does not make the package valid, analyze the user_errors table to determine the cause of the invalid package :
select text
from user_errors
where name = ‘ ';

Utility TO CHECK TECHSTACK COMPONENTS-APPS-Database

2010-12-11T03:32:00.000-08:00

There is a good utility for checking all the techstack components and there versions present in e-business application. This is applicable to both 11i and R12 environments.

There is a script $FND_TOP/patch/115/bin/txkInventory.pl on apps side which is going to fetch the versions of all components on apps side. This script can be run by giving input to $FND_TOP/patch/115/bin/TXKScript.pl script. TXKScript.pl script takes 2 mandatory arguments, one is the script to run and another is the directory for storing log file and out file. Other then these arguments you must give context file name and location and apps password as well. Also you need to give outfile where it will create report of techstack components.

Login to apps side of your application and source the environment. Run the below command

(appmgr06) appmgr - -bash $ perl $FND_TOP/patch/115/bin/TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$OA_HTML/techstack_info.html
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** STDOUT = /slot06/appmgr/scmc2mq0comn/rgf/scmc2mq0/TXK/txkInventory_Fri_May_16_04_01_03_2008_stdout.log
Reportfile /slot06/appmgr/scmc2mq0comn/html/techstack_info.html generated successfully.

You can access the above report file using http://(hostname.domain):(port)/OA_HTML/techstack_info.html

The report will look like this.

The report will have following information.

* Summary of environment details
* Inventory of HTTP Server Node
* Inventory of Forms Server Node
* Inventory of Concurrent Processing Server Node
* Inventory of Administration Server Node

Similarly you can check on DB side as well

(oracle06) scmc2mq0 - -bash $ perl $ORACLE_HOME/appsutil/bin/TXKScript.pl -script=$ORACLE_HOME/appsutil/bin/txkInventory.pl -txktop=$ORACLE_HOME/appsutil/temp -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$ORACLE_HOME/appsutil/temp/master_db.html

Upgrade from 11.5.10.2 to R12 Upgrade Oracle Applications from 11.5.10.2 to R12 System Overview

2010-12-11T00:34:00.001-08:00

Version 11.5.10.2
Oracle Applications Vision Instance
OS: Redhat Linux update 5
Type: Single Node instance
Applications OS User: applmgrupd
Installed directory: /d01/oracle/singlenode
Database 9i OS User : oracleupd
9i Database Home = /d01/oracle/singlenode/testdb/9.2.0
10g Database Home = /d01/oracle/singlenode/testdb/10.2.0
Instance SID: TEST
Host: linux1

Task – To Upgrade Oracle Applications 11.5.10.2 to R12
Note: Please use vision instance as it is already Multi-Org, Multi-Org is mandatory in R12

Note: The below upgrade is only for testing purposes, not to be implemented in production servers, please do refer metalink for more information.

Upgrade path
• Applications R11.5.6 and below requires to first upgrade to R11.5.10.2/10gR2
• Applications R11.5.7 and up can be directly upgraded to R12
• Database in all releases must have been upgraded to 10gR2 (11.5.9.2 / 11.5.10.2) or should be done during upgrade to R12 (11.5.9.1/11.5.10.1 and below

Here are steps to upgrade Oracle Application 11.5.10.2 to R12, let’s split the activity into 6 steps
1> Please do upgrade the database from 9.2.0.6 to 10.2.0.3, the steps are available in the below link

2> Apply the below patches in your existing 11.5.10.2 environment

Shut down the entire E-Business Suite environment and perform a full backup
4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12
5726010 - AD Preparation Scripts for R12

3> Run rapidwiz to create R12 code tree

4> Apply the below patches in R12 environment
4502962 – R12 Minipack

5> Configuration Phase – R12

6> Apply the online help

2> Apply the below patches in your existing 11.5.10.2 environment
4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
*5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12
5726010 - AD Preparation Scripts for R12

Enable the maintenance mode

adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=adadmin.log
menu_option=ENABLE_MAINT_MODE workers=4

-- Apply Patch 4712852
unzip p4712852_11i_LINUX.zip
cd 4712852

-- Takes 5 minutes
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=4712852.log
patchtop=/software/upgrades/11.5.10.2toR12/r11/4712852 driver=u4712852.drv workers=4
-- Apply patch u5753359.drv
unzip p5753359_11i_LINUX.zip

-- Takes 2 minutes
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=5753359.log
patchtop=/software/upgrades/11.5.10.2toR12/r11/5753359 driver=u5753359.drv workers=4
-- Apply patch u5467526.drv

unzip p5467526_11i_LINUX.zip
cd 5467526
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=5467526.log
patchtop=/software/upgrades/11.5.10.2toR12/r11/5467526 driver=u5467526.drv workers=4

-- Apply patch u5120936.drv
unzip p5120936_11i_GENERIC.zip
cd 5120936
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=5120936.log
patchtop=/software/upgrades/11.5.10.2toR12/r12/5120936 driver=u5120936.drv workers=4
cd $AD_TOP/patch/115/sql

sqlplus apps/apps @adtums.sql /usr/tmp

-- Review TUMS report in /usr/tmp
-- Apply patch 5726010
unzip p5726010_11i_GENERIC.zip

cd 5726010/

sqlplus apps/apps @adgncons.sql apps apps
# modify the adcrtbsp.sql to specify the path for new tablespaces

sqlplus system/manager @adcrtbsp.sql

su - oracleupd

mkdir -p $ORACLE_HOME/appsutil/admin
cd $ORACLE_HOME/appsutil/admin
cp /software/upgrades/11.5.10.2toR12/r12/5726010/adgrants.sql .

sqlplus '/ as sysdba' @adgrants.sql applsys
-- Run rapidwiz to create R12 code tree
The installation will install R12 applications components and the database home
Select upgrade to Oracle Applications R12
Select upgrade actions screen
select create upgrade file system

enter the parameters required to set up your new environment run rapid install Here are the screen shots for the same

The Installation creates APPL_TOP, COMMON_TOP, INST_TOP and 10g Oracle Home
Move the new environment file into .bash_profile
su - applmgrupd
cd /d01/oracle/apps/apps_st/appl
mv .bash_profile .bash_profile_11.5.10
cat APPSVIS_linux1.env >> /home/applmgrupd/.bash_profile
Now onwards, you are connected to R12 application
-- Apply patch AD.A 4502962
-- takes 6 minutes
unzip p4502962_R12_LINUX.zip
cd 4502962/
adpatch

Please enter the name of your AutoPatch driver file: u4502962.drv
-- Run the American English upgrade patch driver
-- Takes 40 hours
cd $AU_TOP/patch/115/driver

adpatch options=nocopyportion,nogenerateportion

Please enter the name of your AutoPatch driver file: u4440000.drv
Run the NLS upgrade patch driver (conditional)
• Download the NLS Release 12 patch (4440000) for each active language in the system.
• Run each driver (u4440000.drv) with adpatch
• NLS patch driver has the same name as the American English patch driver.
Synchronize NLS and American English product patches (conditional)
• Generate manifest using perl $AD_TOP/bin/adgennls.pl
• Backup manifest file $APPL_TOP/admin/$TWO_TASK/out/adgennls.txt
• http://updates.oracle.com/TransSync
• Don’t check Translation Level Updates
• Upload manifest
• Download and apply patch
-- Configuration Phase
Disable maintenance mode
Run autoconfig

Run rapidwiz to configure and start processes
Sign on to R12 applications check concurrent managers

Run autoconfig
su - applmgrupd
perl $AD_TOP/bin/admkappsutil.pl
su - oracleupd
cd $ORACLE_HOME
cp /d01/oracle/inst/apps/TEST_linux1/admin/out/appsutil.zip .
unzip -o appsutil.zip
cd $ORACLE_HOME/appsutil/scripts/TEST_linux1
sh adautocfg.sh

Run rapidwiz to configure and start processes
-----------------------------------------
rapidwiz - Upgrade to Oracle Applications Release 12 -> Upgrade Action -> Select Configure Upgraded

Release 12 instance
$INST_TOP/admin/VIS_linux1.xml
/d01/oracle/inst/apps/TEST_linux1/appl/admin/TEST_linux1.xml

Note: I got the below error, but all services and the application is running.

Sign on to R12 applications check concurrent managers

Connect as sysadmin/sysadmin

-- Apply online help
cd $AU_TOP/patch/115/driver/
adpatch options=hotpatch,nocopyportion,nogenerateportion
Please enter the name of your AutoPatch driver file : u5051400.drv
-- Gather schema statistics for CBO
Release 12 employs cost-based optimization, which examines FND table statistics to determine the most
efficient access paths and join
methods for executing SQL statements. These statistics are gathered by the FND_STATS process, which
you initiate by running the
Gather Schema Statistics concurrent program.
From your Release 11i APPL_TOP, complete the following steps:
1. Log in to Oracle Applications with the System Administrator responsibility.
2. Navigate to the Submit Request window (Request > Run).
3. Submit the Gather Statistics program. (Schema)

References
1. Upgrading Oracle Applications Note# 289788.1
2. Oracle 10gR2 Database Preparation Guidelines for an E-Business Suite R12 Note#403339.1
3. Oracle applications R11.5.10.2 Maintenance pack Installation Note#316365.1
4. Oracle Applications Upgrade Guide: Release 11i to Release 12 Part# B31566-01
5. Database Initialization Parameters for Oracle Applications Release 12 Note#396009.1
6. Oracle Applications Release Notes Release 12 Note# 405293.1
7. E-Business Suite Release 12 Upgrade Sizing and Best Practices Note# 399362.1
8. Upgrading to R12 Note# 414710.1
9. Maintenance Wizard Note# 215527.1

All Control file lost Recover database from RMAN Backup (ORA-00205: error in identifying control file)

2010-12-10T05:10:00.000-08:00

ll Control file lost Recover database from RMAN Backup (ORA-00205: error in identifying control file)
During media failure (single disk crash) the online redo log is usually available so it is possible to recover up to the point of failure. Quite often ,Windows Oracle instance can be started in nomount mode (i.e. spfile is also available).

In this scenario all Control files are lost and it’s not multiplexed. In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available.

In this example RMAN is not using catalog.

Try to start database and found following error.

d:> sqlplus sys/**** as sysdba;

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 29 11:05:12 2009

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

Connected to an idle instance.

ORCL: SQL> startup;
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1291576 bytes
Variable Size 306186952 bytes
Database Buffers 213909504 bytes
Redo Buffers 2899968 bytes
ORA-00205: error in identifying control file, check alert log for more info

In Alter log file I found following error:

Tue Sep 29 11:05:18 2009
ALTER DATABASE MOUNT
Tue Sep 29 11:05:18 2009
ORA-00202: control file: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Complete Recovery by last night RMAN BACKUP set

1. d:\> rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Sep 29 10:19:36 2009

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

connected to target database: ORCL (not mounted)

2. RMAN> set dbid=1096578125
executing command: SET DBID

****set DBID - get this from the name of the controlfile autobackup.
For example, if autobackup name is CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is 1507972899. This step will not be required if the instance is

3. RMAN> set controlfile autobackup format for device type disk to 'D:\ABC\BACKUP\RMAN\ORCL\cf_%d_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

**** Verify the format of your backup set

4. RMAN> restore controlfile from autobackup;
Starting restore at 29-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: D:\oracle\product\10.2.0\flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20090929
channel ORA_DISK_1: looking for autobackup on day: 20090928
channel ORA_DISK_1: autobackup found: D:\ABC\BACKUP\RMAN\ORCL\cf_ORCL_c-10965
78125-20090928-03
channel ORA_DISK_1: control file restore from autobackup complete
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 29-SEP-09

*** Now that control files have been restored, the instance can mount the

5. RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

6. RMAN> restore database;
Starting restore at 29-SEP-09
Starting implicit crosscheck backup at 29-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 29-SEP-09

Starting implicit crosscheck copy at 29-SEP-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 29-SEP-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_51_5D38N85L_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_01.DBF
restoring datafile 00006 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_02.DBF
restoring datafile 00007 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS1_01.DBF
restoring datafile 00008 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS1_02.DBF
restoring datafile 00009 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS2_01.DBF
restoring datafile 00010 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS2_02.DBF
restoring datafile 00011 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS3_01.DBF
restoring datafile 00012 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS3_02.DBF
restoring datafile 00013 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS4_01.DBF
restoring datafile 00014 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS4_02.DBF
restoring datafile 00015 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS5_01.DBF
restoring datafile 00016 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS5_02.DBF
restoring datafile 00017 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS6_01.DBF
restoring datafile 00018 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS6_02.DBF
restoring datafile 00019 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_S_01.DBF
restoring datafile 00020 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_S_02.DBF
restoring datafile 00021 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_SI_01.DBF
restoring datafile 00022 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_SI_02.DBF
restoring datafile 00023 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7_01.DBF

restoring datafile 00024 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7_02.DBF

restoring datafile 00025 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7I_01.DB
F
restoring datafile 00026 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7I_02.DB
F
restoring datafile 00027 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUD_01.DBF

restoring datafile 00028 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUD_02.DBF

restoring datafile 00029 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUDI_01.DB
F
restoring datafile 00030 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUDI_02.DB
F
restoring datafile 00031 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_RHAPSODY_0
1.DBF
restoring datafile 00032 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_RHAPSODY_0
2.DBF
restoring datafile 00033 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_OPIS_RPT_0
1.DBF
restoring datafile 00034 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_OPIS_RPT_0
2.DBF
channel ORA_DISK_1: reading from backup piece D:\ABC\BACKUP\RMAN\ORCL\DB_ORCL
_T698759044_S731_P1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ABC\BACKUP\RMAN\ORCL\DB_ORCL_T698759044_S731_P1 tag=FULL BACK
UP
channel ORA_DISK_1: restore complete, elapsed time: 00:01:39
Finished restore at 29-SEP-09

** Database must be recovered because all datafiles have been restored from backup

7. RMAN> recover database;

Starting recover at 29-SEP-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 48 to 52 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC
archive log thread 1 sequence 53 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\ORADATA\ORCL\REDO02.LOG
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=46
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=47
channel ORA_DISK_1: reading from backup piece D:\ABC\BACKUP\RMAN\ORCL\ARCH_P
OM_1_T698759096_S734_P1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ABC\BACKUP\RMAN\ORCL\ARCH_ORCL_1_T698759096_S734_P1 tag=TAG20
090928T114456
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_46_5D475JO8_.ARC thread=1 sequence=46
channel default: deleting archive log(s)
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_46_5D475JO8_.ARC recid=641 stamp=698841715
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_47_5D475K17_.ARC thread=1 sequence=47
channel default: deleting archive log(s)
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_47_5D475K17_.ARC recid=640 stamp=698841713
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_51_5D38N85L_.ARC thread=1 sequence=48 to 51
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC thread=1 sequence=52
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG thread=1 se
quence=53
media recovery complete, elapsed time: 00:00:18
Finished recover at 29-SEP-09

8. RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/29/2009 10:42:28
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

-- Recovery completed. The database must be opened with RESETLOGS
-- because a backup control file was used. Can also use
-- "alter database open resetlogs" instead.

9. RMAN> alter database open resetlogs;
database opened

**** Several points are worth emphasizing.*****
1. Recovery using a backup controlfile should be done only if a current control file is
unavailable.
2. All datafiles must be restored from backup. This means the database will need to be
recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.

3. As with any database recovery involving RESETLOGS, take a fresh backup immediately.

4. Technically the above is an example of complete recovery - since all committed
transactions were recovered. However, some references consider this to be incomplete
recovery because the database log sequence had to be reset.
After recovery using a backup controlfile, all temporary files associated with locallymanaged tablespaces are no longer available. You can check that this is so by querying the view V$TEMPFILE - no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:

SQL> alter tablespace temp add tempfile
'D:\oracle_data\datafiles\ORCL\TEMP01.DBF';
Tablespace altered.

Reclaim disk space from database/ Reduce data file size to free disk

2010-12-10T05:03:00.000-08:00

My tablespace is fragmented and I want to reduce the size of my dbf files to free up disk. How do I reclaim disk space in Oracle?

Oracle maps the physical files (customer.dbf) to their logical "tablespace" construct and Oracle places objects (tables & indexes) into the tablespace.
Within the tablespace, objects are scattered throughout the tablespace and corresponding datafiles.

Tables, indexes and tablespaces will naturally fragment as a function of update activity and Oracle has many methods for reclaiming disk space and a segment advisor which will recommend when tables and indexes will benefit from a reorganization to free up disk space.

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.

Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space.

So how much DISK space you can claim from your data file (Calculate)?

Let run this script:

SELECT substr(FILE_NAME,1,55),
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) Order by SAVINGS

You will ask for BOCKSIZE.

You can find database block size from intXXX.ora file.
db_block_size=8192

So in this case enter 8192 as parameter.

The output will be like this:

ORCL: SQL> SELECT substr(FILE_NAME,1,55),
2 CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
3 CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
4 CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
5 FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
6 WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) Order by SAVINGS
7 /
old 2: CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
new 2: CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SMALLEST,
old 3: CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
new 3: CEIL( BLOCKS*8192/1024/1024) CURRSIZE,
old 4: CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
new 4: CEIL( BLOCKS*8192/1024/1024) - CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SAVINGS

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_AUD_02.DBF 80 80 0
D:\ ORADATA\ORCL\DATA6_02.DBF 200 200 0
D:\ ORADATA\ORCL\DATA1_02.DBF 520 520 0
D:\ ORADATA\ORCL\DATA_S_03.DBF 20 20 0
D:\ ORADATA\ORCL\DATA_AUD_01.DBF 60 60 0
D:\ ORADATA\ORCL\DATA_02.DBF 120 120 0
D:\ ORADATA\ORCL\DATA4_01.DBF 20 20 0
D:\ ORADATA\ORCL\DATA5_02.DBF 100 100 0
D:\ ORADATA\ORCL\DATA_AUDI_01.DBF 17 20 3
D:\ ORADATA\ORCL\SYSAUX01.DBF 197 200 3
D:\ ORADATA\ORCL\DATA_AUDI_02.DBF 16 20 4

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_01.DBF 135 140 5
D:\ ORADATA\ORCL\DATA_SI_01.DBF 13 20 7
D:\ ORADATA\ORCL\DATA6_01.DBF 213 220 7
D:\ ORADATA\ORCL\DATA2_02.DBF 9 20 11
D:\ ORADATA\ORCL\DATA2_01.DBF 8 20 12
D:\ ORADATA\ORCL\DATA4_02.DBF 27 40 13
D:\ ORADATA\ORCL\DATA_S_02.DBF 26 40 14
D:\ ORADATA\ORCL\DATA1_01.DBF 545 560 15
D:\ ORADATA\ORCL\USERS01.DBF 2 19 17
D:\ ORADATA\ORCL\DATA5_01.DBF 102 120 18
D:\ ORADATA\ORCL\DATA_HL7_02.DBF 1 20 19

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_HL7I_02.DBF 1 20 19
D:\ ORADATA\ORCL\DATA3_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_HL7_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_HL7I_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA3_02.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_S_01.DBF 20 40 20
D:\ ORADATA\ORCL\DATA_SI_02.DBF 16 40 24

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\SYSTEM01.DBF 265 300 35
D:\ ORADATA\ORCL\UNDOTBS01.DBF 34 85 51

Let’s write a dynamic script to resize data file

set pagesize 0
set verify off
set heading off
set feedback off
set linesize 300

prompt Running Shrink_data_&&1..dbsql
spool Shrink_data_&&1..dbsql

SELECT 'ALTER DATABASE DATAFILE '|| ' '''|| substr(FILE_NAME,1,65) ||' '''|| ' RESIZE ' || CEIL(
(NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) ||'M; ' FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID,MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) and CEIL(
BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )>1

spool off
set echo on
spool Shrink_data_&&1..log

rem @Shrink_data_&&1..dbsql
spool

spool off

exit

You will see all entries of data files in spool output (Shrink_data.log).

For example:
ALTER DATABASE DATAFILE 'D:\ ORADATA\ORCL\SYSTEM01.DBF ' RESIZE 265M;

This is a dynamic script and tested in several databases.

Calculate the total size of Oracle Database .

2010-12-09T04:10:00.000-08:00

The following script calculates the storage allocated to an Oracle database, i.e., its overall size. You will need DBA privilege to run this script.

sql>define_editor=ed
sql>select ROUND( ( a.data_size + b.temp_size +
c.redo_size + d.cf_size +
e.bct_size)
/1024/1024/1024
) "total_GB_size"
from
( select SUM(bytes) data_size from v$datafile) a,
( select NVL(sum(bytes),0) temp_size from v$tempfile) b,
( select SUM(bytes) redo_size from v$log) c,
( select SUM(block_size*file_size_blks) cf_size from v$controlfile) d,
( select NVL(bytes,0) bct_size from v$block_change_tracking) e

sql>select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files )a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c

The following command calculates the Harddisk space of linux i.e., its overall size. You will need root privilege to run this script.

#fdisk -l | grep Disk

#vi /proc/cpuinfo

# free -m

Data Guard configuring

2010-12-07T06:38:00.000-08:00

The purpose of this blog entry is to show you how to create manually a physical standby database.

The first will be a basic primary and standby database setup on 2 different machines. In this case, I used VMWARE.

If we afford to lose few minutes of data then this is very good cheap solution for disaster recovery solution.

The Standby Database

• Oracle supplied disaster recovery (DR) solution.
• Operates in 2 modes –manual and automatic.
• No GUI.
• Available since 7.3.4.
• Available with Std Edition one

Step by step document to create manual Standby Database

1 - Prerequisites

• Same Oracle software version must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
• The Primary Database must run in ARCHIVELOG mode.
• Each primary and standby database must have its own control file.
• If primary and standby database
• s are placed on the same system, initialization parameters must be adjusted correctly.

Preparing Primary Database for Standby Database creation
Configuration in Primary Database

2 Ensure the primary database in ARCHIVELOG mode

Ensure the primary database in ARCHIVELOG mode using following command.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\ora92\database\archive\ORCLC
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11

3 - Identify the primary database Datafiles
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:\ORACLE\ORADATA\ORCLC\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCLC\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCLC\CWMLITE01.DBF
C:\ORACLE\ORADATA\ORCLC\DRSYS01.DBF
C:\ORACLE\ORADATA\ORCLC\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCLC\INDX01.DBF
C:\ORACLE\ORADATA\ORCLC\ODM01.DBF
C:\ORACLE\ORADATA\ORCLC\TOOLS01.DBF
C:\ORACLE\ORADATA\ORCLC\USERS01.DBF
C:\ORACLE\ORADATA\ORCLC\XDB01.DBF

10 rows selected.

4 - Make a cold backup copy of Primary Database
Make a cold backup copy of primary database by performing following steps:

4.1 Shutdown the Primary Database
Issue the following statement to shutdown the primary database.
SQL> shutdown immediate;

4.2 Copy the Datafiles to standby location
Copy the redo log files and Datafiles identified in section 3 to standby location.
Note: Primary Database must be shutdown while copying the files.

5 - Restart the Primary Database

Execute following command to restart the Primary Database.
SQL> startup;

6 - Create Control file for Standby Database
Issue the following command on primary database to create control file for the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\oradata\stby\control_sb01.ctl';

Database altered.
The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the cold backup of Datafiles and redo logs. Copy this standby controlfile to the standby server.

Configuration in Standby Database
7 - Create pfile for standby database from the primary database

Copy the pfile from the primary database to the standby server

8 - Set initialization parameters on physical standby database
Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made. Edit created pfile from primary database.

db_name - Not modified. The same name as the primary database.

compatible - Not modified. The same as the primary database,
control_files - Specify the path name and filename for the standby control file .

log_archive_start - Not modified. The same as the setting for the primary database, TRUE

db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required.

log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required.

instance_name - If both primary and standby databases are to reside in the same server, specify a different value for the standby database than the primary database. If standby database will be in a different server, then ignore this.
lock_name_space - - If both primary and standby databases are to reside in the same server, Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter.

Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database.

(Refer Annexure for initialization parameter settings for primary and standby database.)
9 - Create a Window service
If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window
C:\>oradim -new -sid ssss -intpwd yyyy -startmode manual

10 - Configure tnsnames for standby database in the standby server

11 - Start Physical standby database in standby mode

Start up the stand by database using following commands
C:\>set oracle_sid=ssss

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\oracle\ora92\database\initstby.ora' nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter database mount standby database;
Database altered.

12. Now you have to transfer archivelogs from Primary database and apply them to standby database

12.1 create a script in primary server to run under windows task at desired intervals (15 mins or 30 mins etc.), to transfer archivelogs from primary to standby:-

move _arch.cmd
==================
pushd C:\oracle\admin\scripts
set ORACLE_SID=xxx
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1

%ORACLE_HOME%\bin\sqlplus dbo/dbo @move_archivelog.sql

robocopy D:\oracle\backup\arch\roc8 \\172.30.36.13\oracle\backup\arch\roc8 /MIR /COPY:DT /FFT /log:robocopystatus.log

The sql below is to force a new archivelog from primary database:

Apply_archivelog.sql
==================
ALTER SYSTEM SWITCH LOGFILE;
exit;

**** Windows robocopy utility is used above to send archivelogs from arch folder in primary server to arch folder in standby server ****

12.2 apply the archivelogs arriving from primary server to the standby database every 15 mins or 30 mins under a windows task in standby server as follows:-

apply_arch.cmd
==============
pushd C:\oracle\admin\scripts
set ORACLE_SID=ssss
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
%ORACLE_HOME%\bin\sqlplus /nolog @ Apply_archivelog.sql

Apply_archivelog.sql
===============
connect sys/oracle as sysdba;
set timing on
set echo on
spool apply_archp.log
recover standby database;
AUTO
spool off
exit

License requirements by Oracle:

Standby database doesn’t need Oracle license for 10 days in calendar year.

Failover – In this type of recovery, nodes are arranged in a cluster and share
one disk array. A Failover cluster is a group of systems, bound together into a
common resource pool. In this type of recovery method, the Production node
acts as the primary node. When the primary node fails, one of the surviving
nodes in the cluster acts as the primary node. Solutions like Oracle Failsafe
(included with Oracle Database EE or SE, SE1), or third party vendor solutions
(e.g. Veritas, HP Service Guard, HACMP, Linux HA - Heartbeat) are used to
manage Failover environments. In this type of environment, Oracle permits
licensed Oracle customers to run some Technology Programs on an
unlicensed spare computer for up to a total of ten separate days in any given
calendar year. Once the primary node is repaired, you must switch back to the
primary node. Once the failover period has exceeded ten days, the failover
node must be licensed.

Source: Oracle SOFTWARE INVESTMENT GUIDE

Testing the environment

Once primary and Standby database is configured and transportation log is configured to transfer we need to test it thoroughly.

We can execute some DML and DDL in primary database. After archive logs are applied in Standby database we should able to see those DLL and DML changes in standby database.

To check it Open the database on read only mode.

SQL> alter database open read only;

After the test re-start the database in mount mode. If you OPEN the database you have to recreate the standby database again.
1. SQL> shutdown immediate;
2. SQL>startup mount;

Checking log file in Standby database:

If you check log file you might see error and warning like below.

ORCL: SQL> recover standby database;
ORA-00279: change 239868 generated at 10/20/2010 12:50:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_1
00_%U_.ARC
ORA-00280: change 239868 for thread 1 is in sequence #100

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 240264 generated at 10/20/2010 13:05:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_1
01_%U_.ARC
ORA-00280: change 240264 for thread 1 is in sequence #101
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_
100_6CY89YTC_.ARC' no longer needed for this recovery

ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_
101_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
TOM: SQL> spool off

If you see error like ORA-00308: cannot open archived log.

Reducing datafile size to recover free space – Oracle Database 10g

2010-12-06T01:47:00.000-08:00

At time, we want to recover some space from database just to allocate the same to some other tablespace or to return it back to OS disk. This situation arises many times.
And many time we hit with error “ORA-03297: file contains used data beyond requested RESIZE value“.
The concept is simple and many of you must be knowing, but just putting in a simpler words.
Lets take an example of one of the datafile in a database.
Lets see the total free space in a datafile 194.
SQL> select sum(bytes)/1024/1024 from dba_free_space
2 where tablespace_name = ‘APPS_TS_TX_DATA’
3 and file_id = 194;
SUM(BYTES)/1024/1024
——————–
1844.125
Now lets see the distribution for the file.
SQL> select file_id, block_id, blocks, bytes, ‘Free’ from dba_free_space
2 where tablespace_name = ‘APPS_TS_TX_DATA’
3 and file_id = 194
4 and rownum < 7
5 order by block_id desc;
   FILE_ID   BLOCK_ID     BLOCKS      BYTES ‘FRE
———- ———- ———- ———- —-
194      35001     220992 1810366464 Free
194      13433         16     131072 Free
194      13417         16     131072 Free
194      13401         16     131072 Free
194      13385         16     131072 Free
194      13369         16     131072 Free
We can see that there are so many blocks which are free. Its divided into chunks of 16 Blocks and each of these chunks are given a block ID. Now we can see that this formating is done until block 35001 and after that there is no partitions. Beyond the block ID 35001, the whole space is available as a single large unit. This is because of high water mark. When a object is created, it will be created physically in the datafile and will be allocated a block. The number of blocks it will be allocated will depend on the parameter “INITIAL EXTENT” which can be given at the time of creating an object. If we dont give this parameter it will take a default value of 16. So 16 block gets assigned when you create any abject, example a table.
You might be wondering that after block 35001, we have all free space and also we have free space at blocks 13401, 13417, 13433 etc. But where are the blocks between 13433 and 35001??
The answer can be found from dba_extents. All the blocks between 13433 and 35001 are occupied by the objects and are not free. That why you are not able to see them in dba_free_space view. But you can find then in dba_extents. So in case of file 194, objects were getting created until block no 35001 (so we can see that block formating till block 35001) and then at later point of time some of the objects got dropped, so the space got freed, but the formating of blocks remain intact (example block IDs which got freed are 13401, 13417, 13433 etc.). This we call it as high water mark for a datafile.
As we saw earlier that we have around 1844.125 MB free space. Can we recover all of them?? I wish .. but no, we cannot. Reason being that, some of the blocks are free “in-between” and there is a fragmentation. To make my point clear, lets try to reduce the file size.
Lets try that !!
This file original size is 2000M

SQL> select bytes/1024/1024 from dba_data_files where file_id = 194;

BYTES/1024/1024
—————
2000
and as per dba_free_space its having 1844.125 MB free space. Lets reduce the file size by 1800 MB and it will definately give error.
SQL> alter database datafile 194 resize 200M;
alter database datafile 194 resize 200M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

What happened??
The reason you are not able to free the space is because we have certain objects created in between and there is no chunk of 1800MB free. The structure of this file is as given below.
X -> Occupied Block
0 -> Free Block

So here block 35001, 13433 are free, but in-between blocks are occupied. When you are trying to reduce the size of datafile by 1800 MB, you are going beyond block ID 35001, where the objects do exits. After those objects there may be free space, but that doesnt matter, you can at max reduce the size of datafile until block ID 35001, and not beyond that.
So here if we see that there are around 220992 blocks free after block ID 35001. That makes a space of around (220992 X 8192 = 1810366464 bytes = 1726.5 MB free). Thats what the bytes column is showing.
So we can reduce the size of this datafile by maximum of 1810366464 bytes = 172.5 MB. If you try to reduce the size more than that, then you will hit with an error ORA-03297: file contains used data beyond requested RESIZE value.
Now try reducing by 1700 MB and it will succeed !!!
SQL> alter database datafile 194 resize 300M;
database altered
What if you want to still reduce the size beyond 1800 MB. i.e. what if you want to make the size to 200MB.
To do that, you need to do following steps.
- Find block ID at 1800th byte.
We know that last 1810366464 bytes are free.
1800 MB = 1887436800 Bytes
Bytes Remaining = 1887436800 – 1810366464 = 77070336 Bytes = 9408 Blocks
Block ID till we want to clear the objects id 35001 – 9408 = 25593
- Drop all the objects which are beyond this block. That will make 1800MB free in this datafile and you can reduce the size of datafile by 1800 MB.
Moral of story is dont get into these many calculations. Its good to know the logic and helps in crisis situation. But not good to go for such drop object, reduce the size if file and recreate the object again.

For 9i or higher versions script.

REM Script is meant for Oracle version 9 and higher

REM -----------------------------------------------



set serveroutput on

exec dbms_output.enable(1000000);



declare



cursor c_dbfile is

select f.tablespace_name,f.file_name,f.file_id,f.blocks,t.block_size

from   dba_data_files f,

dba_tablespaces t

where  f.tablespace_name = t.tablespace_name

and    t.status = 'ONLINE'

order  by f.tablespace_name,f.file_id;



cursor c_freespace(v_file_id in number) is

select block_id, block_id+blocks max_block

from   dba_free_space

where  file_id = v_file_id

order  by block_id desc;



/* variables to check settings/values */

dummy number;

checkval varchar2(10);

block_correction number;



/* running variable to show (possible) end-of-file */

file_min_block number;



/* variables to check if recycle_bin is on and if extent as checked is in ... */

recycle_bin boolean:=false;

extent_in_recycle_bin boolean;



/* exception handler needed for non-existing tables note:344940.1 */

sqlstr varchar2(100);

table_does_not_exist exception;

pragma exception_init(table_does_not_exist,-942);



begin



/* recyclebin is present in Oracle 10.2 and higher and might contain extent as checked */

begin

select value into checkval from v$parameter where  name = 'recyclebin';

if checkval = 'on'

then

recycle_bin := true;

end if;

exception

when no_data_found

then

recycle_bin := false;

end;



/* main loop */

for c_file in c_dbfile

loop

/* initialization of loop variables */

dummy :=0;

extent_in_recycle_bin := false;

file_min_block := c_file.blocks;



begin





<<check_free>>



for c_free in c_freespace(c_file.file_id)

loop

/* if blocks is an uneven value there is a need to correct with -1 to compare with end-of-file which is even */

block_correction := (0-mod(c_free.max_block,2));

if file_min_block = c_free.max_block+block_correction

then



/* free extent is at end so file can be resized */

file_min_block := c_free.block_id;



else

/* no more free extent at end of file, file cannot be further resized */

exit check_free;

end if;

end loop;

end;



/* check if file can be resized, minimal size of file 16 blocks */

if (file_min_block = c_file.blocks) or (c_file.blocks <= 16)

then



dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);

dbms_output.put_line('cannot be resized no free extents found');

dbms_output.put_line('.');



else



/* file needs minimal no of blocks which does vary over versions */

if file_min_block < 16

then

file_min_block := 16;

end if;



dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);

dbms_output.put_line('current size:  '||(c_file.blocks*c_file.block_size)/1024||'K'||' can be resized to:  '||round((file_min_block*c_file.block_size)/1024)||'K (reduction of:  '||round(((c_file.blocks-file_min_block)/c_file.blocks)*100,2)||' %)');



/* below is only true if recyclebin is on */

if recycle_bin

then

begin

sqlstr:='select distinct 1 from recyclebin$ where file#='||c_file.file_id;

execute immediate sqlstr into dummy;



if dummy > 0

then



dbms_output.put_line('Extents found in recyclebin for above file/tablespace');

dbms_output.put_line('Implying that purge of recyclebin might be needed in order to resize');

dbms_output.put_line('SQL> purge tablespace '||c_file.tablespace_name||';');

end if;

exception

when no_data_found

then null;

when table_does_not_exist

then null;

end;

end if;

dbms_output.put_line('SQL> alter database datafile  '''||c_file.file_name||''' resize  '||round((file_min_block*c_file.block_size)/1024)||'K;');

dbms_output.put_line('.');



end if;



end loop;



end;

/

Example output for Oracle version 9 and higher:

Tablespace: TEST Datafile: /oradata/v112/test01.dbf
cannot be resized no free extents found
.
Tablespace: UNDOTBS1 Datafile: /oradata/v112/undotbs01.dbf
current size: 9384960K can be resized to: 106496K (reduction of: 98.87 %)
SQL> alter database datafile '/oradata/v112/undotbs01.dbf' resize 106496K;
.
Tablespace: USERS Datafile: /oradata/v112/users01.dbf
current size: 328960K can be resized to: 117248K (reduction of: 64.36 %)
Extents found in recyclebin for above file/tablespace
Implying that purge of recyclebin might be needed in order to resize
SQL> purge tablespace USERS;
SQL> alter database datafile '/oradata/v112/users01.dbf' resize 117248K;

3. Redo Log File Management

2010-12-03T02:09:00.001-08:00

What Is the Online Redo Log?
The most crucial structure for recovery operations is the online redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle database has an associated online redo log to protect the database in case of an instance failure.
Redo Threads
Each database instance has its own online redo log groups. These online redo log groups, multiplexed or not, are called an instance's thread of online redo. In typical configurations, only one database instance accesses an Oracle database, so only one thread is present. When running Oracle Real Application Clusters, however, two or more instances concurrently access a single database and each instance has its own thread.
This chapter describes how to configure and manage the online redo log when the Oracle9i Real Application Clusters feature is not used. Hence, the thread number can be assumed to be 1 in all discussions and examples of statements.
Online Redo Log Contents
Online redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the rollback segment data block, and the transaction table of the rollback segments.
Redo entries record data that you can use to reconstruct all changes made to the database, including the rollback segments. Therefore, the online redo log also protects rollback data. When you recover the database using redo data, Oracle reads the change vectors in the redo records and applies the changes to the relevant blocks.
Redo records are buffered in a circular fashion in the redo log buffer of the SGA (see "How Oracle Writes to the Online Redo Log") and are written to one of the online redo log files by the Oracle background process Log Writer (LGWR). Whenever a transaction is committed, LGWR writes the transaction's redo records from the redo log buffer of the SGA to an online redo log file, and a system change number (SCN) is assigned to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.
Redo records can also be written to an online redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to an online redo log file, even though some redo records may not be committed. If necessary, Oracle can roll back these changes.
How Oracle Writes to the Online Redo Log
The online redo log of a database consists of two or more online redo log files. Oracle requires a minimum of two files to guarantee that one is always available for writing while the other is being archived (if in ARCHIVELOG mode).
LGWR writes to online redo log files in a circular fashion. When the current online redo log file fills, LGWR begins writing to the next available online redo log file. When the last available online redo log file is filled, LGWR returns to the first online redo log file and writes to it, starting the cycle again. Figure 7-1 illustrates the circular writing of the online redo log file. The numbers next to each line indicate the sequence in which LGWR writes to each online redo log file.
Filled online redo log files are available to LGWR for reuse depending on whether archiving is enabled.
* If archiving is disabled (NOARCHIVELOG mode), a filled online redo log file is available once the changes recorded in it have been written to the datafiles.
* If archiving is enabled (ARCHIVELOG mode), a filled online redo log file is available to LGWR once the changes recorded in it have been written to the datafiles and once the file has been archived.
Figure Circular Use of Online Redo Log Files by LGWR

Active (Current) and Inactive Online Redo Log Files
At any given time, Oracle uses only one of the online redo log files to store redo records written from the redo log buffer. The online redo log file that LGWR is actively writing to is called the current online redo log file.
Online redo log files that are required for instance recovery are called active online redo log files. Online redo log files that are not required for instance recovery are called inactive.
If you have enabled archiving (ARCHIVELOG mode), Oracle cannot reuse or overwrite an active online log file until ARCn has archived its contents. If archiving is disabled (NOARCHIVELOG mode), when the last online redo log file fills writing continues by overwriting the first available active file.
Log Switches and Log Sequence Numbers
A log switch is the point at which Oracle ends writing to one online redo log file and begins writing to another. Normally, a log switch occurs when the current online redo log file is completely filled and writing must continue to the next online redo log file. However, you can specify that a log switch occurs in a time-based manner, regardless of whether the current online redo log file is completely filled. You can also force log switches manually.
Oracle assigns each online redo log file a new log sequence number every time that a log switch occurs and LGWR begins writing to it. If Oracle archives online redo log files, the archived log retains its log sequence number. The online redo log file that is cycled back for use is given the next available log sequence number.
Each online or archived redo log file is uniquely identified by its log sequence number. During crash, instance, or media recovery, Oracle properly applies redo log files in ascending order by using the log sequence number of necessary archived and online redo log files.
Planning the Online Redo Log
This section describes guidelines you should consider when configuring a database instance's online redo log, and contains the following topics:
* Multiplexing Online Redo Log Files
* Placing Online Redo Log Members on Different Disks
* Setting the Size of Online Redo Log Members
* Choosing the Number of Online Redo Log Files
* Controlling Archive Lag
Multiplexing Online Redo Log Files

Oracle provides the capability to multiplex an instance's online redo log files to safeguard against damage to its online redo log files. When multiplexing online redo log files, LGWR concurrently writes the same redo log information to multiple identical online redo log files, thereby eliminating a single point of redo log failure.
Note:
Oracle recommends that you multiplex your redo log files. The loss of the log file data can be catastrophic if recovery is required.
Figure Multiplexed Online Redo Log Files

The corresponding online redo log files are called groups. Each online redo log file in a group is called a member. In Figure , A_LOG1 and B_LOG1 are both members of Group 1, A_LOG2 and B_LOG2 are both members of Group 2, and so forth. Each member in a group must be exactly the same size.
Notice that each member of a group is concurrently active, or, concurrently written to by LGWR, as indicated by the identical log sequence numbers assigned by LGWR. In Figure 7-2, first LGWR writes to A_LOG1 in conjunction with B_LOG1, then A_LOG2 in conjunction with B_LOG2, and so on. LGWR never writes concurrently to members of different groups (for example, to A_LOG1 and B_LOG2).
Responding to Online Redo Log Failure
Whenever LGWR cannot write to a member of a group, Oracle marks that member as INVALID and writes an error message to the LGWR trace file and to the database's alert file to indicate the problem with the inaccessible files. LGWR reacts differently when certain online redo log members are unavailable, depending on the reason for the unavailability.
If Then
LGWR can successfully write to at least one member in a group

Writing proceeds as normal. LGWR simply writes to the available members of a group and ignores the unavailable members.
LGWR cannot access the next group at a log switch because the group needs to be archived

Database operation temporarily halts until the group becomes available, or, until the group is archived.
All members of the next group are inaccessible to LGWR at a log switch because of media failure

Oracle returns an error and the database instance shuts down. In this case, you may need to perform media recovery on the database from the loss of an online redo log file.
If the database checkpoint has moved beyond the lost redo log, media recovery is not necessary since Oracle has saved the data recorded in the redo log to the datafiles. Simply drop the inaccessible redo log group. If Oracle did not archive the bad log, use ALTER DATABASE CLEAR UNARCHIVED LOG to disable archiving before the log can be dropped.
If all members of a group suddenly become inaccessible to LGWR while it is writing to them

Oracle returns an error and the database instance immediately shuts down. In this case, you may need to perform media recovery. If the media containing the log is not actually lost--for example, if the drive for the log was inadvertently turned off--media recovery may not be needed. In this case, you only need to turn the drive back on and let Oracle perform instance recovery.
Legal and Illegal Configurations
To safeguard against a single point of online redo log failure, a multiplexed online redo log is ideally symmetrical: all groups of the online redo log have the same number of members. Nevertheless, Oracle does not require that a multiplexed online redo log be symmetrical. For example, one group can have only one member, while other groups have two members. This configuration protects against disk failures that temporarily affect some online redo log members but leave others intact.
The only requirement for an instance's online redo log is that it have at least two groups. Figure legal and illegal multiplexed online redo log configurations. The second configuration is illegal because it has only one group.
Figure Legal and Illegal Multiplexed Online Redo Log Configuration

Placing Online Redo Log Members on Different Disks
When setting up a multiplexed online redo log, place members of a group on different disks. If a single disk fails, then only one member of a group becomes unavailable to LGWR and other members remain accessible to LGWR, so the instance can continue to function.
If you archive the redo log, spread online redo log members across disks to eliminate contention between the LGWR and ARCn background processes. For example, if you have two groups of duplexed online redo log members, place each member on a different disk and set your archiving destination to a fifth disk. Consequently, there is never contention between LGWR (writing to the members) and ARCn (reading the members).
Datafiles and online redo log files should also be on different disks to reduce contention in writing data blocks and redo records.
Setting the Size of Online Redo Log Members
When setting the size of online redo log files, consider whether you will be archiving the redo log. Online redo log files should be sized so that a filled group can be archived to a single unit of offline storage media (such as a tape or disk), with the least amount of space on the medium left unused. For example, suppose only one filled online redo log group can fit on a tape and 49% of the tape's storage capacity remains unused. In this case, it is better to decrease the size of the online redo log files slightly, so that two log groups could be archived for each tape.
With multiplexed groups of online redo logs, all members of the same group must be the same size. Members of different groups can have different sizes. However, there is no advantage in varying file size between groups. If checkpoints are not set to occur between log switches, make all groups the same size to guarantee that checkpoints occur at regular intervals.
Choosing the Number of Online Redo Log Files
The best way to determine the appropriate number of online redo log files for a database instance is to test different configurations. The optimum configuration has the fewest groups possible without hampering LGWR's writing redo log information.
In some cases, a database instance may require only two groups. In other situations, a database instance may require additional groups to guarantee that a recycled group is always available to LGWR. During testing, the easiest way to determine if the current online redo log configuration is satisfactory is to examine the contents of the LGWR trace file and the database's alert log. If messages indicate that LGWR frequently has to wait for a group because a checkpoint has not completed or a group has not been archived, add groups.
Consider the parameters that can limit the number of online redo log files before setting up or altering the configuration of an instance's online redo log. The following parameters limit the number of online redo log files that you can add to a database:
* The MAXLOGFILES parameter used in the CREATE DATABASE statement determines the maximum number of groups of online redo log files for each database. Group values can range from 1 to MAXLOGFILES. The only way to override this upper limit is to re-create the database or its control file. Thus, it is important to consider this limit before creating a database. If MAXLOGFILES is not specified for the CREATE DATABASE statement, Oracle uses an operating system specific default value.
* The MAXLOGMEMBERS parameter used in the CREATE DATABASE statement determines the maximum number of members for each group. As with MAXLOGFILES, the only way to override this upper limit is to re-create the database or control file. Thus, it is important to consider this limit before creating a database. If no MAXLOGMEMBERS parameter is specified for the CREATE DATABASE statement, Oracle uses an operating system default value.
You can force all enabled online redo log threads to switch their current logs in a time-based fashion. In a primary/standby configuration, changes are made available to the standby database by archiving and shipping logs of the primary site to the standby database. The changes that are being applied by the standby database can lag the changes that are occurring on the primary database.
This lag can happen because the standby database must wait for the changes in the primary database's online redo log to be archived (into the archived redo log) and then shipped to it. To control or limit this lag, you set the ARCHIVE_LAG_TARGET initialization parameter. Setting this parameter allows you to limit, measured in time, how long the lag can become.
Setting the ARCHIVE_LAG_TARGET Initialization Parameter
When you set the ARCHIVE_LAG_TARGET initialization parameter, you cause Oracle to examine an instance's current online redo log periodically. If the following conditions are met the instance will switch the log:
* The current log was created prior to n seconds ago, and the estimated archival time for the current log is m seconds (proportional to the number of redo blocks used in the current log), where n + m exceeds the value of the ARCHIVE_LAG_TARGET initialization parameter.
* The current log contains redo records.
In an Oracle Real Application Clusters environment, the instance also nudges other threads into switching and archiving logs if they are falling behind. This can be particularly useful when one instance in the cluster is more idle than the other instances (as when you are running a 2-node primary/secondary configuration of Oracle Real Application Clusters).
Initialization parameter ARCHIVE_LAG_TARGET specifies the target of how many seconds of redo the standby could lose in the event of a primary shutdown or crash if the Data Guard environment is not configured in a no-data-loss mode. It also provides an upper limit of how long (in the number of seconds) the current log of the primary database can span. Because the estimated archival time is also considered, this is not the exact log switch time.
The following initialization parameter setting sets the log switch interval to 30 minutes (a typical value).
ARCHIVE_LAG_TARGET = 1800
A value of 0 disables this time-based log switching functionality. This is the default setting.
You can set the ARCHIVE_LAG_TARGET initialization parameter even if there is no standby database. For example, the ARCHIVE_LAG_TARGET parameter can be set specifically to force logs to be switched and archived.
ARCHIVE_LAG_TARGET is a dynamic parameter and can be set with the ALTER SYSTEM SET statement.
Caution:
The ARCHIVE_LAG_TARGET parameter must be set to the same value in all instances of an Oracle Real Application Clusters environment. Failing to do so results in unspecified behavior and is strongly discouraged.
Factors Affecting the Setting of ARCHIVE_LAG_TARGET
Consider the following factors when determining if you want to set the ARCHIVE_LAG_TARGET parameter and in determining the value for this parameter.
* Overhead of switching (as well as archiving) logs
* How frequently normal log switches occur as a result of log full conditions
* How much redo loss is tolerated in the standby database
Setting ARCHIVE_LAG_TARGET may not be very useful if natural log switches already occur more frequently than the interval specified. However, in the case of irregularities of redo generation speed, the interval does provide an upper limit for the time range each current log covers.
If the ARCHIVE_LAG_TARGET initialization parameter is set to a very low value, there can be a negative impact on performance. This can force frequent log switches. Set the parameter to a reasonable value so as not to degrade the performance of the primary database.
Creating Online Redo Log Groups and Members
Plan the online redo log of a database and create all required groups and members of online redo log files during database creation. However, there are situations where you might want to create additional groups or members. For example, adding groups to an online redo log can correct redo log group availability problems.
To create new online redo log groups and members, you must have the ALTER DATABASE system privilege. A database can have up to MAXLOGFILES groups.
To create a new group of online redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause.
The following statement adds a new group of redo logs to the database:
ALTER DATABASE
ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K;
Note:
Use fully specify filenames of new log members to indicate where the operating system file should be created. Otherwise, the files will be created in either the default or current directory of the database server, depending upon your operating system.
You can also specify the number that identifies the group using the GROUP option:
ALTER DATABASE
ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')
SIZE 500K;
Using group numbers can make administering redo log groups easier. However, the group number must be between 1 and MAXLOGFILES. Do not skip redo log file group numbers (that is, do not number your groups 10, 20, 30, and so on), or you will consume space in the control files of the database.
Creating Online Redo Log Members
In some cases, it might not be necessary to create a complete group of online redo log files. A group could already exist, but not be complete because one or more members of the group were dropped (for example, because of a disk failure). In this case, you can add new members to an existing group.
To create new online redo log members for an existing group, use the SQL statement ALTER DATABASE with the ADD LOG MEMBER parameter. The following statement adds a new redo log member to redo log group number 2:
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;
Notice that filenames must be specified, but sizes need not be. The size of the new members is determined from the size of the existing members of the group.
When using the ALTER DATABASE statement, you can alternatively identify the target group by specifying all of the other members of the group in the TO parameter, as shown in the following example:
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo'
TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');
Note:
Fully specify the filenames of new log members to indicate where the operating system files should be created. Otherwise, the files will be created in either the default or current directory of the database server, depending upon your operating system. You may also note that the status of the new log member is shown as INVALID. This is normal and it will change to active (blank) when it is first used.
Relocating and Renaming Online Redo Log Members
You can use operating system commands to relocate online redo logs, then use the ALTER DATABASE statement to make their new names (locations) known to the database. This procedure is necessary, for example, if the disk currently used for some online redo log files is going to be removed, or if datafiles and a number of online redo log files are stored on the same disk and should be separated to reduce contention.
To rename online redo log members, you must have the ALTER DATABASE system privilege. Additionally, you might also need operating system privileges to copy files to the desired location and privileges to open and back up the database.
Before relocating your redo logs, or making any other structural changes to the database, completely back up the database in case you experience problems while performing the operation. As a precaution, after renaming or relocating a set of online redo log files, immediately back up the database's control file.
Use the following steps for relocating redo logs. The example used to illustrate these steps assumes:
* The log files are located on two disks: diska and diskb.
* The online redo log is duplexed: one group consists of the members /diska/logs/log1a.rdo and /diskb/logs/log1b.rdo, and the second group consists of the members /diska/logs/log2a.rdo and /diskb/logs/log2b.rdo.
* The online redo log files located on diska must be relocated to diskc. The new filenames will reflect the new location: /diskc/logs/log1c.rdo and /diskc/logs/log2c.rdo.
Steps for Renaming Online Redo Log Members
1. Shut down the database.
SHUTDOWN
2. Copy the online redo log files to the new location.
Operating system files, such as online redo log members, must be copied using the appropriate operating system commands. See your operating system specific documentation for more information about copying files.
Note:
You can execute an operating system command to copy a file (or perform other operating system commands) without exiting SQL*Plus by using the HOST command. Some operating systems allow you to use a character in place of the word HOST. For example, you can use ! in UNIX.
The following example uses operating system commands (UNIX) to move the online redo log members to a new location:
mv /diska/logs/log1a.rdo /diskc/logs/log1c.rdo
mv /diska/logs/log2a.rdo /diskc/logs/log2c.rdo
3. Startup the database, mount, but do not open it.
CONNECT / as SYSDBA
STARTUP MOUNT
4. Rename the online redo log members.
Use the ALTER DATABASE statement with the RENAME FILE clause to rename the database's online redo log files.
ALTER DATABASE
RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';
5. Open the database for normal operation.
The online redo log alterations take effect when the database is opened.
ALTER DATABASE OPEN;
Dropping Online Redo Log Groups and Members
In some cases, you may want to drop an entire group of online redo log members. For example, you want to reduce the number of groups in an instance's online redo log. In a different case, you may want to drop one or more specific online redo log members. For example, if a disk failure occurs, you may need to drop all the online redo log files on the failed disk so that Oracle does not try to write to the inaccessible files. In other situations, particular online redo log files become unnecessary. For example, a file might be stored in an inappropriate location.
Dropping Log Groups
To drop an online redo log group, you must have the ALTER DATABASE system privilege. Before dropping an online redo log group, consider the following restrictions and precautions:
* An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)
* You can drop an online redo log group only if it is inactive. If you need to drop the current group, first force a log switch to occur.
* Make sure an online redo log group is archived (if archiving is enabled) before dropping it. To see whether this has happened, use the V$LOG view.
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP# ARC STATUS
--------- --- ----------------
1 YES ACTIVE
2 NO CURRENT
3 YES INACTIVE
4 YES INACTIVE
Drop an online redo log group with the SQL statement ALTER DATABASE with the DROP LOGFILE clause.
The following statement drops redo log group number 3:
ALTER DATABASE DROP LOGFILE GROUP 3;
When an online redo log group is dropped from the database, and you are not using the Oracle Managed Files feature, the operating system files are not deleted from disk. Rather, the control files of the associated database are updated to drop the members of the group from the database structure. After dropping an online redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files.
When using Oracle-managed files, the cleanup of operating systems files is done automatically for you.
Dropping Online Redo Log Members
To drop an online redo log member, you must have the ALTER DATABASE system privilege. Consider the following restrictions and precautions before dropping individual online redo log members:
* It is permissible to drop online redo log files so that a multiplexed online redo log becomes temporarily asymmetric. For example, if you use duplexed groups of online redo log files, you can drop one member of one group, even though all other groups have two members each. However, you should rectify this situation immediately so that all groups have at least two members, and thereby eliminate the single point of failure possible for the online redo log.
* An instance always requires at least two valid groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.) If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid. To see a redo log file's status, use the V$LOGFILE view. A redo log file becomes INVALID if Oracle cannot access it. It becomes STALE if Oracle suspects that it is not complete or correct. A stale log file becomes valid again the next time its group is made the active group.
* You can drop an online redo log member only if it is not part of an active or current group. If you want to drop a member of an active group, first force a log switch to occur.
* Make sure the group to which an online redo log member belongs is archived (if archiving is enabled) before dropping the member. To see whether this has happened, use the V$LOG view.
To drop specific inactive online redo log members, use the ALTER DATABASE statement with the DROP LOGFILE MEMBER clause.
The following statement drops the redo log /oracle/dbs/log3c.rdo:
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo';
When an online redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping an online redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log file.
To drop a member of an active group, you must first force a log switch.
Forcing Log Switches
A log switch occurs when LGWR stops writing to one online redo log group and starts writing to another. By default, a log switch occurs automatically when the current online redo log file group fills.
You can force a log switch to make the currently active group inactive and available for online redo log maintenance operations. For example, you want to drop the currently active group, but are not able to do so until the group is inactive. You may also wish to force a log switch if the currently active group needs to be archived at a specific time before the members of the group are completely filled. This option is useful in configurations with large online redo log files that take a long time to fill.
To force a log switch, you must have the ALTER SYSTEM privilege. Use the ALTER SYSTEM statement with the SWITCH LOGFILE clause.
The following statement forces a log switch:
ALTER SYSTEM SWITCH LOGFILE;
Verifying Blocks in Redo Log Files
You can configure Oracle to use checksums to verify blocks in the redo log files. If you set the initialization parameter DB_BLOCK_CHECKSUM to TRUE, block checking is enabled for all Oracle database blocks written to disk, including redo log blocks. The default value of DB_BLOCK_CHECKSUM is FALSE.
If you enable block checking, Oracle computes a checksum for each redo log block written to the current log. Oracle writes the checksum in the header of the block. Oracle uses the checksum to detect corruption in a redo log block. Oracle tries to verify the redo log block when it writes the block to an archive log file and when the block is read from an archived log during recovery.
If Oracle detects a corruption in a redo log block while trying to archive it, the system attempts to read the block from another member in the group. If the block is corrupted in all members the redo log group, then archiving cannot proceed.
Note:
There is some overhead and decrease in database performance with DB_BLOCK_CHECKSUM enabled. Monitor your database performance to decide if the benefit of using data block checksums to detect corruption outweights the performance impact.
Clearing an Online Redo Log File
An online redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.
The following statement clears the log files in redo log group number 3:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
This statement overcomes two situations where dropping redo logs is not possible:
* If there are only two log groups
* The corrupt redo log file belongs to the current group
If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.
If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. Oracle writes a message in the alert log describing the backups from which you cannot recover.
Note:
If you clear an unarchived redo log file, you should make another backup of the database.
If you want to clear an unarchived redo log that is needed to bring an offline tablespace online, use the UNRECOVERABLE DATAFILE clause in the ALTER DATABASE CLEAR LOGFILE statement.
If you clear a redo log needed to bring an offline tablespace online, you will not be able to bring the tablespace online again. You will have to drop the tablespace or perform an incomplete recovery. Note that tablespaces taken offline normal do not require recovery.
Viewing Online Redo Log Information
Use the following views to display online redo log information.
View Description
V$LOG

Displays the redo log file information from the control file
V$LOGFILE

Identifies redo log groups and members and member status
V$LOG_HISTORY

Contains log history information
The following query returns the control file information about the online redo log for a database.
SELECT * FROM V$LOG;
GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------ ------- ----- ------- ------- --- --------- ------------- ---------
1 1 10605 1048576 1 YES ACTIVE 11515628 16-APR-00
2 1 10606 1048576 1 NO CURRENT 11517595 16-APR-00
3 1 10603 1048576 1 YES INACTIVE 11511666 16-APR-00
4 1 10604 1048576 1 YES INACTIVE 11513647 16-APR-00
To see the names of all of the member of a group, use a query similar to the following:
SELECT * FROM V$LOGFILE;
GROUP# STATUS MEMBER
------ ------- ----------------------------------
1 D:\ORANT\ORADATA\IDDB2\REDO04.LOG
2 D:\ORANT\ORADATA\IDDB2\REDO03.LOG
3 D:\ORANT\ORADATA\IDDB2\REDO02.LOG
4 D:\ORANT\ORADATA\IDDB2\REDO01.LOG
If STATUS is blank for a member, then the file is in use.

Oracle Database Basic

2010-12-02T01:40:00.000-08:00

What Is Oracle?

Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.

What Is an Oracle Database?

An Oracle database is a collection of data treated as a big unit in the database server.

What Is an Oracle Instance?

Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database.

What Is a Parameter File?

A parameter file is a file that contains a list of initialization parameters and a value for each parameter. You specify initialization parameters in a parameter file that reflect your particular installation. Oracle supports the following two types of parameter files:

Server Parameter Files - Binary version. Persistent.
Initialization Parameter Files - Text version. Not persistent.

What Is a Server Parameter File?

A server parameter file is a binary file that acts as a repository for initialization parameters. The server parameter file can reside on the machine where the Oracle database server executes. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup.

What Is a Initialization Parameter File?

An initialization parameter file is a text file that contains a list of initialization parameters. The file should be written in the client's default character set. Sample initialization parameter files are provided on the Oracle distribution medium for each operating system. A sample file is sufficient for initial use, but you will probably want to modify the file to tune the database for best performance. Any changes will take effect after you completely shut down and restart the instance.

What is System Global Area (SGA)?

The System Global Area (SGA) is a memory area that contains data shared between all database users such as buffer cache and a shared pool of SQL statements. The SGA is allocated in memory when an Oracle database instance is started, and any change in the value will take effect at the next startup.

What is Program Global Area (PGA)?

A Program Global Area (PGA) is a memory buffer that is allocated for each individual database session and it contains session specific information such as SQL statement data or buffers used for sorting. The value specifies the total memory allocated by all sessions, and changes will take effect as new sessions are started.

What Is a User Account?

A user account is identified by a user name and defines the user's attributes, including the following:

Password for database authentication
Privileges and roles
Default tablespace for database objects
Default temporary tablespace for query processing work space

What Is the Relation of a User Account and a Schema?

User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.

What Is a User Role?

A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users.

What is a Database Schema?

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include: tables, views, and other types of data objects.

What Is a Database Table?

A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.

What Is a Table Index?

Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

What Is an Oracle Tablespace?

An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.

Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

What Is an Oracle Data File?

An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.

What Is a Static Data Dictionary?

Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views:

An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.

What Is a Dynamic Performance View?

Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them.

What Is a Recycle Bin?

Recycle bin is a logical storage to hold the tables that have been dropped from the database, in case it was dropped in error. Tables in recycle bin can be recovered back into database by the Flashback Drop action. Oracle database recycle save the same purpose as the recycle bin on your Windows desktop.
Recycle bin can be turned on or off in the recyclebin=on/off in your parametere file.

What Is SQL*Plus?

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.

What Is Transport Network Substrate (TNS)?

TNS, Transport Network Substrate, is a foundation technology, built into the Oracle Net foundation layer that works with any standard network transport protocol.

What Is Open Database Communication (ODBC)?

ODBC, Open Database Communication, a standard API (application program interface) developed by Microsoft for Windows applications to communicate with database management systems.
Oracle offers ODBC drivers to allow Windows applications to connect Oracle server through ODBC.