Data Guard with Flashback

This arcticle shows How to setup and use flashback in DATA GUARD setup.

• Enable the flashback at primary database
  SQL> Shutdown immediate
  SQL> Startup mount
  SQL> Alter database flashback on;
  SQL> Alter database open;
  SQL> select flashback_on from v$database;
  
  Enable the flashback at standby database
  SQL> recover managed standby database cancel;
  SQL> Alter database flashback on;
  SQL> select flashback_on from v$database;
  SQL> alter database recover managed standby database disconnect from session;
  
  Need of Flashback of standby database or not ?
  
  Check the current SCN at standby database, if it is lower than the flashback SCN than standby database will be synchronized itself after Primary database flashback. But If it not then there is need to flashback the standby database with 5-10 number lower than flashback SCN to get synchronized.
  
  For example: Here current SCN at standby is 37054496 and primary database is flashback to SCN 37054499 which is higher so no needs to flashback the standby database.
  
  SQL> select name,to_char(current_scn),database_role from v$database;
  NAME TO_CHAR(CURRENT_SCN) DATABASE_ROLE
  --------- ---------------------------------------- ----------------
  DRGTRG1 37054496 PHYSICAL STANDBY
  
  
  
  SQL> select name,to_char(current_scn),database_role from v$database;
  NAME TO_CHAR(CURRENT_SCN) DATABASE_ROLE
  --------- ---------------------------------------- ----------------
  DRGTRG1 37054617 PRIMARY
  
  
  Flashback at Primary database
  
  SQL> shutdown immediate;
  Database closed.
  Database dismounted.
  ORACLE instance shut down.
  
  SQL> startup mount;
  ORACLE instance started.
  Total System Global Area 1191182336 bytes
  Fixed Size 1267140 bytes
  Variable Size 620759612 bytes
  Database Buffers 553648128 bytes
  Redo Buffers 15507456 bytes
  Database mounted.
  
  SQL> FLASHBACK DATABASE TO SCN 37054499;
  Flashback complete.
  
  SQL> alter database open resetlogs;
  Database altered.
  
  SQL> alter system switch logfile;
  System altered.
  
  Entries in the alert.log at Primary database
  
  ALTER DATABASE MOUNT
  Tue May 5 14:16:14 2009
  Setting recovery target incarnation to 2
  Tue May 5 14:16:14 2009
  Successful mount of redo thread 1, with mount id 795560426
  Tue May 5 14:16:14 2009
  Allocated 15937428 bytes in shared pool for flashback generation buffer
  Starting background process RVWR
  RVWR started with pid=16, OS id=14674
  Tue May 5 14:16:14 2009
  Database mounted in Exclusive Mode
  Completed: ALTER DATABASE MOUNT
  Tue May 5 14:18:11 2009
  FLASHBACK DATABASE TO SCN 37054499
  Tue May 5 14:18:11 2009
  Flashback Restore Start
  Flashback Restore Complete
  Flashback Media Recovery Start
  parallel recovery started with 3 processes
  Tue May 5 14:18:13 2009
  Recovery of Online Redo Log: Thread 1 Group 3 Seq 123 Reading mem 0
  Mem# 0: /oracle/oradata/drgtrg1/redo/redo03.log
  Tue May 5 14:18:14 2009
  Recovery of Online Redo Log: Thread 1 Group 1 Seq 124 Reading mem 0
  Mem# 0: /oracle/oradata/drgtrg1/redo/redo01.log
  Tue May 5 14:18:14 2009
  Recovery of Online Redo Log: Thread 1 Group 2 Seq 125 Reading mem 0
  Mem# 0: /oracle/oradata/drgtrg1/redo/redo02.log
  Tue May 5 14:18:14 2009
  Incomplete Recovery applied until change 37054539
  Flashback Media Recovery Complete
  Completed: FLASHBACK DATABASE TO SCN 37054499
  Tue May 5 14:18:31 2009
  alter database open resetlogs
  Tue May 5 14:18:32 2009
  RESETLOGS after incomplete recovery UNTIL CHANGE 37054539
  Resetting resetlogs activation ID 785436917 (0x2ed0d0f5)
  Tue May 5 14:19:40 2009
  Setting recovery target incarnation to 3
  Tue May 5 14:19:40 2009
  Assigning activation ID 795560426 (0x2f6b49ea)
  LGWR: STARTING ARCH PROCESSES
  ARC0 started with pid=20, OS id=14715
  Tue May 5 14:19:40 2009
  ARC0: Archival started
  ARC1: Archival started
  LGWR: STARTING ARCH PROCESSES COMPLETE
  ARC1 started with pid=21, OS id=14717
  LNS1 started with pid=22, OS id=14719
  Tue May 5 14:19:44 2009
  Thread 1 advanced to log sequence 2 (thread open)
  Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /oracle/oradata/drgtrg1/redo/redo02.log
  Successful open of redo thread 1
  Tue May 5 14:19:44 2009
  MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
  Tue May 5 14:19:44 2009
  ARC0: Becoming the 'no FAL' ARCH
  ARC0: Becoming the 'no SRL' ARCH
  Tue May 5 14:19:44 2009
  ARC1: Becoming the heartbeat ARCH
  Tue May 5 14:19:44 2009
  LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
  Tue May 5 14:19:44 2009
  ARC1: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
  Tue May 5 14:19:44 2009
  SMON: enabling cache recovery
  Tue May 5 14:19:44 2009
  Successfully onlined Undo Tablespace 1.
  Tue May 5 14:19:44 2009
  Expanded controlfile section 11 from 108 to 216 records
  Requested to grow by 108 records; added 4 blocks of records
  Tue May 5 14:19:45 2009
  Dictionary check beginning
  Dictionary check complete
  Tue May 5 14:19:47 2009
  SMON: enabling tx recovery
  Tue May 5 14:19:47 2009
  Database Characterset is AL32UTF8
  Opening with internal Resource Manager plan
  where NUMA PG = 1, CPUs = 4
  replication_dependency_tracking turned off (no async multimaster replication found)
  Starting background process QMNC
  QMNC started with pid=23, OS id=14721
  Tue May 5 14:19:49 2009
  LOGSTDBY: Validating controlfile with logical metadata
  Tue May 5 14:19:49 2009
  LOGSTDBY: Validation complete
  Tue May 5 14:19:50 2009
  db_recovery_file_dest_size of 10240 MB is 2.38% used. This is a
  user-specified limit on the amount of space that will be used by this
  database for recovery-related files, and does not reflect the amount of
  space available in the underlying filesystem or ASM diskgroup.
  Tue May 5 14:19:51 2009
  Completed: alter database open resetlogs
  Tue May 5 14:20:59 2009
  Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: /oracle/oradata/drgtrg1/redo/redo03.log
  ++++++++++
  Entries in the alert.log at Standby database
  
  Media Recovery Log /oracle/oradata01/drgtrg2/stby_archive/1_124_675957688.arc
  Media Recovery Waiting for thread 1 sequence 125 (in transit)
  Tue May 5 14:15:23 2009
  RFS[2]: Possible network disconnect with primary database
  Aborting archivelog file creation: /oracle/oradata01/drgtrg2/stby_archive/1_125_675957688.arc
  If this a network disconnect, then this archivelog will be fetched again
  by GAP resolution mechanism.
  Redo Shipping Client Connected as PUBLIC
  -- Connected User is Valid
  RFS[3]: Assigned to RFS process 6846
  RFS[3]: Identified database type as 'physical standby'
  Tue May 5 14:19:44 2009
  Redo Shipping Client Connected as PUBLIC
  -- Connected User is Valid
  RFS[4]: Assigned to RFS process 6844
  RFS[4]: Identified database type as 'physical standby'
  Tue May 5 14:19:44 2009
  RFS[3]: New Archival REDO Branch: 686067511 Current: 675957688
  Tue May 5 14:19:44 2009
  Primary database is in MAXIMUM PERFORMANCE mode
  Primary database is in MAXIMUM PERFORMANCE mode
  Tue May 5 14:19:44 2009
  RFS[3]: Archived Log: '/oracle/oradata01/drgtrg2/stby_archive/1_1_686067511.arc'
  RFS[3]: Detected missing archivals for Branch(resetlogs_id): 675957688
  RFS[3]: Last archived SCN: 0:37054497 Last change SCN: 0:37054539
  RFS[3]: New Archival REDO Branch(resetlogs_id): 686067511 Prior: 675957688
  RFS[3]: Archival Activation ID: 0x2f6b49ea Current: 0x2ed0d0f5
  RFS[3]: Effect of primary database OPEN RESETLOGS
  RFS[3]: Managed Standby Recovery process is active
  New incarnation branch detected in ArchiveLog, filename /oracle/oradata01/drgtrg2/stby_archive/1_1_686067511.arc
  Inspection of file changed rdi from 2 to 3
  Setting recovery target incarnation to 3
  Tue May 5 14:19:44 2009
  RFS[3]: Incarnation entry added for Branch(resetlogs_id): 686067511 (drgtrg2)
  Tue May 5 14:19:44 2009
  Setting recovery target incarnation to 3
  Tue May 5 14:19:45 2009
  MRP0: Incarnation has changed! Retry recovery...
  Tue May 5 14:19:45 2009
  Errors in file /oracle/admin/drgtrg2/bdump/drgtrg2_mrp0_5627.trc:
  ORA-19906: recovery target incarnation changed during recovery
  Recovery interrupted!
  Tue May 5 14:19:48 2009
  Errors in file /oracle/admin/drgtrg2/bdump/drgtrg2_mrp0_5627.trc:
  ORA-19906: recovery target incarnation changed during recovery
  Tue May 5 14:20:08 2009
  Managed Standby Recovery not using Real Time Apply
  parallel recovery started with 3 processes
  Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 37054539
  Tue May 5 14:20:08 2009
  Waiting for all non-current ORLs to be archived...
  Media Recovery Waiting for thread 1 sequence 125 branch(resetlogs_id) 675957688
  Fetching gap sequence in thread 1 branch(resetlogs_id) 675957688, gap seq 125-125
  Error 12514 received logging on to the standby
  FAL[client, MRP0]: Error 12514 connecting to (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=traindb2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=drgtrg1_XPT)(SERVER=dedicated))) for fetching gap sequence
  Tue May 5 14:20:08 2009
  Errors in file /oracle/admin/drgtrg2/bdump/drgtrg2_mrp0_5627.trc:
  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
  Tue May 5 14:20:38 2009
  Error 12514 received logging on to the standby
  FAL[client, MRP0]: Error 12514 connecting to (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=traindb2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=drgtrg1_XPT)(SERVER=dedicated))) for fetching gap sequence
  Tue May 5 14:20:38 2009
  Errors in file /oracle/admin/drgtrg2/bdump/drgtrg2_mrp0_5627.trc:
  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
  Tue May 5 14:20:40 2009
  RFS[3]: Archived Log: '/oracle/oradata01/drgtrg2/stby_archive/1_125_675957688.arc'
  Tue May 5 14:20:59 2009
  RFS[4]: Archived Log: '/oracle/oradata01/drgtrg2/stby_archive/1_2_686067511.arc'
  Primary database is in MAXIMUM PERFORMANCE mode
  Tue May 5 14:21:08 2009
  Media Recovery Log /oracle/oradata01/drgtrg2/stby_archive/1_125_675957688.arc
  Media Recovery Log /oracle/oradata01/drgtrg2/stby_archive/1_1_686067511.arc
  Media Recovery Log /oracle/oradata01/drgtrg2/stby_archive/1_2_686067511.arc
  Media Recovery Waiting for thread 1 sequence 3 (in transit)
  

