Custom Top Create in Oracle APPS R12

create a Custom Top in Oracle R12

SCHEMA NAME : CUST
TOP NAME : CUST_TOP
Application : CUST Custom Application
Data Group : CUSTGroup
Request Group : CUST Request Group
Menu : CUST_CUSTOM_MENU
Responsibility : CUST Custom

STEP:1
Create the directory structure for your custom application files.

cd $APPL_TOP
mkdir CUST
mkdir CUST/12.0.0
mkdir CUST/12.0.0/admin
mkdir CUST/12.0.0/admin/sql
mkdir CUST/12.0.0/admin/odf
mkdir CUST/12.0.0/sql
mkdir CUST/12.0.0/bin
mkdir CUST/12.0.0/reports
mkdir CUST/12.0.0/reports/US
mkdir CUST/12.0.0/forms
mkdir CUST/12.0.0/forms/US
mkdir CUST/12.0.0/lib
mkdir CUST/12.0.0/out
mkdir CUST/12.0.0/log

STEP:2

2) Add the custom module into the environment

cd $APPL_TOP
[oracle@r0469 appl]$ cd /oracle/VIS/apps/apps_st/appl
[oracle@r0469 appl]$ vi CUSTOMVIS_r0469.env
CUST_TOP=/oracle/VIS/apps/apps_st/appl/cust/12.0.0
export CUST_TOP
:wq

STEP:3
Enter in APPSVIS.env file :-

customfile=/oracle/VIS/apps/apps_st/appl/CUSTOMVIS_r0469.env
if [ -f $customfile ]; then
. /oracle/VIS/apps/apps_st/appl/CUSTOMVIS_r0469.env
fi

STEP:4
Stop APPLICATION Tier
[oracle@r0469 scripts]$ ./adstpall.sh apps/apps

Run ADAUTOCFG.sh

[oracle@r0469 scripts]$ ./adautocfg.sh
AutoConfig completed successfully.

Start APPLICATION Tier
[oracle@r0469 scripts]$ ./adstrtal.sh apps/apps

STEP:5

Create new tablespace for database objects:

[oracle@r0469 ~]$ cd /oracle/VIS/db/tech_st/11.1.0/
[oracle@r0469 11.1.0]$ . VIS_r0469.env
[oracle@r0469 11.1.0]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Aug 31 16:23:12 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace cust datafile '/oracle/VIS/db/apps_st/data/cust.dbf' size 20M;

Tablespace created.

Create User:-

SQL> create user cust identified by cust default tablespace cust;
SQL> grant connect, resource to cust;

STEP:6
Register your Oracle Schema
Login to Applications with System Administrator responsibility
Navigate to Application-->Register
Application = CUST Custom
Short Name = CUST
Basepath = CUST_TOP
Description = CUST Custom Application

STEP:7
Register Oracle User
Naviate to Security-->Oracle-->Register
Database User Name = CUST
Password = CUST
Privilege = Enabled
Install Group = 0
Description = CUST Custom Application User

STEP:8
Add Application to a Data Group
Navigate to Security-->Oracle-->DataGroup
Data Group = CUSTGroup
Description = CUST Custom Data Group
Click on "Copy Applications from" and pick Standard data Group, then add the following entry.
Application = CUST Custom
Oracle ID = APPS
Description = CUSTCustom Application

STEP:9
Create custom request group
This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility (which is defined at a later stage)
Navigate to Security-->responsbility-->Request
Group = CUST Request Group
Application = CUST Custom
Code = CUST
Description = CUST Custom Requests
We will not define any requests to add to the group at this stage, but you can add some now if required.

STEP:10
Create custom menu
This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility (which is defined at a later stage) We will create two menus, one for Core Applications and one for Self Service.
Navigate to Application-->Menu
Menu = CUST_CUSTOM_MENU
User Menu Name = CUST Custom Application
Menu Type = <leave blank>
Description = CUST Custom Application Menu
Seq = 100
Prompt = View Requests
Submenu = <leave blank>
Function = View All Concurrent Requests
Description = View Requests

Seq = 110
Prompt = Run Requests
Submenu = <leave blank>
Function = Requests: Submit
Description = Submit Requests

Menu = CUST_CUSTOM_MENU_SSWA
User Menu Name = CUSTCustom Application SSWA
Menu Type = <leave blank>
Description = CUST Custom Application Menu for SSWA

STEP:11
Create new responsibility. One for Core Applications and One for Self Service (SSWA)
Navigate to Security-->Responsibility-->Define
Responsibility Name = CUST Custom
Application = CUSTCustom
Responsibility Key = CUSTCUSTOM
Description = CUST Custom Responsibility
Available From = Oracle Applications
Data Group Name = CUSTGroup
Data Group Application = CUST Custom
Menu = CUST Custom Application
Request Group Name = CUST Request Group

