How to query RMAN backups from recovery catalog

If you use EM grid or db control for checking backups everyday, you don't have to query the recovery catalog database. However, grid control gives stupid errors from time to time and you have to manually query the backup status from the recovery catalog database. Here is the query which has results like EM backup reports view:

  SELECT db_name,
         status,
         input_type,
         start_time,
         end_time,
         output_device_type,
         input_bytes_display,
         output_bytes_display,
         time_taken_display
    FROM RC_RMAN_BACKUP_JOB_DETAILS
   WHERE start_time > SYSDATE - 7
ORDER BY NVL (end_time, '01-JAN-1900') DESC

another detailed view:





  SELECT b.name,
         a.handle,
         ROUND (bytes / 1024 / 1024) size_mb,
         tag,
         start_time,
         completion_time,
         elapsed_seconds,
         compressed,
         device_type
    FROM rc_backup_piece a, rc_database b
   WHERE a.db_id = b.dbid AND a.start_time > SYSDATE - 7
ORDER BY start_time DESC

How to restart from windows to linux / linux to windows on dual boot PCs

If you have a dual boot installation of Ubuntu and Windows 7 on the same PC like me, you need to switch windows for gaming or business purposes occasionally. Menu selection screen only shows few seconds and if you miss, you have to restart the PC again. I need a shortcut on both linux and windows desktop to switch OS after restart. 

You may want to use permanently default OS on grub and search "one time boot" options. Here I will explain a method for changing default OS by a small script on both windows and linux sides.

Please note that I'm using EXT2 file system on linux for this method to work. Last version 0.51 of ext2fsd software claims that they support writing to ext3/4 volumes too. However I didn't try it yet. If someone try this method with ext3 or 4 please notify me... 



On Windows side:

• Download and Install ext2fsd on windows.  http://www.ext2fsd.com/ 

This software mounts ext2 partitions and maps to windows. 

• Copy and edit the grub.cfg script that set the default OS as linux. 0 is Ubuntu, 5 is Windows on my PC's grub configuration. So I change the value to 0 to open the pc in linux OS. 

#

# DO NOT EDIT THIS FILE

#

# It is automatically generated by grub-mkconfig using templates

# from /etc/grub.d and settings from /etc/default/grub

#

### BEGIN /etc/grub.d/00_header ###

if [ -s $prefix/grubenv ]; then

  set have_grubenv=true

  load_env

fi

set default="0"

if [ "${prev_saved_entry}" ]; then

  set saved_entry="${prev_saved_entry}"

  save_env saved_entry

  set prev_saved_entry=

  save_env prev_saved_entry

  set boot_once=true

fi

function savedefault {

  if [ -z "${boot_once}" ]; then

    saved_entry="${chosen}"

    save_env saved_entry

  fi

}

• Create a batch script to copy and overwrite the grub.cfg file to linux partititon and restart windows.

batch script "restart to linux" is:


start /d "C:\Program Files\Ext2Fsd" Ext2Mgr.exe
timeout /t 10
copy d:\grub.cfg I:\boot\grub
shutdown /r /t 0

Meaning:

start /d "C:\Program Files\Ext2Fsd" Ext2Mgr.exe 

Here letter "I" is my drive that has grub config file "/boot/grub/grub.cfg".

timeout /t 10 


wait for 10 seconds to start the ext2fsd program and mount the drivers



 copy d:\grub.cfg I:\boot\grub

Copy the grub.cfg file which has default OS as linux



shutdown /r /t 0 


Reboot windows immediately.




On Linux side:

• We need to use sudo without password in the script file.  Run visudo and add your username to its config file.

# Cmnd alias specification

# User privilege specification
root    ALL=(ALL:ALL) ALL

# Members of the admin group may gain root privileges
%admin ALL=(ALL) ALL

# Allow members of group sudo to execute any command
%sudo   ALL=(ALL:ALL) ALL

#includedir /etc/sudoers.d
username  ALL=(ALL) NOPASSWD:ALL

• Copy and modify grub.cfg file that has default OS as windows. Here "5" is windows OS on my PC.

grub.cfg file must be like:

#
# DO NOT EDIT THIS FILE
#
# It is automatically generated by grub-mkconfig using templates
# from /etc/grub.d and settings from /etc/default/grub
#

### BEGIN /etc/grub.d/00_header ###
if [ -s $prefix/grubenv ]; then
  set have_grubenv=true
  load_env
fi
set default="5"
if [ "${prev_saved_entry}" ]; then
  set saved_entry="${prev_saved_entry}"
  save_env saved_entry
  set prev_saved_entry=
  save_env prev_saved_entry
  set boot_once=true
fi

• create a file on linux desktop, name it as "reboot_to_windows", give execute grants

# chomod 777 reboot_to_windows

• Add these lines to copy the modified grub.cfg file to /boot/grub directory and reboot linux

sudo cp -f /home/oturan/Desktop/grub.cfg /boot/grub

sudo reboot

Thats all! It worked for me, hope it works on your PC too. Bye.

Step by step oracle 10GR2 data guard installation with RMAN duplicate.

Notes: 

