Running RMAN backups from crontab

As any other Unix/Linux dude or DBA you probably want to automate all tasks that can be automated, and backup is surely one thing that falls into this category. Like most people I use RMAN for backups, and to automate RMAN you should do some script work.

The first script performs a complete backup

[oracle@oradb01 ~]$ cat rman_backup_full.sh

connect target /
backup incremental level 0 cumulative device type disk tag 'FULL' database; 
backup device type disk tag 'FULL_ARCH' archivelog all not backed up;
run {
allocate channel oem_backup_disk1 type disk  maxpiecesize 1000 G;
backup tag 'FULL_CNTRL' current controlfile;
release channel oem_backup_disk1;
}
allocate channel for maintenance type disk;
delete noprompt obsolete device type disk;
release channel;
exit;

The second script performs an incremental  backup.

[oracle@oradb01 ~]$ cat rman_backup_incr.sh

connect target /

backup incremental level 1 cumulative device type disk tag ‘INCR’ database;
backup device type disk tag ‘INCR_ARCH’ archivelog FROM TIME ‘SYSDATE-7’;
run {
allocate channel oem_backup_disk1 type disk  maxpiecesize 1000 G;
backup tag ‘INCR_CNTRL’ current controlfile;
release channel oem_backup_disk1;
}
allocate channel for maintenance type disk;
delete noprompt obsolete device type disk;
release channel;

exit;

Here is the runner scripts for backup scripts that i created above.

[oracle@primarydb ~]$ less run_rman_backup_full.sh 

#!/bin/sh

RMANDATE=`date +%d_%m_%Y`
WHEN=`date +%d_%m_%Y-%H:%M:%S`
RMANLOGS=»/u01/flash_recovery_area/PRIDB/rmanlogs»
ORACLE_BIN=»/u01/oracle/app/oracle/product/11.2.0/db_1/bin»
ORACLE_SID=»DB»

echo «Backup Completed.. » $WHEN

. ~/.bash_profile

$ORACLE_BIN/rman cmdfile /home/oracle/rman_backup_full.sh log $RMANLOGS/rman_backup_full_$RMANDATE.log
WHEN=`date +%d_%m_%Y-%H:%M:%S`

echo «RMAN Backup Completed.. » $WHEN

[oracle@primarydb ~]$

[oracle@primarydb ~]$ less run_rman_backup_incr.sh 

#!/bin/sh

RMANDATE=`date +%d_%m_%Y`
WHEN=`date +%d_%m_%Y-%H:%M:%S`
RMANLOGS=»/u01/flash_recovery_area/PRIDB/rmanlogs»
ORACLE_BIN=»/u01/oracle/app/oracle/product/11.2.0/db_1/bin»
ORACLE_SID=»DB»

echo «Backup Completed.. » $WHEN

. ~/.bash_profile

$ORACLE_BIN/rman cmdfile /home/oracle/rman_backup_incr.sh log $RMANLOGS/rman_backup_incr_$RMANDATE.log
WHEN=`date +%d_%m_%Y-%H:%M:%S`

echo «RMAN Backup Completed.. » $WHEN

Now, my scripts are ready to run. But first i need to check log file destination in script and then they are ready to add crontab.

We can add scripts to crontab by

[oracle@oradb01 ~]$ crontab -e [Make sure you do this as the Oracle-user, not root!]

00 20 * * * /bin/sh  /home/oracle/Scripts/run_rman_backup_full.sh > /dev/null
00 50 * * * /bin/sh  /home/oracle/Scripts/run_rman_backup_incr.sh > /dev/null

You can check your new crontab by

[oracle@oradb01 ~]$ crontab -l

00 20 * * * /bin/sh  /home/oracle/Scripts/run_rman_backup_full.sh > /dev/null
00 50 * * * /bin/sh  /home/oracle/Scripts/run_rman_backup_incr.sh > /dev/null

These are just examples, and you might want to use a different schedule to optimize time schedule or to adjust the scripts in terms of what you want RMAN to do for you.

 

Oracle Active Data Guard setup

This article is just for my own reference – it’s been mostly copied from http://oracle-base.com/articles/11g/data-guard-setup-11gr2.php as a backup, in case the article will be removed. It has also been adjusted slightly to be more in line with own preference and methods.