Responsibility Name =CUST Custom SSWA
Application = CUST Custom
Responsibility Key = CUST CUSTOMSSWA
Description = CUST Custom Responsibility SSWA
Available From = Oracle Self Service Web Applications
Data Group Name = CUST Group
Data Group Application = CUST Custom
Menu = CUST Custom Application SSWA
Request Group Name = CUST Request Group

STEP:12
Add responsibility to user
Navigate to Security-->User-->Define
Add CUST Custom responsibility to users as required.

STEP:13
Other considerations
You are now ready to create your database Objects, custom Reports, Forms, Packages, etc

Create the source code files in the CUST_TOP directory appropriate for the type of object. For example forms
would be located in $CUST_TOP/forms/US or package source code in $CUST_TOP/admin/sql for example.

Database Objects, such as tables, indexes and sequences should be created in the CUST schema, then you need to
a) Grant all privilege from each custom data object to the APPS schema.
For example : logged in as CUST user
grant all privileges on myTable to apps;

b) Create a synonym in APPS for each custom data object
For example : logged in as APPS user
create synonym myTable for CUST.myTable;

*********************************END***************************************

Starting / Stopping the Oracle RAC Cluster

oracle 11g RAC : Starting and Stopping Instances and RAC databases with srvctl and sql
==================
A. srvctl
==================
1. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
2. Stopping Instances and RAC databases with srvctl
[oracle@node1 ~]$ srvctl stop database -d node
3. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is not running on node node1
Instance node2 is not running on node node2
4. Starting Instances and RAC databases with srvctl
[oracle@node1 ~]$ srvctl start database -d node
5. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
6. Stopping Instances and RAC databases with srvctl (not all instance)
[oracle@node1 ~]$ srvctl stop instance -d node -i node2
7. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is not running on node node2
8. Starting Instances and RAC databases with srvctl (not all instance)
[oracle@node1 ~]$ srvctl start instance -d node -i node2
9. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2
==================
B. sql command
==================
10. status database with sql
[oracle@node1 ~]$ sqlplus system/oracle0@node1
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node1 node1 STARTED 1 OPEN
node2 node2 STARTED 2 OPEN
sql> exit;
11. Stopping Instances and RAC databases with sql (not all instance, in ex : node1)
[oracle@node1 ~]$ sqlplus sys/oracle0@node1 as sysdba
sql> shutdown;
sql> exit;
[oracle@node1 ~]$
12. status database with srvctl
[oracle@node1 ~]$ sqlplus system/oracle0@node2
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node2 node2 STARTED 2 OPEN
sql> exit;
[oracle@node1 ~]$
13. Starting Instances and RAC databases with sql (not all instance, in ex : node1)
[oracle@node1 ~]$ sqlplus sys/oracle0@node1 as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Tue Sep 23 13:46:08 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12521: TNS:listener does not currently know of instance requested in
connect descriptor
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1303244 bytes
Variable Size 557845812 bytes
Database Buffers 289406976 bytes
Redo Buffers 5160960 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@node1 ~]$
=========================================
Note use command : sqlplus / as sysdba
=========================================
ERROR:
ORA-12521: TNS:listener does not currently know of instance requested in
connect descriptor
14. status database with sql
[oracle@node1 ~]$ sqlplus system/oracle0@node2
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node2 node2 STARTED 2 OPEN
node1 node1 STARTED 1 OPEN

At this point, we've installed and configured Oracle RAC 10g entirely and have a fully functional clustered database.
After all the work done up to this point, you may well ask, "OK, so how do I start and stop services?" If you have followed the instructions in this guide, all services—including Oracle Clusterware, all Oracle instances, Enterprise Manager Database Console, and so on—should start automatically on each reboot of the Linux nodes.
There are times, however, when you might want to shut down a node and manually start it back up. Or you may find that Enterprise Manager is not running and need to start it. This section provides the commands (using SRVCTL) responsible for starting and stopping the cluster environment.
Ensure that you are logged in as the oracle UNIX user. We will runn all commands in this section from linux1:

# su - oracle

$ hostname
linux1

Stopping the Oracle RAC 10g Environment
The first step is to stop the Oracle instance. When the instance (and related services) is down, then bring down the ASM instance. Finally, shut down the node applications (Virtual IP, GSD, TNS Listener, and ONS).

$ export ORACLE_SID=orcl1
$ emctl stop dbconsole
$ srvctl stop instance -d orcl -i orcl1
$ srvctl stop asm -n linux1
$ srvctl stop nodeapps -n linux1

Starting the Oracle RAC 10g Environment
The first step is to start the node applications (Virtual IP, GSD, TNS Listener, and ONS). When the node applications are successfully started, then bring up the ASM instance. Finally, bring up the Oracle instance (and related services) and the Enterprise Manager Database console.

$ export ORACLE_SID=orcl1
$ srvctl start nodeapps -n linux1
$ srvctl start asm -n linux1
$ srvctl start instance -d orcl -i orcl1
$ emctl start dbconsole

