Oracle DBA & Data Science Enthusiast: September 2016

Wednesday 21 September 2016

Create and Monitor ACFS using cmd

1. Configure a initialization script to load the ACFS modules at startup.

[root@node1 ~]# vi /etc/init.d/acfsload
#!/bin/sh

# chkconfig: 2345 30 21
# description: Load Oracle ACFS drivers at system boot
/data01/app/11.2.0/grid_1/bin/acfsload start -s

[root@node1 ~]# chmod u+x /etc/init.d/acfsload

[root@node1 ~]# chkconfig --list acfsload
acfsload 0:off 1:off 2:on 3:on 4:on 5:on 6:off

[root@node1 ~]# /data01/app/11.2.0/grid_1/bin/acfsload start -s

[root@node1 ~]# lsmod | grep -i oracle
oracleacfs 1976568 0
oracleadvm 243152 0
oracleoks 426742 2 oracleacfs,oracleadvm
oracleasm 53865 1

2. Create diskgroups for acfs volumes :

SQL> CREATE DISKGROUP ACFS_DG1 EXTERNAL REDUNDANCY DISK 'ORCL:DISK3'
ATTRIBUTE 'compatible.asm' = '11.2.0.2.0', 'compatible.rdbms' = '11.2.0.2.0', 'compatible.advm' = '11.2.0.2.0' ;

Diskgroup created.

SQL> CREATE DISKGROUP ACFS_DG2 EXTERNAL REDUNDANCY DISK 'ORCL:DISK1'
ATTRIBUTE 'compatible.asm' = '11.2.0.2.0', 'compatible.rdbms' = '11.2.0.2.0', 'compatible.advm' = '11.2.0.2.0' ;

Diskgroup created.

SQL> select name,STATE , TYPE , TOTAL_MB , FREE_MB from v$asm_diskgroup where name in ('ACFS_DG1' ,'ACFS_DG2' );

NAME STATE TYPE TOTAL_MB FREE_MB
-------------------- --------------- -------------------- ---------- ----------
ACFS_DG1 MOUNTED EXTERN 5114 5064
ACFS_DG2 MOUNTED EXTERN 3067 3017

SQL> select name,compatibility, database_compatibility from v$asm_diskgroup where name in ('ACFS_DG1' ,'ACFS_DG2' );;

NAME COMPATIBILITY DATABASE_COMPATIBILITY
------------ -------------------- -------------------------------------
ACFS_DG1 11.2.0.2.0 11.2.0.2.0
ACFS_DG2 11.2.0.2.0 11.2.0.2.0

3. Create/Add volumes to the diskgroups :

SQL> alter diskgroup ACFS_DG1 add volume 'ACFS_vol1' size 5000M;

Diskgroup altered.

SQL> alter diskgroup ACFS_DG2 add volume 'ACFS_vol2' size 3000M;

Diskgroup altered.

4. Enable the Volumes

SQL> alter diskgroup ACFS_DG2 enable volume 'ACFS_vol2';

Diskgroup altered.

SQL> alter diskgroup ACFS_DG1 enable volume 'ACFS_vol1' ;

Diskgroup altered.

SQL> SELECT dg.name AS diskgroup, v.volume_name, v.volume_device, v.mountpath,v.state FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME v WHERE dg.group_number = v.group_number;

DISKGROUP   VOLUME_NAME VOLUME_DEVICE MOUNTPATH STATE
------------------------------ --------------- ------------------------------ ---------- ------------------------
ACFS_DG1   ACFS_VOL1 /dev/asm/acfs_vol1-454 ENABLED
ACFS_DG2   ACFS_VOL2 /dev/asm/acfs_vol2-444   ENABLED

volinfo -G ASMCMD> volinfo -G ACFS_DG1 ACFS_VOL1
Diskgroup Name: ACFS_DG1

Volume Name: ACFS_VOL1
Volume Device: /dev/asm/acfs_vol1-454
State: ENABLED
Size (MB): 5024
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage:
Mountpath:

ASMCMD> volinfo -G ACFS_DG2 ACFS_VOL2
Diskgroup Name: ACFS_DG2