Data Guard Physical Standby Setup in Oracle Database 11g Release 2

Data Guard is the name for Oracle’s standby database solution, used for disaster recovery and high availability. To achieve more or less real time failover I’ve investigaed the options of Active Data Guard (ADG). The setup/configuration can be done in a manual fashion, or more automated with the «duplication method»

Prereqs

In order to use Oracle Active Data Guard you need two servers, and in my test scenario I’ve used two VMs with Oracle Linux 6.6 and Oracle Database 11.2.0.2. I’d also recommend the article and methods described here http://www.oracle.com/technetwork/articles/servers-storage-admin/ginnydbinstallonlinux-488779.html to simplify the process of installing required packages and setting the correct kernel parameters.

The primary node has a running instance of the database (testdb is the name), the standby server has Oracle software only

 Setup Primary server

For this section, Oracle 11G R2 have been installed, and has a running instance (TESTDB is my database and instance). For ADG to work you need RMAN, thus the primary database must be in archivelog mode.

Check that the primary database is in archivelog mode.

SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL>

By default the database is in a noarchivelog mode, so you’ll have to switch to archivelog mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

If you for some reason want forced logging enabled, issue the following command.

ALTER DATABASE FORCE LOGGING;

Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to «TESTDB» on the primary database.

SQL> show parameter db_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_name 			     string	 TESTDB

SQL> show parameter db_unique_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 TESTDB

SQL>

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of theLOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value «TESTDB_STBY».

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTDB,TESTDB_STBY)';

Set suitable remote archive log destinations. In this case I’m using the flash recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=testdb_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and path differences between the servers.

ALTER SYSTEM SET FAL_SERVER=TESTDB_STBY;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='TESTDB_STBY','TESTDB' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='TESTDB_STBY','TESTDB'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.

Service Setup

Entries for the primary and standby databases are needed in the «$ORACLE_HOME/network/admin/tnsnames.ora» files on both servers. You can create these using the Network Configuration Utility (netca) or do it manually. The following entries were used during this setup.

TESTDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TESTDB.WORLD)
    )
  )

TESTDB_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TESTDB.WORLD)
    )
  )

Backup Primary Database

If you are planning to use an active duplicate to create the standby database, then this step is unnecessary. For a backup-based duplicate, or a manual restore, take a backup of the primary database.

$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Create Standby Controlfile and PFILE

Create a controlfile for the standby database by issuing the following command on the primary database. I prefer to have the temporary files stored in case I need them, thus I create a directory on my first datadisk (/d01/tmp).

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/d01/tmp/testdb_stby.ctl';

Create a parameter file for the standby database.

CREATE PFILE='/d01/tmp/initTESTDB_stby.ora' FROM SPFILE;

Amend the PFILE making the entries relevant for the standby database. I’m making a replica of the original server, so in my case I only had to amend the following parameters.

*.db_unique_name='TESTDB_STBY'
*.fal_server='TESTDB'
*.log_archive_dest_2='SERVICE=testdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB'

Standby Server Setup (Manual)

Copy Files

Create the necessary directories on the standby server.

$ mkdir -p /d01/app/oracle/oradata/TESTDB
$ mkdir -p /d01/app/oracle/flashrecovery_area/TESTDB
$ mkdir -p /d01/app/oracle/admin/TESTDB/adump

Copy the files from the primary to the standby server.

$ # Standby controlfile to all locations.
$ scp oracle@ora01:/d01/tmp/testdb_stby.ctl /d01/app/oracle/oradata/TESTDB/control01.ctl
$ cp /d01/app/oracle/oradata/TESTDB/control01.ctl /u01/app/oracle/flash_recovery_area/TESTDB/control02.ctl

$ # Archivelogs and backups
$ scp -r oracle@ora01:/d01/app/oracle/flash_recovery_area/TESTDB/archivelog /d01/app/oracle/flash_recovery_area/TESTDB
$ scp -r oracle@ora01:/d01/app/oracle/flash_recovery_area/TESTDB/backupset /d01/app/oracle/flash_recovery_area/TESTDB