Start/Stop All Instances with SRVCTL

Shutdown RAC Database Instances on all nodes: Suppose DBA has two node RAC database,
So he has to stop all instances from all DB nodes. Here,
I am taking an example of two node RAC. First,
I am checking on which server database is running and then stopping and verify for the same.

Syntax: srvctl stop database -d {databasename-Airtel}

[oracle@node2 ~]$ srvctl status database -d Airtel

Instance Airtel1 is running on node node1
Instance Airtel2 is running on node node2

[oracle@node2 ~]$ srvctl stop database -d Airtel

[oracle@node2 ~]$ srvctl status database -d Airtel
Instance Airtel1 is not running on node node1

Instance Airtel2 is not running on node node2

Start/stop all the instances and their enabled services. I have included this step just for fun as a way to bring down all instances!

$ srvctl start database -d orcl

$ srvctl stop database -d orcl

Checking CRS Status

Clusterware Resource Status Check

Stop the Oracle clusterware stack

Start the Oracle clusterware stack

Start the Oracle Database

Stop the Oracle Database

Checking CRS Status

crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

srvctl status nodeapps

VIP tzdev1vip is enabled
VIP tzdev1vip is running on node: tzdev1rac
VIP tzdev2vip is enabled
VIP tzdev2vip is running on node: tzdev2rac
Network is enabled
Network is running on node: tzdev1rac
Network is running on node: tzdev2rac
GSD is disabled
GSD is not running on node: tzdev1rac
GSD is not running on node: tzdev2rac
ONS is enabled
ONS daemon is running on node: tzdev1rac
ONS daemon is running on node: tzdev2rac
eONS is enabled
eONS daemon is running on node: tzdev1rac
eONS daemon is running on node: tzdev2rac

Clusterware Resource Status Check

$ crsctl status 
  resource -t


  --------------------------------------------------------------

  NAME                  
  TARGET  STATE      SERVER            
  STATE_DETAILS

  -------------------------------------------------------------

  Local Resources

  ---------------------------------------------------------------

  ora.LISTENER.lsnr

                      
  ONLINE  ONLINE    tzdev1rac

                      
  ONLINE  ONLINE    tzdev2rac

  ora.asm

                      
  OFFLINE OFFLINE   tzdev1rac

                      
  OFFLINE OFFLINE   tzdev2rac

  ora.eons

                      
  ONLINE  ONLINE    tzdev1rac

                      
  ONLINE  ONLINE    tzdev2rac

  ora.gsd

                      
  OFFLINE OFFLINE   tzdev1rac

                      
  OFFLINE OFFLINE   tzdev2rac

  ora.net1.network

                      
  ONLINE  ONLINE    tzdev1rac

                      
  ONLINE  ONLINE    tzdev2rac

  ora.ons

                      
  ONLINE  ONLINE    tzdev1rac

                      
  ONLINE  ONLINE    tzdev2rac

  -----------------------------------------------------------------

  Cluster Resources

  ------------------------------------------------------------------

  ora.LISTENER_SCAN1.lsnr

  1                     
  ONLINE  ONLINE    tzdev1rac

  ora.LISTENER_SCAN2.lsnr

  1                     
  ONLINE  ONLINE    tzdev2rac

  ora.LISTENER_SCAN3.lsnr

  1                     
  ONLINE  ONLINE    tzdev2rac

  ora.oc4j

  1                     
  OFFLINE OFFLINE

  ora.scan1.vip

  1                     
  ONLINE  ONLINE    tzdev1rac

  ora.scan2.vip

  1                     
  ONLINE  ONLINE    tzdev2rac

  ora.scan3.vip

  1                     
  ONLINE  ONLINE    tzdev2rac

  ora.trezor.db

  1                     
  ONLINE  ONLINE    tzdev1rac         
  Open

  2                     
  ONLINE  ONLINE    tzdev2rac

  ora.tzdev1rac.vip

  1                     
  ONLINE  ONLINE    tzdev1rac

  ora.tzdev2rac.vip

  1                     
  ONLINE  ONLINE    tzdev2rac

Stop the Oracle clusterware stack

crsctl stop crs or you can run

