BeepityBop: windows

Showing posts with label windows. Show all posts

Thursday, 3 November 2016

Random backup failure and multiple snapshot control file entries on ASM

Every ~30th backup of our cluster database fails. The RMAN error on NODE 1 can be seen below:
Starting backup at 01-NOV-16
channel backup_disk1: starting compressed full datafile backup set
channel backup_disk1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/<DB_NAME>/users01.dbf
input datafile file number=00012 name=+DATA/<DB_NAME>/users02.dbf
input datafile file number=00009 name=+DATA/<DB_NAME>/undotbs2_01.dbf
input datafile file number=00003 name=+DATA/<DB_NAME>/undotbs1_01.dbf
input datafile file number=00002 name=+DATA/<DB_NAME>/sysaux01.dbf
input datafile file number=00001 name=+DATA/<DB_NAME>/system01.dbf
input datafile file number=00005 name=+DATA/<DB_NAME>/gpaudit01.dbf
input datafile file number=00008 name=+DATA/<DB_NAME>/perf_data01.dbf
input datafile file number=00011 name=+DATA/<DB_NAME>/tracker01.dbf
input datafile file number=00007 name=+DATA/<DB_NAME>/issues01.dbf
input datafile file number=00010 name=+DATA/<DB_NAME>/users01.dbf
input datafile file number=00004 name=+DATA/<DB_NAME>/apex_data01.dbf
channel backup_disk1: starting piece 1 at 01-NOV-16
channel backup_disk1: finished piece 1 at 01-NOV-16
piece handle=H:\RMAN_BACKUPS\<DB_NAME>_3ORJQ6QM_1_1.RMAN tag=TAG20161101T065933 comment=NONE
channel backup_disk1: backup set complete, elapsed time: 00:47:50
channel backup_disk1: starting compressed full datafile backup set
channel backup_disk1: specifying datafile(s) in backup set
released channel: backup_disk1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on backup_disk1 channel at 11/01/2016 07:47:25
ORA-00245: control file backup failed; target is likely on a local file system

The alert log on NODE 1 shows this:
Tue Nov 01 07:47:24 2016
Errors in file E:\ORACLE\diag\rdbms\<DB_NAME>\<DB_NAME>1\trace\<DB_NAME>1_ora_22556.trc:
ORA-00245: control file backup failed; target is likely on a local file system Tue Nov 01 07:47:26 2016 Thread 1 cannot allocate new log, sequence 28242 Checkpoint not complete

The alert log error on NODE 2 is:
Tue Nov 01 07:47:24 2016
Control file backup creation failed:
failure to open backup target file E:\ORACLE\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\SNCF<DB_NAME>1.ORA.
Errors in file E:\ORACLE\diag\rdbms\<DB_NAME>\<DB_NAME>2\trace\<DB_NAME>2_lgwr_1008.trc:
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

The above alert log error occurs when RMAN attempts to write the snapshot controlfile to a local file system. But, the snapshot control location is not on the local system, it is configured to a shared mount point between both nodes that is always accessible as shown below.

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'H:\RMAN_Backups\<DB_NAME>_SNAPCF.RMAN';

So what could be the issue? Well, the part I omit is the fact that there were multiple entries for the snapshot file in the control file. The real output looks like this:

E:\dba>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Nov 3 10:07:00 2016

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

connected to target database: <DB_NAME> (DBID=11111111111)

RMAN> show all;
...............
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'H:\RMAN_Backups\<DB_NAME>_SNAPCF.RMAN';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'H:\RMAN_BACKUPS\<DB_NAME>_SNAPCF.RMAN';
...............

This means at some point, RMAN is defaulting to factory configuration to achieve its snapshot backup. I believe it may be because the mutliple parameters are causing RMAN to fall through to this behaviour. The difference between them is case sensitivity.

I found a match on meta link:
Bug 17879299 - Duplicate snapshot controlfile entries are shown by RMAN if snapshot controlfile is on ASM (Doc ID 17879299.8)

