Standby Database

May 21, 2012 Leave a comment

Data Guard promises:

  • Disaster recovery
  • High availability
  • Data protection
  • Flexible balancing between data protection and performance requirements
  •  Simple management through the data guard broker.
  • Gap detection


1. Very low failure rate
All system components are duplicated. The primary and standby instances can run on different hosts. They can also have separate locations depending on the safety requirements.

2. Very short downtime

If an error occurs in the primary database system and you have to recover the database, you can perform the recovery very quickly on the standby host. You can avoid the time-consuming datafile restore, since these files are already located on the standby host.

The only thing you need to do is to import the last entries from the redo log files. Therefore, the standby instance can take over the tasks of the primary instance very quickly.

3. Significant decrease of the load on the production host

The database backup requires considerable resources and time for large databases. Since the backup can run on the standby host, the load on the primary instance is reduced significantly.
Therefore, the resources on the production host are fully available for production operation, and you do not need to interrupt or restrict database operation for a backup.

4. Consistency

Applying redo log files to the standby database immediately verifies their consistency. No other tool can achieve this level of verification.


1. High costs

For a standby database scenario, all system components need to be available in duplicate. In particular, duplicate hardware resources (CPU, hard disks, and so on) are expensive.

2. High system administration expense

You need to set up the standby host. If structural changes are made on the primary database system, you must make the required resources are available on the standby host. When the standby instance has taken over production operation – a “takeover” – you must set up a replacement standby database.

3. High requirements for switchover software

So that the standby instance can take over production operation, the appropriate switchover software is required. The user and the suppliers of the hardware and system software are responsible for selecting this software and making sure that it functions correctly.

Step-by-step instructions on how to create a Physical Standby Database on Windows and UNIX servers, and maintenance tips on the databases in a Data Guard Environment:

(Article by Hailie Jiao )

In this example the database version is The Primary database and Standby database are located on different machines at different sites. The Primary database is called PRIM and the Standby database is called STAN. I use Flash Recovery Area, and OMF.

a) Setup the environment

1. Make sure the operating system and platform architecture on the primary and standby systems are the same.
2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.
3. Test the Standby Database creation on a test environment first before working on the Production database.

b) On the Primary Database Side

1. Enable forced logging on your primary database:


2. Create a password file if it doesn’t exist.
1) To check if a password file already exists, run the following command:

SQL> select * from v$pwfile_users;

2) If it doesn’t exist, use the following command to create one:

On Windows:
 $cd %ORACLE_HOME%\database
 $orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
 (Note: Replace xxxxxxxxx with the password for the SYS user.)
 $Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
 (Note: Replace xxxxxxxxx with your actual password for the SYS user.)

3. Configure a Standby Redo log.

1)  The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:

SQL> select bytes from v$log;


2) Use the following command to determine your current log file groups:

SQL> select group#, member from v$logfile;

3) Create standby Redo log groups.
Primary database had 3 log file groups originally and 3 standby redo log groups are created now using the following commands:


4) To verify the results of the standby redo log groups creation, run the following query:

SQL>select * from v$standby_log;

4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:

SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;

5. Set Primary Database Initialization Parameters

Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.

1) Create pfile from spfile for the primary database:

On Windows:
SQL>create pfile=’\database\pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).
SQL>create pfile=’/dbs/pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).
2) Edit pfilePRIM.ora to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)

# Specify the location of the standby DB datafiles followed by the primary location;
# Specify the location of the standby DB online redo log files followed by the primary location 

6. Create spfile from pfile, and restart primary database using the new spfile.

Data Guard must use SPFILE. Create the SPFILE and restart database.

On windows:
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
(Note- specify your Oracle home path to replace ‘’).
SQL> shutdown immediate;
SQL> startup nomount pfile=’/dbs/pfilePRIM.ora’;
SQL>create spfile from pfile=’/dbs/pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;

c) On the Standby Database Site:

1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:

