How to kill Oracle RMAN backup job

Related image

 

 

 

 

 

 

We can kill RMAN backup job with the help of 2 methods, as below:

Method I:
Alter system kill session:

First, get the SID and SERIAL# from below query:

SQL> select b.sid, b.serial#, a.spid, b.client_info
from v$process a, v$session b
where a.addr=b.paddr and client_info like 'rman%';
SID        SERIAL#    SPID         CLIENT_INFO
---------- ---------- ------------ ---------------------------------
592        12         865          rman channel=full_chanel

OR

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;
SID        SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
592        12         1          9115569    19258880   47.33

Use the following command to kill RMAN backup job:

SQL> alter system kill session '592,12' immediate;
system altered.

Backup job killed successfully, simultaneously you will get below mentioned error log in RMAN backup logs:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/01/2017 00:09:15
RMAN-10038: database session for channel full_chanel terminated unexpectedly

Method II:

Directly kill RMAN job from OS level with the help of “kill -9”

[oracle@PR ~]$ ps -ef | grep rman|grep -v grep
oracle 2348 3124 3 01:28 pts/1 00:00:00 rman target /
[oracle@PR ~]$ kill -9 2348

Image result for Graphic horizontal lines png

Steps to configure Oracle 11g

Data Guard Physical Standby – Active Data Guard Part-I

Here in this article I am going to cover steps by step approach to configure Oracle 11g Data Guard Physical Standby.

In my case, Ingredients to simulate Physical Standby data guard environment are as below:

  • 2 VM’s, Primary and DR with enough CPU and RAM in order to run oracle database.
  • Primary server configuration
    • CentOS 6.5
    • Server name: pr
    • IP: 192.168.17.131
    • Oracle 11g software plus oracle instance.
    • Oracle SID/Global_name: RTS
    • Oracle db_unique_name: RTS
  • Secondary server configuration
    • CentOS 6.5
    • Server name: dr
    • IP: 192.168.17.132
    • Oracle 11g software only.
    • Oracle SID/Global name: RTS
    • Oracle db_unique_name: RTSDR

Note:

  • Oracle version on Primary and secondary should be identical. i.e. In my case it’s 11.2.0.1.0
  • db_unique_name on standby and primary database should be different in order to used in DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.
  • Primary and DR server should ping each other by IP as well as its server name. In order to ping with its server name, edit /etc/hosts file accordingly.

 

Primary Server Configurations:

<Step – 1 >

Enable Archive log:
Primary database is in No Archive Mode, Enable it:

 SQL> archive log list
 Database log mode No Archive Mode
 Automatic archival Disabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 1
 Current log sequence 3
 SQL> SHUTDOWN IMMEDIATE;
 SQL> STARTUP MOUNT;
 SQL> ALTER DATABASE ARCHIVELOG;
 SQL> ALTER DATABASE OPEN;
 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 1
 Next log sequence to archive 3
 Current log sequence 3

<Step – 2 >

Enable force logging with the help of following SQL command:

SQL> ALTER DATABASE FORCE LOGGING;

<Step – 3 >

Verify initialization parameters db_name and db_unique_name on primary, In my case those are set to: RTS

 SQL> show parameter db_name
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_name string RTS
 SQL> show parameter db_unique_name
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_unique_name string RTS

<Step – 4 >

Issue following command to set LOG_ARCHIVE_CONFIG parameter for data guard config.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RTS,RTSDR)';

Ensure your above changes as below:

 SQL> show parameter LOG_ARCHIVE_CONFIG
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_config string DG_CONFIG=(RTS,RTSDR)

<Step – 5 >

Issue following to set LOG_ARCHIVE_DEST_2, In my case i have used flash recovery area for remote archive log destination.

Note: Service and DB_UNIQUE_NAME reference the remote standby location.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RTSDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTSDR';

Ensure your above changes as below:

SQL> show parameter LOG_ARCHIVE_DEST_2
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_dest_2 string SERVICE=rtsdr NOAFFIRM ASYNC V
 ALID_FOR=(ONLINE_LOGFILES,PRIM
 ARY_ROLE) DB_UNIQUE_NAME=rtsdr

Enable LOG_ARCHIVE_DEST_2:

SQL> alter system set log_archive_dest_state_2=enable;
SQL> show parameter log_archive_dest_state_2
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_dest_state_2 string ENABLE