This is a bug which occurs from time to time in a database that has been configured with ASM.
The bug note says there is no workaround. This may be true, but the solution to correct this, and correct the random failure of backups, is to clear and reinstate the parameter in lower case as shown below:

E:\dba>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Nov 3 10:07:00 2016

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

connected to target database: <DB_NAME> (DBID=1566787861)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name <DB_NAME> are:
....................................
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'H:\RMAN_Backups\<DB_NAME>_SNAPCF.RMAN';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'H:\RMAN_BACKUPS\<DB_NAME>_SNAPCF.RMAN';

RMAN> configure snapshot controlfile name clear;

old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'H:\RMAN_Backups\<DB_NAME>_SNAPCF.RMAN';
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'H:\RMAN_BACKUPS\<DB_NAME>_SNAPCF.RMAN';
RMAN configuration parameters are successfully reset to default value

RMAN> show all;

RMAN configuration parameters for database with db_unique_name <DB_NAME> are:
....................
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORACLE\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\SNCF<DB_NAME>1.ORA'; # default

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'h:\rman_backups\<DB_NAME>_snapcf.rman';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'h:\rman_backups\<DB_NAME>_snapcf.rman';
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name <DB_NAME> are:
..........................
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'h:\rman_backups\<DB_NAME>_snapcf.rman';

RMAN>

Now I will monitor to see if backups continue to fail. I am hoping this bug is the cause of the sporadic failures.

Monday, 26 September 2016

Creating a Physical Standby Database in 11.2.0.3

Image courtesy of the movie "the shining". Go watch it, it is very good. The twins represent DR sites in a way in that they are mirrored. They also represent database systems in that when it is time to open DR, the administrator is likely to be scared: he or she is either dealing with auditors or a non recoverable disaster - both quite scary. An Oracle standby database is a database that is synchronized at a data block level, so when the DR system is bust open in an emergency, the system should be identical to the production database it was tethered to - at least up to the last log the DR site consumed. There are no Oracle Streams or Microsoft replication type tethering mechanisms, it is kept in sync using archivelog files shed from the primary database. It is also a one way operation, at least in standard edition.

Below are notes to setup a standby database in Windows 2012.

The steps in this guide assume the the server has the exact same drives as the primary production. This means that I am not going to use any *convert* parameters to handle differences in file names. If the drives do differ, then I would use convert to handle the difference in lettering.

1. Install the database service:
In an administration cmd window, I execute the following:

set ORACLE_SID=<STANDBY_SID>
set ORACLE_HOME=E:\Oracle\Product\11.2.0.3\dbhome_1
set PATH=%ORACLE_HOME%\bin;E:\Oracle\product\11.2.0.3\dbhome_1;%PATH%

E:\Oracle\product\11.2.0.3\dbhome_1\BIN\oradim -DELETE -SID <STANDBY_SID>

E:\Oracle\product\11.2.0.3\dbhome_1\BIN\oradim -new -SID <STANDBY_SID> -startmode manual -pfile 'E:\Oracle\Product\11.2.0.3\dbhome_1\database\init<STANDBY_SID>.ora'

If there is no pfile, make or copy one across using the primary database.
The next step is to create the folders where the control, data and redolog files will be restored.

2. Create the directories:
In windows command, execute the following:
mkdir f:\Oracle\Oradata\<STANDBY_SID>\
mkdir g:\Oracle\Oradata\<STANDBY_SID>\
mkdir h:\Oracle\Oradata\archive\<STANDBY_SID>
mkdir h:\Oracle\Oradata\<STANDBY_SID>

3. Copy the password file and a pfile:
Copy the following key files from the primary server to the standby database database directory:
prod01:E:\Oracle\prduct\11.2.0.3\dbhome_1\database\PWD<STANDBY_SID>.oraprod01:E:\Oracle\prduct\11.2.0.3\dbhome_1\database\init<STANDBY_SID>.ora
To
standby1:E:\Oracle\prduct\11.2.0.3\dbhome_1\database\