SQL>shutdown immediate;

On Standby Server (While the Primary database is shut down):
1) Create directory for data files, for example, on windows,
On UNIX, create the directory accordingly.

2) Copy the data files and temp files over.

3) Create directory (multiplexing) for online logs, for example, on Windows,
E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG and
On UNIX, create the directories accordingly.

4) Copy the online logs over.

2. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:

SQL>startup mount;
SQL>alter database create standby controlfile as ‘STAN.ctl;

3. Copy the Primary DB pfile to Standby server and rename/edit the file.

1) Copy pfilePRIM.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.
2) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)

 # Specify the location of the primary DB datafiles followed by the standby location
 # Specify the location of the primary DB online redo log files followed by the standby location
(Note: Not all the parameter entries are listed here.)

4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.

5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations

6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to \database folder, and on UNIX copy it to /dbs directory. And then rename the password file.

7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual

8. Configure listeners for the primary and standby databases.

1) On Primary system: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener.

$lsnrctl stop
$lsnrctl start

2) On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener.

$lsnrctl stop
$lsnrctl start

9. Create Oracle Net service names.
1) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:

$tnsping PRIM
$tnsping STAN

2) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN

10. On Standby server, setup the environment variables to point to the Standby database.


11. Start up nomount the standby database and generate a spfile.

On Windows:
 SQL>startup nomount pfile=’\database\pfileSTAN.ora’;
 SQL>create spfile from pfile=’\database\pfileSTAN.ora’;
 -- Restart the Standby database using the newly created SPFILE.
 SQL>shutdown immediate;
 SQL>startup mount;
 SQL>startup nomount pfile=’/dbs/pfileSTAN.ora’;
 SQL>create spfile from pfile=’/dbs/pfileSTAN.ora’;
 -- Restart the Standby database using the newly created SPFILE.
 SQL>shutdown immediate;
 SQL>startup mount;
 (Note- specify your Oracle home path to replace ‘’).

12. Start Redo apply

1) On the standby database, to start redo apply:

SQL>alter database recover managed standby database disconnect from session;

If you ever need to stop log apply services:

SQL> alter database recover managed standby database cancel;

13. Verify the standby database is performing properly:

1) On Standby perform a query:

SQL>select sequence#, first_time, next_time from v$archived_log;

2) On Primary, force a logfile switch:

SQL>alter system switch logfile;

3) On Standby, verify the archived redo log files were applied:

SQL>select sequence#, applied from v$archived_log order by sequence#;

14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.

To start real-time apply:

SQL> alter database recover managed standby database using current logfile disconnect;

15. To create multiple standby databases, repeat this procedure.

d) Maintenance:

1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.

2. Cleanup the archive logs on Primary and Standby servers.

I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.

For the standby database, I run RMAN to backup and delete the archive logs once per week.

$rman target /@STAN;
RMAN>backup archivelog all delete input;

To delete the archivelog backup files on the standby server, I run the following once a month:

RMAN>delete backupset;

3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server.

Refer to section b) 2, step 2 to update/recreate password file for the Standby database.

Source / Reference Links:


Database and Web Server on the same machine?

May 11, 2012 Leave a comment
Just a thought of having separate servers for database and web landed me on the following links:

My catch:

Single Server

Pros:  Performance gain, less latency time

Cons:  Vulnerable

Separate servers  

Pros:  Security advantage, scalability

Cons: Expense on extra hardware

More on this soon!

Active Connections to Oracle Database

May 3, 2012 Leave a comment

V$SESSION displays session information for each current session.

substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
— b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
b.paddr = a.addr
and type=’USER’
order by spid;


Categories: General, Uncategorized

Automatic Tuning of Undo Retention and Optimization

May 1, 2012 Leave a comment

Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured.

If the undo tablespace is fixed size, the database tunes the retention period for the best possible undo retention for that tablespace size and the current system load. This tuned retention period can be significantly greater than the specified minimum retention period.