$ # Parameter file.
$ scp oracle@ora01:/d01/tmp/initTESTDB_stby.ora /d01/tmp/initTESTDB_stby.ora

$ # Remote login password file.
$ scp oracle@ora01:$ORACLE_HOME/dbs/orapwTESTDB $ORACLE_HOME/dbs

Notice, the backups were copied across to the standby server as part of the FRA copy. If your backups are not held within the FRA, you must make sure you copy them to the standby server and make them available from the same path as used on the primary server.

Start Listener

Make sure the listener is started on the standby server.

$ lsnrctl start

Restore Backup

Create the SPFILE form the amended PFILE.

$ export ORACLE_SID=TESTDB
$ sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE='/d01/tmp/initTESTDB_stby.ora';

Restore the backup files.

$ export ORACLE_SID=TESTDB
$ rman target=/

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

Create Redo Logs

Create online redo logs for the standby. It’s a good idea to match the configuration of the primary server.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/d01/app/oracle/oradata/TESTDB/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/d01/app/oracle/oradata/TESTDB/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/d01/app/oracle/oradata/TESTDB/online_redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following is standby redo logs must be created on both servers.

ALTER DATABASE ADD STANDBY LOGFILE ('/d01/app/oracle/oradata/TESTDB/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/d01/app/oracle/oradata/TESTDB/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/d01/app/oracle/oradata/TESTDB/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/d01/app/oracle/oradata/TESTDB/standby_redo04.log') SIZE 50M;

Once this is complete, we can start the apply process.

Standby Server Setup (DUPLICATE)

Copy Files

Create the necessary directories on the standby server.

$ mkdir -p /d01/app/oracle/oradata/TESTDB
$ mkdir -p /d01/app/oracle/flash_recovery_area/TESTDB
$ mkdir -p /d01/app/oracle/admin/TESTDB/adump

Copy the files from the primary to the standby server.

$ # Standby controlfile to all locations.
$ scp oracle@ora01:/d01/tmp/testdb_stby.ctl /d01/app/oracle/oradata/TESTDB/control01.ctl
$ cp /d01/app/oracle/oradata/TESTDB/control01.ctl /d01/app/oracle/flash_recovery_area/TESTDB/control02.ctl

$ # Parameter file.
$ scp oracle@ora01:/d01/tmp/initTESTDB_stby.ora /d01/tmp/initTESTDB_stby.ora

$ # Remote login password file.
$ scp oracle@ora01:$ORACLE_HOME/dbs/orapwTESTDB $ORACLE_HOME/dbs

Start Listener

When using active duplicate, the standby server requires static listener configuration in a «listener.ora» file. In this case I used the following configuration.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB.WORLD)
      (ORACLE_HOME = /d01/app/oracle/product/12.0.1/db_1)
      (SID_NAME = TESTDB)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora02.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /d01/app/oracle

Make sure the listener is started on the standby server.

$ lsnrctl start

Create Standby Redo Logs on Primary Server

The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.

ALTER DATABASE ADD STANDBY LOGFILE ('/d01/app/oracle/oradata/TESTDB/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/d01/app/oracle/oradata/TESTDB/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/d01/app/oracle/oradata/TESTDB/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/d01/app/oracle/oradata/TESTDB/standby_redo04.log') SIZE 50M;

Create Standby Using DUPLICATE

Start the auxillary instance on the standby server by starting it using the temporary «init.ora» file.

$ export ORACLE_SID=TESTDB
$ sqlplus / as sysdba

SQL> STARTUP NOMOUNT PFILE='/d01/tmp/initTESTDB_stby.ora';

Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.

$ rman TARGET sys/password@TESTDB AUXILIARY sys/password@TESTDB_STBY

Now issue the following DUPLICATE command.

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='TESTDB_STBY' COMMENT 'Is standby'
    SET LOG_ARCHIVE_DEST_2='SERVICE=testdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB'
    SET FAL_SERVER='TESTDB' COMMENT 'Is primary'
  NOFILENAMECHECK;

A brief explanation of the individual clauses is shown below.

  • FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
  • FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
  • DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
  • SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
  • NOFILENAMECHECK: Destination file locations are not checked.