Some common cronjob entries

To schedule the activity at last day of every month at 10:00 pm:

0 22 * * * [ `/bin/date \+\%d` -eq `/bin/echo \`/usr/bin/cal\` /bin/awk '{print $NF}'` ] && /oracle/backups/scripts/exports/your_script.sh

To schedule the activity at every 5 min:

0,5,10,15,20,25,30,35,40,45,50,55 * * * * /home/oracle/scripts/fs_ck_del.sh
or
*/5 * * * * /home/oracle/scripts/fs_ck_del.sh

To get the output of crontab jobs

Add below as 1st line in crontab file
MAILTO=yourname@xyxabc.com
This ensures any error output from the cron jobs and any output from your script gets emailed to your email address. To stop any email address or Mailbox file being generated when a command is executed, use MAILTO="".

To get complete log of crontab jobs

Go to Home directory of “oracle” user (for example user oracle)
vi /home/oracle/.forward ( if it does not existing create it.)
add email address(s)
abc@xyzabc.com

To delete all trc files exist only at /oracle mount point

* 18 * * * /usr/bin/find /oracle -mount -name '*.trc' -exec /bin/rm -rf {} \;

To delete exact 2 days old all “arc” files

30 19 * * * /usr/bin/find /oracle/backup/suppdev/archives -mtime 2 -name 'suppdev*.arc' -exec /bin/rm -f {} \;