4. Restore the spfile
Now I begin the restoration process. If not done already, take a backup of the primary database. You will also need a standby controlfile from the primary site;

create standby controlfile as 'C:\<FOLDER>\<STANDBY_SID>_standby_control.rman'

Copy the backup pieces from the primary database server into a holding location on the standby database server:
C:\RMAN_Backups\<STANDBY_SID>_2016_04_05\

Execute the following in CMD:
set ORACLE_SID=<STANDBY_SID>
set ORACLE_HOME=E:\Oracle\Product\11.2.0.3\dbhome_1

rman target / nocatalog

Once done, I startup the database with force:

startup force nomount;

OUTPUT
RMAN> startup force nomount;

Oracle instance started

Total System Global Area 12827369472 bytes

Fixed Size 2267184 bytes
Variable Size 5704255440 bytes
Database Buffers 7113539584 bytes
Redo Buffers 7307264 bytes

Now I restore the database. I restore the spfile from the backup piece containing the spfile file. I can find the piece holding this file by reviewing the RMAN log file generated during the backup.
The clue is the piece containing the following meta data;
SPFILE Included: Modification time: 20-SEP-16

I then plug the piece name into the following command and restore:
restore spfile to 'E:\Oracle\product\11.2.0.3\dbhome_1\database\spfile<STANDBY_SID>.ora' from 'C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_21RGMSG7_1_
.RMAN';

OUTPUT
RMAN> restore spfile to 'E:\Oracle\product\11.2.0.3\dbhome_1\database\spfile<STANDBY_SID>.ora' from 'C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_21RGMSG7_1_
.RMAN';

Starting restore at 26-SEP-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1712 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_21RGMSG7_1_1.RMAN
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 26-SEP-16

RMAN>

5. Restore the control files
Now restore the control files. Find the control file backup piece from the log file. It should be the same as the spfile piece used in the previous step. Similarly, the clue is to find the piece with the following metadata identifier:

Control File Included: Ckp SCN: 19783506609 Ckp time: 25-SEP-16

Load up RMAN
rman target / nocatalog

Execute the following:
restore controlfile from 'C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_21RGMSG7_1_1.RMAN';

OUTPUT
RMAN> restore controlfile from 'C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_21RGMSG7_1_1.RMAN';

Starting restore at 26-SEP-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1712 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=F:\ORACLE\ORADATA\<STANDBY_SID>\CONTROL01.CTL
output file name=G:\ORACLE\ORADATA\<STANDBY_SID>\CONTROL02.CTL
output file name=H:\ORACLE\ORADATA\<STANDBY_SID>\CONTROL03.CTL
Finished restore at 26-SEP-16

Mount the database once the control files are deployed:

mount database;

OUTPUT
RMAN> mount database;

database mounted
released channel: ORA_DISK_1

6. Catalog the backup pieces
Now to catalog the full backupset:

OUTPUT
RMAN> catalog start with 'C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 09/26/2016 15:34:17
RMAN-06189: current DBID 1020978749 does not match target mounted database (1025452237)

The problem here is I need to exit rman and connect again with nocatalog after restoring and mounting the control files:

exit
rman target / nocatalog
catalog start with 'C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\';

OUTPUT:

RMAN> catalog start with 'C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\';

searching for all files that match the pattern C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\

List of Files Unknown to the Database
=====================================
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_2016_09_25_backup_list.log
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_20RGMRRN_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_21RGMSG7_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_22RGMSGG_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_23RGMSGN_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_24RGMSGV_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_25RGMSH6_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_26RGMSHE_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_27RGMSHM_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_28RGMSHT_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_29RGMSI5_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_2ARGMSI9_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_STANDBY_CONTROL.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_TRACE_CONTROL.RMAN

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_20RGMRRN_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_21RGMSG7_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_22RGMSGG_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_23RGMSGN_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_24RGMSGV_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_25RGMSH6_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_26RGMSHE_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_27RGMSHM_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_28RGMSHT_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_29RGMSI5_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_2ARGMSI9_1_1.RMAN
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_STANDBY_CONTROL.RMAN