If the undo tablespace is configured with the AUTOEXTEND option, the database tunes the undo retention period to be somewhat longer than the longest-running query on the system at that time. Again, this tuned retention period can be greater than the specified minimum retention period.

Determine the current retention period by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT view. This view contains one row for each 10-minute statistics collection interval over the last 4 days. (Beyond 4 days, the data is available in the DBA_HIST_UNDOSTAT view.) TUNED_UNDORETENTION is given in seconds.

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
 to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention
 from v$undostat order by end_time;
 --------------- --------------- -------------------
 04-FEB-05 00:01 04-FEB-05 00:11 12100
 07-FEB-05 23:21 07-FEB-05 23:31 86700
 07-FEB-05 23:31 07-FEB-05 23:41 86700
 07-FEB-05 23:41 07-FEB-05 23:51 86700
 07-FEB-05 23:51 07-FEB-05 23:52 86700
576 rows selected.

Calculating UNDO_RETENTION for given UNDO Tabespace

The following query will helps to optimize the UNDO_RETENTION parameter:

Otimal Undo Retention





Image Source

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time!

Actual Undo Size

 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND = c.tablespace_name
 AND a.ts# = b.ts#;

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 FROM v$undostat;

DB Block Size

 FROM v$parameter
WHERE name = 'db_block_size';

Optimal Undo Retention

770 [Sec]

Using Inline Views:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 ROUND((d.undo_size / (to_number(f.value) *
 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
 SELECT SUM(a.bytes) undo_size
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND = c.tablespace_name
 AND a.ts# = b.ts#
 ) d,
 v$parameter e,
 v$parameter f,
 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 FROM v$undostat
 ) g
WHERE = 'undo_retention'
 AND = 'db_block_size'

Calculating required UNDO Size for given Database Activity

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Again, all in one query:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
 g.undo_block_per_sec) / (1024*1024) 
 SELECT SUM(a.bytes) undo_size
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND = c.tablespace_name
 AND a.ts# = b.ts#
 ) d,
 v$parameter e,
 v$parameter f,
 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 FROM v$undostat
 ) g
 WHERE = 'undo_retention'
 AND = 'db_block_size'


The previous query may return a “NEEDED UNDO SIZE” that is less than the “ACTUAL UNDO SIZE”. If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.

References / Source:

Database Schema Documentation

April 26, 2012 Leave a comment

Tools that provide options to generate database schema documentation are found all over web. But you may end up with a paid version of the tool in the end.

Oracle SQL Developer itself has an option to generate a HTML document on  database schema.

  • Open Oracle SQL Developer.
  • Create a connection to the desired database.
  • Right click on the connection and find ‘Generate DB Doc’ option.
  • Select the option and specify the target directory.

Other open source tools for database schema documentation:

Using MySQL with .Net

April 25, 2012 Leave a comment

Required Components:

  • MySQL 5.0 database server
  • MySQL Connector/Net 1.0.6 or higher
  • MySQL Administrator 1.1 or higher
  • .NET Framework 1.0 or higher
  • Visual Studio 2002 or higher

Download and install MySQL Community Server
MySQL Community Edition is a freely downloadable version of the world’s most popular open source database that is supported by an active community of open source developers and enthusiasts.

Download Connector/Net
Connector/Net is a fully-managed ADO.NET driver for MySQL.

  • Download link : Connector/Net
  • Unzip the ZIP file to get the .MSI file and install it by double clicking on it.

Connect to MYSQL from .NET

  • Go to the solution explorer in Visual Studio and select Add References option.
  • From Add References dialog box, select “MySQL.Data” option and click on Ok.
  • Refer the following code to connect to MYSQL.
// Connection string for a typical local MySQL installation
string cnnString = 

// Create a connection object and data adapter
MySqlConnection cnx = new MySqlConnection(cnnString);
MySqlDataAdapter adapter = new MySqlDataAdapter();