Volume Name: ACFS_VOL2
Volume Device: /dev/asm/acfs_vol2-444
State: ENABLED
Size (MB): 3008
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage:
Mountpath:

ASMCMD> volinfo -a
Diskgroup Name: ACFS_DG1

Volume Name: ACFS_VOL1
Volume Device: /dev/asm/acfs_vol1-454
State: ENABLED
Size (MB): 5024
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage:
Mountpath:

Diskgroup Name: ACFS_DG2

Volume Name: ACFS_VOL2
Volume Device: /dev/asm/acfs_vol2-444
State: ENABLED
Size (MB): 3008
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage:
Mountpath:

5. Register mount point

[root@node1 ~]# mkdir -p /acfs_mount/acfs_vol1_DG1

[root@node1 ~]# acfsutil registry -a -f /dev/asm/acfs_vol1-454 /acfs_mount/acfs_vol1_DG1
acfsutil registry: mount point /acfs_mount/acfs_vol1_DG1 successfully added to Oracle Registry

[root@node1 ~]# mkdir -p /acfs_mount/acfs_vol2_DG2
[root@node1 ~]# acfsutil registry -a -f /dev/asm/acfs_vol2-444 /acfs_mount/acfs_vol2_DG2
acfsutil registry: mount point /acfs_mount/acfs_vol2_DG2 successfully added to Oracle Registry

[root@node1 ~]# df -kh
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_node1-lv_root
28G 16G 11G 61% /
tmpfs 1004M 326M 679M 33% /dev/shm
/dev/sda1 485M 55M 405M 12% /boot
Softwares 84G 11G 73G 13% /media/sf_Softwares
[root@node1 ~]#

6. Now mount the acfs_mounts created above :

[root@node1 ~]# mkfs.acfs -f /dev/asm/acfs_vol1-454
mkfs.acfs: version = 11.2.0.4.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/acfs_vol1-454
mkfs.acfs: volume size = 5268045824
mkfs.acfs: Format complete.

[root@node1 ~]# mkfs.acfs -f /dev/asm/acfs_vol2-444
mkfs.acfs: version = 11.2.0.4.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/acfs_vol2-444
mkfs.acfs: volume size = 3154116608
mkfs.acfs: Format complete.

[root@node1 ~]# mount.acfs /dev/asm/acfs_vol1-454 /acfs_mount/acfs_vol1_DG1

[root@node1 ~]# mount.acfs /dev/asm/acfs_vol2-444 /acfs_mount/acfs_vol2_DG2

[root@node1 ~]# df -kh
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_node1-lv_root
28G 16G 11G 61% /
tmpfs 1004M 326M 679M 33% /dev/shm
/dev/sda1 485M 55M 405M 12% /boot
Softwares 84G 11G 73G 13% /media/sf_Softwares
/dev/asm/acfs_vol1-454
5.0G 49M 4.9G 1% /acfs_mount/acfs_vol1_DG1
/dev/asm/acfs_vol2-444
3.0G 45M 2.9G 2% /acfs_mount/acfs_vol2_DG2

[root@node1 ~]# chown -R oracle:oinstall /acfs_vol1/
[root@node1 ~]# chown -R oracle:oinstall /acfs_vol2/
[root@node1 ~]# chmod -R 775 /acfs_vol1/
[root@node1 ~]# chmod -R 775 /acfs_vol2/

7. Verify the configuration

ASMCMD> volinfo -a
Diskgroup Name: ACFS_DG1

Volume Name: ACFS_VOL1
Volume Device: /dev/asm/acfs_vol1-454
State: ENABLED
Size (MB): 5024
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath: /acfs_mount/acfs_vol1_DG1

Diskgroup Name: ACFS_DG2

Volume Name: ACFS_VOL2
Volume Device: /dev/asm/acfs_vol2-444
State: ENABLED
Size (MB): 3008
Resize Unit (MB): 32
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath: /acfs_mount/acfs_vol2_DG2

SQL>SELECT dg.name AS diskgroup, v.volume_name, v.volume_device, v.mountpath,v.state FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME v WHERE dg.group_number = v.group_number;