To delete more than 2 days old all “arc” files

30 19 * * * /usr/bin/find /oracle/backup/suppdev/archives -mtime +2 -name 'suppdev*.arc' -exec /bin/rm -f {} \;

To run sql statement through crontab

00 23 * * * (sqlplus system/abc123 @/home/oracle/scripts/truncate_st.sql)
00 23 * * * (sqlplus system/abc123”space”@/home/oracle/scripts/truncate_st.sql)
-------truncate_st.sql-----
tuncate table userid.systemlog;
exit;
--------------end of sql--------------

To copy (scp) more than one day old arc files to another box “dbserver.xyzabc.com” at ‘/oracle/oradata/archives’

08 16 * * * /usr/bin/find /oracle/backups/test/archive -type f -name '*.arc' -mtime +1 -exec /usr/bin/scp {} oracle@dbserver.xyzabc.com:/oracle/oradata/archives \;

Changing the Database Character Set to AL32UTF8 / UTF8

1-Installing and configuring CSSCAN in 10g and 11g

cd $ORACLE_HOME/rdbms/admin
set oracle_sid=
sqlplus /nolog
SQL>conn / as sysdba
SQL>set TERMOUT ON
SQL>set ECHO ON
SQL>spool csminst.log
SQL> START csminst.sql

2-Take full export of database as well other backups (RMAN or Hot)
(Do not to use expdp )

