Database Source Code Output

Today the development databases are inaccessible. The network team perform a migration over the weekend and now most of the development teams are unable to do their work. I've taken this opportunity to note something down. Its a mechanic I've used for a number of years when working in a Windows environment.

First, a bit of context. I spent several years working with Oracle in Linux and Unix. For those years, I made use of parametrized shell scripting and SQLPLUS to make short work analytical queries for the purposes of resolving domain/business problems quickly without the need to write out SQL. I had become accustomed to scripting in a Linux environment and automating my tasks. Moving to a Windows environment effectively clipped my wings as I was no longer able to use scripts and work with immediacy. Or so I thought. Within a week of working with SQL server, I made a fantastic transition into using Linux shell scripts in the windows environment using Linux emulator and SQLCMD. One of the positive effects is that I can make use of native Windows applications through the shell script. One of these little inventions is detailed below. This method can also be used with SQLPLUS in Windows.

Enter SourceDump
The sourcedump function is one that takes in a module name and dumps out the entire obejct into a .sql file. Once dumped, it is opened with Notepad++.
1. The first part required is a script that dumps the module out. This is a SQL database so I make use of sys.all_sql_modules instead of Oracle’s dba_source.

set nocount on
use $(Database)
go

select top 1 
       definition
  from sys.all_sql_modules (nolock)
 where object_name(object_id) like '%$(search_phrase)%'
   and object_id > 0

I place this query into a file called
c:\mine\sql\r_sql_source_dump.sql

2. The next task is to have a folder to store the files output from the script.
c:\mine\sourcedump

Once these 2 requirements are met, I move onto the shell configuration and functions.
 
 
3. In the Linux emulator, open the .profile file with vim editor
[rayb.] ➤ vim $HOME/.profile