// Create a SQL command object
string cmdText = "SELECT * FROM verse";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

// Create a fill a Dataset
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;

// Bind the DataSet
// ... Place your databinding code here ...

If you’re already accustomed to data binding in .NET, 
you should immediately recognize the similarities.  
The only difference is you use the MySqlXxx classes 
instead of the OleDbXxx or SqlXxx classes.
Source/ Reference Links:
Categories: MySQL Tags: ,

Oracle Database Security Checklist

April 24, 2012 Leave a comment

Checklist to keep secure an Oracle Database:

1. Install only what is required
The Oracle Database CD pack contains a host of options and products in addition to the database server. Install additional products and options only as necessary.

2. Lock and Expire Default User Accounts
If a manual (i.e. without using Database Configuration Assistant) installation of Oracle Database is performed, then no default database users are locked upon successful installation of the database server. Left open in their default states, these user accounts can be exploited to gain unauthorized access to data or disrupt database operations.

a) Enterprise Manager Accounts
The preceding list of accounts depends on whether you choose to install Enterprise Manager. If so, SYSMAN and DBSNMP are open as well, unless you configure Enterprise Manager for Central Administration. In this case, the SYSMAN account (if present) will be locked as well.

3. Change Default User Passwords
The most trivial method by which Oracle Database can be compromised is a default database server user account which still has a default password associated with it even after installation.

a) Change default passwords of administrative users
Oracle Database 10g enables you to use the same or different passwords for the SYS, SYSTEM, SYSMAN and DBSNMP administrative accounts. Use different passwords for each: in any Oracle environment (production or test), assign strong, secure, and distinct passwords to these administrative accounts.

b) Change default passwords of all users
Each of the accounts install with a default password that is exactly the same as that user account (For example, user MDSYS installs with the password MDSYS).

c) Enforce password management
Oracle recommends that basic password management rules (such as password length, history, complexity, and so forth) as provided by the database be applied to all user passwords and that all users be required to change their passwords periodically.

Oracle also recommends, if possible, using Oracle Advanced Security (an option to the Enterprise Edition of Oracle Database) with network authentication services (such as Kerberos), token cards, smart cards or X.509 certificates.

4. Enable Data Dictionary Protection
Oracle recommends that customers implement data dictionary protection to prevent users having the ANY system privileges from using such privileges on the data dictionary.

To enable dictionary protection, set the following configuration parameter to FALSE, in the init<sid>.ora control file: O7_DICTIONARY_ACCESSIBILITY = FALSE

5. Practicing the principle of least privilege

a) Grant necessary privileges only
The principle of least privilege is that users be given only those privileges that are actually required to efficiently perform their jobs.

To implement this principle, restrict the following as much as possible:
1) The number of SYSTEM and OBJECT privileges granted to database users, and
2) The number of people who are allowed to make SYS-privileged connections to the database.

b) Revoke unnecessary privileges from the public user group
Revoke all unnecessary privileges and roles from the database server user group PUBLIC. PUBLIC acts as a default role granted to every user in an Oracle database.

The more powerful packages that may potentially be misused are:

c) Grant a role to users only if they need all privileges of the role
Ensure that the roles you define contain only the privileges that reflect job responsibility. If your application users do not need all the privileges encompassed by an existing role, then apply a different set of roles that supply just the right privileges.
Altenatively, create and assign a more restricted role.

d)Restrict permissions on run-time facilities
Do not assign all permissions to any database server run-time facility such as the Oracle Java Virtual Machine (OJVM). Grant specific permissions to the explicit document root file paths for such facilities that may execute files and packages outside the database server.

6. Enforce access controls cffectively and authenticate clients stringently

a) Authenticate client properly
By default, Oracle allows operating-system-authenticated logins only over secure connections, which precludes using Oracle Net and a shared server configuration. This default restriction prevents a remote user from impersonating another operating system user over a network connection.

Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE forces the RDBMS to accept the client operating system user name received over a nonsecure connection and use it for account access. Since clients, such as PCs, are not trusted to perform operating system authentication properly, it is very poor security practice to turn on this feature.