1. Virtualbox on Ubuntu 11.10 with 2 virtual machine  Oracle Linux 5 is used for Data Guard installation.
2. First installed Oracle Linux 5 on Virtualbox (hostname=oracle10g), installed requied rpms, installed oracle software and a database named ORCL. Then clone this virtual machine as hostname=oracle10gs
3. Primary hostname : oracle10g, Standby hostname: oracle10gs
4. Primary database UNIQUE NAME: ORCL, standby database UNIQUE NAME: ORCL_STBY
5. Primarydatabase Net Service Name: ORCL, standby database Net Service Name: ORCL_STBY
6. This is a phiysical standby database with Maximum Performance mode. In this mode: 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.

ON PRIMARY SIDE:

• Check for Primary database whether archivelog mode is on.

SQL> SELECT log_mode FROM v$database;

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

Put the database to archivelog mode:

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

• Enable force logging:

SQL> ALTER DATABASE FORCE LOGGING;

• Create password file if it is not exist:

$ orapwd file=$ORACLE_HOME/dbs/orapwORCL password=****** entries=30

• Add these parameters to primary instance ORCL init file:

DB_UNIQUE_NAME=ORCL 

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)'
LOG_ARCHIVE_DEST_2='SERVICE=ORCL_STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY' 

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 

LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'

LOG_ARCHIVE_MAX_PROCESSES=30

STANDBY_FILE_MANAGEMENT=AUTO

STANDBY_ARCHIVE_DEST='/u01/db_files/ARCH'

• Primary Database: Standby Role Initialization Parameters

These parameters are needed when there is a switchower between primary role and standby role. 

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

#These parameters are needed when switchover occurs.

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

#FAL_SERVER=ORCL_STBY

#FAL_CLIENT=ORCL

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

#If file name conversion is needed:

#DB_FILE_NAME_CONVERT='boston','chicago'

#LOG_FILE_NAME_CONVERT='/arch1/boston/','/arch1/chicago/'

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

#STANDBY_FILE_MANAGEMENT=AUTO

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

• Modify tnsnames.ora by adding standby instance and catalog database instance of RMAN:

ORCL_STBY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle10gs)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL)

    )

  )

CATALOG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle10g)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = CATALOG)

    )

  )

• Add standby logfiles: 

Number of standby logfile groups must be +1 of number of redo log file groups. In my case, number of redolog file groups:3. I need to create 4 standby redolog file groups.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oradata/standby05.dbf') SIZE 50M

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oradata/standby06.dbf') SIZE 50M

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/oradata/standby07.dbf') SIZE 50M

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u01/oradata/standby08.dbf') SIZE 50M

• Backup the database  using RMAN  with standby controlfile

connect to rman:

rman catalog rman/rman@CATALOG target /

Run the script:

run

{

allocate channel ch1 type disk format '/u01/backup/std_%U';

allocate channel ch2 type disk format '/u01/backup/std_%U';

BACKUP DATABASE INCLUDE CURRENT CONTROLFILE FOR STANDBY PLUS ARCHIVELOG;

}

• Copy or share these backup files under /u01/backup to standby instance

$ scp /u01/backup/* oracle@oracle10gs:/u01/backup

• Copy the init file to standby side

$ scp  $ORACLE_HOME/dbs/initORCL.ora oracle@oracle10gs:/u01

ON STANDBY SIDE:

• modify init parameter by adding these lines:

DB_NAME=ORCL

DB_UNIQUE_NAME=ORCL_STBY

FAL_SERVER=ORCL

FAL_CLIENT=ORCL_STBY

LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'

STANDBY_FILE_MANAGEMENT=AUTO

LOG_ARCHIVE_CONFIG='dg_config=(ORCL,ORCL_STBY)'

LOG_ARCHIVE_DEST_1='LOCATION=/u01/db_files/ARCH'

Log_archive_dest_state_1=ENABLE

• add tnsnames.ora entries:

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle10g)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL)

    )

  )

CATALOG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle10g)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = CATALOG)

    )

  )

• start the standby listener

lsnrctl start

• start the instance in nomount state:

set environments:

export ORACLE_HOME=/u01/app/oracle/product/10.2.0/realdb_1

export ORACLE_SID=ORCL

export PATH=$PATH:$ORACLE_HOME/bin

$ sqlplus / as sysdba

startup nomount pfile=?/dbs/initORCL.ora

• restore database

$ rman catalog rman/rman@CATALOG target sys/******@ORCL auxiliary /

 run

{

allocate  channel C1 device type disk;

allocate  channel C2 device type disk;

set until SCN 1391907;

RESTORE DATABASE;

RECOVER DATABASE;

}

• start apply process: 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you need to cancel the process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

If you need to open the standby process read only:

alter database open read only;

• Control scripts

Shows Data Guard log numbers:

Primary side:

select thread#, sequence# from v$log

Standby side

select thread#, sequence#, status from v$managed_standby;

Last archived log on standby

select max(sequence#) "Last Seq Recieved" from v$archived_log;

Shows archive processes status. If log_archive_dest_state_2 is VALID, it writes archive logs to standby side .

select dest_name,status,target,destination,db_unique_name from v$archive_dest;

Stop/start writing to standby side:

alter system set log_archive_dest_state_2=ENABLE

alter system set log_archive_dest_state_2=DEFER 

Dataguard messages:

select message from v$dataguard_status where dest_id = 2;