<Step – 6 > 

Set log_archive_format parameter as below:

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> show parameter log_archive_format
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_format string %t_%s_%r.arc

Set log_archive_max_processes parameter to 30:

SQL> alter system set log_archive_max_processes=30;
SQL> show parameter log_archive_max_processes
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_max_processes integer 30

Set remote_login_passwordfile parameter to exclusive:

 SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
 SQL> show parameter remote_login_passwordfile
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 remote_login_passwordfile string EXCLUSIVE

<Step – 7 > 

Set fal_server and fal_client parameter for primary database:

SQL> alter system set fal_server=RTSDR;
SQL> show parameter fal_server
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 fal_server string RTSDR
SQL> alter system set fal_client='RTS';
 SQL> show parameter fal_client
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 fal_client string RTS

Set standby_file_management parameter to auto:

 SQL> alter system set standby_file_management=auto;
 SQL> show parameter standby_file_management
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 standby_file_management string AUTO

<Step – 8 > 

Configure listener file and copy it to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
Listener file:

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = RTS)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = RTS)
 ) )
 LISTENER =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
 )
 ADR_BASE_LISTENER = /u01/app/oracle

<Step – 9 > 

Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both the servers.

 RTS =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 (GLOBAL_NAME = RTS)
 (UR=A)
 ) )
 RTSDR =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 (GLOBAL_NAME = RTS)
 (UR=A)
 ) )

Ensure your above configuration by TNSPing utility on Primary and standby:

 [oracle@pr admin]$ tnsping RTS
 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:09
 Copyright (c) 1997, 2009, Oracle. All rights reserved.
 Used parameter files:
 /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = rts) (GLOBAL_NAME = RTS)))
 OK (10 msec)
 [oracle@pr admin]$ tnsping RTSDR
 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:15
 Copyright (c) 1997, 2009, Oracle. All rights reserved.
 Used parameter files:
 /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS)))
 OK (20 msec)

<Step – 10 > 

Backup primary database via RMAN backup utility:

 [oracle@pr admin]$ rman target /
 Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 4 02:02:11 2014
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
 connected to target database: RTS (DBID=1421312347)
 RMAN> backup database plus archivelog;

Note: Backup location: Flash Recovery Area.

<Step – 11> 

Create Standby control file.

SQL> alter database create standby controlfile as '/u01/bkup/stndbyctrl.ctl';

<Step – 12>

Create pfile from spfile:

SQL> create pfile='/u01/bkup/initRTS.ora' from spfile;

<Step – 13>

After creating parameter file as above, edit following changes in newly created pfile:

 *.db_unique_name='RTSDR'
 *.fal_server='RTS';
 *.log_archive_dest_2='SERVICE=RTS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTS'

<Step – 14>