3-Invalid objects.

SQL> Select owner, object_name, object_type, status from dba_objects where status ='INVALID';


4-Orphaned Datapump master tables (10g and up)

SELECT o.status, o.object_id, o.object_type,
o.owner'.'object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;


5-Drop default schemas

For example : The 'HR', 'OE', 'SH', 'PM', 'IX', 'BI' and 'SCOTT' users are by default sample schema's.


6-Check the Source database for "Lossy" (invalid code points in the source character set).
Note: Always run Csscan connecting with a 'sysdba' connection/user,do not use "system" or "cmsig" user.

csscan FULL=Y FROMCHAR=current_charaterset TOCHAR=target_charaterset LOG=log_file_name CAPTURE=N ARRAY=1000000 PROCESS=2
csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=AL32UTF8 LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=2

This will create 3 files :

dbcheck.out a log of the output of csscan
dbcheck.txt a Database Scan Summary Report
dbcheck.err contains the rowid's of the Lossy rows reported in dbcheck.txt (if any).

If all the data in the database is stored correctly at the moment then there should only be "Changeless" data reported in dbcheck.txt.
If this is the case please go to point 5).

7-Check for "Convertible" and "Truncation" data when going to AL32UTF8

csscan FULL=Y TOCHAR=AL32UTF8 LOG=toutf8 CAPTURE=Y SUPPRESS=1000 ARRAY=1000000 PROCESS=2

This will create 3 files :
toutf8.out a log of the output of csscan
toutf8.txt the Database Scan Summary Report
toutf8.err contains the rowid's of the Convertible and Lossy rows reported in toutf8.txt


8-Objects in the recyclebin

Sqlplus>PURGE DBA_RECYCLEBIN;


9-Steps needed to use Alter Database Character Set / Csalter:

For 10g and up:

a. Export all the "Convertible" data (tables) ( no tables belong to sys schemas) ( NLS_LANG is set to the current database character set)
b. Truncate the exported tables of point a
c. Run csscan again with the syntax of point 5) to verify you only have "convertible" CLOB in the data dictionary and all other data is "changeless".
d. If this is now correct then proceed to step 10), otherwise do the same again for the rows you've missed out.
e. Import the exported data again.


