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
Monday, July 20, 2009
Monday, July 13, 2009
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’;
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’;
Sunday, July 5, 2009
Identify and kill the locking session(s) automatically
This summary is not available. Please
click here to view the post.
Saturday, May 16, 2009
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
Saturday, May 9, 2009
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)
Sunday, May 3, 2009
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 \;
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 \;
Subscribe to:
Posts (Atom)