List of Files Which Where Not Cataloged
=======================================
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_2016_09_25_backup_list.log
RMAN-07517: Reason: The file header is corrupted
File Name: C:\RMAN_RESTORE\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_TRACE_CONTROL.RMAN
RMAN-07517: Reason: The file header is corrupted

7. Restore the database
Once cataloged, I restore the database files:

restore database;

OUTPUT
RMAN> restore database;

Starting restore at 26-SEP-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1712 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to F:\ORACLE\ORADATA\<STANDBY_SID>\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to F:\ORACLE\ORADATA\<STANDBY_SID>\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to F:\ORACLE\ORADATA\<STANDBY_SID>\UNDOTBS1_01.DBF
channel ORA_DISK_1: restoring datafile 00004 to F:\ORACLE\ORADATA\<STANDBY_SID>\APEX_DATA01.DBF
channel ORA_DISK_1: restoring datafile 00005 to F:\ORACLE\ORADATA\<STANDBY_SID>\AUDIT01.DBF
channel ORA_DISK_1: restoring datafile 00006 to F:\ORACLE\ORADATA\<STANDBY_SID>\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00007 to F:\ORACLE\ORADATA\<STANDBY_SID>\ISSUES01.DBF
channel ORA_DISK_1: restoring datafile 00008 to F:\ORACLE\ORADATA\<STANDBY_SID>\PERF_DATA01.DBF
channel ORA_DISK_1: restoring datafile 00009 to F:\ORACLE\ORADATA\<STANDBY_SID>\UNDOTBS2_01.DBF
channel ORA_DISK_1: restoring datafile 00010 to F:\ORACLE\ORADATA\<STANDBY_SID>\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00011 to F:\ORACLE\ORADATA\<STANDBY_SID>\ISSUE02.DBF
channel ORA_DISK_1: restoring datafile 00012 to F:\ORACLE\ORADATA\<STANDBY_SID>\USERS02.DBF
channel ORA_DISK_1: reading from backup piece H:\RMAN_BACKUPS\<STANDBY_SID>_20RGMRRN_1_1.RMAN

RMAN> exit

Recovery Manager complete.

E:\Oracle\product\11.2.0.3\dbhome_1\BIN>

8. Restore the standby control file
Now I restore the standby control file backup taken from the original backup:

rman target / nocatalog
shutdown immediate
startup nomount
restore controlfile from 'C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_STANDBY_CONTROL.RMAN';
exit

OUTPUT
E:\Oracle\product\11.2.0.3\dbhome_1\BIN>rman target / nocatalog

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26

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

connected to target database: <STANDBY_SID> (DBID=1010768491, not open)
using target database control file instead of recovery catalog

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area 12827369472 bytes

Fixed Size 2293232 bytes
Variable Size 5838471696 bytes
Database Buffers 6979321856 bytes
Redo Buffers 7282688 bytes

RMAN> restore controlfile from 'C:\RMAN_Restore\<STANDBY_SID>_2016_09_25\<STANDBY_SID>_STANDBY_CONTROL.RMAN';

Starting restore at 26-SEP-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1522 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=F:\ORACLE\ORADATA\<STANDBY_SID>\CONTROL01.CTL
output file name=G:\ORACLE\ORADATA\<STANDBY_SID>\CONTROL02.CTL
output file name=H:\ORACLE\ORADATA\<STANDBY_SID>\CONTROL03.CTL
Finished restore at 26-SEP16

RMAN> exit
Recovery Manager complete.

E:\Oracle\product\11.2.0.3\dbhome_1\BIN>

9. Mount the database as a physcial standby

Startup and mount the database in standby mode using:
alter database mount standby database;

