Incremental RMAN backup approach in 2 node RAC and standalone standby

Incremental RMAN backup approach in 2 node RAC and standalone standby
----------------------------------------------------------------------

Primary - 2 node RAC - deopvdvvnode5a(TEST1),deopvdvvnode5b(TEST2)
Secondary - standalone DB - deopvdvvnode4a(TEST1)

1) Find current_scn number from both primary and standby.

PRIMARY:TEST\sys> select current_scn from v$database;

CURRENT_SCN
-----------
   65445955

TEST1\sys> select current_scn from v$database;

CURRENT_SCN
-----------
   42188320

2) Find the exact time , since its lagging. Need to hit for both the scn on primary , since on standby it will fail.

PRIMARY:TEST\sys> select scn_to_timestamp(65445955) from dual;

SCN_TO_TIMESTAMP(65445955)
---------------------------------------------------------------------------
02-MAR-16 04.02.32.000000000 PM

select scn_to_timestamp(42188320) from dual;

3)  Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

4)  Shutdown the standby database

5)  On the primary, take an incremental backup from the SCN number where the standby has been stuck:

run {
allocate channel c1 type disk format '/opt/app/oracle/oraback/%U.rmb';
backup incremental from scn 42188320 database;
}

RMAN> run {
allocate channel c1 type disk format '/opt/app/oracle/oraback/%U.rmb';
backup incremental from scn 42188320 database;
}2> 3> 4>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=15 instance=TEST1 device type=DISK

Starting backup at 02-MAR-16

backup will be obsolete on date 09-MAR-16
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DG_TEST/test/datafile/sysaux.257.893130419
input datafile file number=00001 name=+DG_TEST/test/datafile/system.256.893130419
input datafile file number=00003 name=+DG_TEST/test/datafile/undotbs1.258.893130419
input datafile file number=00006 name=+DG_TEST/test/datafile/undotbs2.268.893130659
input datafile file number=00005 name=+DG_TEST/test/datafile/example.267.893130515
input datafile file number=00004 name=+DG_TEST/test/datafile/users.259.893130419
channel c1: starting piece 1 at 02-MAR-16
channel c1: finished piece 1 at 02-MAR-16
piece handle=/opt/app/oracle/oraback/0dqvg095_1_1.rmb tag=TAG20160302T164109 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:46

backup will be obsolete on date 09-MAR-16
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 02-MAR-16
channel c1: finished piece 1 at 02-MAR-16
piece handle=/opt/app/oracle/oraback/0eqvg0an_1_1.rmb tag=TAG20160302T164109 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-MAR-16
released channel: c1

6)  On the primary, create a new standby controlfile:

PRIMARY:TEST\sys> alter database create standby controlfile as '/opt/app/oracle/oraback/DEL1_standby.ctl';

Database altered.

7)  Copy all the files from primary to standby host:

8)  Bring up the instance in nomount mode:

SQL> startup nomount

9)  Check the location of the controlfile:

SQL> show parameter control_files

10) Restore the standby control file.

RMAN> restore standby controlfile from '/opt/app/oracle/oraback/DEL1_standby.ctl';

11)  Mount the standby database:

RMAN> alter database mount;

12) Catalog the backups.

13) Recover these files.

RMAN> recover database;

Starting recover at 02-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=329 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2016 17:01:29
RMAN-06094: datafile 1 must be restored

RMAN> exit

Error came as RMAN was using information from control file with Primary ASM directory strucuture. So we need to use switch command to upate RMAN repository.

14) Switch datafiles.

RMAN> switch datafile 1 to copy;
RMAN> switch datafile 2 to copy;
RMAN> switch datafile 3 to copy;
RMAN> switch datafile 4 to copy;
RMAN> switch datafile 5 to copy;
RMAN> switch datafile 6 to copy;

15) Start recovery again.

TRAINING_STANDBY:deopvdvvnode4a:[TEST1]:/opt/app/oracle/oraback> rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 2 17:12:43 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2191276923, not open)

RMAN> recover database;

Starting recover at 02-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=312 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DG_DATA/test_stby/datafile/system.257.899494475
destination for restore of datafile 00002: +DG_DATA/test_stby/datafile/sysaux.261.899494475
destination for restore of datafile 00003: +DG_DATA/test_stby/datafile/undotbs1.260.899494475
destination for restore of datafile 00004: +DG_DATA/test_stby/datafile/users.263.899494493
destination for restore of datafile 00005: +DG_DATA/test_stby/datafile/example.256.899494475
destination for restore of datafile 00006: +DG_DATA/test_stby/datafile/undotbs2.262.899494491
channel ORA_DISK_1: reading from backup piece /opt/app/oracle/oraback/0dqvg095_1_1.rmb
channel ORA_DISK_1: piece handle=/opt/app/oracle/oraback/0dqvg095_1_1.rmb tag=TAG20160302T164109
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

starting media recovery

archived log for thread 1 with sequence 7232 is already on disk as file +DG_DATA/test_stby/archivelog/2016_03_02/thread_1_seq_7232.882.905446609
archived log for thread 2 with sequence 7064 is already on disk as file +DG_DATA/test_stby/archivelog/2016_03_02/thread_2_seq_7064.884.905446609
archived log file name=+DG_DATA/test_stby/archivelog/2016_03_02/thread_1_seq_7232.882.905446609 thread=1 sequence=7232
archived log file name=+DG_DATA/test_stby/archivelog/2016_03_02/thread_2_seq_7064.884.905446609 thread=2 sequence=7064
unable to find archived log
archived log thread=1 sequence=7233
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2016 17:13:06
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7233 and starting SCN of 65468812

RMAN> exit

Error is exepected as all the archives has been applied and its waiting for a particular sequence.