crsctl stop has

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.crsd' on 'tzdev1rac'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.trezor.db' on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'tzdev1rac'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'tzdev1rac' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'tzdev1rac'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'tzdev1rac' succeeded
CRS-2673: Attempting to stop 'ora.tzdev1rac.vip' on 'tzdev1rac'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'tzdev1rac' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'tzdev1rac'
CRS-2677: Stop of 'ora.tzdev1rac.vip' on 'tzdev1rac' succeeded
CRS-2672: Attempting to start 'ora.tzdev1rac.vip' on 'tzdev2rac'
CRS-2677: Stop of 'ora.scan2.vip' on 'tzdev1rac' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'tzdev2rac'
CRS-2677: Stop of 'ora.scan3.vip' on 'tzdev1rac' succeeded
CRS-2672: Attempting to start 'ora.scan3.vip' on 'tzdev2rac'
CRS-2676: Start of 'ora.tzdev1rac.vip' on 'tzdev2rac' succeeded
CRS-2677: Stop of 'ora.trezor.db' on 'tzdev1rac' succeeded
CRS-2676: Start of 'ora.scan2.vip' on 'tzdev2rac' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'tzdev2rac'
CRS-2676: Start of 'ora.scan3.vip' on 'tzdev2rac' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN3.lsnr' on 'tzdev2rac'
CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'tzdev2rac' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN3.lsnr' on 'tzdev2rac' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.eons' on 'tzdev1rac'
CRS-2677: Stop of 'ora.ons' on 'tzdev1rac' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'tzdev1rac'
CRS-2677: Stop of 'ora.net1.network' on 'tzdev1rac' succeeded
CRS-2677: Stop of 'ora.eons' on 'tzdev1rac' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'tzdev1rac' has completed
CRS-2677: Stop of 'ora.crsd' on 'tzdev1rac' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.ctssd' on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.evmd' on 'tzdev1rac'
CRS-2677: Stop of 'ora.cssdmonitor' on 'tzdev1rac' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'tzdev1rac' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'tzdev1rac' succeeded
CRS-2677: Stop of 'ora.evmd' on 'tzdev1rac' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'tzdev1rac' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'tzdev1rac'
CRS-2677: Stop of 'ora.cssd' on 'tzdev1rac' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'tzdev1rac'
CRS-2673: Attempting to stop 'ora.gipcd' on 'tzdev1rac'
CRS-2677: Stop of 'ora.gipcd' on 'tzdev1rac' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'tzdev1rac' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'tzdev1rac' has completed
CRS-4133: Oracle High Availability Services has been stopped.

Start the Oracle clusterware stack

crsctl start crs or you can run

crsctl start has
CRS-4123: Oracle High Availability Services has been started.

Start the Oracle Database

To start all Oracle RAC instances for a database, enter the following command, where db_name is the name of the database:
$ oracle_home/bin/srvctl start database -d db_name (this command is starting all the instances)

Stop the Oracle Database

To shut down all Oracle RAC instances for a database, enter the following command, where db_name is the name of the database:
$ oracle_home/bin/srvctl stop database -d db_name (this command is starting all the instances)

Recovery Manager (RMAN)

Recovery manager is a platform non-specific utility for coordinating you backup and restoration procedures across multiple servers. In my opinion it’s value is limited if you only have on or two instances, but it comes into it’s own where large numbers of instances on multiple platforms are used. The reporting features alone mean that you should never find yourself in a position where your data is in danger due to failed backups.

The functionality of RMAN is too diverse to be covered in this article so I shall focus on the basic backup and recovery functionality.

Create Recovery Catalog
Register Database
Cold Backup
Restore & Recover The Whole Database
Restore & Recover A Subset Of The Database
Incomplete Recovery
Lists And Reports

Create Recovery Catalog

First create a user to hold the recovery catalog:

CONNECT sys/password@HTB
 
-- Create tablepsace to hold repository
CREATE TABLESPACE "TOOLS"
DATAFILE 'E:\ORACLE\ORADATA\DDBA1\TOOLS01.DBF' SIZE 10M
AUTOEXTEND ON NEXT 1024K
EXTENT MANAGEMENT LOCAL;
 
-- Create rman schema owner
CREATE USER rman IDENTIFIED BY rman
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE tools
QUOTA UNLIMITED ON tools;
 
GRANT connect, resource, recovery_catalog_owner TO rman;

Then create the recovery catalog:

The recovery catalog database should be created on a different host, on different disks, and in a different database from the target databse

you will be backing up. If you do not, the benefits of using a recovery catalog are lost if you loose the database and need to restore.

c:\> rman catalog rman/rman@HTB
 
Recovery Manager: Release 8.1.7.0.0 - Production
 
RMAN-06008: connected to recovery catalog database
RMAN-06428: recovery catalog is not installed
 
RMAN> create catalog tablespace tools;
 
RMAN-06431: recovery catalog created
 
RMAN> exit
 
 
Recovery Manager complete.
 
C:\>

Register Database

Each database to be backed up by RMAN must be registered:

C:\>rman target sys/password@HTB rcvcat rman/rman@dba1 msglog

 'C:\Oracle\Backup\HTB\HTB_Daily_Backup.log'
 
Recovery Manager: Release 8.1.7.0.0 - Production
 
RMAN-06005: connected to target database: HTB (DBID=955315395)
RMAN-06008: connected to recovery catalog database
 
RMAN> register database;
 
