Business Continuity - Implement Dataguard on R12

IMPLEMENTING Dataguard Physical Standby on R12 on 11g Database Using RMAN Hot Backup
=====================================================================


==> Machine 01 has an E-Business Suite Release 12.0.4 (PROD) DB_NAME=PROD01

==> Machine 02 is clean and will be used as the Standby machine DB_NAME=PROD02

==> Machine 03 will be clean and used as the Observer

1. Prepare the Existing Database as PRIMARY ==> Machine 01

1.Enable Archivelog mode in PRIMARY

SQL>archive log list

>> The output for above command will show whether archive is enabled or Not <<
>> If output shows as follows,then proceed for next step in this section to ENABLE ARCHIVE LOG Mode

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/PROD/11.1.0/db_1/RDBMS/
Oldest online log sequence 386
Current log sequence 387

SQL> shutdown immediate
SQL> startup mount

SQL> alter database force logging;
SQL> alter database archivelog;


>>
>>


2. Create Database Password Files

cd $ORACLE_HOME/dbs
orapwd file=orapw password= ignorecase="Y"

3. Create IFILE and add ifile entry in the pfile

Add the following Entry at end of pfile

IFILE=/oracle/PROD/db/tech_st/11.1.0/dbs/PROD__ifile.ora

Create IFILE as follows:

[oracle@emeaicm01 dbs]$ vi PROD_emeaicm01_ifile.ora


4. Create directory called " PROD_emeaicm01" and place all network files under this folder

[oracle@emeaicm01 PROD_emeaicm01]$ mkdir PROD_emeaicm01

Add IFILE entry on both TNSNAMES.ORA and LISTENER.ORA to use IFILE at the end of file

In Listener.ora
IFILE=/oracle/PROD/db/tech_st/11.1.0/network/admin/PROD_emeaicm01/listener_ifile.ora

In Tnsnames.ora

IFILE=/oracle/PROD/db/tech_st/11.1.0/network/admin/PROD_emeaicm01/PROD_emeaicm01_ifile.ora

Now, Create common IFILE for both Listener and Tnsnames entry

[oracle@emeaicm01 PROD_emeaicm01]$ vi PROD_emeaicm01_ifile.ora



3. Add the following Entries in for standby configuration

PROD01=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=YES)
(FAILOVER=YES)
(ADDRESS=(PROTOCOL=tcp)(HOST=emeaicm01.uk.oracle.com)(PORT=1533))
)
(CONNECT_DATA=
(SID=PROD)
)
)


PROD02=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=YES)
(FAILOVER=YES)
(ADDRESS=(PROTOCOL=tcp)(HOST=emeaicm02.uk.oracle.com)(PORT=1533))
)
(CONNECT_DATA=
(SID=PROD)
)
)



4. At PRIMARY , Set following parameters in IFILE

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY'
LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf'
DB_RECOVERY_FILE_DEST_SIZE = 107374182400
DB_RECOVERY_FILE_DEST= '/oracle/archive'
DB_UNIQUE_NAME=PROD01
LOG_ARCHIVE_CONFIG='dg_config=(PROD01,PROD02)'
LOG_ARCHIVE_DEST_2='service=PROD02 valid_for=(online_logfiles,primary_role) db_unique_name=PROD02 LGWR ASYNC=20480 OPTIONAL REOPEN=15 NET_TIMEOUT=30'
LOG_ARCHIVE_DEST_STATE_2=enable
FAL_SERVER='PROD02'
FAL_CLIENT='PROD01'
#standby_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST'
standby_file_management=AUTO
parallel_execution_message_size=8192


5. Create standby redolog

>>>Create standby redo logs on the primary database to support the standby role. The standby redo logs must be the same size as the primary database online redo logs.
It is recommended that the number of standby redo logs is one more than the number of online redo logs. <<<<

Assume, We have 2 Online logs in PRIMARY, then we need to add 3 standby log files

SQL> alter database add standby logfile thread 1 group 3 ( '/oracle/PROD/db/apps_st/data/stdbylog03a.dbf', '/oracle/PROD/db/apps_st/data/stdbylog03b.dbf') size 1G;
SQL> alter database add standby logfile thread 1 group 4 ( '/oracle/PROD/db/apps_st/data/stdbylog04a.dbf', '/oracle/PROD/db/apps_st/data/stdbylog04b.dbf') size 1G;
SQL> alter database add standby logfile thread 1 group 5 ( '/oracle/PROD/db/apps_st/data/stdbylog05a.dbf', '/oracle/PROD/db/apps_st/data/stdbylog05b.dbf') size 1G;


Clone the Database Software to Standby
-----------------------------------------

6. Prepare the Database Tier for Cloning

As ORACLE user on one of the production database servers, run the adpreclone.pl script.
For Example:

cd $ORACLE_HOME/appsutil/scripts/
perl adpreclone.pl dbTier

In our case

cd $ORACLE_HOME/appsutil/scripts/PROD_emeaicm01
perl adpreclone.pl dbTier

Supply the APPS password when requested
Use the same port pool as Source

7. Copy Database Tier to Standby

Use following command to tar the source file , copy and untar in target ==> The following command will automatically tar,copy and untar in the target system

$ tar czvf - . |ssh -l oracle emeaicm02.uk.oracle.com "cd /oracle/PROD/db/tech_st; tar xvzf -"


>>>> It is recommended to use the same directory names on the standby site. <<<<<<


8. Configure the Standby Database Tier using Rapid Clone

