Friday, May 30, 2014

Implementing Class of Secure Transport (COST)

To safeguard against security vulnerability (CVE-2012-1675) as published here, COST (Class of Secure Transport) parameter is used to restrict any remote instance to get registered with the listener running on a database server. This is done by adding a parameter SECURE_REGISTER_<LISTENER_NAME> = (TCP) in the listener.ora file.

In the following demonstration, a listener is running on a Linux server where container/pluggable databases and services are registered with the listener - as can be seen in the following screenshot.























Now I have a database (salman11) running on a remote windows based machine where setting remote_listener parameter would make salman11 database to register with this listener

Adding remote_listener parameter to salman11


Checking listener where salman11 has got itself registered























Now implementing COST by adding SECURE_REGISTER_LISTENER = (TCP)

############################

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = salman1.salman.com)(PORT = 1521))
    )
  )

SECURE_REGISTER_LISTENER=(TCP)

############################

Add SECURE_REGISTER_LISTENER = (TCP) and reload/restart the listener. After doing this, salman11 database is no longer be seen registered with this listener.


Sunday, May 18, 2014

RMAN Backup Performance

Finishing an RMAN backup on time (fast) is usually what every DBA looks for. Here I would be discussing my experience of RMAN backup scenarios which I tested for speed and performance. Environment details are as follows.

  • Backups were tested for a 3 nodes RAC (10.2.0.4) database on Windows 2003. 
  • Database had more than 250 datafiles (files sizes ranging from 5G to 10G) with total datafiles size around 3.4 Terabytes and segments size ( SUM(bytes) FROM dba_segments) around 3.1 Terabytes). 
  • Backups were taken on NFS mounted NAS disk.
  • Time was recorded for 250G (input size) data backup and then calculated the time for whole 3.1T data.
Backups script (slight modifications were done to the script for each scenario)
################################################################

connect target /;
allocate channel for maintenance type disk ;
delete noprompt force obsolete ;
run
{
allocate channel disk10 type disk connect /@instance1;
allocate channel disk20 type disk connect /@instance2 ;
allocate channel disk30 type disk connect /@instance3;

backup as compressed backupset format '\\<IP_Address>\backup_folder\ora_%U.rbf' incremental level 0 database filesperset 1;
sql "alter system archive log current";
sql "alter system switch logfile";

backup format '\\<IP_Address>\backup_folder\arc_%U.rbf' archivelog all  delete input;
backup current controlfile format '\\<IP_Address>\backup_folder\ctl_%U.rbf';
}
allocate channel for maintenance type disk ;
delete noprompt force obsolete ;
delete noprompt archivelog all backed up 1 times to disk;

########################################################################

Some Points Worth discussing (from Oracle Documentation)
  1. Parallelism has no or minimal benefit of multiple channels if writing to a single disk/tape (See test cases of local disks backup bellow)
  2. If you have data striped across several disks, you don’t need to multiplex at all and hence can set MAXOPENFILES to 1.
  3. FILESPERSET defaults to 64 or number of datafiles divided by number of channels - whichever is lower.
  4. MAXOPENFILES defaults to 8 which means open 8 files at a time and multiplex them. Setting it to 1 means no multiplexing and write all files in sequence in a single backup set.
  5. Multiplexing is determined by lesser of FILESPERSET and MAXOPENFILES values.
  6. MAXSETSIZE and MAXPIECESIZE are used to restrict the size of backups if there is limitation on disk/tape sizes. 
  7. The FILESPERSET parameter determines how many datafiles should be included in each backup set, while MAXOPENFILES defines how many datafiles RMAN can read from simultaneously and multiplex them together.
Assume that you are backing up six datafiles with one RMAN channel. If FILESPERSET is 6 and MAXOPENFILES is 1, then the channel includes 6 datafiles in a set but does not multiplex the files because RMAN is not reading from more than one file simultaneously. The channel reads one file at a time and writes to the backup piece. In this case, the degree of multiplexing is 1.

Now, assume that FILESPERSET is 6 and MAXOPENFILES is 3. In this case, the channel can read and write in the following order:
Read from datafiles 1, 2, and 3 simultaneously and write to the backup piece 
Read from datafiles 4, 5 and 6 simultaneously and write to the backup piece 

Note that multiplexing too many files can decrease restore performance. If possible, group files that will be restored together into the same backup set. Assume that RMAN backs up seventeen files with FILESPERSET = 64 and MAXOPENFILES = 16 and then you decide to restore data17.dbf which is datafile 17, then RMAN will read the multiplexed data for the first sixteen files and then starts reading the data for data17.dbf. In this case, moving to the beginning of the  backup of data17.dbf may take more time than the restore itself.