RMAN-03022: compiling command: register
RMAN-03023: executing command: register
RMAN-08006: database registered in recovery catalog
RMAN-03023: executing command: full resync
RMAN-08002: starting full resync of recovery catalog
RMAN-08004: full resync complete
 
RMAN>

Existing user-created backups can be added to the catalog using:

catalog datafilecopy 'C:\Oracle\Oradata\HTB.dbf';
catalog archivelog 'log1', 'log2', 'log3', ... 'logN';

Cold Backup

This RMAN script starts by doing a a clean mount of the database. It then backs up the datafiles, controlfile and archivelogs, with old archive logs deleted in the process. Finally the database is opened.

replace script 'HTB_daily_backup' {
 
  # make sure database is shutdown cleanly
  shutdown immediate;
  startup force dba pfile=c:\Oracle\Admin\HTB\pfile\init.ora;
  shutdown immediate;
  
  
  #Mount the database and start backup
  startup mount pfile=c:\Oracle\Admin\HTB\pfile\init.ora;
 
  
  # Backup datafile, controlfile and archivelogs
  allocate channel ch1 type
    disk format 'C:\Oracle\Backup\HTB\%d_DB_%u_%s_%p';
  backup database include current controlfile
    tag = 'HTB_daily_backup';
  release channel ch1;
 
 
  # Open the database
  alter database open;
 
 
  # Archive all logfiles including current
  sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
 
 
  # Backup outdated archlogs and delete them
  allocate channel ch1 type
    disk format 'C:\Oracle\Backup\HTB\%d_ARCH_%u_%s_%p';
  backup archivelog
    until time 'Sysdate-2' all
    delete input;
  release channel ch1;
 
  
  # Backup remaining archlogs
  allocate channel ch1 type
    disk format 'C:\Oracle\Backup\HTB\%d_ARCH_%u_%s_%p';
  backup archivelog all;
  release channel ch1;
  
}

The file can be loaded as a stored script and run using the following commands:

RMAN> @c:\Oracle\Backup\HTB_daily_backup.txt
RMAN> run {execute script HTB_daily_backup; }

The RMAN output can be a bit unnerving at first. You should expect something like:

RMAN> run {execute script HTB_daily_backup; }
RMAN-03021: executing script: HTB_daily_backup
 
RMAN-03022: compiling command: shutdown
RMAN-06405: database closed
RMAN-06404: database dismounted
RMAN-06402: Oracle instance shut down
 
RMAN-03022: compiling command: startup
RMAN-06196: Oracle instance started
RMAN-06199: database mounted
RMAN-06400: database opened
 
Total System Global Area      13375516 bytes
 
Fixed Size                       75804 bytes
Variable Size                 12402688 bytes
Database Buffers                819200 bytes
Redo Buffers                     77824 bytes
 
RMAN-03022: compiling command: shutdown
RMAN-06405: database closed
RMAN-06404: database dismounted
RMAN-06402: Oracle instance shut down
 
RMAN-03022: compiling command: startup
RMAN-06193: connected to target database (not started)
RMAN-06196: Oracle instance started
RMAN-06199: database mounted
 
Total System Global Area      13375516 bytes
 
Fixed Size                       75804 bytes
Variable Size                 12402688 bytes
Database Buffers                819200 bytes
Redo Buffers                     77824 bytes
 
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: ch1
RMAN-08500: channel ch1: sid=14 devtype=DISK
 
RMAN-03022: compiling command: backup
RMAN-03023: executing command: backup
RMAN-08008: channel ch1: starting full datafile backupset
RMAN-08502: set_count=51 set_stamp=437320255 creation_time=09-AUG-01
RMAN-08010: channel ch1: specifying datafile(s) in backupset
RMAN-08522: input datafile fno=00001 name=C:\ORACLE\ORADATA\HTB\SYSTEM01.DBF
RMAN-08011: including current controlfile in backupset
RMAN-08522: input datafile fno=00009 name=C:\ORACLE\ORADATA\HTB\DES601.DBF
RMAN-08522: input datafile fno=00002 name=C:\ORACLE\ORADATA\HTB\RBS01.DBF
RMAN-08522: input datafile fno=00008 name=C:\ORACLE\ORADATA\HTB\OEM_REPOSITORY.ORA
RMAN-08522: input datafile fno=00003 name=C:\ORACLE\ORADATA\HTB\USERS01.DBF
RMAN-08522: input datafile fno=00004 name=C:\ORACLE\ORADATA\HTB\TEMP01.DBF
RMAN-08522: input datafile fno=00006 name=C:\ORACLE\ORADATA\HTB\INDX01.DBF
RMAN-08522: input datafile fno=00007 name=C:\ORACLE\ORADATA\HTB\DR01.DBF
RMAN-08522: input datafile fno=00005 name=C:\ORACLE\ORADATA\HTB\TOOLS01.DBF
RMAN-08013: channel ch1: piece 1 created
RMAN-08503: piece handle=C:\ORACLE\BACKUP\HTB\HTB_DB_1JD11UHV_51_1 comment=NONE
RMAN-08525: backup set complete, elapsed time: 00:05:52
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
 
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: ch1
 