DISKGROUP VOLUME_NAM VOLUME_DEVICE MOUNTPATH STATE
---------- ---------- ------------------------------ ------------------------------ ---------------
ACFS_DG1 ACFS_VOL1 /dev/asm/acfs_vol1-454 /acfs_mount/acfs_vol1_DG1 ENABLED
ACFS_DG2 ACFS_VOL2 /dev/asm/acfs_vol2-444 /acfs_mount/acfs_vol2_DG2 ENABLED

Tuesday 20 September 2016

How to find LUN assigned to Oracle ASM Library

[root@node1 ~]# oracleasm listdisks
DISK1
DISK2
DISK3
DISK4

[root@node1 ~]# /etc/init.d/oracleasm querydisk -p DISK1
Disk "DISK1" is a valid ASM disk
/dev/sdb1: LABEL="DISK1" TYPE="oracleasm"
[root@node1 ~]# /etc/init.d/oracleasm querydisk -p DISK2
Disk "DISK2" is a valid ASM disk
/dev/sdc1: LABEL="DISK2" TYPE="oracleasm"
[root@node1 ~]# /etc/init.d/oracleasm querydisk -p DISK3
Disk "DISK3" is a valid ASM disk
/dev/sdd1: LABEL="DISK3" TYPE="oracleasm"
[root@node1 ~]# /etc/init.d/oracleasm querydisk -p DISK4
Disk "DISK4" is a valid ASM disk
/dev/sde1: LABEL="DISK4" TYPE="oracleasm"

Saturday 17 September 2016

Configure DG BROKER 12c

Primary Database :
DB_NAME DATABASE_ROLE DB_UNIQUE_NAME VERSION
---------------- -------------------- ---------------- --------------- --------------- ----------
orcl PRIMARY orcl 12.1.0.2.0

Standby Database :
DB_NAME DATABASE_ROLE DB_UNIQUE_NAME VERSION
---------------- -------------------- ---------------- --------------- --------------- -------
orcl PHYSICAL STANDBY orcl_dg 12.1.0.2.0

1. . On both primary and standby set db_broker_start to TRUE

SQL> alter system set dg_broker_start=true scope=both;
System altered.

SQL> show parameter dg_broker_start ;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE

Step 2 : Edit the listener.ora file on both primary and standby :

Include the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME in both primary and standby database .
To set the value, lets check the db_domain and db_unique_name value .

primary :
============

SQL> show parameter db_unique_name ;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl

SQL> show parameter db_domain ;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string

Since the value of db_domain is null so the the value of GLOBAL_DBNAME = orcl_DGMGRL for primary database

[grid@node1 ~]$ cat /data01/app/grid/product/12.1.0/grid/network/admin/listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /data01/app/oracle/product/12c/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL)
(ORACLE_HOME = /data01/app/oracle/product/12c/db_1)
(SID_NAME = orcl)
)
)

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

standby :
=========
SQL> show parameter db_unique_name ;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl_dg

SQL> show parameter db_domain ;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string

Since the value of db_domain is null so GLOBAL_DBNAME = orcl_dg_DGMGRL for standby database .

[grid@node2 ~]$ cat /data01/app/grid/product/12.1.0/grid/network/admin/listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /data01/app/oracle/product/12c/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_dg_DGMGRL)
(ORACLE_HOME = /data01/app/oracle/product/12c/db_1)
(SID_NAME = orcl)
)
)

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

Configure the Data Guard Configuration