Note that the key words "standby database" are optional according to the documentation, the db will know what it is to be mounted as per the control file.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size 2267184 bytes
Variable Size 7918847952 bytes
Database Buffers 4898947072 bytes
Redo Buffers 7307264 bytes
SQL> alter database mount standby database;

Database altered.

SQL>
10. Checkout the new database
Check the database status
C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26
Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY

SQL>

11. Apply database logs
Now all I do is load the log files into the archive log dest or sync them using robocopy and periodically run the following through a scheduler:

recover automatic standby database;
cancel
exit

If done correctly, my alert log should have a surge of log files consumed through media recovery, with many lines reading as follows:
Media Recovery Log G:\ORACLE\ORADATA\ARCHIVE\<STANDBY_SID>\<STANDBY_SID>_0922977743_0001_0000001053.ARC
Media Recovery Log G:\ORACLE\ORADATA\ARCHIVE\<STANDBY_SID>\<STANDBY_SID>_0922977743_0001_0000001054.ARC
Media Recovery Log G:\ORACLE\ORADATA\ARCHIVE\<STANDBY_SID>\<STANDBY_SID>_0922977743_0001_0000001055.ARC

Eventually, the alert log should spit out the following error:
Errors with log G:\ORACLE\ORADATA\ARCHIVE\<STANDBY_SID>\<STANDBY_SID>_0922977743_0001_0000001055.ARC
ORA-00308: cannot open archived log 'G:\ORACLE\ORADATA\ARCHIVE\<STANDBY_SID>\<STANDBY_SID>_0922977743_0001_0000001055.ARC'

That is to be expected - the database has eaten all the logs, and it would like more, but it cannot have them until the primary database sheds more logs. So don't be surprised to see Media Recovery Log messages punctuated with error logs - each error depicts the next log the standby database is expecting.

12. Monitoring
Once the system is self reliant and able to copy and apply logs, I will need to monitor the difference in time between the primary and standby. I do this by measuring the difference in hours between the current date and the timestamp of the last applied log. I can use the below script with an email scheduler to help keep an eye on the pair. If the time begins falling behind and the hour difference becomes > 1 (considering the pair in close proximity within the site), I will know there is a problem and I will need to investigate.

conn / as sysdba

define p_log_dir='&1'

set echo off
set verify off
set linesize 1000
set pages 100

spool &1.\standby_hours_behind.log

PROMPT <STANDBY_SID> DATABASE
PROMPT ======================