10-Running Csalter/Alter Database Character Set

For 10g and up

shutdown
startup restrict
SPOOL Nswitch.log
@@?/rdbms/admin/csalter.plb
-- Csalter will aks confirmation - do not copy paste the whole actions on one time
-- sample Csalter output:
-- 3 rows created.
...
-- This script will update the content of the Oracle Data Dictionary.
-- Please ensure you have a full backup before initiating this procedure.
-- Would you like to proceed (Y/N)?y
-- old 6: if (UPPER('&conf') <> 'Y') then
-- New 6: if (UPPER('y') <> 'Y') then
-- Checking data validility...
-- begin converting system objects

-- PL/SQL procedure successfully completed.

-- Alter the database character set...
-- CSALTER operation completed, please restart database

-- PL/SQL procedure successfully completed.
...
-- Procedure dropped.

shutdown
startup


11-Check the character set

SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

12 - Reload the data pump packages after a change to AL32UTF8 in 10g and up.

If you use 10g or up then the datapump packages need to be reloaded after a conversion to AL32UTF8. In order to do this run the following scripts from $ORACLE_HOME/rdbms/admin in sqlplus connected as "/ AS SYSDBA":

For 10.2.X and higher:
catnodp.sql
catdph.sql
catdpb.sql

For 10.1.X:
catnodp.sql
catdp.sql


13- Possible error

---------------
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validility...
Unrecognized convertible date found in scanner result
PL/SQL procedure successfully completed.
Checking or Converting phrase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

PL/SQL procedure successfully completed.
4 rows deleted.

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

Check the log file toutf8.err 's "[Application data individual exceptions]"
Export all the tables coming under above session and truncate them

Re-run CSSAN command and check again “Application data individual exceptions”
csscan FULL=Y TOCHAR=AL32UTF8 LOG=toutf8 CAPTURE=Y SUPPRESS=1000 ARRAY=1000000 PROCESS=2

=================More info================

Note:745809.1 Installing and configuring CSSCAN in 10g and 11g
Note:225912.1 Changing the Database Character Set - a short overview
Note:260192.1 Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode)

Note:258904.1 Convertible data in data dictionary: Workarounds when changing character set

Some Scripts for TEMP Tablespace usage

Temp Tablespace Usage

set linesize 1000
col tablespace format a10 trunc
col username format a8 trunc
col osuser format a6 trunc
col sid_pid format a10 trunc
col machine format a10 trunc
col sid format 9990
col serial# format 99990
col spid format a5 trunc
compute sum label 'TOTAL' of blocks_mb on report;
break on report

select t1.tablespace
, sum( t1.blocks * ( select to_number(value) from v$parameter where name = 'db_block_size') ) / 1024 / 1024 blocks_mb , sum( t1.extents ) tot_extents
, t2.username , t2.osuser, t2.status, t2.SID ',' t2.serial# SID_PID
, t4.spid, t2.sql_address, t1.sqlhash, t1.sql_idfrom v$sort_usage t1
, v$session t2, v$process t4
where t1.SESSION_ADDR = t2.SADDR
and (t2.PROCESS=t4.SPID or t2.paddr = t4.addr)
group by t1.tablespace , t2.username , t2.osuser , t2.machine , t2.status
, t2.schemaname , t2.program , t2.SID ',' t2.serial# , t4.spid
, t2.sql_address , t1.sqlhash , t1.sql_id order by 2;


This will give the size of the temporary tablespace:

select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;

This will give the "high water mark" of that temporary tablespace (= max used at one time):

select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;

This will give current usage:

select ss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;

kill: (27795) - Operation not permitted

If you want to kill on own your session in Linux. For example: If you login as oracle and your session got hung.

[oracle@testbox exports]$ who -u
root tty1 Oct 11 00:24 old 6396
oracle pts/0 Oct 30 15:03 00:09 27795 (10.0.0.61)
root pts/1 Oct 30 11:07 23:57 29527 (10.0.0.37)
oracle pts/2 Oct 31 11:33 . 3476 (10.0.0.61)

kill and killall are not working