7. Restrict Operating System Access
Limit the number of operating system users. Limit the privileges of the operating system accounts (administrative, root-privileged or DBA) on the Oracle Database host (physical machine) to the least privileges needed for the user’s tasks.

Oracle also recommends:
•     Restricting the ability to modify the default file and directory permissions for the Oracle Database home (installation) directory or its contents. Even privileged operating system users and the Oracle owner should not modify these permissions, unless instructed otherwise by Oracle.
•     Restricting symbolic links.

8. Restrict Network Access

a) Use a firewall
Keep the database server behind a firewall. Oracle Database network infrastructure, Oracle Net (formerly known as Net8 and SQL*Net), offers support for a variety of firewalls from various vendors.

b) Never poke a hole through a firewall
If Oracle Database is behind a firewall, then do not, under any circumstances, poke a hole through the firewall.
For example, do not leave open port 1521 for Oracle Listener to make a connection to the Internet or vice versa.

c) Protect the Oracle listener
Because the listener acts as the database gateway to the network, it is important to limit the consequences of malicious interference:
Restrict the privileges of the listener, so that it cannot read or write files in the database or the Oracle server address space.

Secure administration of the database by doing the following:

1. Prevent online administration by requiring the administrator to have write privileges on the LISTENER.ORA file and the listener password:

Add or alter this line in the LISTENER.ORA file ADMIN_RESTRICTIONS_LISTENER=ON
Then RELOAD the configuration.

2. Use SSL when administering the listener, by making the TCPS protocol the first entry in the address list as follows:

 (HOST =
 (PORT = 8281)))

3. Always establish a secure, well-formed password for the Oracle listener to prevent remote configuration of the Oracle listener.
4. Remove the external procedure configuration from the listener.ora file if you do not intend to use such procedures.
5. Monitor listener activity.

d) Monitor who accesses your systems

Instead of authenticating client computers over the Internet, make use of user authentication which avoids client system issues that include falsified IP addresses, hacked operating systems or applications, and falsified or stolen client system identities.

The following steps improve client computer security:
a) Configure the connection to use SSL.
Using SSL (Secure Sockets Layer) communication makes eavesdropping unfruitful and enables the use of certificates for user and server authentication.
b) Set up certificate authentication for clients and servers such that:

i. The organization is identified by unit and certificate issuer and the user is identified by distinguished name and certificate issuer.
ii. Applications test for expired certificates.
iii. Certificate revocation lists are audited.

e) Check network IP addresses

Use the Oracle Net valid node checking security feature to allow or deny access to Oracle server processes from network clients with specified IP addresses. To use this feature, set the following protocol.ora (Oracle Net configuration file) parameters:

tcp.validnode_checking = YES
tcp.excluded_nodes = {list of IP addresses}
tcp.invited_nodes = {list of IP addresses}

The first parameter turns on the feature whereas the latter parameters respectively deny and allow specific client IP addresses from making connections to the Oracle listener (This helps in preventing potential Denial of Service attacks).

f) Encrypt network traffic

If possible, use Oracle Advanced Security to encrypt network traffic between clients, databases, and application servers.

g) Harden the operating system

Harden the host operating system by disabling all unnecessary operating system services. Both UNIX and Windows platforms provide a variety of operating system services, most of which are not necessary for most deployments. Such services include FTP, TFTP, TELNET, and so forth. Be sure to close both the UDP and TCP ports for each service that is being disabled. Disabling one type of port and not the other does not make the operating system more secure.

9. Apply all security patches

Always apply all relevant and current security patches for both the operating system on which Oracle Database resides and Oracle Database itself, and for all installed Oracle Database options and components.

10. Contact Oracle Security products if you come across a vulnerability in Oracle Database

Source: Based on Oracle Documentaion