Copy parameter file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 [oracle@pr bkup]$ scp initRTS.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Copy standby control file to DR @ location: /u01/app/oracle/oradata/RTS/control01.ctl & /u01/app/oracle/flash_recovery_area/RTS/control02.ctl

 [oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/oradata/RTS/control01.ctl
 [oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/RTS/control02.ctl

Copy password file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 [oracle@pr ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
 [oracle@pr dbs]$ scp orapwRTS oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Copy Listener file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin

 [oracle@pr bkup]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
 [oracle@pr admin]$ scp listener.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

Edit DR database listener file with host name. ( i.e. (HOST = dr))

And finally copy RMAN backup to DR @ flash recovery area.

 [oracle@pr ~]$ cd /u01/app/oracle/flash_recovery_area/
 [oracle@pr flash_recovery_area]$ scp -r RTS oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/

Standby/DR Server Configurations:

<Step – 15>

Startup standby database in mount state:
Set following environment variable as oracle user OR edit those in /home/oracle/.bash_profile in order to set it for every time while oracle user logged in:

 export ORACLE_SID=RTS
 export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
 export PATH=$PATH:/u01/app/oracle/product/11.2.0/dbhome_1/bin
 [oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump

 SQL> sqlplus / as sysdba
 SQL> startup mount
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 566231800 bytes
 Database Buffers 398458880 bytes
 Redo Buffers 5988352 bytes
 Database mounted.

<Step – 16>

Create spfile from pfile:

 SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initRTS.ora';

<Step – 17>

Start listener on standby:

[oracle@dr ~]$ lsnrctl start

<Step – 18>

Create follwoing directories on DR server, in case those are not available:
/u01/app/oracle/admin/RTS/adump
/u01/app/oracle/flash_recovery_area

<Step – 19>

Restore and recover database @ DR with RMAN backup utility.

RMAN> list backup of database summary;
 List of Backups
 ===============
 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
 ------- -- -- - ----------- --------------- ------- ------- ---------- ---
 6 B F A DISK 05-OCT-14 1 1 NO TAG20141005T065604
RMAN> restore database;
RMAN> recover database;

Note: Recovery of database would be failed with RMAN-06054 error, We can ignore it because RMAN will ask for unknown archive log ( i.e. next archive log sequence, i.e. 10 ) who is not also available on Primary database.

Error log: RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1009554

<Step – 20>

Create standby redolog file to Primary and DR for the user of switch over, It should be match the configuration of the primary server.
Note: Create one additional standby redolog file on both.

 SQL> sqlplus / as sysdba
 SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=spfile;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby1.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby2.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby3.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby4.log') size 51M;
 SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile;

<Step – 21>

Start apply process @ DR.

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;

In case of you want to cancel apply process, issue following command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

<Step – 22>

After graceful completion of above apply process, verify archive logs on Primary as well as DR.
On Primary: 

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 8
 Next log sequence to archive 10
 Current log sequence 10

On Standby:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 8
 Next log sequence to archive 0
 Current log sequence 10

By above result, archive logs on both the databases are in sync now.

To test your data guard configuration, generate archive logs on primary site and verify it on DR site:

SQL> alter system switch logfile; //Give this command multiple times for testing.

On Primary:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 19
 Next log sequence to archive 21
 Current log sequence 21

OR

SQL> select max(sequence#) from v$archived_log;
 MAX(SEQUENCE#)
 --------------
 20

On Standby:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 19
 Next log sequence to archive 0
 Current log sequence 21

OR

 SQL> select max(sequence#) from v$archived_log;
 MAX(SEQUENCE#)
 --------------
 20

Cheers!! Our Data Guard configuration has been configured successfully.

Verify database roles by below mentioned SQL query:

On Primary:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS READ WRITE PRIMARY

On DR:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS MOUNTED PHYSICAL STANDBY

You can also verify total number of log sequence generated and applied on DR site, by below SQL query:

 SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
 SQL> SELECT sequence#, first_time, next_time, applied
 FROM v$archived_log
 ORDER BY sequence#;
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
 ---------- --------- --------- ---------
 3 04-OCT-14 04-OCT-14 YES
 4 04-OCT-14 04-OCT-14 YES
 5 04-OCT-14 04-OCT-14 YES
 6 04-OCT-14 05-OCT-14 YES
 7 05-OCT-14 05-OCT-14 YES
 8 05-OCT-14 05-OCT-14 YES
 9 05-OCT-14 05-OCT-14 YES
 10 05-OCT-14 05-OCT-14 YES
 11 05-OCT-14 05-OCT-14 YES
 12 05-OCT-14 05-OCT-14 YES
 13 05-OCT-14 05-OCT-14 YES
 14 05-OCT-14 05-OCT-14 YES
 15 05-OCT-14 05-OCT-14 YES
 16 05-OCT-14 05-OCT-14 YES
 17 05-OCT-14 05-OCT-14 YES
 18 05-OCT-14 05-OCT-14 YES
 19 05-OCT-14 05-OCT-14 YES
 20 05-OCT-14 05-OCT-14 YES

In case of you are facing any kind of error than following SQL query will help you to diagnose it.

 SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
 DEST_NAME
 --------------------------------------------------------------------------------
 STATUS ERROR
 --------- -----------------------------------------------------------------
 LOG_ARCHIVE_DEST_2
 VALID

Note: LOG_ARCHIVE_DEST_2 should be VALID in order to continue Data Guard sync.

OR

SQL> select message from v$dataguard_status;

Note: This command will give you appropriate message about the dataguard current status.

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

Protection Mode:
Default protection mode of newly configured standby database would be maximum performance mode.
There are 3 protection modes: Maximum Availability, Maximum Performance and Maximum Protection. for more information, click me.

By above mentioned steps you can configure Data Guard in your environment, Stay tune with our next article about Data Guard Switch Over and Switch Back steps.