16) Start the managed recovery process.

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

17) Check the primary and standby with below queries to find if primary/standby are in sync.

Primary:
SQL >

select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PRIMARY:TEST\sys>   2    3    4

   THREAD# Last Primary Seq Generated
---------- --------------------------
         1                       7242
         2                       7070


PhyStdby:
SQL >

select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

   THREAD# Last Standby Seq Received
---------- -------------------------
         1                      7242
         2                      7070


PhyStdby:
SQL >

TEST1\sys> select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and applied='YES'
group by thread# order by 1;  2    3    4    5

   THREAD# Last Standby Seq Applied
---------- ------------------------
         1                     7241
         2                     7070


Note - Though its a 2-node cluster, incremental approach do not change much. Once all steps are done, you might see a lot of latency for thread 2. But after some time, both the threads will be in sync. 

RAC useful Commands

1) Check the status of the cluster
-------------------------------
crsctl check crs
crsctl check cluster
crsctl check cluster -all

2) Check the status of the services on the cluster
-----------------------------------------------
crsctl status resource -t

3) Stop crs on all nodes
---------------------

which crsctl
sudo path/crsctl stop cluster -all

4) Stop crs on specifc node
------------------------
sudo path/crsctl stop has

5) Disable CRS from starting on reboot
-----------------------------------
sudo path/crsctl disable has

6) Enable CRS to start on reboot
-----------------------------
sudo path/crsctl enable has

7) Start crs on specifc node
-------------------------
sudo path/crsctl start has

8) Check database status
---------------------
srvctl status database -d REMCORP

9) Stop / Start an instance
------------------------
srvctl stop instance -i REMCORP1 -d REMCORP
srvctl start instance -i REMCORP1 -d REMCORP

10) Stop / Start an Database
------------------------
srvctl stop database -d REMCORP
srvctl start database -d REMCORP

srvctl modify database -d REMCORP -s open,mount

11) Stop/Start listener on a node
-----------------------------
srvctl status listener -n remedy-ebu-db1
srvctl stop listener -n remedy-ebu-db1
srvctl start listener -n remedy-ebu-db1

12) Check the voting disk
---------------------
crsctl query css votedisk

13) Query the network addresses
---------------------------
oifcfg getif

14) Show OCR backup – the voting file (disk) is also backed up with the OCR backup
------------------------------------------------------------------------------
ocrconfig -showbackup

15) Taking a manual backup of the OCR
-------------------------------
ocrconfig -manualbackup

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

1) On Node check if the DB is in archivelog mode or not

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

2) Enable Force Logging
ALTER DATABASE FORCE LOGGING;

3) Initialization Parameters

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

SQL> show parameter db_name

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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      CRAMPBB

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 the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "CRAMPRD_STBY".

**SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CRAMPBB,PBBADG)';

Set suitable remote archive log destinations. In this case I'm using the fast 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.

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


ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='archCRAMPBB_%r_%t_%s.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;



**ALTER SYSTEM SET FAL_SERVER=PBBADG;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; (on both nodes)

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

on ADG and PBB
---------------

PBBADG=
(DESCRIPTION =
 (ADDRESS_LIST =
  (LOAD_BALANCE = yes)
  (ADDRESS = (PROTOCOL = TCP)(HOST = cramuatrpt-scan)(PORT = 1527))
  (ADDRESS = (PROTOCOL = TCP)(HOST = inpudicrmrdbdevrpt1v)(PORT = 1527))
  (ADDRESS = (PROTOCOL = TCP)(HOST = inpudicrmrdbdevrpt2v)(PORT = 1527))
 )
 (CONNECT_DATA =
   (SERVICE_NAME = PBBADG)
   (failover_mode=(type=select)(method=basic))
    )
  )

CRAMPBB=
(DESCRIPTION =
 (ADDRESS_LIST =
  (LOAD_BALANCE = yes)
  (failover=on)
  (ADDRESS = (PROTOCOL = TCP)(HOST = crampbb-scan.vsnl.co.in)(PORT = 1527))
  (ADDRESS = (PROTOCOL = TCP)(HOST = inpudicrmrdbuatpbb1v.vsnl.co.in)(PORT = 1527))
  (ADDRESS = (PROTOCOL = TCP)(HOST = inpudicrmrdbuatpbb2v.vsnl.co.in)(PORT = 1527))
 )
 (CONNECT_DATA =
   (SERVICE_NAME = CRAMPBB)
   (failover_mode=(type=select)(method=basic))
    )
  )


5) Backup Primary DB

run
{
allocate channel c1 type disk format '/export/rman_backup_for_standby/rman_db_%T_%U.rman';
backup as compressed backupset full database PLUS ARCHIVELOG;
release channel c1;
}

6) Create standby controlfile

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/PBBADG.ctl'

7) Create a parameter file for the standby database.

CREATE PFILE='/tmp/PBBADG1.ora' FROM SPFILE;

8) Ammend below parameters in pfile

*.db_unique_name='PBBADG'
*.fal_server='PBBADG'
*.log_archive_dest_2='SERVICE=CRAMPBB NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CRAMPBB'


STANDby server commands

9) check if all listener are up and running

10) Create the SPFILE form the amended PFILE.

SQL> create spfile from pfile=


run {

set until sequence=35 thread=1;
recover database;
}



11) Create redo logs

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g1_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g1_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g2_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g2_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g3_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g3_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g4_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g4_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g5_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g5_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g6_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g6_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g7_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g7_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g8_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g8_m02.dbf';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

12) Create Standby Log file

ALTER DATABASE ADD STANDBY LOGFILE GROUP 9  '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 18 '+DATA' SIZE 1G;

13) Create password file with orapwd utility.