DGMGRL> connect sys/******@orcl
Connected as SYSDBA.
DGMGRL>
DGMGRL> CREATE CONFIGURATION 'ORCLCONF' AS PRIMARY DATABASE IS 'ORCL' CONNECT IDENTIFIER IS ORCL;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Failed.

Reason : (For 12.1, for SERVICE= for Remote lad's if the NOREGISTER attribute is Not set, then you must remove/clear the lad before creating the broker configuration. Doc ID 1582179.1)

SQL> show parameter log_Archive_dest_2 ;

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------------------------------------------------------
log_archive_dest_2 string SERVICE=orcl_dg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_dg

SQL> alter system set log_Archive_dest_2='';

System altered.

SQL> show parameter log_Archive_dest_2 ;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string

DGMGRL> connect sys/****@orcl
Connected as SYSDBA.

DGMGRL> CREATE CONFIGURATION 'ORCLCONF' AS PRIMARY DATABASE IS 'orcl' CONNECT IDENTIFIER IS orcl;
Configuration "ORCLCONF" created with primary database "orcl"

DGMGRL> ADD DATABASE 'orcl_dg' AS CONNECT IDENTIFIER IS orcl_dg MAINTAINED AS PHYSICAL ;
Database "orcl_dg" added

DGMGRL> show configuration

Configuration - ORCLCONF
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_dg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration
Enabled.

DGMGRL> show configuration

Configuration - ORCLCONF
Protection Mode: MaxPerformance
Members:
orcl - Primary database
orcl_dg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 10 seconds ago)

DGMGRL> show database verbose orcl

Database - orcl

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'orcl'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.73)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DATA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show database verbose orcl_dg

Database - orcl_dg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 9.00 KByte/s
Active Apply Rate: 24.00 KByte/s
Maximum Apply Rate: 247.00 KByte/s
Real Time Query: OFF
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'orcl_dg'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA, +DATA2'
LogFileNameConvert = '+DATA, +DATA2'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node2.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_dg_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DATA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

DGMGRL> validate database verbose orcl

Database Role: Primary database
Ready for Switchover: Yes
Capacity Information:
Database Instances Threads
orcl 1 1
Temporary Tablespace File Information:
orcl TEMP Files: 1
Flashback Database Status:
orcl: Off
Data file Online Move in Progress:
orcl: No
Transport-Related Information:
Transport On: Yes
Log Files Cleared:
orcl Standby Redo Log Files: Cleared
Automatic Diagnostic Repository Errors:
Error orcl
No logging operation NO
Control file corruptions NO
System data file missing NO
System data file corrupted NO
System data file offline NO
User data file missing NO
User data file corrupted NO
User data file offline NO
Block Corruptions found NO

DGMGRL> validate database verbose orcl_dg

Database Role: Physical standby database
Primary Database: orcl
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Capacity Information:
Database Instances Threads
orcl 1 1
orcl_dg 1 1
Temporary Tablespace File Information:
orcl TEMP Files: 1
orcl_dg TEMP Files: 1
Flashback Database Status:
orcl: Off
orcl_dg: Off
Data file Online Move in Progress:
orcl: No
orcl_dg: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds (computed 1 second ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 1 second ago)
Transport Status: Success
Log Files Cleared:
orcl Standby Redo Log Files: Cleared
orcl_dg Online Redo Log Files: Cleared
orcl_dg Standby Redo Log Files: Available
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(orcl) (orcl_dg)
1 3 2 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(orcl_dg) (orcl)
1 3 0 Insufficient SRLs
Warning: standby redo logs not configured for thread 1 on orcl

Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(orcl) (orcl_dg)
1 50 MBytes 50 MBytes
Apply-Related Property Settings:
Property orcl Value orcl_dg Value
DelayMins 0 0
ApplyParallel AUTO AUTO
Transport-Related Property Settings:
Property orcl Value orcl_dg Value
LogXptMode ASYNC ASYNC
RedoRoutes <empty> <empty>
Dependency <empty> <empty>
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
MaxConnections 1 1
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE
LogShipping ON ON
Automatic Diagnostic Repository Errors:
Error orcl orcl_dg
No logging operation NO NO
Control file corruptions NO NO
SRL Group Unavailable NO NO
System data file missing NO NO
System data file corrupted NO NO
System data file offline NO NO
User data file missing NO NO
User data file corrupted NO NO
User data file offline NO NO
Block Corruptions found NO NO

How to set up data guard broker for RAC

https://martincarstenbach.wordpress.com/2012/06/29/how-to-set-up-data-guard-broker-for-rac/

Saturday 10 September 2016

RMAN duplicate database having multiple directories to assign to BACKUP LOCATION

By default RMAN picks one backup location to do duplicate with clause 'BACKUP LOCATION' .

Many times user don't have much space in single location ,hence, the backup piece distributed

to different location and at same time want to use 'BACKUP LOCATION' feature for duplicate.

How to perform the RMAN duplication using the BACKUP LOCATION clause when the RMAN backuppieces are stored on several mount points or directories?

1/ Create the softlink on all of the individual RMAN backuppieces required for the RMAN duplicate, as shown in the example here. You may change the '/tmp/rmanbkup' on a different directory where you want to create those softlinks.

mkdir /tmp/rmanbkup

ln -s /o/zfssa/backup01/scs/84oqpfif_1_1 /tmp/rmanbkup/84oqpfif_1_1

ln -s /o/zfssa/backup02/scs/87oqpfif_1_1 /tmp/rmanbkup/87oqpfif_1_1

-- You can run the following SQL statements to dynamically generate the Unix command "ln -s" for the

-- individual RMAN backuppieces required for the RMAN duplicate, as shown here.

-- In the following SQL statement, just modify the start date and end date that corresponds to when

-- the backup started and backup completed.

set linesize 200 pagesize 0 verify off echo off feedback off

select 'ln -s ' || handle || ' /tmp/rmanbkup' || substr(handle, instr( handle, '/', -1 ))

from v$backup_piece

where completion_time between to_date ('05-DEC-2013 20:00:00','DD-MON-RRRR HH24:MI:SS')

and to_date('05-DEC-2013 22:00:00','DD-MON-RRRR HH24:MI:SS')

and device_type = 'DISK'

order by handle;

2/ In the RMAN duplicate, the BACKUP LOCATION is '/tmp/rmanbkup', as shown here.

DUPLICATE DATABASE TO gvlt03

SPFILE

SET CONTROL_FILES='+DATA_GXPB'

SET DB_FILE_NAME_CONVERT='+DATA_GXTB','+DATA_GXPB','RECO_GXTB','RECO_GXPB','DBFS_DG','DBFS_DG'

SET LOG_FILE_NAME_CONVERT='+DATA_GXTB','+DATA_GXPB','RECO_GXTB','RECO_GXPB','DBFS_DG','DBFS_DG'

SET DB_CREATE_ONLINE_LOG_DEST_1='+DATA_GXPB'

SET DB_CREATE_ONLINE_LOG_DEST_2='+DATA_GXPB'

SET DB_RECOVERY_FILE_DEST='+RECO_GXPB'

SET instance_number='1'

SET DIAGNOSTIC_DEST='/u01/app/oowner'

set audit_file_dest='/u01/app/oowner/admin/gvlt03/adump'

BACKUP LOCATION '/tmp/rmanbkup'

NOFILENAMECHECK;

Sunday 4 September 2016

How to check that Database is consistent after incomplete recovery before OPEN RESETLOGS

For cold/offline backups, no archivelogs/recovery is necessary. You can simply open the database with resetlogs.
However for HOT/ONLINE backups, ALL archivelogs from backup start to backup end must be applied before the database can be opened this is the MINIMUM amount of recovery needed.

To determine which log was current at the time the backup completed, note the COMPLETION time of the database backup take this from the backup log.
If this is an RMAN backup you can also query the RMAN metadata. Ensure that the environment variable NLS_DATE_FORMAT is set before invoking rman so that timestamps as well as date are returned:

For unix:
export NLS_DATE_FORMAT='dd-mon-rr hh24:mi:ss'
rman target /

For windows:
set nls_date_format='dd-mon-rr hh24:mi:ss'

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;

check 1. Checkpoint Time and Fuzziness

SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZZY STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
-------------------------------------------------------------------------------------
NO ONLINE 5311260 31-AUG-2011 23:10:14 6
YES ONLINE 5311260 31-AUG-2011 23:10:14 1

a) Verify that the checkpoint_time / checkpoint_change# is in line with your intended UNTIL TIME/SCN. If not, recover the database further if you have more archived logs available.
b) If FUZZY=YES for some datafiles, it means more recovery is required.

Above check can be considered PASSED when :
a) Verified that all the datafiles are at the some checkpoint_time, and this is your intended Point in time.
b) Fuzzy=NO for SYSTEM, UNDO and all intended datafiles. For datafiles with Fuzzy=YES, either recover them further or bring them OFFLINE if no further archived logs are available.

Check 2: Absolute Fuzzy

Occasionally, it is possible to see Fuzzy=NO and same checkpoint_change# for all the intended datafiles but OPEN RESETLOGS still fails

select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZZY STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
-------------------------------------------------------------------------------------
NO ONLINE 5311260 31-AUG-2011 23:10:14 7

SQL> ALTER DATABASE OPEN RESETLOGS ;
ORA01194:file 4 needs more recovery to be consistent
ORA01110:data file 3: '/u01/app/oracle/oradata/prod111/undotbs02.dbf'

Hence, we should perform additional fuzzy check known as Absolute Fuzzy Check:

SQL> select hxfil file#, substr(hxfnm, 1, 50) name, fhscn checkpoint_change#, fhafs Absolute_Fuzzy_SCN, max(fhafs) over () Min_PIT_SCN from x$kcvfh where fhafs!=0 ;

FILE# NAME CHECKPOINT_CHANGE ABSOLUTE_FUZZY_SCN MIN_PIT_SCN
------------------------------------------------------------------------------------------------------
4 /u01/app/oracle/oradata/prod111/undotbs01.dbf 5311260 5311524 5311524
6 /u01/app/oracle/oradata/prod111/system01.dbf 5311260 5311379 5311524

Note: Column Min_PIT_SCN will return same value even for multiple rows as we have applied ANALYTICAL "MAX() OVER ()" function on it.
Above query indicates that the recovery must be performed at least UNTIL SCN 5311524 to make datafiles consistent and ready to OPEN. Since the checkpoint_change# is smaller than Min_PIT_SCN, the datafiles will ask for more recovery.

Above check can be considered PASSED when:
a) No rows selected from above query (i.e. Min_PIT_SCN is 0 (Zero) for all the datafiles)
b) Min_PIT_SCN is returned less than Checkpoint_Change#

Check 3: Archive Logs Required:

Query the controlfile to find the latest archivelog required for recovery. Lets say the backup completed at 31-AUG-2011 23:20:14:

SQL> V$ARCHIVED_LOG
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;

If the above query does not return any rows, it may be that the information has aged out of the controlfile run
the following query against v$log_history.

SQL> V$ LOG_HISTORY view does not have a column NEXT_TIME
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> select a.THREAD#, a.SEQUENCE#, a.FIRST_TIME from V$LOG_HISTORY a
where FIRST_TIME =
( SELECT MAX(b.FIRST_TIME) FROM V$LOG_HISTORY b
WHERE b.FIRST_TIME < to_date('31-AUG-11 23:20:14', 'DD-MON-RR HH24:MI:SS')
) ;

The sequence# returned by the above query is the log sequence current at the time the backup ended let say 530 thread 1.

For minimum recovery use: (Sequence# as returned +1 ) :

RMAN> RUN
{
SET UNTIL SEQUENCE 531 THREAD 1;
RECOVER DATABASE;
}

If this is a RAC implementation the use this SQL instead to query the controlfile:

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;

For minimum recovery use the log sequence and thread that has the lowest NEXT_CHANGE# returned by the above query.

Above check can be considered PASSED when:
All archivelogs from the time of the backup to the end of the backup is available for use during recovery

Saturday 3 September 2016

RMAN Backup Script

cat /backups2/rman_backup.sh

DATESTAMP=`date '+%y%m%d%H%M'`
NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
export NLS_DATE_FORMAT
export ORACLE_SID=ORCL
export BACKUP_TYPE=LVL0
MSGLOG=backup_${BACKUP_TYPE}_${ORACLE_SID}_${DATESTAMP}.log
export BACKUP_LOCATION=/data01/BACKUP

$ORACLE_HOME/bin/rman msglog $MSGLOG append << EOF
connect target
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
backup as compressed backupset incremental level 0 format '${BACKUP_LOCATION}/${ORACLE_SID}_${BACKUP_TYPE}_%U.bak' database;
backup archivelog all format '${BACKUP_LOCATION}/${ORACLE_SID}_arch_%U.bak' ;
backup as copy current controlfile format '${BACKUP_LOCATION}/${ORACLE_SID}_Control_%U.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
}
EOF

Full backup CMD FILE :

nohup rman target / cmdfile=/home/oracle/backup_full.cmd log=/home/oracle/backup_rmanlog &

Total Pageviews