Oracle database's fixed_date feature usage script

Some time, there is requirement to advance the database’s date to test month end or advance date related batch process. One way to change the date at O/s level but it may difficult if there are more than databases in Database Server box.

Below script is used fixed_date feature to set advance date and increments / synchronies the time after every 2 minutes through script.

Create a folder (for example dt) and create below script files.

Create or Edit instance_list_node2 to include database(s) for fix /Advance date

# cat instance_list_node2
orcl
demodb
testdb

Main script to get changed date and time of database(s).

# cat clock_change.ksh

#!/usr/bin/ksh
############################################
# never-ending loop to change database clock
############################################
export ORACLE_HOME=/oracle/oradata01/as10g # change the Oracle home name
export JOBDIR=/home/oracle/scripts/dt # change the folder name
export NODENUM=2
export LISTFILE=/home/oracle/scripts/dt/instance_list_node${NODENUM} # change the folder name
cd $JOBDIR

# start with q resync of hours and minutes...
CHANGE_TYPE=1
exec < ${LISTFILE}
while read line
do
export ORACLE_SID=${line}
TYPE=RESYNC
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change_sql_generator.sql
TIME=`date '+%H:%M:%S'`
sed -e "s/\*/${TIME}/g" ${JOBDIR}/resync_clock_change_model.sql > ${JOBDIR}/resync_clock_change.sql
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change.sql

if [ $? -eq 0 ]
then
echo ${ORACLE_SID} $TYPE" clock update ran " `date` >> ${JOBDIR}/clock_change.log
else
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo ${ORACLE_SID} $TYPE" CLOCK UPDATE FAILED " `date` >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
fi
done
sleep 120

#
# This is an infinite loop...
#
while [ 1 -eq 1 ]
do
CHANGE_TYPE=0
HR=`date '+%H'`
exec < ${LISTFILE}
while read line
do
export ORACLE_SID=${line}

if [ ${HR} -eq 01 ]
then
CHANGE_TYPE=1
fi
if [ ${HR} -eq 07 ]
then
CHANGE_TYPE=1
fi
if [ ${HR} -eq 13 ]
then
CHANGE_TYPE=1
fi
if [ ${HR} -eq 19 ]
then
CHANGE_TYPE=1
fi

if [ ${CHANGE_TYPE} -eq 1 ]
then
TYPE=RESYNC
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change_sql_generator.sql
TIME=`date '+%H:%M:%S'`
sed -e "s/\*/${TIME}/g" ${JOBDIR}/resync_clock_change_model.sql > ${JOBDIR}/resync_clock_change.sql
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change.sql
else
CHANGE_TYPE=2
TYPE=INCREMENTAL
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/clock_change1.sql
fi

if [ ${?} -eq 0 ]
then
echo ${ORACLE_SID} ${TYPE}" clock update ran " `date` >> ${JOBDIR}/clock_change.log
else
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo ${ORACLE_SID} $TYPE" CLOCK UPDATE FAILED " `date` >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
fi
done
sleep 120
done


To get new date after 24 hrs.