RMAN-03022: compiling command: alter db
RMAN-06400: database opened
 
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT
RMAN-03023: executing command: sql
 
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: ch1
RMAN-08500: channel ch1: sid=14 devtype=DISK
 
RMAN-03022: compiling command: backup
RMAN-03025: performing implicit partial resync of recovery catalog
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03023: executing command: backup
RMAN-08009: channel ch1: starting archivelog backupset
RMAN-08502: set_count=52 set_stamp=437320626 creation_time=09-AUG-01
RMAN-08014: channel ch1: specifying archivelog(s) in backup set
RMAN-08504: input archivelog thread=1 sequence=226 recid=11 stamp=437307841
RMAN-08504: input archivelog thread=1 sequence=227 recid=12 stamp=437309722
RMAN-08504: input archivelog thread=1 sequence=228 recid=13 stamp=437316806
RMAN-08504: input archivelog thread=1 sequence=229 recid=14 stamp=437317665
RMAN-08504: input archivelog thread=1 sequence=230 recid=15 stamp=437319111
RMAN-08504: input archivelog thread=1 sequence=231 recid=16 stamp=437320622
RMAN-08013: channel ch1: piece 1 created
RMAN-08503: piece handle=C:\ORACLE\BACKUP\HTB\HTB_ARCH_1KD11UTI_52_1 comment=NONE
RMAN-08525: backup set complete, elapsed time: 00:00:04
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
 
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: ch1
 
RMAN> exit
 
Recovery Manager complete.

The recovery catalog should be resyncronized on a regular basis so that changes to the database structure and presence of new archive logs is recorded. Some commands perform partial and full resyncs implicitly, but a full resync should be scheduled at regular intervals.

resync catalog;

Restore & Recover The Whole Database

Recovering from a media failure is as simple as:

run {
    startup mount pfile=c:\Oracle\Admin\HTB\pfile\init.ora;
 
    allocate channel ch1 type disk;
 
    restore database;
    recover database;
  
    release channel ch1;
}

This will result in all datafiles being restored then recovered. RMAN will apply archive logs as necessary until the recovery is complete. The sort of results you should expect are:

Recovery Manager: Release 8.1.7.0.0 - Production
 
RMAN-06005: connected to target database: HTB (DBID=955315395)
RMAN-06008: connected to recovery catalog database
 
RMAN> run {
2>     startup mount pfile=c:\Oracle\Admin\HTB\pfile\init.ora;
3>
4>     allocate channel ch1 type disk;
5>
6>     restore database;
7>     recover database;
8>   
9>     release channel ch1;
10>}
 
RMAN-03022: compiling command: startup
RMAN-06193: connected to target database (not started)
RMAN-06196: Oracle instance started
RMAN-06199: database mounted
 
Total System Global Area      13375516 bytes
 
Fixed Size                       75804 bytes
Variable Size                 12402688 bytes
Database Buffers                819200 bytes
Redo Buffers                     77824 bytes
 
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: ch1
RMAN-08500: channel ch1: sid=14 devtype=DISK
 
RMAN-03022: compiling command: restore
 
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel ch1: starting datafile backupset restore
RMAN-08502: set_count=51 set_stamp=437320255 creation_time=09-AUG-01
RMAN-08089: channel ch1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to C:\ORACLE\ORADATA\HTB\SYSTEM01.DBF
RMAN-08523: restoring datafile 00002 to C:\ORACLE\ORADATA\HTB\RBS01.DBF
RMAN-08523: restoring datafile 00003 to C:\ORACLE\ORADATA\HTB\USERS01.DBF
RMAN-08523: restoring datafile 00004 to C:\ORACLE\ORADATA\HTB\TEMP01.DBF
RMAN-08523: restoring datafile 00005 to C:\ORACLE\ORADATA\HTB\TOOLS01.DBF
RMAN-08523: restoring datafile 00006 to C:\ORACLE\ORADATA\HTB\INDX01.DBF
RMAN-08523: restoring datafile 00007 to C:\ORACLE\ORADATA\HTB\DR01.DBF
RMAN-08523: restoring datafile 00008 to C:\ORACLE\ORADATA\HTB\OEM_REPOSITORY.ORA
RMAN-08523: restoring datafile 00009 to C:\ORACLE\ORADATA\HTB\DES601.DBF
RMAN-08023: channel ch1: restored backup piece 1
RMAN-08511: piece handle=C:\ORACLE\BACKUP\HTB\HTB_DB_1JD11UHV_51_1

tag=HTB_DAILY_BACKUP params=NULL
RMAN-08024: channel ch1: restore complete
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
 
RMAN-03022: compiling command: recover
 