Once the command is complete, we can start the apply process.

Start Apply Process

Start the apply process on standby server.

# Foreground redo apply. Session never returns until cancel. 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

# Background redo apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you need to cancel the apply process, issue the following command.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

Provided you have configured standby redo logs, you can start real-time apply using the following command.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

Test Log Transport

On the primary server, check the latest archived redo log and force a log switch.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

Check the new archived redo log has arrived at the standby server and been applied.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

Protection Mode

There are three protection modes for the primary database:

  • Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
  • Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
  • Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

By default, for a newly created standby database, the primary database is in maximum performance mode.

SELECT protection_mode FROM v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL>

The mode can be switched using the following commands. Note the alterations in the redo transport attributes.

-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=testdb_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=testdb_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=testdb_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the original standby database issue the following commands.

-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP;

Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.

Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

Since the standby database is now the primary database it should be backed up immediately.

The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily (shown here). If not, the whole setup process must be followed, but this time using the original primary server as the standby.

Flashback Database

It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.

An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database. That process is shown here.

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

To switch the standby database into read-only mode, do the following.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

To resume managed recovery, do the following.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.

Snapshot Standby

Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.

If you are using RAC, turn off all but one of the RAC instances. Make sure the instance is in MOUNT mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

Make sure managed recovery is disabled.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Convert the standby to a snapshot standby. The following example queries the V$DATABASE view to show that flashback database is not enabled prior to the conversion operation.

SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL>

You can now do treat the standby like any read-write database.

To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

SQL>

The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.

Installing and configuring Oracle 11.2 on Centos 6.6

Required RPMs