At Standby Site, Execute the following adcfgclone.pl script for new database Oracle home:

$ cd $ORACLE_HOME/appsutil/clone/bin
perl adcfgclone.pl dbTechStack

Respond to the prompts appropriately:

Target instance is a Real Application Cluster (RAC) instance (y/n):
"n" in the single instance case
Target System database name:
Note, db_name, not db_unique_name
Number of DATA_TOP's on the target system [4]:
Usually only one for ASM.
Target system DATA_TOP 1:
For example, /oracle/PROD

Source The New Environment

$ cd /oracle/PROD/db/tech_st/11.1.0/
$ . . PROD_emeaicm02.env

9. Configure New Database for Communication between Primary and Standby

When adjusting parameters on the production site for Data Guard setup, you created an include file
holding TNS service definitions. Copy the file to the $TNS_ADMIN directory at your standby site and
name it _ifile.ora.

$ cd $TNS_ADMIN
$ mv PROD_emeaicm01_ifile.ora PROD_emeaicm02_ifile.ora


10. Configure Standby Database Data Guard Parameters

$ vi PROD_emeaicm02_ifile.ora
>> Add/change the following parameters <<

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY'
LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf'
DB_RECOVERY_FILE_DEST_SIZE = 107374182400
DB_RECOVERY_FILE_DEST= '/oracle/archive'
log_archive_config='dg_config=(PROD_IN01,PROD_IN02)'
LOG_ARCHIVE_DEST_STATE_2=enable
#standby_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST'
standby_file_management=AUTO
parallel_execution_message_size=8192
db_unique_name=PROD_IN02
log_archive_dest_2='service=PROD_IN01 valid_for=(online_logfiles,primary_role) db_unique_name=PROD_IN01 LGWR ASYNC=20480 OPTIONAL REOPEN=15 NET_TIMEOUT=30'
fal_server='PROD_IN01'
fal_client='PROD_IN02'
log_file_name_convert='xx','xx'



Clone Application Tier to Standby
----------------------------------

# Use the same ORACLE_BASE as Source i.e /oracle/PROD
# Use the same port pool as Source, i.e port pool 12
# Setup SSH on both Primary and Standby nodes - Follow - How to setup SSH.doc
# Setup cron to periodically synchronise concurrent manager log and out files - Follow - How to use crontab to rsync CCM log and out files.doc


11. Prepare the Application Tier for Cloning

Log in to the APPLMGR user and run the following commands to prepare the application tier for
cloning:

$ cd /admin/scripts
$ perl adpreclone.pl appsTier


12. Copy the Application Tier to Standby

SR czvf - . |ssh -l oracle emeaicm02.uk.oracle.com "cd /oracle/PROD/apps; tar xzvf -"

13. Configure Standby Application Tier using Rapid Clone
Run adcfgclone.pl first to start configuring the new application tier file systems. These steps must be run
on each application tier node:

$ cd /clone/bin
$ perl adcfgclone.pl atTechStack

Answer prompts appropriately:

SRget system database SID:
Note: db_name, not db_unique_name
SRget system database server node:
Database server hostname, could be any node for RAC node if RAC used

To complete the configuration of environment run AutoConfig using the following commands:

$ cd /ad/12.0.0/bin
$ adconfig.sh contextfile=/appl/admin/.xml run=INSTE8

>>>AutoConfig will report errors regarding to database connection, these errors can be ignored as the
database is unavailable. <<<<

14. Setup cron to periodically synchronise concurrent manager log and out files

emeaicm01.uk.oracle.com is machine A (Primary machine)
emeaicm02.uk.oracle.com is machine B (Standby machine)

On the Standby machine:
mkdir -p /log
mkdir -p /out

mkdir -p /oracle/PROD/inst/apps/PROD_emeaicm01/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_emeaicm01/logs/appl/conc/out

On the Primary Machine
mkdir -p /log
mkdir -p /out

mkdir -p /oracle/PROD/inst/apps/PROD_emeaicm02/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_emeaicm02/logs/appl/conc/out

You can set the timings to push files according to your requirement.
In our case, we have set the timing for 30 mintues as follows:

From machine A
===============

The files will be pushing from machine A every 30 minutes, on the hour and half hour, so our entries will be:

$ crontab -e

#
## Push log and out files from this machine to emeaicm02 every 30 minutes, on the hour and half hour
#
00,30 * * * * rsync -av /oracle/PROD/inst/apps/PROD_emeaicm01/logs/appl/conc oracle@emeaicm02: /oracle/PROD/inst/apps/PROD_emeaicm01/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_00-30.log
#

As crontab uses “vi” syntax, save changes using :wq!

From machine B
==============

We will also be pushing files from machine B every 30 minutes, on the quarter hour and three quarter hour, so our entries will be:

#
## Push log and out from this machine to emeaicm01 every 30 minutes at quarter past, and quarter to.
#
15,45 * * * * rsync -av /oracle/PROD/inst/apps/PROD_emeaicm02/logs/appl/conc oracle@emeaicm01:/oracle/PROD/inst/apps/PROD_emeaicm02/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_15-45.log
#


15. Setup SSH on both Primary and Standby nodes

On Primary Node: (Machine 1)
----------------------------

· Log in as oracle
· Generate the ssh key by issuing the following command:
ssh-keygen -t rsa (then press enter 3 times)

$ ssh-keygen -t rsa
Generating public/private rsa key pair.