2. Adjust the $PATH variable to increase its scope to key SQL directories (SQLCMD + DLL's) and include a text editor (Notepad++ is what I make use of)
export PATH=$PATH:"/drives/c/Program Files/Notepad++":"/drives/c/Program Files (x86)/Microsoft SQL Server/Client SDK/ODBC/130/Tools/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/Tools/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/DTS/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/Tools/Binn/ManagementStudio/":"/drives/c/Program Files/TortoiseSVN/bin":"/drives/c/Users/rayb/AppData/Local/Microsoft/WindowsApps"

3. Add the following shell function to the .profile file
sourcedump()
{
  sqlcmd -y 0 -w 65000 -S "<SERVER_IP>,<INSTANCE_PORT>" -v Database="$1" -U sa -P <PASSWORD> -v search_phrase="$2" -o "c:\\mine\\sourcedump\\$2.sql" -i c:\\mine\\sql\\r_sql_source_dump.sql
  notepad++ "c:\\mine\\sourcedump\\$2.sql"
}

4. Resource the .profile file
[2017-05-22 09:14.04]  ~
[rayb.       ] ➤ source $HOME/.profile

[2017-05-22 09:14.09]  ~
[rayb.        ] ➤

5. Output a module

SQL Server Top Running Queries by CPU seconds per minute

I have arrived in a new environment. I handed the real application cluster over in a stable condition and have since moved into a new Microsoft environment. In the past week, I've been writing a number of SQL scripts for SQLCMD. The scripts will speed up the process of learning and understanding the environment.

The script below was refurbished for the current environment - it is a TOPCPU script I wrote for another SQL environment around 4 years ago. It follows a similar form to the one I use for Oracle environments. Unfortunately, the code below is indented badly on the page - I will come back to cleaning it up.  The substring is for controlled output through SQLCMD. The script divides the duration of the users session by the amount of CPU seconds consumed and then orders the results by this new figure. It is not 100% perfect but it often sheds light on whats going on inside the system and any session that is out of control will usually spike to the top of the report. It has served me well over the years. It can also be adjusted to report on IO, though through my anecdotal experience, the TOPCPU often reports bad sessions to the top more often than TOPIO.

:setvar NOCOUNT ON

use $(database)
go

print ''
print '___________________________________________________________LOAD UP'

print ''
print '=============================================='
print 'TOP CPU CONSUMING SESSIONS ON : $(database)'
print '=============================================='
print ''
go

     select top 20
            substring(convert(varchar,sp.spid),0,4) sid,
            substring(sp.hostname,0,10) hostname,
            substring(object_name(st.objectid,sp.dbid),0,20) obj_name,            substring(convert(varchar(45),replace(replace(replace(st.text,char(13),''),char(10),''),char(9),'')),0,45) text,
            substring(sp.loginame,0,10) loginame,
            substring(db_name(sp.dbid),0,10) did,
            CASE WHEN sp.blocked > 0 THEN 'YES' ELSE 'NO' END blx,
            substring(sp.lastwaittype,0,15) lastwaittype,
            substring(sp.status,0,10) status,
            substring(sp.program_name,0,15) program_name,            substring(convert(varchar(15),replace(replace(sp.cmd,char(13),''),char(10),'')),0,15) cmd,
            substring(sp.nt_domain,0,10) nt_domain,
            substring(sp.nt_username,0,8) nt_username,
            convert(varchar(6),datediff(minute,sp.login_time,current_timestamp )) minutes,
            sp.login_time,
            convert(varchar(6),
                    sp.cpu/case 
                             when datediff(minute,sp.login_time,current_timestamp ) <= 0 then 1 
                             else datediff(minute,sp.login_time,current_timestamp ) end) cpu_pm
       from sys.sysprocesses sp (nolock)
cross apply sys.dm_exec_sql_text(sp.sql_handle) as st 
 order by sp.cpu/case 
                   when datediff(minute,sp.login_time,current_timestamp ) <= 0 then 1 
                   else datediff(minute,sp.login_time,current_timestamp ) end  desc
go

print ''
print '_________________________________________________________COMPLETED'
print ''

Results below - obfuscated owing to the sensitive nature of the business. The script was setup on a screen resolution at 1920x1080 - the output may be jumbled on something smaller.

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.

Free State, Clarens 2016

The weekend was spent in a quite town in the Free State. The skater in the pictures is a friend of mine. He skates down hills, sometimes at ~90km per hour. 

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.

RAC Attack 12c - Setting up enterprise grade clusterware in your home

I was able to build a 2 node 12C cluster from the ground up using a home PC. The 2 node RAC cluster is comprised of 2 Linux 6 VM machines each consuming 1.5G of memory. The cluster runs alright and shares 4 ASM virtual box drives. The job took 2 evenings.

To do this, I used a guide created by the RAC attack group. The guys at RAC attack have done a great job in detailing each and every step  in the setup. The guide I used is here

Oracle RAC - home rolling an ORA.CVU patch

I have managed to stop the cluster from wasting away memory chunks owing to BUG [Note 1523366.1]. Oracle's solution is to opatch. However, the client cannot do that at this point owing to the country wide dependency on the system. In addition to this, to opatch the system is risky considering the fragile nature of RAC in Windows. I am not 100% confident that an opatch will be successful and I would sooner create a new cluster  than to apply a patch to and Oracle RAC system on Windows.

For the original discovery of the bug - see here
For the fragility of the cluster - see here


A recap: the command that is left open every 6 hours during the CVU health check is:

C:\Windows\system32\cmd.exe /K E:\OracleGrid\11.2.0.3\bin\cluvfy comp health -_format

A screenshot can be found here when the orphaned session grows in numbers:

For the memory leak fix, I did the following.

I backup a copy of the bat file: E:\OracleGrid\11.2.0.3\bin\cluvfy.bat 

I then open the original cluvfy,bat in notepad and carefully make the following changes

CHANGE 1
FROM:
if not (%CRSHOME%)==() (
  @set "CV_HOME=%CRSHOME%"
)

set CMDPATH=%~dp0

TO:
if not (%CRSHOME%)==() (
  @set "CV_HOME=%CRSHOME%"
)

set EXIT_OPTION=

if "%CVU_RESOURCE_OPTIONS%"=="" set EXIT_OPTION=/B

set CMDPATH=%~dp0

CHANGE 2
FROM:
exit /B %errorlevel%
goto done

:ERROR
exit /B 1

TO:
exit %EXIT_OPTION% %errorlevel%
goto done

:ERROR
exit %EXIT_OPTION% 1

Once the change is made, save and copy the file across to node 2. I can also move the CVU to avoid creating any unwanted issues using while carrying out the above changes

srvctl relocate cvu -n <node name>

However, considering the batch file is executed every 6 hours, the chances are slim.

The idea to carry this out came after looking at the source code of the 11.2.0.4 home - I noted a bug fix by a certain Oracle developer @ Oracle. I diff'd the old and new CVU, evaluated the developers intention, and took only what I needed from his fix to manually patch the CVU myself. I first recreated the problem in the 11.2.0.4 test environment, monitored it for a day, patched it with my fix, and monitored for a few days. Once done satisfied, I took the 'patch' live. This fix will allow the in house DBA and his manager to have a cluster that no longer crashes once a month - forever. The nightmare is over and this solution will suffice until we create a new cluster for 12c. Done fixed it.