When installing Oracle 11G R2 on CentOS 6.6 you will find a dozen install guides with prereq’s and what not. Below are the information from Oracle (https://docs.oracle.com/cd/E11882_01/install.112/e24326/toc.htm#BHCGJCEA)

binutils-2.20.51.0.2-5.11.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
compat-libstdc++-33-3.2.3-69.el6.i686
gcc-4.4.4-13.el6 (x86_64)
gcc-c++-4.4.4-13.el6 (x86_64)
glibc-2.12-1.7.el6 (i686)
glibc-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6.i686
ksh
libgcc-4.4.4-13.el6 (i686)
libgcc-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6.i686
libstdc++-devel-4.4.4-13.el6 (x86_64)
libstdc++-devel-4.4.4-13.el6.i686
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6.i686
libaio-devel-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6.i686
make-3.81-19.el6
sysstat-9.0.4-11.el6 (x86_64)

As of November 20th 2014, CentOS 6.6 have newer versions, and they can all be installed with the following command

# sudo yum install binutils compat-libcap1 compat-libstdc++ gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel make sysstat

However, some of these packages also have to be installed with 32-bit editions as well as the x64-editions. I did not verify that I needed all of them but my working system has these 32-bit packages installed

glibc-2.12-1.149.el6.i686
ksh-20120801-21.el6.1.i686
compat-libstdc++-296-2.96-144.el6.i686
nss-softokn-freebl-3.14.3-17.el6.i686
libgcc-4.4.7-11.el6.i686
glibc-devel-2.12-1.149.el6.i686

If you have newer versions of the x64-packages, you just substitute the x86_64 with i686. Eg with libgcc you might have libgcc-4.4.7-11.el6.x86_64 installed, to get the 32-bit edition do

# sudo yum install libgcc-4.4.7-11.el6.i686

to get the corresponding 32-bit package.

System parameters

Oracle 11G R2 require a few other parameter changes as well. Here are the relevant things you should change

Edit /etc/security/limits.conf and add the following at the bottom

oracle soft nproc 2047
oracle hard nofile 65536

Edit /etc/security/limits.d/90-nproc.conf and make it look like this

* soft nproc 1024
root soft nproc unlimited
oracle soft nproc 2047
oracle hard nproc 16384

You will surely see a note about missing pdksh-5.2.14 as well, even with all the prereq’s installed

Edit /»your_source»/database/stage/cvu/cv/admin/cvu_config and change the following

# Fallback to this distribution id
CV_ASSUME_DISTID=OEL4

to

# Fallback to this distribution id
CV_ASSUME_DISTID=OEL6

Change your /etc/sysctl.conf to this

# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.
# Controls IP packet forwarding
net.ipv4.ip_forward = 0
# Controls source route verification
net.ipv4.conf.default.rp_filter = 1
# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0
# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0
# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1
# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1
# Disable netfilter on bridges.
#net.bridge.bridge-nf-call-ip6tables = 0
#net.bridge.bridge-nf-call-iptables = 0
#net.bridge.bridge-nf-call-arptables = 0
# Controls the default maxmimum size of a mesage queue
kernel.msgmnb = 65536
# Controls the maximum size of a message, in bytes
kernel.msgmax = 65536
# Controls the maximum shared segment size, in bytes
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
# oracle-rdbms-server-11gR2-preinstall setting for fs.file-max is 6815744
fs.file-max = 6815744
# oracle-rdbms-server-11gR2-preinstall setting for kernel.sem is '250 32000 100 128'
kernel.sem = 250 32000 100 128
# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmni is 4096
kernel.shmmni = 4096
# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 1073741824 on x86_64
# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 2097152 on i386
# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmax is 4398046511104 on x86_64
# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmmax is 4294967295 on i386
kernel.shmmax = 4398046511104
# oracle-rdbms-server-11gR2-preinstall setting for kernel.panic_on_oops is 1 per Orabug 19212317
kernel.panic_on_oops = 1
# oracle-rdbms-server-11gR2-preinstall setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144
# oracle-rdbms-server-11gR2-preinstall setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304
# oracle-rdbms-server-11gR2-preinstall setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144
# oracle-rdbms-server-11gR2-preinstall setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576
# oracle-rdbms-server-11gR2-preinstall setting for fs.aio-max-nr is 1048576
fs.aio-max-nr = 1048576
# oracle-rdbms-server-11gR2-preinstall setting for net.ipv4.ip_local_port_range is 9000 65500
net.ipv4.ip_local_port_range = 9000 65500

A simple .bash_profile for the oracle-user might look like this

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
 . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=oratest01.my.domain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=TEST; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=TEST; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

If you’re on DHCP network, make sure you include your hostname in the /etc/hosts file, eg

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.1 oratest01.my.domain oratest01

To get RMAN and EMM working you also need to get the tnsnames and listener up and running

My simple config looks like this

listener.ora
# listener.ora Network Configuration File: /d01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
 (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(HOST=oratest01.my.domain)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 )
)
SID_LIST_LISTENER=
 (SID_LIST=
 (SID_DESC=
 (GLOBAL_DBNAME=TEST)
 (SID_NAME=TEST)
 (ORACLE_HOME=/u01/app/oracle/product/11.2/db_1/)
 )
 )
# SECURE_REGISTER_LISTENER = (IPC)
tnsnames.ora
# tnsnames.ora Network Configuration File: /d01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_TEST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oratest01.my.domain)(PORT = null))

TEST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oratest01.my.domain)(PORT = 1521))

 (CONNECT_DATA =
 (SERVER = SHARED)
 (SERVICE_NAME = TEST)
 )
 )

Probably more to come…

Change disk sector size from 520 bytes to 512 bytes

If you happen to have an old SAN which contains perfectly usable disks, you might experience that hooking these disks onto youe SAS HBA/Raid-controller won’t work out of the box. I recently pulled 11 disks from an old EMC AX-4 for reuse in a PowerEdge 720XD (10 disks for RAID-10 plus 1 hotspare and 1 SSD for CacheCade), and noticed that the disks had an unsupported formatting. I forgot to screenshot the info from my disks, so I snipped the info from http://pissedoffadmins.com/general/unsupported-sector-size-520.html

From dmesg

[ 86.717949] Vendor: IBM Model: IC35L146 CLAR146 Rev: R58A 
[ 86.717970] Type: Direct-Access ANSI SCSI revision: 03 
[ 86.720959] sdb : unsupported sector size 520. 
[ 86.720966] SCSI device sdb: 0 512-byte hdwr sectors (0 MB) 
[ 86.722822] sdb: Write Protect is off 
[ 86.722828] sdb: Mode Sense: e3 00 00 08 
[ 86.725797] SCSI device sdb: drive cache: write through 
[ 86.725908] sd 0:0:1:0: Attached scsi disk sdb 
[ 86.726103] sd 0:0:1:0: Attached scsi generic sg1 type 0