· Press the Enter key three times.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
5a:58:7e:65:ae:71:1a:82:2c:95:98:5b:48:b3:52:ac oracle@emeaicm15.uk.oracle.com
· CD into the .ssh directory, which is located under the home directory. Note that a file called id_rsa.pub is created.

$ ls -lrt $HOME/.ssh/
total 24
-rw-r--r-- 1 oracle dba 948 Mar 3 10:57 known_hosts
-rw-r--r-- 1 oracle dba 240 Mar 4 10:15 id_rsa.pub
-rw------- 1 oracle dba 887 Mar 4 10:15 id_rsa

· Update authorized_keys file to enable ssh ‘loopback’

$ cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys

· Use SCP to copy id_rsa.pub to the /tmp directory of the Remote Nodes.
$ scp $HOME/.ssh/id_rsa.pub oracle@emeaicm16.uk.oracle.com:/tmp
oracle@emeaicm16.uk.oracle.com's password:
id_rsa.pub 100% 240 0.2KB/s 00:00

On the Remote Node(s):

· Login as the oracle user
· Remove specialized .login, .profile or similar files which cause extra verbiage to be displayed to the screen.
· Create a directory called .ssh in the home directory
$ mkdir $HOME/.ssh
mkdir: cannot create directory `/home/oracle/.ssh': File exists

· Copy the content of /tmp/id_rsa.pub (copied from the Primary Node) into a new file named authorized_keys. (If you already have a file called authorized_keys in this directory, open it in vi, go to the bottom of the file, add one blank line and then copy and paste the contents of the file /tmp/id_rsa.pub into the existing file and save it.)

$ cat /tmp/id_rsa.pub >> $HOME/.ssh/authorized_keys

· Verify that the .ssh directory permission is set to 700
$ chmod 700 $HOME/.ssh
· Verify that the authorized_keys file permission is set to 600
$ chmod 600 $HOME/.ssh/authorized_keys
· Verify that the $HOME directory does not have write permissions for group and others.
$ chmod go-w $HOME
· Ensure that the ssh daemon is running
$ ps -ef |grep -i ssh

root 2418 22004 0 10:25 ? 00:00:00 sshd: oracle [priv]
oracle 2420 2418 0 10:25 ? 00:00:00 sshd: oracle@pts/1
oracle 2690 2421 0 10:37 pts/1 00:00:00 grep -i ssh
root 22004 1 0 Feb29 ? 00:00:00 /usr/sbin/sshd

Test the installation:
~~~~~~~~~~~~~~~~~~~~~


· Login to the PrimaryNode as oracle user.

Execute the following command, replacing [user] with the username on the Remote Node, and [SERVER] with the host name of the Remote Node:
ssh -l [user] [SERVER]
$ uname -n
emeaicm15.uk.oracle.com

$ ssh -l oracle emeaicm15 (connect to same machine)
Last login: Tue Mar 4 10:41:52 2008 from emeaicm15.uk.oracle.com

$ uname -n
emeaicm15.uk.oracle.com
· When prompted to confirm the identity, respond "Yes".
· Disconnect from the server.

$ exit
Connection to emeaicm16 closed

$ ssh -l oracle emeaicm16 (connect to machine 2)
Last login: Tue Mar 4 10:41:52 2008 from emeaicm15.uk.oracle.com

$ uname -n
emeaicm16.uk.oracle.com
· When prompted to confirm the identity, respond "Yes".
· Disconnect from the server.

$ exit
Connection to emeaicm16 closed
· Execute the following commands:

$ uname -n
emeaicm15.uk.oracle.com

$ ssh oracle@emeaicm16 uname -n
emeaicm16.uk.oracle.com

The remote hostname should appear, followed by the prompt, and you should not be asked to enter and ID or password. If you are prompted for a password, verify the following:
§ The content of the authorized_keys file contains the content of id_rsa.pub of the client
§ The permissions of the authorized_keys file are set to 600.
§ The permissions of the .ssh directory are set to 700.
The permissions of the $HOME directory do not include execute for group and other.


Now repeat the steps again as follows (to enable SSH in the opposite direction)

On Standby Node: (machine 2)

· Log in as oracle
· Generate the ssh key by issuing the following command:
ssh-keygen -t rsa (then press enter 3 times)

$ ssh-keygen -t rsa
Generating public/private rsa key pair.

· Press the Enter key three times.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
6c:7d:4c:5c:21:9d:d6:8e:1f:6d:9c:52:2f:88:1c:3d oracle@emeaicm16.uk.oracle.com
· CD into the .ssh directory, which is located under the home directory. Note that a file called id_rsa.pub is created.

$ ls -lrt $HOME/.ssh/
-rw-r--r-- 1 oracle dba 703 Feb 18 09:54 known_hosts
-rw-r--r-- 1 oracle dba 240 Mar 4 10:32 authorized_keys
-rw-r--r-- 1 oracle dba 240 Mar 4 10:49 id_rsa.pub
-rw------- 1 oracle dba 887 Mar 4 10:49 id_rsa

· Update authorized_keys file to enable ssh ‘loopback’

$ cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys
· Use SCP to copy id_rsa.pub to the /tmp directory of the Remote Nodes.

$ scp $HOME/.ssh/id_rsa.pub oracle@emeaicm15.uk.oracle.com:/tmp
oracle@emeaicm15.uk.oracle.com's password:
id_rsa.pub 100% 240 0.2KB/s 00:00

On the Primary Node: (Machine 1)

· Login as the oracle user
· Copy the content of /tmp/id_rsa.pub (copied from the Primary Node) into file named authorized_keys
$ cat /tmp/id_rsa.pub >> $HOME/.ssh/authorized_keys
· Verify that the .ssh directory permission is set to 700
$ chmod 700 $HOME/.ssh
· Verify that the authorized_keys file permission is set to 600
$ chmod 600 $HOME/.ssh/authorized_keys
· Verify that the $HOME directory does not have write permissions for group and others.
$ chmod go-w $HOME
· Ensure that the ssh daemon is running
$ ps -ef |grep -i ssh

root 2418 22004 0 10:25 ? 00:00:00 sshd: oracle [priv]
oracle 2420 2418 0 10:25 ? 00:00:00 sshd: oracle@pts/1
oracle 2690 2421 0 10:37 pts/1 00:00:00 grep -i ssh
root 22004 1 0 Feb29 ? 00:00:00 /usr/sbin/sshd

Test the installation at both Node(s):
--------------------------------------

· Login to the Remote Node as oracle user (Machine 2).
· Execute the following command, replacing [user] with the username on the Remote Node, and [SERVER] with the host name of the Remote Node:
ssh -l [user] [SERVER]

$ uname -n
emeaicm16.uk.oracle.com

$ ssh -l oracle emeaicm16 (connect to same machine)
Last login: Tue Mar 4 10:41:52 2008 from emeaicm16.uk.oracle.com

$ uname -n
emeaicm16.uk.oracle.com

$ exit
Connection to emeaicm16 closed

$ ssh -l oracle emeaicm15 (connect to machine 1)
Last login: Tue Mar 4 10:41:52 2008 from emeaicm16.uk.oracle.com

$ uname -n
emeaicm15.uk.oracle.com
· When prompted to confirm the identity, respond "Yes".
· Disconnect from the server.

$ exit
Connection to emeaicm15 closed
· Execute the following commands:

$ uname -n
emeaicm16.uk.oracle.com

$ ssh oracle@emeaicm15 uname -n
emeaicm15.uk.oracle.com

The remote hostname should appear, followed by the prompt, and you should not be asked to enter and ID or password. If you are prompted for a password, verify the following:
§ The content of the authorized_keys file contains the content of id_rsa.pub of the client
§ The permissions of the authorized_keys file are set to 600.
§ The permissions of the .ssh directory are set to 700.
§ The permissions of the $HOME directory do not include execute for group and other.



16. Establish the Standby Database

Create a hot backup using Recovery Manager (RMAN)

Take Backup of Primary Database and Copy the backup to Standby(Machine02) using RMAN HOT BACKUP

a)set RDBMS_ORACLE_HOME env file
b) $ rman target /

RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup database plus archivelog;
}

>>>>For RMAN: Monitoring Recovery Manager Jobs ==> Ref Note 144640.1

To monitor the Rman Backup job, Please execute the following in SQLPLUS as sysdba

SQL> SELECT sid, spid, client_info FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND client_info LIKE '%id=rman%';



SQL> SELECT sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork
/


SQL> SELECT sid, seconds_in_wait AS sec_wait, event FROM v$session_wait
WHERE wait_time = 0
ORDER BY sid;

Take backup of current controlfile at primary for standby site in RMAN

For Backup as Backupset:

RMAN> run
{
allocate channel c1 type disk;
backup current controlfile for standby;
}


For Backup as COPY :

RMAN> run
{
allocate channel c1 type disk;
backup as copy current controlfile for standby;
}

Once backup is done, copy the backup with archivelogs & Standby controlfile to remote location(standby)

rsync -av /oracle/archive oracle@machine02.oracle.com:/oracle/archive

17. At Standby (Machine02), Startup mount the Standby database

The following command will automatically duplicate the database using backup taken at primary and
mount the database

$ rman target sys/oracle@prod_in01 auxiliary sys/oracle@prod_in02

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY
DORECOVER NOFILENAMECHECK;


Run the following command to check the standby database details

SQL> select database_role,switchover_status,checkpoint_change#,switchover#,dataguard_broker,guard_status,current_scn,flashback_on from v$database

The output should be similar to the following:


DATABASE_ROLE SWITCHOVER_STATUS CHECKPOINT_CHANGE# SWITCHOVER# DATAGUAR GUARD_S CURRENT_SCN FLASHBACK_ON
--------------- ------------------ ---------------------- ----------- --------------- ------------------- ------------------
PHYSICAL STANDBY NOT ALLOWED 5965111847227 83208404 ENABLED NONE 5965112465873 YES


18. On the primary database enable the previously deferred remote destination by executing this command
from SQL*Plus connected as sysdba:

SQL> alter system set log_archive_dest_state_2=enable SID='*';

19. Place the standby database in managed recovery by executing this command from SQL*Plus
connected as sysdba:

SQL> recover managed standby database using current logfile disconnect;

20. Verify whether the standby is correctly applying redo from the primary.
On the primary database, archive the current log using the following SQL statement:

>>Find the current Sequence and last archived log sequence:

SQL> archive log list;

>>Archive the current redolog sequence:

SQL> alter system archive log current;


On the standby database, query the v$archived_log view to verify that the logs are received and applied:

SQL> select thread#, sequence#, applied,
to_char(first_time, 'mm/dd/yy hh24:mi:ss') first,
to_char(next_time, 'mm/dd/yy hh24:mi:ss') next,
to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion
from v$archived_log order by first_time;

You can also execute the following query to find the shipping details on both primary and standby

SQL> select process,status,sequence# from v$managed_standby;

21. If incase the logs are not shipping, Please check the following

a) Check alertlog file on Primary and see whether log_archive_dest_2 is reachable and check any TNS
error, if the standby destination not reachable, fix the TNS issue

b) Check the status of the log_archive_dest_2

SQL> select status,error from v$archive_dest;

>> If any error from above statement, you shall defer it temporarily

SQL> alter system set log_archive_dest_state_2=defer;

c) Once the error is rectified on log_archive_dest_2, you can execute the following query to enable it

SQL> alter system set log_archive_dest_state_2=enable;

d) If logs are not shipping and if you want to apply manually, you shall move/copy the missing archivelog
from primary to standby manually using OS scp command and run the following command to apply
the changes

SQL> alter database register logfile '/oracle/archive/PROD_IN02/archivelog/2008_11_18/o1_mf_1_34_4l5bb7b4_.arc';


SWITCHOVER
============

1. Switchover Primary Database to Remote Standby Database

a) Shutdown Production E-Business Suite -
Shut down E-Business Suite application tier. Ensure the application is completely shutdown.

b) On the NoteIMARY database , Issue the following command to commit to switchover to
standby:

SQL> alter database commit to switchover to standby with session shutdown;

c) Shutdown the NoteIMARY database
SQL> shutdown immediate

d) Stop the NoteIMARY database listener: on all database nodes:

lsnrctl stop

e) On the STANDBY database, verify that it is ready to be converted to the new primary:

SQL> select switchover_status from v$database;

You should see the following value:
SWITCHOVER_STATUS
-----------------
TO PRIMARY

f) On the STANDBY database, execute the following command to convert it to be the new primary:

SQL> alter database commit to switchover to primary;

g) Enable Flashback on the STANDBY database (Optional)

SQL> alter database flashback on;

h) Open the STANDBY Database (New Primary database)

SQL> alter database open;

i) Remove the Old Application Topology
Connect to the new primary database using SQL*Plus as user APPS and execute the following
commands:

SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
SQL> commit;

j) Configure the Standby Database Tier
Run AutoConfig on STANDBY database(New Primary) node to configure the Oracle home for use
by the E-Business Suite.

$ cd $ORACLE_HOME/appsutil/scripts/
$ ./adautocfg.sh

k) Start Original PRIMARY as Standby
Start the database listener on database node at the original primary site:

$ lsnrctl start

Start and mount database instance and then start managed recovery:

SQL> startup mount;
SQL> recover managed standby database using current logfile disconnect;

On the New PRIMARY database at the standby site enable remote archiving by executing this
command from SQL*Plus connected as sysdba:

SQL> alter system set log_archive_dest_state_2=enable SID='*';

Verify whether logs are switching from New PRIMARY to this Standby database as described in
Step 20.

l) Perform the Cloning Finishing Tasks as per Note 406982.1

How to change the characterset of Oracle 10g DB

Decide the character set you want to change and check whether new character is superset of old character set

1.SQL> shutdown immediate
2.SQL> startup open restrict
3.SQL> alter database character set internal_use UTF8;
4.SQL> shutdown immediate
5.SQL> startup

Refer the following Metalink Note for more details:

Note 225912.1 Changing the Database Character Set - a short overview
Note 227330.1 Character Sets & Conversion - Frequently Asked Questions

How to kill all ORACLE Process in one command

At OS prompt, Execute the following command to kill all ORACLE process

$ kill -9 `ps -ef |grep PROD |awk '{print $2}'`

RMAN Backup Validation Check

To Test and Check the Integrity for Backups
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The database prevents operations that result in unusable backup files or corrupt restored datafiles. The database server automatically does the following:

* Blocks access to datafiles while they are being restored or recovered
* Allows only one restore operation for each datafile at a time
* Ensures that incremental backups are applied in the correct order
* Stores information in backup files to allow detection of corruption

To Detect of Logical Block Corruption
--------------------------------------

This tests data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log

1. Start RMAN in nocatalog mode and connect to your database:

From the operating system prompt issue at database host :

$ rman target / nocatalog

2. From the RMAN> prompt issue the validate command with the "check logical"
clause:

The following example shows how to validate a single datafile:

run {
allocate channel d1 type disk;
backup check logical validate datafile 77;
release channel d1;
}


If you wish to monitor the progress of RMAN backup validate, you may issue this
query:

SQL> select sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and totalwork != 0
and sofar <> totalwork
/


3. Once the validate process is complete, you either check the alert log or a
view depending on the version of Oracle being used.

In Oracle9i and beyond you can query the view name V$DATABASE_BLOCK_CORRUPTION
to determine what corruption, if any, was found by RMAN.

Query to find All Background_processes in the Database

Background Processes

Background processes support various types of Oracle processing

Each version of Oracle has a set of associated background processes. Each background process has a descriptor in the fixed area of the SGA. Background process descriptors are of type ksbdp. The descriptors can be seen in the level 2 GLOBAL_AREA dump for example:

ALTER SESSION SET EVENTS 'immediate trace name global_area level 2';

Every background process descriptor has an internal (structure) name e.g. ktmprc_ and an external (display) name e.g. SMON. A list of background process descriptors together with the external and internal names can be obtained using the following query:

SELECT ksbdd.ksbddidn, ksmfsv.ksmfsnam, ksbdd.ksbdddsc
FROM x$ksbdd ksbdd, x$ksbdp ksbdp, x$ksmfsv ksmfsv
WHERE ksbdd.indx = ksbdp.indx
AND ksbdp.addr = ksmfsv.ksmfsadr
ORDER BY ksbdd.ksbddidn;

how to change expired status of user accounts.txt

SQL> select username,account_status from dba_users where username='DIP';

USERNAME ACCOUNT_STATUS
----------------------- ---------------
SCOTT EXPIRED & LOCKED


SQL> alter user SCOTT account unlock;

User altered.

SQL> select username,account_status from dba_users where username='SCOTT';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT EXPIRED

SQL> select password from dba_users where username='SCOTT';

PASSWORD
------------------------------
CE4A36B8E06CA59C

SQL> alter user scott identified by values 'CE4A36B8E06CA59C';

User altered.

SQL> select username,account_status from dba_users where username='SCOTT';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT OPEN

Difference Between AFN & RFN

RELATIVE FILE NO.

Relative file no is the file no. generated which belongs to only one tablespace.
Every tablespace has its own datafiles will be referred by Relative file no.
So the relative file no. can be repeated in each tablespaces.
Eg; TAblespace users(TS#1) has 2 datafiles then Relative file no. REL file no. 1 and 2 for tablespace #1.
Tablespace userdata(TS#2) has 2 datafiles then Relative file no. will be 1 and 2 for tablespace # 2.

A tablespace can have only 1022 max datafiles .. so Relative file no will be 1022 for each tablespace max.

ABSOLUTE FILE NO.

Whereas Absolute file no is Datafile No. generated uniquely at database level.

How to use crontab to rsync CCM log and out files

About CRON
cron is a unix utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix.

Crontab Restrictions

You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use
crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.
Crontab Commands
export EDITOR=vi specify a editor to open crontab file
crontab -e edit/create crontab file
crontab -l display your crontab file.
crontab -r remove your crontab file.
crontab -v * display the last time crontab file was edited
* This option is only available on a few systems.
Crontab syntax
A crontab file has five fields for specifying day, date and time followed by the command to be run at that interval.
* * * * * command to be executed- - - - -| | | | || | | | +----- day of week (0 - 6) (Sunday=0)| | | +------- month (1 - 12)| | +--------- day of month (1 - 31)| +----------- hour (0 - 23)+------------- min (0 - 59)
* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas.
An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).

Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

Crontab Example
A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.
30 18 * * * rm /home/someuser/tmp/*
Changing the parameter values as below will cause this command to run at different time schedule below :
min hour day/month month day/week Execution time
30 0 1 1,6,12 * At 00:30 Hrs on 1st of Jan, June & Dec.
:
0 20 * 10 1-5 At 8.00 PM every weekday (Mon-Fri) only in Oct.
:
0 0 1,10,15 * * At midnight on 1st ,10th & 15th of month
:
5,10 0 10 * 1 At 12.05,12.10 every Monday & on 10th of every month
:
Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

Workshop Task - Establish log and out File Synchronization
In the workshop, the requirement is to synchronise the concurrent manager “log” and “out” files from the Primary to the Standby machine, every 15 minutes.

In this example, we are using the following machines:-
machine01.uk.oracle.com is machine A (Primary machine)

machine02.uk.oracle.com is machine B (Standby machine)

On the Standby machine:
mkdir -p /log
mkdir -p /out

mkdir -p /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc/out

On the Primary Machine
mkdir -p /log
mkdir -p /out

mkdir -p /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc/out

From machine A
We will be pushing files from machine A every 30 minutes, on the hour and half hour, so our entries will be:
$ crontab -e

#
## Push log and out files from this machine to machine02 every 30 minutes, on the hour and half hour
#
00,30 * * * * rsync -av /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc oracle@machine02: /oracle/PROD/inst/apps/PROD_machine01/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_00-30.log
#

As crontab uses “vi” syntax, save changes using :wq!

From machine B
We will also be pushing files from machine B every 30 minutes, on the quarter hour and three quarter hour, so our entries will be:
#
## Push log and out from this machine to machine01 every 30 minutes at quarter past, and quarter to.
#
15,45 * * * * rsync -av /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc oracle@machine01:/oracle/PROD/inst/apps/PROD_machine02/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_15-45.log
#

How to Setup SSH

On Primary Node: (Machine 1) Example: primary.oracle.com

• Log in as oracle
• Generate the ssh key by issuing the following command:

ssh-keygen -t rsa (then press enter 3 times)

$ ssh-keygen -t rsa

Generating public/private rsa key pair.

• Press the Enter key three times.

Enter file in which to save the key (/home/oracle/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /home/oracle/.ssh/id_rsa.

Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.

The key fingerprint is:

5a:58:7e:65:ae:71:1a:82:2c:95:98:5b:48:b3:52:ac oracle@primary.oracle.com

• CD into the .ssh directory, which is located under the home directory. Note that a file called id_rsa.pub is created.

$ ls -lrt $HOME/.ssh/

total 24

-rw-r--r-- 1 oracle dba 948 Mar 3 10:57 known_hosts

-rw-r--r-- 1 oracle dba 240 Mar 4 10:15 id_rsa.pub

-rw------- 1 oracle dba 887 Mar 4 10:15 id_rsa

• Update authorized_keys file to enable ssh ‘loopback’

$ cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys

• Use SCP to copy id_rsa.pub to the /tmp directory of the Remote Nodes.

$ scp $HOME/.ssh/id_rsa.pub oracle@stdby.oracle.com:/tmp

oracle@stdby.oracle.com's password:

id_rsa.pub 100% 240 0.2KB/s 00:00

On the Remote Node(s): Example: stdby.oracle.com

• Login as the oracle user
• Remove specialized .login, .profile or similar files which cause extra verbiage to be displayed to the screen.
• Create a directory called .ssh in the home directory

$ mkdir $HOME/.ssh

mkdir: cannot create directory `/home/oracle/.ssh': File exists