[oracle@testbox ~]$ kill -9 27795
-bash: kill: (27795) - Operation not permitted


[oracle@testbox exports]$ killall -9 27795
27795: no process killed

Solution

You need to get right PID to kill session.


[oracle@testbox exports]$ps ax | grep -i oracle

23945 ? Ss 2:00 oracleclpro1 (LOCAL=NO)
27795 ? Ss 0:00 sshd: oracle [priv]
27832 ? S 0:00 sshd: oracle@pts/0
30206 ? Ss 4:29 oraclepriv1 (LOCAL=NO)
32617 ? Ss 0:00 oracletest1 (LOCAL=NO)

[oracle@testbox exports]$ kill -9 27832


You are now all set.

How to setup transparent switchover/failover to application and client

Clients transparently connect to whichever node/instance is currently running the primary database in Data Guard environment.

1. Set the below entry to tnsname.ora on both nodes
== TEST1 is db_name
== show parameter db_name
== 102.168.50.15 is host ip address

ALL_LISTENERS_TEST1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 102.168.50.15)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 102.168.50.16)(PORT = 1521))
)

2. Set below entry to client’s tnsname.ora file.

TEST1_PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 102.168.50.15)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 102.168.50.16)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST1_PRIMARY)
)
)


3.
(Optional)
If the application is a java program that uses the thin jdbc driver the following connect string can be used instead of the TNS alias.

testdb=any name
test1_primary = service name
102.168.50.16= host ip address
102.168.50.16= host ip address

tetdb.db.url="jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=TRUE)(ADDRESS_LIST=(ADDRESS=(PROTO=TCP)(HOST=102.168.50.15)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=102.168.50.16)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test1_primary)))"


4. login as sysdba and execute on below trigger on primary database and it will automatically be shipped to the standby database with the redo log stream.


declare
db_name v$database.name%type;
service_name varchar2(64);
net_name varchar2(64);
begin
select name into db_name from v$database;
service_name := db_name '_PRIMARY';
net_name := db_name '_PRIMARY';
DBMS_SERVICE.CREATE_SERVICE(service_name,net_name);
end;
/

CREATE OR REPLACE TRIGGER enable_primary_service
after startup ON database
declare
db_name v$database.name%type;
db_role v$database.database_role%type;
service_name varchar2(64);
begin
select name,database_role into db_name,db_role from v$database;
if db_role = 'PRIMARY'
then
service_name := db_name '_PRIMARY';
DBMS_SERVICE.START_SERVICE(service_name,NULL);
end if;
END;
/

5. Shutdown and restart primary database to activate the login trigger

6. Check for service at primary database
Sqlplus>Show parameter service

7. Connect to database from client machine

8. Do the switchover and again connect to database from client machine.

9. It should be successfully without making any change in client tnsnames.ora file.

To Auotmate the backup of oracle database init parameter file

Options:

1. Backup file name : dbname_dayofyear_bk_init.ora: e.x. dev10g_234_bk_init.ora

2. Backup folder: /home/oracle/scripts

3. /bin/date +%j : day (in number) of year
/bin/date +%w: day of week (0..6); 0 represents Sunday


#********Begin*****************
#/bin/ksh
#/home/oracle/scripts/backup_initfile.ksh
if [ $# -ne 1 ]
then
echo Usage Error. Please provide DBNames
echo Usage: Ex. ./backup_initfile.ksh dbname
exit
fi

export ORACLE_SID=$1
BACKUP_FOLDER=\'/home/oracle/scripts
BACKUP_FILENAME_tmp=${ORACLE_SID}_`/bin/date +%a /usr/bin/tr A-Z a-z`.trc
BACKUP_FILENAME=${ORACLE_SID}_`/bin/date +%j /usr/bin/tr A-Z a-z`_bk_init.ora\'
#!/bin/ksh
echo ""
echo ""
echo "GETTING INIT FILE"
echo ""
(sqlplus /nolog<connect / as sysdba
create pfile=$BACKUP_FOLDER/$BACKUP_FILENAME from spfile;
EOF
)
#********The End*****************

To execcute in crontab

35 09 * * * /home/oracle/scripts/backup_initfile.ksh db_name