As can be seen – these disks had an unsupported sector size (520 vs the regular 512). In order to get the disks working I installed the sg3_utils on my CentOS machine.

Perform a scan and see the disks available for a sector size change

# sg_scan -i
/dev/sdb: scsi channel=0 id=2 lun=0
Vendor: IBM Model: IC35L146 CLAR146 [rmb=0 cmdq=1 pqual=0 pdev=0x0]

As said – my output wasn’t exactly like this, but you get the idea… Then perform the sector size change

# sg_format –format –size=512 /dev/sdb
Vendor: IBM Model: IC35L146 CLAR146 peripheral_type: disk [0x0]
Mode Sense (block descriptor) data, prior to changes:
Number of blocks=573653847 [0x22314357]
Block size=520 [0x208]
A FORMAT will commence in 10 seconds
ALL data on /dev/sg8 will be DESTROYED
Press control-C to abort

I had Seagate 600GB 15k disks, and I believe the formatting was close to 60 minutes per disk. I had 11 of these, and to speed things up I just did’em all in parallell😉 The PERC complains about unsupported disks, but I imagine these disks will work just fine in our dev-environment.

VMware and PowerCLI

Sooner or later you may have to do some «serious work» outside of the vCenter GUI or vCenter client. PowerCLI on a Windows machine is the obvious choice here. I’m in the middle of upgrading an ESXi 5.0 cluster with a non-SSO based vCenter to a new 5.5 cluster with a SSO-capable vCenter 5.5 server. Downtime is critical, and migration from one cluster to a new when using dvSwitches does not work without some tinkering.

Doing some reading I’ve found that one way to do this is to create old school vSwitches with all the needed VLAN’s and change the Network on all VMs from dvSwitches to vSwitches. Testing show a packet loss of 1-2 packets, which is acceptable. The main issue here is creating the new vSwitches. No biggie if you have a SOHO lab, but with 12 servers and 40 VLANs we’re talking click-click-click if you’re using a GUI.

First of all – get PowerCLI installed and configured

http://blogs.vmware.com/PowerCLI/2013/03/back-to-basics-connecting-to-vcenter-or-a-vsphere-host.html

http://searchvmware.techtarget.com/tip/Bulk-VMware-administration-Using-PowerCLI-with-standard-switches

Check the references for PowerCLI and Virtual Switches

https://www.vmware.com/support/developer/PowerCLI/PowerCLI41U1/html/New-VirtualSwitch.html

Then read these http://www.mikelaverick.com/2014/01/back-to-basics-configuring-standard-vswitch-with-powercli-part-three-of-three/ as well as http://www.gabesvirtualworld.com/migrating-distributed-vswitch-to-new-vcenter/ and you should be ready to go!

Another issue here is naturally if you use EVC in you cluster, and maybe stepped up a notch if the new cluster have more recent hardware. We’re in this situation, going from Dell 11G to 12G/13G servers, and we do have to reboot the machines anyhow. To get it somewhat smooth I intend to fill one of the ESXi 5.0 servers with as many VMs as common sense tells me to, remove it from the old vCenter and add it to the new SSO-enabled vCenter. The VMs on this particular server will be shut down and then migrated onto the new cluster, and probably upgraded with the newer hardware version/VMwareTools.

Oracle Solaris Virtualbox and Tools

If you want some VMs on your Solaris-machine, Virtualbox is the simple choice. Feels like a dumbed down version of Vmware workstation or Hyper-V on Windows 8, but it seems to do the job for quick testing. Virtualbox should also work fine on a wide variety of OS’es other than Solaris.

A useful tool if you don’t have easy access to the Solaris GUI you may want to try RemoteBox on your Windows-machine. Download from http://sourceforge.net/projects/remotebox/ and see the following for how to get it running on Windows.

http://sourceforge.net/p/remotebox/discussion/RemoteBox/thread/d9309282/?limit=50#374b/fe48

Windows Storage Spaces 2012 – some references

Just a few links and references that I’ve found helpful…