select trunc(24*(sysdate-max(first_time)),2) as hours_behind,
max(sequence#) as last_applied_sequence_number,
max(sequence#)+1 as next_sequence_number
from v$log_history;

spool off

OUTPUT:
SQL> @H:\scripts\sql\standby_hours_behind.sql h:\scripts
Connected.
<STANDBY_SID> DATABASE
======================

HOURS_BEHIND LAST_APPLIED_SEQUENCE_NUMBER NEXT_SEQUENCE_NUMBER
------------ ---------------------------- --------------------
.36 1136 1137

The system is just over 20 minutes apart - considering it rotates logs every 10 minutes, this is acceptable for the site.

Thursday, 30 June 2016

Daily Drive Space Report

This post aims to setup an automatic space report email script for Windows. In Linux, the function df -m is used to display the amount of space on drives in clear readable manner.

/* Snippet taken from MOBAXTERM */
[2016-06-29 15:01.14] /drives/c/mine/sql
[$$$$] ➤ df -m
Filesystem 1M-blocks Used Available Use% Mounted on
C:/mine/home 304893 108067 196826 35% /home/mobaxterm
C: 304893 108067 196826 35% /drives/c
J: 215040 177142 37898 82% /drives/j
S: 112635 87374 25261 78% /drives/s
U: 572412 438530 133882 77% /drives/u
V: 153597 22683 130914 15% /drives/v
W: 153597 22683 130914 15% /drives/w
X: 215040 177142 37898 82% /drives/x
Y: 153597 22683 130914 15% /drives/y
Z: 572412 438530 133882 77% /drives/z

───────────────────────────────────────────────────────────────────
[2016-06-30 08:57.54] /drives/c/mine/sql
[$$$$] ➤

In Windows, there is no df -m nor does the UnxUtils df.exe seem to want to work as shown below.
C:\mine\Programs\unixutils\df -m
C:\mine\Programs\unixutils\df: cannot read table of mounted filesystems

To help keep tabs on a development environment and the disk space the backups consume, I've resorted a home made solution.

REQUIREMENTS:

1. Access to Task Scheduler
2. A from address from a recognized mail server within your domain.
3. A home folder to deploy the scripts

Step 1
Create the directories
mkdir E:\dba\script\backupfile_report

Step 2
Create the file backupfile_report.bat in the above working directory. Once created, load it with the following script:
SET WORKING_DIR=E:\dba\script\backupfile_report
SET PATH=%PATH%;%WORKING_DIR%
SET MONITOR_DIRECTORY=G:\RMAN_Backups

cd %WORKING_DIR%
E:

SETLOCAL EnableDelayedExpansion

REM change code page 1252 is used to remove the Unicode characters of the dir command output
chcp 1252

REM ^ is used to escape characters like greater or less than sybols from the output
REM The pre tag is used so that the email spaces the output at fixed spacing
echo ^<pre^> > %WORKING_DIR%\backupfile_report.log

echo FILES IN BACKUP DIR >> %WORKING_DIR%\backupfile_report.log
echo =================== >> %WORKING_DIR%\backupfile_report.log

dir /O:D %MONITOR_DIRECTORY% | find "7z" >> %WORKING_DIR%\backupfile_report.log

echo ^<br^> >> %WORKING_DIR%\backupfile_report.log
echo FREE SPACE >> %WORKING_DIR%\backupfile_report.log
echo ========== >> %WORKING_DIR%\backupfile_report.log
dir G:\ | find "bytes free" >> %WORKING_DIR%\backupfile_report.log

echo ^</pre^> >> %WORKING_DIR%\backupfile_report.log

mailhtml.vbs <FROM_ADDRESS> <TO_ADDRESS> "<SERVER> Backup Location Report" %WORKING_DIR%\backupfile_report.log

Step 3
Create the emailing script mailhtml.vbs in the same directory as the batch file.
Load the file up with the following script.

'Grab the parameters
from_address = WScript.Arguments(0)
to_address = WScript.Arguments(1)
subject = WScript.Arguments(2)
file = WScript.Arguments(3)

'Set the file reading variables
Const ForReading = 1
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

'Set the mail properties
SMTPServer = "<SMTP_RELAY_SERVER>"
Recipient = to_address
From = from_address
Subject = subject

'Create the mail object
GenericSendmail SMTPserver, From, Recipient, Subject, Message
Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message)
Message = fso.OpenTextFile(file,ForReading).ReadAll

'Assisgn the values to our mail object and send the mail off
set msg = WScript.CreateObject("CDO.Message")
msg.From = From
msg.To = Recipient
msg.Subject = Subject
msg.HTMLBody = Message
msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msg.Configuration.Fields.Update
msg.Send
End Sub

Step 4
Create the task scheduler for the batch script. To see how this is done, see Step 4 in the "Cluster Health Report" post. Alternatively, import the following task scheduler export:

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
<RegistrationInfo>
<Date>2016-06-29T14:36:45.1784573</Date>
<Author>ADMIN</Author>
<Description>This reports to us the G:\RMAN_Backup location as well as the space left on the G drive.</Description>
</RegistrationInfo>
<Triggers>
<CalendarTrigger>
<StartBoundary>2016-06-29T05:30:00</StartBoundary>
<Enabled>true</Enabled>
<ScheduleByDay>
<DaysInterval>1</DaysInterval>
</ScheduleByDay>
</CalendarTrigger>
</Triggers>
<Principals>
<Principal id="Author">
<UserId>ADMIN</UserId>
<LogonType>S4U</LogonType>
<RunLevel>HighestAvailable</RunLevel>
</Principal>
</Principals>
<Settings>
<MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
<DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
<StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
<AllowHardTerminate>true</AllowHardTerminate>
<StartWhenAvailable>false</StartWhenAvailable>
<RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
<IdleSettings>
<StopOnIdleEnd>true</StopOnIdleEnd>
<RestartOnIdle>false</RestartOnIdle>
</IdleSettings>
<AllowStartOnDemand>true</AllowStartOnDemand>
<Enabled>true</Enabled>
<Hidden>false</Hidden>
<RunOnlyIfIdle>false</RunOnlyIfIdle>
<WakeToRun>false</WakeToRun>
<ExecutionTimeLimit>P3D</ExecutionTimeLimit>
<Priority>7</Priority>
</Settings>
<Actions Context="Author">
<Exec>
<Command>E:\dba\script\backupfile_report\backupfile_report.bat</Command>
<WorkingDirectory>E:\dba\script\backupfile_report\</WorkingDirectory>
</Exec>
</Actions>
</Task>

Here is the contents of the email sent.

OUTPUT:

FILES IN BACKUP DIR
===================
2016/06/27 05:00 PM 694 DB1_2016_06_27.7z
2016/06/27 05:12 PM 566 DB2_2016_06_27.7z
2016/06/27 05:33 PM 13 397 DB3_2016_06_27.7z
2016/06/27 05:38 PM 897 066 419 DB4_2016_06_27.7z
2016/06/27 05:38 PM 694 DB5_2016_06_27.7z
2016/06/27 05:47 PM 1 728 202 632 DB6_2016_06_27.7z
2016/06/27 05:56 PM 1 216 270 881 DB7_2016_06_27.7z
2016/06/27 06:06 PM 1 927 492 151 DB8_2016_06_27.7z
2016/06/27 06:14 PM 1 733 744 322 DB9_2016_06_27.7z
2016/06/28 05:00 PM 694 DB1_2016_06_28.7z
2016/06/28 05:24 PM 566 DB2_2016_06_28.7z
2016/06/28 05:54 PM 13 397 DB3_2016_06_28.7z
2016/06/28 05:58 PM 892 120 199 DB4_2016_06_28.7z
2016/06/28 05:58 PM 694 DB5_2016_06_28.7z
2016/06/28 06:07 PM 1 720 066 139 DB6_2016_06_28.7z
2016/06/28 06:16 PM 1 213 519 663 DB7_2016_06_28.7z
2016/06/28 06:39 PM 1 906 291 500 DB8_2016_06_28.7z
2016/06/28 06:54 PM 1 721 172 646 DB9_2016_06_28.7z
2016/06/29 05:00 PM 694 DB1_2016_06_29.7z
2016/06/29 06:10 PM 13 577 939 773 DB2_2016_06_29.7z
2016/06/29 07:00 PM 10 541 191 641 DB3_2016_06_29.7z
2016/06/29 07:16 PM 857 023 418 DB4_2016_06_29.7z
2016/06/29 07:29 PM 694 DB5_2016_06_29.7z
2016/06/29 08:13 PM 1 666 050 028 DB6_2016_06_29.7z
2016/06/29 08:49 PM 1 160 641 332 DB7_2016_06_29.7z
2016/06/29 09:16 PM 1 878 692 682 DB8_2016_06_29.7z
2016/06/29 09:43 PM 1 720 452 716 DB9_2016_06_29.7z
2016/06/29 10:18 PM 5 990 226 449 DB0_2016_06_29.7z

FREE SPACE
==========
13 Dir(s) 48 683 343 872 bytes free

Monday, 20 June 2016

Upgrading Apex from 4.0.2 to 4.2 in Oracle XE

This post features the steps to upgrade Apex 4.0.2 to 4.2.2 in a Windows XE database.
The process of upgrading apex is pretty standard so this guide can apply a few versions of Apex.

OVERVIEW:
1. Create the APEX_DATA tablespace
2. Extract the APEX install files into a working directory
3. Shutdown the APEX gateway listener
4. Run the installation file
5. Check the registry and post installation status
6. Startup the APEX gateway listener

1. Create the APEX_DATA tablespace

CREATE TABLESPACE apex_data DATAFILE 'C:\ORACLE\APP\ORACLE\ORADATA\XE\APEX_DATA.DBF' SIZE 2G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2. Shutdown the APEX gateway listener
EXEC DBMS_XDB.sethttpport(0);

3. Extract the APEX install files into a working directory

I’ve moved the old $ORACLE_HOME/apex directory aside into $ORACLE_HOME/apex.4.0.2
I’ve extracted the 4.2.2 zip file into the $ORACLE_HOME/apex directory

4. Run the installation file

In cmd, change directory to your apex directory:
C:\oracle\app\oracle\product\11.2.0\server\apex

Log in to sqlplus as sysdba
sqlplus /nolog
conn / as sysdba

Check the current apex version in the database
SQL> select comp_name, version, status from dba_registry where comp_name = 'Oracle Application Express';

COMP_NAME VERSION STATUS
---------------------------- ---------------- ------------------
Oracle Application Express 4.0.2 VALID

Run the apex installer
SQL> @apexins.sql APEX_DATA APEX_DATA TEMP /i/
. ____ ____ ____ ____
. / \ | \ /\ / | /
.| || / / \ | | |
.| ||--- ---- | | |--
.| || \ / \ | | |
. \____/ | \/ \ \____ |____ \____
.
. Application Express (APEX) Installation.
..........................................
.
... Checking prerequisites (MANUAL)
.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

.
... Prerequisite checks passed.
.

Performing Application Express component validation - please wait...

old 6: if '^UPGRADE' = '1' then
new 6: if '2' = '1' then
old 7: dbms_registry.loaded('APEX','^version');
new 7: dbms_registry.loaded('APEX','4.2.2.00.11');
old 8: elsif '^UPGRADE' = '2' then
new 8: elsif '2' = '2' then
old 10: dbms_registry.upgraded('APEX','^version');
new 10: dbms_registry.upgraded('APEX','4.2.2.00.11');
old 12: dbms_registry.loaded('APEX','^version');
new 12: dbms_registry.loaded('APEX','4.2.2.00.11');
Completing registration process. 08:59:08
Validating installation. 08:59:08
...Database user "SYS", database schema "APEX_040200", user# "49" 08:59:08
...Compiled 0 out of 3004 objects considered, 0 failed compilation 08:59:09
...266 packages
...258 package bodies
...452 tables
...11 functions
...16 procedures
...3 sequences
...457 triggers
...1320 indexes
...211 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 08:59:09
...Completed key object existence check 08:59:09
...Setting DBMS Registry 08:59:09
...Setting DBMS Registry Complete 08:59:09
...Exiting validate 08:59:09

PL/SQL procedure successfully completed.

timing for: Validate Installation
Elapsed: 00:00:00.53
old 1: alter session set current_schema = ^APPUN
new 1: alter session set current_schema = APEX_040200

Session altered.

timing for: Complete Installation
Elapsed: 00:18:40.58

PL/SQL procedure successfully completed.

GLOBAL_NAME
------------------------------
SYS

1 row selected.

The apex installer will run for about 10-20 minutes while it reinstalls its libraries.

It will boot you out once it has completed.

5. Check the registry and post installation status
SQL> select comp_name, version, status from dba_registry where comp_name = 'Oracle Application Express';

COMP_NAME VERSION STATUS
------------------------------ ------------------------------ ---------------
Oracle Application Express 4.2.2.00.11 VALID

SQL>

6. Startup the APEX gateway listener
EXEC DBMS_XDB.sethttpport(8080);

Done