# cat resync_clock_change_sql_generator.sql
set feedback off
set heading off
spool /home/oracle/scripts/dt/resync_clock_change_model.sql # change the folder name
select 'alter system set FIXED_DATE = '''to_char(sysdate,'YYYY-MM-DD-*')''' ;' from dual;
select 'exit;' from dual;
spool off
exit;


To get changed new time

# cat clock_change1.sql
set feedback off
set heading off
spool /home/oracle/scripts/dt/clock_change2.sql # change the folder name
select 'alter system set FIXED_DATE = '''to_char(sysdate + 2/1440,'YYYY-MM-DD-HH24:MI:SS')''' ;' from dual;
select 'exit;' from dual;
spool off
@/home/oracle/scripts/dt/clock_change2.sql ; # change the folder name
exit;


# Intermediate file

# cat resync_clock_change.sql
alter system set FIXED_DATE = '2012-01-12-19:59:18' ;
exit;

# Intermediate file

# cat clock_change2.sql
alter system set FIXED_DATE = '2012-01-12-23:11:18' ;
exit;

To implement the script:


chmod 777 clock_change.ksh

SQL> shutdown immediate;
SQL> startup mount
SQL>alter system set FIXED_DATE='2011-12-31-10:00:00'; # any advance /back date

To start script (infinite loop)

#nohup ./clock_change.ksh &

To Verify the date and time of database. This script will change the time after every 2 mins and date will be changed after 24 hrs.

SQL>alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

SQL> select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
31-12-2011 10:00:00

To stop the script for particular database

Remove the database name from file instance_list_node2

To stop the script

#ps -ef grep clock_change
#kill -9 process_id

Steps to install S/w & Database Oracle 11g2 on Linux x86-64

Steps to install Oracle 11g2 S/w & Database on Linux x86-64 with Screen shots:

PRE-INSTALLATION STEPS
Complete steps to configure the raw Linux box.

INSTALLATION STEPS
Complete steps to install Oracle 11g2 s/w and create of test database.

http://tinyurl.com/oracle-11g2-installation-pdf

How to setup transparent Switchover / Failover to application and clients

If there is a failover or switchover and if user(s) are trying to connect to database with same connect string, end up with below error message

C:\>sqlplus system/password@trg1
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 17 12:29:25 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

Clients transparently can 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 Data Guard nodes

TRG is a Database (show parameter db_name) – Replace TRG with your database name.
Change the IP address of both nodes in below tnsnames.ora entry

ALL_LISTENERS_TRG =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.XX)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.YY)(PORT = 1521))
)

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

TRG_PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.XX)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.YY)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TRG_PRIMARY)
) )

3. Setup for Application server.

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.
Service Name =TRG_primary (change have any server name)
1st host ip address 10.0.0.XX
2nd host ip address 10.0.0.YY

TRG_primary.db.url="jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=TRUE)(ADDRESS_LIST=(ADDRESS=(PROTO=TCP)(HOST=10.0.0.XX)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.YY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TRG_primary)))"

4. Login as sysdba and create abelow trigger on Primary Database and it will be automatically 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. After restart of database, Check for service at primary database

SQL> Show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string trg1, TRG_PRIMARY

7. Connect to database from client machine with connect string “trg_primary”
sqlplus system/xxxx@trg_primary

8. Do the switchover either through Data Guard (dgmgrl) or manually

#dgmgrl sys/password
DGMGRL> switchover to 'trg1';

9. after successful switchover, Primary Database is now TRG1 but Client should be successfully connect with same connect string without making any change in client tnsnames.ora file.

sqlplus system/password@trg_primary

Step by step Data Guard implementation

Step by step Data Guard implementation (On Oracle 10gR2)

Assuming that PRIMARY Database is up and running and Oracle s/w is installed on STANDY Server.
Oracle_home=/oracle/ora10g
Oracle_base=/oracle

PRIMARY Server:


Host name : traindb1
Database name (db_name): trg
Database Unique Name (db_unique_name): trg
TNS Service : trg (Through this service, the STANDBY machine will be connected to PRIMARY machine)

STANDBY Server:

Host name : traindb2
Database name (db_name): trg
Database Unique Name (db_unique_name): trg1
TNS Service: trg1 (Through this service, the PRIMARY machine will be connected to Standy machine)

At PRIMARY Server

If password file does not exist than create the password file:

/oracle/ora10g2/bin/orapwd file= /oracle/ora10g2/dbs/orapwtrg password=password


Add following parameters in the inittrg.ora file on the PRIMARY Machine (If exist replace with new value):

*.archive_lag_target=0
*.db_file_name_convert='/oracle/oradata/trg1','/oracle/oradata01/trg','/oracle/oradata01/trg1','/oracle/oradata01/trg' *.db_unique_name='trg'
*.dg_broker_start=TRUE
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb1.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg_XPT)(INSTANCE_NAME=trg)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb2.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg1_XPT)(SERVER=dedicated)))'
*.Log_archive_config='DG_CONFIG=(trg,trg1)'
*.log_archive_dest_1='location="/oracle/oradata01/trg/archives"','valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb2.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg1_XPT)(INSTANCE_NAME=trg1)(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="trg1" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
*.log_archive_trace=0
*.log_file_name_convert='/oracle/oradata/trg1','/oracle/oradata/trg'
*.Service_names='trg'
*.standby_archive_dest='/oracle/oradata01/trg/archives'
*.standby_file_management='AUTO'

Take the Database to Archive Mode if it not:

SQL> startup mount;

SQL> Alter database ArchiveLog;

Enable Force Logging:

SQL> Alter database Force Logging;

Check the status:

SQL>select FORCE_LOGGING from V$database;

Create standby redo logs on the PRIMARY Database to support the standby role. The standby redo logs are the same size as the PRIMARY Database online logs. The recommended number of standby redo logs is one more than the number of online redo logs for each thread. For example two online redo logs for each thread, three standby redo logs are required for each thread.

A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the Standby Redo Logs (SRL) with the same size as the online redo logs.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/oracle/oradata/trg/StbyRedo4.log') SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/oracle/oradata/trg/StbyRedo5.log') SIZE 52428800;


ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/oracle/oradata/trg/StbyRedo6.log') SIZE 52428800;

Check the STANDBY logfiles:

SQL>SELECT * FROM V$STANDBY_LOG


Listener entry at PRIMARY machine:

(SID_DESC =
(GLOBAL_DBNAME = trg_DGMGRL)
(ORACLE_HOME = /oracle/ora10g2)
(SID_NAME = trg)
(SERVICE_NAME = trg)
)

#lsnrctl stop
#lsnrctl start


TNS Entry in TNSnames.ora file through which the PRIMARY Server will be connected to the STANDBY Server.

trg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = traindb1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trg)
)
)

trg1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = traindb2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trg1)
)
)



Check the connectivity from the SQL Prompt:

SQL> connect sys/oracle@trg as sysdba

Setup of SSH and SCP:

# mkdir ~/.ssh
# chmod 700 ~/.ssh
#/usr/bin/ssh-keygen -t rsa
#/usr/bin/ssh-keygen -t dsa

#cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
#cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

#scp authorized_keys traindb1:~/.ssh/authorized_keys
#cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
#cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

Backup the PRIMARY Database to create the STANDBY Database:

Make sure that you can have same folder on source and STANDBY Database
On both Nodes (Primary & Standby) :

#mkdir -p /oracle/oradata01/backup/rmanbackup

On PRIMARY Server, connect to the PRIMARY Database and create a PFILE from the SPFILE

SQL> create pfile='/oracle/oradata01/backup/rmanbackup/inittrg.ora' from spfile;

#rman target /
Rman>crosscheck archivelog all;

Rman> delete force noprompt expired archivelog all;

Rman> backup device type disk format '/oracle/oradata01/backup/rmanbackup/%u' database plus archivelog;

Rman> backup device type disk format '/oracle/oradata01/backup/rmanbackup/ctr_%u' current controlfile for standby;

#scp * traindb2:/oracle/oradata01/backup/rmanbackup

Now “Mount” the PRIMARY Database ( To avoid RMAN error messages, RMAN-03002, RMAN-05500):

SQL>shutdown immediate;
SQL>startup mount


CONFIGURATION ON STANDBY

mkdir -p /oracle/admin/trg1/bdump
mkdir -p /oracle/admin/trg1/cdump
mkdir -p /oracle/admin/trg1/udump
mkdir -p /oracle/admin/trg1/adump
*make change path as per available mount point
mkdir -p /oracle/oradata01/trg1/stby_archive

mkdir -p /oracle/oradata/trg1

Add STANDBY Database name in /etc/oratab file:
trg1:/oracle/ora10g2:Y


Copy inittrg.ora file from PRIMARY Server to STANDBY Server, rename to inittrg1.ora
Open file inittrg1.ora in vi editor, Rename trg1 to trg3 globally, rename trg to trg1 globally than rename trg3 to trg globally.


Add / Check following parameters in the inittrg1.ora file on the STANDBY Server


*.archive_lag_target=0
*.db_file_name_convert='/oracle/oradata/trg','/oracle/oradata01/trg1','/oracle/oradata01/trg','/oracle/oradata01/trg1' *.db_unique_name='trg1'
*.dg_broker_start=TRUE
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb1.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg1_XPT)(INSTANCE_NAME=trg1)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb2.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg_XPT)(SERVER=dedicated)))'
*.Log_archive_config='DG_CONFIG=(trg1,trg)'
*.log_archive_dest_1='location="/oracle/oradata01/trg1/archives"','valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb2.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg_XPT)(INSTANCE_NAME=trg)(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="trg" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
*.log_archive_trace=0
*.log_file_name_convert='/oracle/oradata/trg','/oracle/oradata/trg1'
*.Service_names='trg1'
*.standby_archive_dest='/oracle/oradata01/trg1/archives'
*.standby_file_management='AUTO'

Create password file at STANDBY Server:


# orapwd file=/oracle/ora10g2/dbs/orapwtrg1 password=password entries=5

Listener entry at STANDBY Server:


(SID_DESC =
(GLOBAL_DBNAME = trg1_DGMGRL)
(ORACLE_HOME = /oracle/ora10g2)
(SID_NAME = trg1)
(SERVICE_NAME = trg1)
)


#lsnrctl stop
#lsnrctl start

TNS Entry in TNSnames.ora file through which the STANDBY Server will be connected to the PRIMARY Server.


trg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = traindb1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trg)
)
)


trg1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = traindb2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trg1)
)
)


Create a spfile at STANDBY Server:

Sqlplus> startup nomount pfile=’/oracle/ora10g2/dbs/inittrg1.ora’
Sqlplus> Create spfile from pfile;

Now startup the STANDBY Database to nomount stage:

#rman auxiliary / target sys/password@trg

connected to target database: DRGTRG3 (DBID=4054010069, not open)
connected to auxiliary database: DRGTRG3 (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;

Query the column DATABASE_ROLE from V$DATABASE to view the role of STANDBY Database. It should return ‘PHYSICAL STANDBY’.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

LOG SHIPPING

On PRIMARY Server:

# sqlplus / as sysdba
SQL> alter database open;
SQL> Alter system switch logfile;

Now on the PRIMARY Database check the status of STANDBY Archiving destination.

SQL> Select Status, Error from v$Archive_dest where dest_id=2;

The STATUS should return – VALID. If it returns Error, then check the connectivity between the PRIMARY and STANDBY machines

LOG APPLY SERVICE

On the STANDBY Database execute the following command to start Managed Recovery Process (MRP). This command is executed on Mount stage.

SQL> Alter Database Recover Managed Standby Database Disconnect;

To check whether the log is applied or not:

SQL> Select Name, Archived, Applied from v$Archived_log;

Now, Connect to the STANDBY Database, and create the standby redo logs to support the standby role.
The standby redo logs must be the same size as the PRIMARY Database online logs. The recommended number of standby redo logs is:
(maximum # of logfiles +1) * maximum # of threads
We have two online log files for each thread. Thus, the number of standby redo logs
should be (2 + 1) * 1 = 3.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8
('/oracle/oradata/trg1/StbyRedo8.log') SIZE 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9
('/oracle/oradata/trg1/StbyRedo9.log') SIZE 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
('/oracle/oradata/trg1/StbyRedo10.log') SIZE 52428800;

Switchover:

At PRIMARY Server:

SQL> Select Database_role from v$Database;
DATABASE_ROLE
----------------
PRIMARY

At Standby Server:

SQL> Select Database_role from v$Database;
PHYSICAL STANDBY

SQL> Select switchover_status from v$Database;
SESSIONS ACTIVE

To perform a switchover (manually)

At PRIMARY Server:

SQL> Alter Database Commit to Switchover to Physical Standby with session Shutdown;

SQL> shutdown immediate;

SQL> startup mount;

Now your PRIMARY Database has become Physical STANDBY:

SQL> Select Database_role from v$database;
PHYSICAL STANDBY

On the STANDBY Database execute the following command to start Managed Recovery Process (MRP). This command is executed on Mount stage.

SQL> Alter Database Recover Managed Standby Database Disconnect;

At STANDBY Server, MRP which is running in the background of the STANDBY Database:

SQL> Alter database Recover Managed Standby Database Cancel;

To perform a manual switchover from Physical STANDBY to PRIMARY:

SQL> Alter Database Commit to Switchover to PRIMARY with session Shutdown;

SQL> Select Database_role from v$Database;
PRIMARY

Now “PHYSICAL STANDBY” Database has become “PRIMARY”:

SQL> shutdown immediate;

SQL> startup;

Now on the PRIMARY Database check the status of Standby Archiving destination:

SQL> Select Status, Error from v$Archive_dest where dest_id=2;


DG broker configuration

show parameter broker

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /oracle/ora10g2/dbs/dr1trg1.dat
dg_broker_config_file2 string /oracle/ora10g2/dbs/dr2trg1.dat
dg_broker_start boolean FALSE

To enable the Data guard broker:

SQL> alter system set dg_broker_start=true scope=both;
System altered.

$ dgmgrl
DGMGRL for Linux: Version 10.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

Connect to PRIMARY Database

DGMGRL> connect sys/oracle@trg
Connected.Connected.

Create broker configuration:

DGMGRL>create configuration 'primary_hst' as
primary database is 'trg'
connect identifier is 'trg';

Add the STANDBY Database ‘TRG1’

DGMGRL> add database 'trg1' as connect identifier is 'trg1' maintained as physical;

Check the configuration:

DGMGRL> show configuration

Configuration
Name: primary_hst
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
trg - Primary database
trg1 - Physical standby database

Current status for "primary_hst":
DISABLED

To enable the DG Broker Configuration:

DGMGRL> enable configuration;
Enabled


DGMGRL> show configuration;
Configuration
Name: primary_hst
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
trg - Primary database
trg1 - Physical standby database

Current status for "primary_hst":
SUCCESS

To show DG broker configuration for PRIMARY Database ‘TRG’:


DGMGRL> show database verbose trg
Database
Name: trg
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
trg

Properties:
InitialConnectIdentifier = 'trg'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/oracle/oradata/trg1, /oracle/oradata01/trg, /oracle/oradata01/trg1, /oracle/oradata01/trg'
LogFileNameConvert = '/oracle/oradata/trg1, /oracle/oradata/trg'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'traindb1.oneshield.com'
SidName = 'trg'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=traindb1.oneshield.com)(PORT=1521))'
StandbyArchiveLocation = '/oracle/oradata01/trg/archives'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'trg_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "trg":
SUCCESS


To edit DG broker configuration:

DGMGRL>edit database 'trg' set property 'DbFileNameConvert'='/oracle/oradata/trg1, /oracle/oradata01/trg, /oracle/oradata01/trg1, /oracle/oradata01/trg';

DGMGRL>edit database 'trg' set property 'LogFileNameConvert'='/oracle/oradata/trg1, /oracle/oradata/trg';


Switchover to STANDBY Database ‘TRG1’:

DGMGRL> switchover to 'trg1';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "trg" on database "trg"
Shutting down instance "trg"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "trg1" on database "trg1"
Shutting down instance "trg1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "trg" on database "trg"
Starting instance "trg"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "trg1" on database "trg1"
Starting instance "trg1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "trg1"

PRIMARY Database Successfully converted to Physical STANDBY.

Failover to STANDBY database ‘TRG1’:

DGMGRL> failover to ‘trg1’;

Identify and kill the locking session(s) automatically

This summary is not available. Please click here to view the post.

Trouble shooting with Grid Control Management Agent installation / Registration

When you got below error message at last screen while installing Grid Control Management Agent S/w

The "/oracle/agent10g/cfgtoollogs/configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.

One of reasons would be incorrect password

Check the Installation log file:

$ORACLE_BASE/oraInventory/logs/installActionsYYYY-MM-DD_HH-MI-SSAM.log
Example: /oracle/oraInventory/logs/installActions2009-05-15_09-05-00AM.log
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Enter Agent Registration password :
Agent is already stopped... Done.
Securing agent... Started.
Requesting an HTTPS Upload URL from the OMS... Done.
Requesting an Oracle Wallet and Agent Key from the OMS... Failed.
Invalid Agent Registration Password.
The Agent has not been secured.

To fixed the problem

Need to register client manually with EM management server.

[oracle@traindb1 bin]$ $AGENT_HOME/emctl secure agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Enter Agent Registration password :
Agent is already stopped... Done.
Securing agent... Started.
Requesting an HTTPS Upload URL from the OMS... Done.
Requesting an Oracle Wallet and Agent Key from the OMS... Done.
Check if HTTPS Upload URL is accessible from the agent... Done.
Configuring Agent for HTTPS in CENTRAL_AGENT mode... Done.
EMD_URL set in /oracle/agent10g/sysman/config/emd.properties
publicKeyFile = /oracle/agent10g/sysman/config/nmosudo.props
Generating RSA keys.
Saving private key in wallet.
SaveKeyWallet: Cannot open wallet (error=28759): file:/oracle/agent10g/sysman/config
SaveKeyWallet: Re-creating wallet.
Reading private key from wallet.
Saving public key into file.
Reading public key from file.
Encrypting clear-text: gensudoprops pass.
Decrypting encrypted-text.
Decrypted Text: gensudoprops pass
Securing agent... Successful.

To start the agent

[oracle@traindb1 bin]$ $AGENT_HOME/emctl start agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ...... started.

Check the status of agent

[oracle@traindb1 bin]$ $AGENT_HOME/emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.4.0
OMS Version : 10.2.0.3.0
Protocol Version : 10.2.0.3.0
Agent Home : /oracle/agent10g
Agent binaries : /oracle/agent10g
Agent Process ID : 31849
Parent Process ID : 31832
Agent URL : https://traindb1.oneshield.com:3872/emd/main/
Repository URL : https://oractrl.oneshield.com:1159/em/upload
Started at : 2009-05-14 11:24:10
Started by user : oracle
Last Reload : 2009-05-14 11:24:10
Last successful upload : (none)
Last attempted upload : (none)
Total Megabytes of XML files uploaded so far : 0.00
Number of XML files pending upload : 6
Size of XML files pending upload(MB) : 5.87
Available disk space on upload filesystem : 31.83%
Last successful heartbeat to OMS : 2009-05-14 11:24:14
---------------------------------------------------------------
Agent is Running and Ready

To upload configuration setting file

Some time Configured file needs to be loaded manually.

[oracle@traindb1 bin]$ $AGENT_HOME/emctl upload
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Check for Uploading of configure file

[oracle@traindb2 bin]$ $AGENT_HOME/emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.4.0
OMS Version : 10.2.0.3.0
Protocol Version : 10.2.0.3.0
Agent Home : /oracle/agent10g
Agent binaries : /oracle/agent10g
Agent Process ID : 9630
Parent Process ID : 9601
Agent URL : https://traindb2.oneshield.com:3872/emd/main/
Repository URL : https://oractrl.oneshield.com:1159/em/upload
Started at : 2009-05-14 12:06:34
Started by user : oracle
Last Reload : 2009-05-14 13:15:14
Last successful upload : 2009-05-16 19:39:10
Total Megabytes of XML files uploaded so far : 83.10
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 77.24%
Last successful heartbeat to OMS : 2009-05-16 19:39:09
---------------------------------------------------------------
Agent is Running and Ready

IPLLIVE IPL LIVE IPL2LIVE CRICKET

You may watch free IPL2 Cricket matches online:

www.cricheats.net

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;