RMAN-03022: compiling command: recover(1)
 
RMAN-03022: compiling command: recover(2)
 
RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-08055: media recovery complete
 
RMAN-03022: compiling command: recover(4)
 
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: ch1
 
RMAN> exit
 
Recovery Manager complete.

Once this process us complete the database can be opened using the ALTER DATABASE OPEN; command.

Restore & Recover A Subset Of The Database

A subset of the database can be restored in a similar fashion:

run {
    sql 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
    restore tablespace users;
    recover tablespace users;
    sql 'ALTER TABLESPACE users ONLINE';
}

Incomplete Recovery

As you would expect, RMAN allows incomplete recovery to a specified time, SCN or sequence number:

run
{
  set until time 'Nov 15 2000 09:00:00';
  # set until scn 1000;       # alternatively, you can specify SCN
  # set until sequence 9923;  # alternatively, you can specify log sequence number
  restore database;
  recover database;
}
 
alter database open resetlogs;

The incomplete recovery requires the database to be opened using the RESETLOGS option.

Lists And Reports

RMAN has extensive listing and reporting functionality allowing you to monitor you backups and maintain the recovery catalog. Here are a few useful commands:

# Show all backup details
list backup;
 
# Show items that beed 7 days worth of
# archivelogs to recover completely
report need backup days = 7 database;  
 
 
# Show/Delete items not needed for recovery
report obsolete;
delete obsolete;
 
# Show/Delete items not needed for point-in-time
# recovery within the last week
report obsolete recovery window of 7 days;
delete obsolete recovery window of 7 days;
 
# Show/Delete items with more than 2 newer copies

 available
report obsolete redundancy = 2 device type disk;
delete obsolete redundancy = 2 device type disk;
 
# Show datafiles that connot currently be recovered
report unrecoverable database;
report unrecoverable tablespace 'USERS';

Oracle Certifications Paths

Oracle Database 12c - Certification Path

Become an Oracle Certified Associate

Validate your expertise - follow the steps below to earn your associate level certification.

Step 1 - Pass this exam.

Oracle Database 11g

OCA :

Pass Exam1Z0-001

+ Pass Exam1Z0-052

OCP

Prior Certification Required:

OCA

+ Complete 1 Qualified Training Course

+ Pass Exam 1Z0-053

Oracle Database 10g

OCA

Pass Exam
1Z0-042

OCP

OCA + Pass Exam 1Z0-043

Managing Oracle on Linux Certified Expert

Prior Certifiation Required:

Oracle Database 10g Administrator Certified Associate

(OR)

Oracle Database 10g Administrator Certified Professional

OR)

Oracle Database 11g Administrator Certified Associate

(OR)

Oracle Database 11g Administrator Certified Professional

(OR)

Complete Course: Oracle Database 10g: Managing Oracle on Linux for DBAs

(+) Pass Exam1Z0-046

(+) Submit Hands On Course Requirement Form

Administering Real Application Clusters Certified Expert
(with prior certification)

Prior Certification Required:

Oracle 10g DBA OCP (OR)11g DBA OCP

(+) Pass Exam 1Z0-048

Administering Real Application Clusters Certified Expert

(without prior certification)

Complete 1 Qualified Training Course of RAC

(+) Pass Exam 1Z0-048

(+) Submit Hands On Course Requirement Form

Oracle E-Business Suite R12

Supply Chain Certified Expert Consultant, Purchasing

Complete 1 Qualified Training Course (+) Pass Exam1Z0-204 (+)Submit Hands On Course Requirement Form (+) Pass Exam 1Z1-225

Supply Chain Certified Expert Consultant, Order Management

Complete 1 Qualified Training Course (+) Pass Exam1Z0-204 (+)Submit Hands On Course Requirement Form (+) Pass Exam1Z1-226

Advanced Supply Chain Planning Certified Expert Consultant

Pass Exam1Z0-204 (+) Pass Exam1Z1-244

Financials Certified Expert Consultant, Payables

Complete 1 Qualified Training Course (+) Pass Exam1Z0-204 (+)Submit Hands On Course Requirement Form (+) Pass Exam1Z1-215

Financials Certified Expert Consultant, Receivables

Complete 1 Qualified Training Course (+) Pass Exam1Z0-204 (+)Submit Hands On Course Requirement Form (+) Pass Exam1Z1-216

Applications Database Administrator Certified Professional

Prior Certification Required:

Oracle 10g DBA OCP (OR)11g DBA OCP (+) Pass Exam1Z1-238

Oracle E-Business Suite 11i

Workflow Certfied Expert 11i

Recommend Course:

11i/2.6 Implement Oracle Workflow (+) Pass Exam1Z0-231

System Administrator Certified Expert 11i

Recommended Course:

11i System Administrator Fundamentals (+) Pass Exam1Z0-232

Applications Database Administrator Certified Professional 11i

Complete 1 Qualified Training Course