Test Cases (Backup Time is in HH:MI:SS Format)
Each RAC node has NFS Shared disk mounted on it.
  1. 3 channels (1 channel per instance) with filesperset 1 and without maxopenfiles (maxopenfiles would be automatically 1 in this case)
    250G took 00:50:00 to complete, expected time for 3.1T is approximately 10:32:00
  2. 6 channels (2 channels per instance) with filesperset 1 and without maxopenfiles (maxopenfiles would be automatically 1 in this case)
    250G took 00:33:00 to complete, expected time for 3.1T is approximately 06:40:00
  3. 9 channels (3 channels per instance) with filesperset 1 and without maxopenfiles (maxopenfiles would be automatically 1 in this case)
    250G took 00:25:40 to complete, expected time for 3.1T is approximately 05:25:00
  4. 12 channels (4 channels per instance) with filesperset 1 and without maxopenfiles (maxopenfiles would be automatically 1 in this case)
    250G took 00:18:00 to complete, expected time for 3.1T is approximately 03:35:00
  5. 15 channels (with 3 instances, 5 channels per instance) with filesperset 1 and without maxopenfiles (maxopenfiles would be automatically 1 in this case)
    250G took 00:16:40 to complete, expected time for 3.1T is approximately 03:30:00
Conclusions
  1. On a single physical server (whether RAC node or single server), if multiple channels are opened to write to a single backup destination disk, there is no significant improvement in backup finish time.
  2. On a single physical server, if backup is being taken on NFS mount, you can get better performance by increasing number of channels - because NFS is usually slow because data has to travel through the network to be written on NFS mount, and having more channels would make RMAN fully utilize storage speed.
    As a personal experience, to take a compressed full backup of a database of 1.3 Terabyte on NFS, backups completed in 10 hours with 4 channels, and completed in 2.5 hours when I used 16 channels (There were 24 CPUs in the server)
  3. On a NFS mounted shared disk (across all RAC nodes), as the number of channels increase (across all RAC nodes), backup time reduces. Reasons is same as mentioned in above point.
  4. For single server or RAC, taking backup on tapes would also - performance can be achieved by having multiple channels for writing in parallel to multiple tape drives.
Point to Note
While specifying multiple channels, you should always keep in mind the number of CPUs you have on your server; because opening several channels may bottleneck your CPUs (by increasing load average or run queue length). Analyze current CPU utilization and allocate channels accordingly. 


Tuesday, May 13, 2014

Converting a Failed Over Standby Database Back to Standby Database

I have seen some articles on the internet for reinstating a primary database after a failover (using flashback database technology – if FLASHBACK RETENTION time has not exceeded), but I was not able to find a document to revert or reinstate a “failed-over” standby database (which has now become primary) back to a standby database (using FLASHBACK technology). 

Some time back I had a scenario where I needed a failover of standby database for testing purposes, and after failover; I needed to rebuild the standby again. I did not want to rebuild the standby because of huge size of the database; as it would have required a very long time for me to rebuild it - then I came up with this idea and tried it, which worked. Detailed steps to perform this are bellow.

Following exercise was performed on Oracle 10.2.0.4 running on Windows 2008. Same steps should also work on any other platform and other Oracle versions greater than 10.2.0.4. For Standby RAC, this should be performed from one node while keep other instance(s) shutdown.


Before Failover

On Primary
Disable log shipping from primary by setting log_archive_dest_state_n to "defer" (we need to make sure that archives remain available on the disk so that these could be sent to standby later, when required)
SQL> ALTER SYSTEM SET log_archive_dest_2=’defer’;

On Standby

Perform following tasks on standby database before performing a failover.
Set up flash recovery area (if not set up already).
SQL> alter system set db_recovery_file_dest_size=40g;
SQL> alter system set db_recovery_file_dest='d:\test\fra\';

For RAC, FRA should be on a shared location - for example on an ASM diskgroup accessible to all instances.
SQL> alter system set db_recovery_file_dest='+data';


Enable standby flashback with flashback retention to a sufficient amount of time based on how long it will take to do the testing on this database after the failover, and before reverting it back to standby.
SQL> shutdown immediate
SQL> startup mount
SQL> alter system set  DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days (in minutes)
SQL> alter database flashback on;

Create a restore point.
SQL> create restore point before_failover;

Perform the Failover
SQL> alter database activate standby database;
SQL> shutdown immediate
SQL> startup

Now we can do the testing on this this failed over stabndby database

Reinstating the opened database back to Standby Role.
After we are done with testing, we can now revert back this failedover standby database, back to standby database using following commands
Flashback database back to Restore Point
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> FLASHBACK DATABASE TO RESTORE POINT before_failover;

Convert database back to Standby
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Enable log shipping from Primary
SQL> ALTER SYSTEM SET log_archive_dest_state_2 = ‘enable’

Related Article