Hello,
This post explains how to restart Oracle Data Guard
Restarting Data Guard
Pausing Data Guard
On standby database (BBMLDR)
[/opt/oracle/app/oracle]> sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 20 10:26:10 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL > Conn sys/as sysdba
Password -******
Connected
SQL> ALTER DATABASE RECOVER STANDBY DATABASE CANCEL;
Restarting
On standby database (BBMLDR)
sqlplus /as sysdba
SQL> startup nomount; (if database server has been shutdown)
SQL> alter database mount standby database; (if the database server has been shutdown)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
or
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
On Primary database (BBMLPROD)
sqlplus /as sysdba
SQL> Alter system switch logfile;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
(above statement archive the current log file and send previous archive log to BBMLDR)
(verify within the alert log of the primary database : vi $HOME/admin/BBMLPROD/bdump/ALert8.log
e.g. ARC1: Standby redo logfile selected for thread 3 sequence 1487 for destination LOG_ARCHIVE_DEST_2
verify on standby database:
select sequence#, name, first_time, next_time , completion_time applied from v$ARCHIVED_LOG order by first_time;
Check the last set of rows to verify that the first_time, next_time and completion_time columns are at least showing current date. Also, the applied column has YES through the data set except on the last few rows because the log hasn't been applied to the database.
Regards,
Sowmya (OSR)
Friday, August 20, 2010
Thursday, August 19, 2010
How to Open the Standby Database when the Primary is Lost -Oracle Data Guard
Hello,
Here are the following steps to activate the standby and open the standby.
Follow these steps to open the standby database
1. Startup Mount
2. Check status
3. Recover if you have logs to apply
4. Finish the Recovery Process
5. Activate the Standby Database
6. Check the new status
7. Open the Database
1. Startup Mount
[/opt/oracle/app/oracle/product/10.2.0/Db_1]> sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 18 10:34:16 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn sys/as sysdba
Enter password:
Connected.
SQL> Startup Mount
2. Check Status
SQL>SQL> select NAME ,LOG_MODE , OPEN_MODE, PROTECTION_MODE , DATABASE_ROLE ,ACTIVATION# from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
ACTIVATION#
-----------
BBMLPROD ARCHIVELOG MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
3731587029
3. Recover if you have logs to apply
In this example we consider that primary is lost and we don’t have more archived logs to be applied.
SQL> Recover Standby Database;
4. Finish the Recovery Process
SQL> Alter database recover managed standby database finish;
Database altered
5. Activate the Standby Database
SQL> Alter Database Activate physical standby database;
Database altered
6. Check the new status
SQL> select NAME , OPEN_MODE, PROTECTION_MODE , DATABASE_ROLE ,
from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
BBMLPROD MOUNTED MAXIMUM PERFORMANCE PRIMARY
7. Open Database
SQL> Alter database open;
Database altered
SQL> select NAME , OPEN_MODE, PROTECTION_MODE , DATABASE_ROLE ,
from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
BBMLPROD READ WRITE MAXIMUM PERFORMANCE PRIMARY
Regards,
Sowmya (OSR)
Here are the following steps to activate the standby and open the standby.
Follow these steps to open the standby database
1. Startup Mount
2. Check status
3. Recover if you have logs to apply
4. Finish the Recovery Process
5. Activate the Standby Database
6. Check the new status
7. Open the Database
1. Startup Mount
[/opt/oracle/app/oracle/product/10.2.0/Db_1]> sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 18 10:34:16 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn sys/as sysdba
Enter password:
Connected.
SQL> Startup Mount
2. Check Status
SQL>SQL> select NAME ,LOG_MODE , OPEN_MODE, PROTECTION_MODE , DATABASE_ROLE ,ACTIVATION# from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
ACTIVATION#
-----------
BBMLPROD ARCHIVELOG MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
3731587029
3. Recover if you have logs to apply
In this example we consider that primary is lost and we don’t have more archived logs to be applied.
SQL> Recover Standby Database;
4. Finish the Recovery Process
SQL> Alter database recover managed standby database finish;
Database altered
5. Activate the Standby Database
SQL> Alter Database Activate physical standby database;
Database altered
6. Check the new status
SQL> select NAME , OPEN_MODE, PROTECTION_MODE , DATABASE_ROLE ,
from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
BBMLPROD MOUNTED MAXIMUM PERFORMANCE PRIMARY
7. Open Database
SQL> Alter database open;
Database altered
SQL> select NAME , OPEN_MODE, PROTECTION_MODE , DATABASE_ROLE ,
from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
BBMLPROD READ WRITE MAXIMUM PERFORMANCE PRIMARY
Regards,
Sowmya (OSR)
Thursday, August 12, 2010
Oracle DBA Check List
Hello,
Here i am going to explain you daily, weekly, monthly responsibilities of Oracle DBA
Oracle DBA Checklist
Daily Procedures
1. Verify all instances are up and running: Run daily reports via Enterprise manager Probe event or through Oracle Grid
2. Look for any new alert log entries: Check for any ORA –errors in you alert log $ORACLE_HOME/admin/bdump/alert_ORACLE_SID.log
3. Verify success of database backup
4. Verify success of database archiving to tape
5. Verify DBSNMP is running
6. Verify free space in tablespaces
7. Verify rollback segment
8. Identify bad growth projection
9. Verify whether archivelogs are backed up successfully to your tapes
10. Verify rollback segment
11. Identify space bound objects
12. Process to review contention for CPU, memory, netowrk or disk resources
13. Copy Archived logs to standby Database and Roll Forward
14. Read DBA manuals, forums, journal and so on.
15. Analyze tables and indexes if needed
Nightly Procedures
1. Collect volumetric data : mk_Volfact.sql, analyze_comp.sql, pop_vol.sql
Weekly Procedures
1. Look for objects that break rules
a. Check for Next_Extents ,
b. Check Existing Extents
c. Check missing PK
d. Check whether all indexes are using INDEXES tablespace
e. Check the consistency of the schema objects between production and test environment
2. Look for security Policy Violations
3. Look for SQL * Net logs for errors, issues
a. Check Client side logs
b. Check server side logs
4. Archvie all alert logs to history
5. Attend Weekly Meetings (Project status , discussions on issues and so on)
Monthly Procedures
1. Look for harmful growth rates
2. Review tuning opppurtunities : Cache hit ration, latch contention etc
3. Look for I/O Contention
4. Review fragmentation : row chaining etc
5. Project Performance into the future
6. Perform tuning and maintenance
7. Provide monthly reports (this depends on company to company)
8. Attend Monthly meetings
Quartely Procedures
1. Provide Quartely Audit Reports if needed (This depends on company to company)
This is checking of all users, roles, privileges and so on
Regards,
Sowmya (OSR)
Here i am going to explain you daily, weekly, monthly responsibilities of Oracle DBA
Oracle DBA Checklist
Daily Procedures
1. Verify all instances are up and running: Run daily reports via Enterprise manager Probe event or through Oracle Grid
2. Look for any new alert log entries: Check for any ORA –errors in you alert log $ORACLE_HOME/admin/bdump/alert_ORACLE_SID.log
3. Verify success of database backup
4. Verify success of database archiving to tape
5. Verify DBSNMP is running
6. Verify free space in tablespaces
7. Verify rollback segment
8. Identify bad growth projection
9. Verify whether archivelogs are backed up successfully to your tapes
10. Verify rollback segment
11. Identify space bound objects
12. Process to review contention for CPU, memory, netowrk or disk resources
13. Copy Archived logs to standby Database and Roll Forward
14. Read DBA manuals, forums, journal and so on.
15. Analyze tables and indexes if needed
Nightly Procedures
1. Collect volumetric data : mk_Volfact.sql, analyze_comp.sql, pop_vol.sql
Weekly Procedures
1. Look for objects that break rules
a. Check for Next_Extents ,
b. Check Existing Extents
c. Check missing PK
d. Check whether all indexes are using INDEXES tablespace
e. Check the consistency of the schema objects between production and test environment
2. Look for security Policy Violations
3. Look for SQL * Net logs for errors, issues
a. Check Client side logs
b. Check server side logs
4. Archvie all alert logs to history
5. Attend Weekly Meetings (Project status , discussions on issues and so on)
Monthly Procedures
1. Look for harmful growth rates
2. Review tuning opppurtunities : Cache hit ration, latch contention etc
3. Look for I/O Contention
4. Review fragmentation : row chaining etc
5. Project Performance into the future
6. Perform tuning and maintenance
7. Provide monthly reports (this depends on company to company)
8. Attend Monthly meetings
Quartely Procedures
1. Provide Quartely Audit Reports if needed (This depends on company to company)
This is checking of all users, roles, privileges and so on
Regards,
Sowmya (OSR)
Wednesday, August 11, 2010
Oracle Grid - Creating Notification Rules (2)
Hello,
In this post i am going to specify some more Notification rules
6. Setting up notification rule for Monitoring Cluster_Database_Critical
Login into Oracle Grid using Dbwatch
Click on preferences -> Rules
Click on Create like ->Provide Name: MONITOR_Cluster_Database_Critical, enter Description, make the rule as public, target type –cluster and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics (select the necessary metrics which are needed and I have mentioned few of them)
a. Blocking Session Count
b. Broken Job Count
c. Failed Job Count
d. Failed Login Count
e. Segment Approaching Maximum Extents Count
f. Segment Not Able to Extend Count
Select Severity as Critical
Click ok and finish
7. Setting up notification rule for Monitoring Cluster_Database_Warning
Login into Oracle Grid using Dbwatch
Click on preferences ->Rules
Click on Create like ->Provide Name: MONITOR_Cluster_Database_Warning, enter Description, make the rule as public, target type –cluster and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics (select the necessary metrics)
a. Blocking Session Count
b. Broken Job Count
c. Failed Job Count
d. Failed Login Count
e. Segment Approaching Maximum Extents Count
f. Segment Not Able to Extend Count
Select Severity as Warning
Click ok and finish
8. Setting up notification rule for Monitoring Database_Instance_Critical
Login into Oracle Grid using Dbwatch
Click on preferences -> Rules
Click on Create like ->Provide Name: MONITOR_Database_Instance_Critical, enter Description, make the rule as public, target type –Database Instance and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics (select the metrics which are needed below are few metrics)
a. Archive Area Used (%)
b. Archiver Hung alert Log Error
c. Archiver Hung Alert Log Error Status
d. Blocking Session Count
e. Broken Job Count
f. Data Block Corruption Alert Log Error
g. Failed Job Count
h. Generic Alert Log Error
i. Process Limit Usage (%)
j. Segment Approaching Maximum Extents Count
Select Severity as Critical
Click ok and finish
9. Setting up notification rule for Monitoring Database_Instance_Warning
Login into Oracle Grid using Dbwatch
Click on preferences -> Rules
Click on Create like -> Provide Name: MONITOR_Database_Instance_Warning, enter Description, make the rule as public, target type –Database Instance and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics (select the metrics which are needed, below are the few metrics)
a. Archive Area Used (%)
b. Archiver Hung alert Log Error
c. Archiver Hung Alert Log Error Status
d. Blocking Session Count
e. Broken Job Count
f. Data Block Corruption Alert Log Error
g. Failed Job Count
h. Generic Alert Log Error
i. Process Limit Usage (%)
j. Segment Approaching Maximum Extents Count
Select Severity as Warning
Click ok and finish
10. Setting up notification rule for Monitoring Host_Critical
Login into Oracle Grid using Dbwatch
Click on preferences -> Rules
Click on Create like -> Provide Name: MONITOR_HOST_CRITICAL, enter Description, make the rule as public, target type –Host and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics
a. CPU in I/O Wait (%)
b. CPU Utilization (%)
c. Filesystem Space Available (%)
d. Memory Utilization (%)
Select Severity as Critical
Click ok and finish
11. Setting up notification rule for Monitoring HOST_Warning
Login into Oracle Grid using Dbwatch
Click on preferences -> Rules
Click on Create like -> Provide Name: MONITOR_HOST_Warning, enter Description, make the rule as public, target type –Host and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics
a. CPU in I/O Wait (%)
b. CPU Utilization (%)
c. Filesystem Space Available (%)
d. Memory Utilization (%)
Select Severity as Warning
Click ok and finish
Regards,
Sowmya (OSR)
In this post i am going to specify some more Notification rules
6. Setting up notification rule for Monitoring Cluster_Database_Critical
Login into Oracle Grid using Dbwatch
Click on preferences -> Rules
Click on Create like ->Provide Name: MONITOR_Cluster_Database_Critical, enter Description, make the rule as public, target type –cluster and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics (select the necessary metrics which are needed and I have mentioned few of them)
a. Blocking Session Count
b. Broken Job Count
c. Failed Job Count
d. Failed Login Count
e. Segment Approaching Maximum Extents Count
f. Segment Not Able to Extend Count
Select Severity as Critical
Click ok and finish
7. Setting up notification rule for Monitoring Cluster_Database_Warning
Login into Oracle Grid using Dbwatch
Click on preferences ->Rules
Click on Create like ->Provide Name: MONITOR_Cluster_Database_Warning, enter Description, make the rule as public, target type –cluster and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics (select the necessary metrics)
a. Blocking Session Count
b. Broken Job Count
c. Failed Job Count
d. Failed Login Count
e. Segment Approaching Maximum Extents Count
f. Segment Not Able to Extend Count
Select Severity as Warning
Click ok and finish
8. Setting up notification rule for Monitoring Database_Instance_Critical
Login into Oracle Grid using Dbwatch
Click on preferences -> Rules
Click on Create like ->Provide Name: MONITOR_Database_Instance_Critical, enter Description, make the rule as public, target type –Database Instance and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics (select the metrics which are needed below are few metrics)
a. Archive Area Used (%)
b. Archiver Hung alert Log Error
c. Archiver Hung Alert Log Error Status
d. Blocking Session Count
e. Broken Job Count
f. Data Block Corruption Alert Log Error
g. Failed Job Count
h. Generic Alert Log Error
i. Process Limit Usage (%)
j. Segment Approaching Maximum Extents Count
Select Severity as Critical
Click ok and finish
9. Setting up notification rule for Monitoring Database_Instance_Warning
Login into Oracle Grid using Dbwatch
Click on preferences -> Rules
Click on Create like -> Provide Name: MONITOR_Database_Instance_Warning, enter Description, make the rule as public, target type –Database Instance and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics (select the metrics which are needed, below are the few metrics)
a. Archive Area Used (%)
b. Archiver Hung alert Log Error
c. Archiver Hung Alert Log Error Status
d. Blocking Session Count
e. Broken Job Count
f. Data Block Corruption Alert Log Error
g. Failed Job Count
h. Generic Alert Log Error
i. Process Limit Usage (%)
j. Segment Approaching Maximum Extents Count
Select Severity as Warning
Click ok and finish
10. Setting up notification rule for Monitoring Host_Critical
Login into Oracle Grid using Dbwatch
Click on preferences -> Rules
Click on Create like -> Provide Name: MONITOR_HOST_CRITICAL, enter Description, make the rule as public, target type –Host and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics
a. CPU in I/O Wait (%)
b. CPU Utilization (%)
c. Filesystem Space Available (%)
d. Memory Utilization (%)
Select Severity as Critical
Click ok and finish
11. Setting up notification rule for Monitoring HOST_Warning
Login into Oracle Grid using Dbwatch
Click on preferences -> Rules
Click on Create like -> Provide Name: MONITOR_HOST_Warning, enter Description, make the rule as public, target type –Host and select apply rule to all agent targets
Select Availability Tab-> select Agent Unreachable and Agent Unreachable
Click on Metrics Tab and select the following metrics
a. CPU in I/O Wait (%)
b. CPU Utilization (%)
c. Filesystem Space Available (%)
d. Memory Utilization (%)
Select Severity as Warning
Click ok and finish
Regards,
Sowmya (OSR)
Oracle Data Guard - Introduction
Hello,
Introduction to Oracle Data Guard
Oracle Data Guard is most effective and comprehensive data protection and disaster recovery solutions available for enterprise data.
Oracle Data Guard maintains, manages and monitors one or more standby database to protect enterprise data from failures, disasters, errors and corruptions. Standby database is a consistent copy of production database. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, thus minimizing the downtime and enabling zero data loss.
Data Guard Configuration
1. Primary Database (Production Database)
2. Standby Database
a. Physical Standby database (Redo Apply)
b. Logical standby database (SQL Apply)
Data Guard Services
1. Log Transport Services
2. Log Apply Services : Redo Apply or SQL Apply
3. Role Management Services
Data Guard Broker
Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations using either GUI (In EM) or CLI(DGMGRL prompt)
Data Guard Protection Modes
1. Maximum Protection: No data loss. Primary database shuts down in case of transmission failure
2. Maximum Availability: No data loss. Primary database tolerates one transmission failure
3. Maximum Performance: A transaction is committed when its redo entries are written to local redo log.
Data Guard and complementary Technologies
1. Oracle Real Application clusters (RAC)
2. Flashback Database
3. Recovery Manager (RMAN)
Summary of Data Guard Benefits
1. Disaster recovery, data protection , and high availability
2. Complete data protection
3. Efficient use of system resources
4. Automatic gap detection and resolution
5. Centralized and simple management
6. Integration with Oracle Database
Types of Standby Databases
1. Physical Standby Databases : It works in 2 modes: Redo Apply ( the database cannot be opened while redo is being applied), open Read only and Open read /write
Although the physical standby database cannot perform both Redo Apply and be opened in read -only mode at the same time, you can switch between them.
Benefits of a Physical Standby Database
1. Efficient disaster recovery and high availability
2. Data Protection
3. Reduction in Primary database workload Performance
2. Logical Standby Database: The logical standby database can be used concurrently for data protection and reporting operations. It has some restrictions on datatypes, types of tables, and types of DDL and DML operations
Benefits of a Logical standby Database
1. It has same benefits as in a Physical standby database
2. Efficient use of standby hardware resources
3. Reduction in primary database workload
Online Redo Logs, Archived Redo Logs, and Standby Redo Logs
Redo data transmitted from the primary database is received by the remote file server (RFS) process on the standby system where the RFS process writes the redo data to archived log files or standby redo log files
Online Redo Logs and Archived Redo Logs
1. Online Redo Logs apply in the primary database and does not apply in the physical standby database
2. Primary database and both physical and logical standby databases each have an archived redo log.
Standby Redo Logs
1. A standby redo log is similar in all ways to an online redo log, except that a standby redo log is used only when the database is running in the standby role to store redo data received from the primary database. It is highly recommended to configure.
Regards,
Sowmya Bandaru (OSR)
Introduction to Oracle Data Guard
Oracle Data Guard is most effective and comprehensive data protection and disaster recovery solutions available for enterprise data.
Oracle Data Guard maintains, manages and monitors one or more standby database to protect enterprise data from failures, disasters, errors and corruptions. Standby database is a consistent copy of production database. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, thus minimizing the downtime and enabling zero data loss.
Data Guard Configuration
1. Primary Database (Production Database)
2. Standby Database
a. Physical Standby database (Redo Apply)
b. Logical standby database (SQL Apply)
Data Guard Services
1. Log Transport Services
2. Log Apply Services : Redo Apply or SQL Apply
3. Role Management Services
Data Guard Broker
Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations using either GUI (In EM) or CLI(DGMGRL prompt)
Data Guard Protection Modes
1. Maximum Protection: No data loss. Primary database shuts down in case of transmission failure
2. Maximum Availability: No data loss. Primary database tolerates one transmission failure
3. Maximum Performance: A transaction is committed when its redo entries are written to local redo log.
Data Guard and complementary Technologies
1. Oracle Real Application clusters (RAC)
2. Flashback Database
3. Recovery Manager (RMAN)
Summary of Data Guard Benefits
1. Disaster recovery, data protection , and high availability
2. Complete data protection
3. Efficient use of system resources
4. Automatic gap detection and resolution
5. Centralized and simple management
6. Integration with Oracle Database
Types of Standby Databases
1. Physical Standby Databases : It works in 2 modes: Redo Apply ( the database cannot be opened while redo is being applied), open Read only and Open read /write
Although the physical standby database cannot perform both Redo Apply and be opened in read -only mode at the same time, you can switch between them.
Benefits of a Physical Standby Database
1. Efficient disaster recovery and high availability
2. Data Protection
3. Reduction in Primary database workload Performance
2. Logical Standby Database: The logical standby database can be used concurrently for data protection and reporting operations. It has some restrictions on datatypes, types of tables, and types of DDL and DML operations
Benefits of a Logical standby Database
1. It has same benefits as in a Physical standby database
2. Efficient use of standby hardware resources
3. Reduction in primary database workload
Online Redo Logs, Archived Redo Logs, and Standby Redo Logs
Redo data transmitted from the primary database is received by the remote file server (RFS) process on the standby system where the RFS process writes the redo data to archived log files or standby redo log files
Online Redo Logs and Archived Redo Logs
1. Online Redo Logs apply in the primary database and does not apply in the physical standby database
2. Primary database and both physical and logical standby databases each have an archived redo log.
Standby Redo Logs
1. A standby redo log is similar in all ways to an online redo log, except that a standby redo log is used only when the database is running in the standby role to store redo data received from the primary database. It is highly recommended to configure.
Regards,
Sowmya Bandaru (OSR)
Subscribe to:
Posts (Atom)