(+) Pass Exam1Z0-235

(+) Pass Exam1Z0-236

(+) Pass Exam1Z0-233

(+) Submit Hands On Course Requirement Form

Financials Consultant, Payables

Complete 1 Qualified Training Course

(+) Pass Exam 1Z0-200

(+) Pass Exam1Z0-211

(+) Submit Hands On Course Requirement Form

(+) Pass Exam1Z0-212

Financials Consultant, Receivables

Complete 1 Qualified Training Course

(+) Pass Exam 1Z0-200

(+) Pass Exam1Z0-211

(+) Submit Hands On Course Requirement Form

(+) Pass Exam1Z0-213

Supply Chain Consultant, Purchasing

Complete 1 Qualified Training Course

(+) Pass Exam 1Z0-200

(+) Pass Exam1Z0-221

(+) Submit Hands On Course Requirement Form

(+) Pass Exam1Z0-222

Supply Chain Consultant, Order Management

Complete 1 Qualified Training Course

(+) Pass Exam 1Z0-200

(+) Pass Exam1Z0-221

(+) Submit Hands On Course Requirement Form

(+) Pass Exam1Z0-223

MiddleWares Path

Oracle Application Server 10g

Oracle Application Server 10g Administrator: Certified Associate

Pass Exam1Z0-311

Oracle Application Server 10g Administrator: Certified Professional (with 1 prior certification)

Prior Certification Required:

Oracle AS 10g Administrator Certified Associate

(+) Complete 1 Qualified Training Course

(+) Pass Exam1Z0-312

(+) Submit Hands On Course Requirement Form

(+) Pass Exam1Z1-226

Oracle Enterprise Linux Administrator

Pass Exam1Z0-402

(OR)

Prior Certification:LPI, Linux+, Ubuntu, Red Hat, or Novell certified

(+) Pass Exam1Z0-403

Certification: Why it is so Improtant?

Why we should do it ; Reason is very simple -

To Create a Marketable Competitive Differentiator

- Certification = Exam + Training

- Sales Team will instruct our prospects & clients to only accept Oracle Certified Consultants

- Nothing but the best!

To Develop & Maintain a World Class Consultant Force

- Product + Consulting Skills + Industry + Field Experience

So If you cleared any Certification Track either it is Oracle Certification/Microsoft or Networking or Project Management, please let me know on my email as well as your expertization skills so that other members of this Forum can have benefit also by taking your expertise advise on different issue. In this way we can help each other.

So guys lets start preparing atleast one certification exam. The simplest is to take the SQL Expert (IZ-001 and IZ-052) at a minimum Or Essentials for 11i /R12.

Sujeet APPS DBA

Custom Top Create in Oracle APPS R12

Starting / Stopping the Oracle RAC Cluster

Recovery Manager (RMAN)

Create Recovery Catalog

Register Database

Cold Backup

Restore & Recover The Whole Database

Restore & Recover A Subset Of The Database

Incomplete Recovery

Lists And Reports

Oracle Certifications Paths

Oracle Database 12c - Certification Path

Become an Oracle Certified Associate

Validate your expertise - follow the steps below to earn your associate level certification.

Step 1 - Pass this exam.

Pass one SQL Exam:

Oracle Database 12c: SQL Fundamentals 1Z0-061 or

Oracle Database 11g: SQL Fundamentals I 1Z0-051 or

Oracle Database SQL Expert 1Z0-047

Step 2 - Pass Exam

Exam

Oracle Database 12c: Installation and Administration 1Z0-062

OCA

OCP

Certification: Why it is so Improtant?

Why we should do it ; Reason is very simple -

To Create a Marketable Competitive Differentiator

To Develop & Maintain a World Class Consultant Force

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database

Create Recovery Catalog

Register Database

Cold Backup

Restore & Recover The Whole Database

Restore & Recover A Subset Of The Database

Incomplete Recovery

Lists And Reports

Oracle Database 12c - Certification Path

Become an Oracle Certified Associate

Validate your expertise - follow the steps below to earn your associate level certification.

Step 1 - Pass this exam.

Pass one SQL Exam: Oracle Database 12c: SQL Fundamentals 1Z0-061 or Oracle Database 11g: SQL Fundamentals I 1Z0-051 or Oracle Database SQL Expert 1Z0-047 Step 2 - Pass Exam Exam Oracle Database 12c: Installation and Administration 1Z0-062

OCA

OCP

Why we should do it ; Reason is very simple -

To Create a Marketable Competitive Differentiator

To Develop & Maintain a World Class Consultant Force

Pass one SQL Exam:

Oracle Database 12c: SQL Fundamentals 1Z0-061 or

Oracle Database 11g: SQL Fundamentals I 1Z0-051 or

Oracle Database SQL Expert 1Z0-047

Step 2 - Pass Exam

Exam

Oracle Database 12c: Installation and Administration 1Z0-062