• Copy the content of /tmp/id_rsa.pub (copied from the Primary Node) into a new file named authorized_keys. (If you already have a file called authorized_keys in this directory, open it in vi, go to the bottom of the file, add one blank line and then copy and paste the contents of the file /tmp/id_rsa.pub into the existing file and save it.)

$ cat /tmp/id_rsa.pub >> $HOME/.ssh/authorized_keys

• Verify that the .ssh directory permission is set to 700

$ chmod 700 $HOME/.ssh

• Verify that the authorized_keys file permission is set to 600

$ chmod 600 $HOME/.ssh/authorized_keys

• Verify that the $HOME directory does not have write permissions for group and others.

$ chmod go-w $HOME

• Ensure that the ssh daemon is running

$ ps -ef |grep -i ssh

root 2418 22004 0 10:25 ? 00:00:00 sshd: oracle [priv]

oracle 2420 2418 0 10:25 ? 00:00:00 sshd: oracle@pts/1

oracle 2690 2421 0 10:37 pts/1 00:00:00 grep -i ssh

root 22004 1 0 Feb29 ? 00:00:00 /usr/sbin/sshd

Test the installation:

• Login to the PrimaryNode as oracle user.
• Execute the following command, replacing [user] with the username on the Remote Node, and [SERVER] with the host name of the Remote Node:

ssh -l [user] [SERVER]

$ uname -n

primary.oracle.com

$ ssh -l oracle primary (connect to same machine)

Last login: Tue Mar 4 10:41:52 2008 from primary.oracle.com

$ uname -n

primary.oracle.com

• When prompted to confirm the identity, respond "Yes".
• Disconnect from the server.

$ exit

Connection to stdby closed

$ ssh -l oracle stdby (connect to machine 2)

Last login: Tue Mar 4 10:41:52 2008 from primary.oracle.com

$ uname -n

stdby.oracle.com

• When prompted to confirm the identity, respond "Yes".
• Disconnect from the server.

$ exit

Connection to stdby closed

• Execute the following commands:

$ uname -n

primary.oracle.com

$ ssh oracle@stdby uname -n

stdby.oracle.com

The remote hostname should appear, followed by the prompt, and you should not be asked to enter and ID or password. If you are prompted for a password, verify the following:

• The content of the authorized_keys file contains the content of id_rsa.pub of the client
• The permissions of the authorized_keys file are set to 600.
• The permissions of the .ssh directory are set to 700.

The permissions of the $HOME directory do not include execute for group and other.

Now repeat the steps again as follows (to enable SSH in the opposite direction)

On Remote Node: (machine 2)

• Log in as oracle
• Generate the ssh key by issuing the following command:

ssh-keygen -t rsa (then press enter 3 times)

$ ssh-keygen -t rsa

Generating public/private rsa key pair.

• Press the Enter key three times.

Enter file in which to save the key (/home/oracle/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /home/oracle/.ssh/id_rsa.

Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.

The key fingerprint is:

6c:7d:4c:5c:21:9d:d6:8e:1f:6d:9c:52:2f:88:1c:3d oracle@stdby.oracle.com

• CD into the .ssh directory, which is located under the home directory. Note that a file called id_rsa.pub is created.

$ ls -lrt $HOME/.ssh/

-rw-r--r-- 1 oracle dba 703 Feb 18 09:54 known_hosts

-rw-r--r-- 1 oracle dba 240 Mar 4 10:32 authorized_keys

-rw-r--r-- 1 oracle dba 240 Mar 4 10:49 id_rsa.pub

-rw------- 1 oracle dba 887 Mar 4 10:49 id_rsa

• Update authorized_keys file to enable ssh ‘loopback’

$ cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys

• Use SCP to copy id_rsa.pub to the /tmp directory of the Remote Nodes.

$ scp $HOME/.ssh/id_rsa.pub oracle@primary.oracle.com:/tmp

oracle@primary.oracle.com's password:

id_rsa.pub 100% 240 0.2KB/s 00:00

On the Primary Node: (Machine 1)

• Login as the oracle user
• Copy the content of /tmp/id_rsa.pub (copied from the Primary Node) into file named authorized_keys

$ cat /tmp/id_rsa.pub >> $HOME/.ssh/authorized_keys

• Verify that the .ssh directory permission is set to 700

$ chmod 700 $HOME/.ssh

• Verify that the authorized_keys file permission is set to 600

$ chmod 600 $HOME/.ssh/authorized_keys

• Verify that the $HOME directory does not have write permissions for group and others.

$ chmod go-w $HOME

• Ensure that the ssh daemon is running

$ ps -ef |grep -i ssh

root 2418 22004 0 10:25 ? 00:00:00 sshd: oracle [priv]

oracle 2420 2418 0 10:25 ? 00:00:00 sshd: oracle@pts/1

oracle 2690 2421 0 10:37 pts/1 00:00:00 grep -i ssh

root 22004 1 0 Feb29 ? 00:00:00 /usr/sbin/sshd

Test the installation:

• Login to the Remote Node as oracle user (Machine 2).
• Execute the following command, replacing [user] with the username on the Remote Node, and [SERVER] with the host name of the Remote Node:

ssh -l [user] [SERVER]

$ uname -n

stdby.oracle.com

$ ssh -l oracle stdby (connect to same machine)

Last login: Tue Mar 4 10:41:52 2008 from stdby.oracle.com

$ uname -n

stdby.oracle.com

$ exit

Connection to stdby closed

$ ssh -l oracle primary (connect to machine 1)

Last login: Tue Mar 4 10:41:52 2008 from stdby.oracle.com

$ uname -n

primary.oracle.com

• When prompted to confirm the identity, respond "Yes".
• Disconnect from the server.

$ exit

Connection to primary closed

• Execute the following commands:

$ uname -n

stdby.oracle.com

$ ssh oracle@primary uname -n

primary.oracle.com

The remote hostname should appear, followed by the prompt, and you should not be asked to enter and ID or password. If you are prompted for a password, verify the following:

• The content of the authorized_keys file contains the content of id_rsa.pub of the client
• The permissions of the authorized_keys file are set to 600.
• The permissions of the .ssh directory are set to 700.
• The permissions of the $HOME directory do not include execute for group and other.

11g ASM New Features

The new features in Automatic Storage Management (ASM) extend the storage management automation, improve scalability, and further simplify management for Oracle Database files.

■ ASM Fast Mirror Resync

A new SQL statement, ALTER DISKGROUP ... DISK ONLINE, can be executed
after a failed disk has been repaired. The command first brings the disk online for
writes so that no new writes are missed. Subsequently, it initiates a copy of all extents
marked as stale on a disk from their redundant copies.
This feature significantly reduces the time it takes to repair a failed diskgroup,
potentially from hours to minutes. The repair time is proportional to the number of
extents that have been written to or modified since the failure.

■ ASM Manageability Enhancements

The new storage administration features for ASM manageability include the following:

■ New attributes for disk group compatibility
To enable some of the new ASM features, you can use two new disk group
compatibility attributes, compatible.rdbms and compatible.asm. These
attributes specify the minimum software version that is required to use disk
groups for the database and for ASM, respectively. This feature enables
heterogeneous environments with disk groups from both Oracle Database 10g and
Oracle Database 11g. By default, both attributes are set to 10.1. You must advance
these attributes to take advantage of the new features.

■ New ASM command-line utility (ASMCMD) commands and options

ASMCMD allows ASM disk identification, disk bad block repair, and backup and
restore operations in your ASM environment for faster recovery.

■ ASM fast rebalance

Rebalance operations that occur while a disk group is in RESTRICTED mode
eliminate the lock and unlock extent map messaging between ASM instances in
Oracle RAC environments, thus improving overall rebalance throughput.
This collection of ASM management features simplifies and automates storage
management for Oracle databases.

■ ASM Preferred Mirror Read

When ASM failure groups are defined, ASM can now read from the extent that is
closest to it, rather than always reading the primary copy. A new initialization
parameter, ASM_PREFERRED_READ_FAILURE_GROUPS, lets the ASM administrator
specify a list of failure group names that contain the preferred read disks for each node
in a cluster.

In an extended cluster configuration, reading from a local copy provides a great
performance advantage. Every node can read from its local diskgroup (failure group),
resulting in higher efficiency and performance and reduced network traffic.

■ ASM Rolling Upgrade

Rolling upgrade is the ability of clustered software to function when one or more of
the nodes in the cluster are at different software versions. The various versions of the
software can still communicate with each other and provide a single system image.
The rolling upgrade capability will be available when upgrading from Oracle
Database 11g Release 1 (11.1).

This feature allows independent nodes of an ASM cluster to be migrated or patched
without affecting the availability of the database. Rolling upgrade provides higher
uptime and graceful migration to new releases.

■ ASM Scalability and Performance Enhancements

This feature increases the maximum data file size that Oracle can support to 128 TB.
ASM supports file sizes greater than 128 TB in any redundancy mode. This provides
near unlimited capacity for future growth. The ASM file size limits are:

■ External redundancy - 140 PB
■ Normal redundancy - 42 PB
■ High redundancy - 15 PB

Customers can also increase the allocation unit size for a disk group in powers of 2 up
to 64 MB.

■ Convert Single-Instance ASM to Clustered ASM

This feature provides support within Enterprise Manager to convert a non-clustered
ASM database to a clustered ASM database by implicitly configuring ASM on all
nodes. It also extends the single-instance to Oracle RAC conversion utility to support
standby databases.

Simplifying the conversion makes it easier for customers to migrate their databases
and achieve the benefits of scalability and high availability provided by Oracle RAC.

■ New SYSASM Privilege for ASM Administration

This feature introduces the new SYSASM privilege to allow for separation of database
management and storage management responsibilities.
The SYSASM privilege allows an administrator to manage the disk groups that can be
shared by multiple databases. The SYSASM privilege provides a clear separation of
duties from the SYSDBA privilege.

For complete list of 11g ASM New Feature, You can refer the following

Note 551205.1 11g ASM New Features Technical White Paper
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-asm.html

11g New Features - Good sites

Good Links for 11g New Features

• Oracle Database Online Documentation 11g Release 1 (11.1)
• Oracle Metalink Note
  
• Oracle Database 11g on OTN
• Oracle Database 11g: The Top Features for DBAs and Developers
• ORACLE-BASE - Articles on Oracle 11g new features
• 40+ Links for Knowing the New Oracle Database 11g