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.
Showing posts with label RMAN. Show all posts
Showing posts with label RMAN. Show all posts
Thursday, 3 November 2016
Friday, 17 June 2016
Duplicate Database in Windows
The database I am cloning to (auxiliary) does not exist yet. The source database is production database (target) and I have copies of its most recent backup. The backup is a warm backup which I've copied from the server and placed in a location which which the duplication will call upon in the duplication process.
The destination environment has a listener which all development database register with so there are no details on setting up the listener.
This note is comprised of the following steps:
1. Create directories for auxiliary.
2. Create the pfile for auxiliary.
3. Create the service for auxiliary.
4. Create the spfile for auxiliary.
5. Adjust auxiliary so that it does not run production jobs upon opening
6. Duplicate the target to auxiliary using a recent warm backup from production
-Specify the target and destination
-Specify the databse file name conversion parameters
-Specify the source backup location from which to duplicate from
-Specify the log group
7. Disable auxiliary jobs
8. Adjust auxiliary to allow for the running of jobs after disabling the production jobs
Below are the descriptions of the <TAGS> found through the note:
<AUXILIARY_INSTANCE> - This is the name of the destination I want to restore a database into
<TARGET_INSTANCE> - This is the name of the database contained within the backup files used in the duplication process
<TARGET_INSTANCE_FOLDER> - This is the folder which holds my backup files used in the duplication process
<CONDITIONS FOR JOBS I WANT TO DISABLE> - This is a substitute for the conditions of which jobs I want to disable after the duplication. It is quite unlikely that jobs within a production database are meant to be running in a non-production environment.
1. Create directories for auxiliary.
The database will make use of the following locations.
REM Place for audits
mkdir -p E:\Oracle\admin\<AUXILIARY_INSTANCE>\adump
REM Place for datafiles
mkdir F:\Oracle\oradata\<AUXILIARY_INSTANCE>
REM Place for archivelogs
mkdir G:\Oracle\oradata\<AUXILIARY_INSTANCE>\archive
2. Create the pfile for auxiliary.
In command, open up notepad with the following command and paste the parameters into the file and save. Adjust accordingly.
notepad E:\oracle\product\11.2.0\dbhome_1\database\init<AUXILIARY_INSTANCE>.ora
*.audit_file_dest='E:\Oracle\admin\<AUXILIARY_INSTANCE>\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL01.CTL','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL02.CTL','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL03.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='<AUXILIARY_INSTANCE>'#Reset to original value by RMAN
*.diagnostic_dest='E:\Oracle'
*.dispatchers='(protocol=TCP)'
*.fixed_date='none'
*.job_queue_processes=0
*.local_listener='LISTENER_LOCAL'
*.log_archive_dest_1='LOCATION=g:\oracle\oradata\<AUXILIARY_INSTANCE>\archive'
*.log_file_name_convert='F:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=1063256064
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.shared_servers=2
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='*'
*NOTE: The destination machine has a listener titled "LISTENER_LOCAL" that services all the instance on the machine. This is what *.LOCAL_LISTENER is all about.
This tells the AUXILIARY database "When you startup - I want you to register yourself with the listener on this machine titled LISTENER_LOCAL". If the environment doesn't have a common listener which instance can register with, then a static entry must be made in the listener.ora file for our new database - I will not cover this here.
*NOTE: Also note that you should have the *.log_file_name_convert parameter in your parameter list, especially if the target and auxiliary are on the same server. Should db_file_name_convert be omitted in both parameter and RMAN script, RMAN will restore the BK files as was and try to write the datafiles back to the location of the source. Datafiles about to be overwritten will halt the duplication process and RMAN will warn you that the destination files are in use by another instance, but this is not the case for redolog files. Duplicate database has, for me in the past, accidentally overwritten the source database redologs if one plays fast and loose with the details of the log file name conversion parameter - the duplicate command will default to the target databases log file location during control file creation if an administrator has not specified log conversion in some form or another.
3. Create the service for auxiliary.
I create the Windows service to represent the new instance.
set ORACLE_SID=<AUXILIARY_INSTANCE>
set ORACLE_HOME=E:\oracle\product\11.2.0\dbhome_1
oradim -new -SID <AUXILIARY_INSTANCE> -startmode manual -pfile 'E:\oracle\product\11.2.0\dbhome_1\database\init<AUXILIARY_INSTANCE>.ora'
OUTPUT
C:\Windows\system32>set ORACLE_SID=<AUXILIARY_INSTANCE>
C:\Windows\system32>set ORACLE_HOME=E:\oracle\product\11.2.0\dbhome_1
C:\Windows\system32>oradim -new -SID <AUXILIARY_INSTANCE> -startmode manual -pfile 'E:\oracle\product\11.2.0\dbhome_1\database\init<AUXILIARY_INSTANCE>.ora'
Instance created.
4. Create the spfile for auxiliary.
Create an spfile from the pfile used to start the database service.
create spfile from pfile;
OUTPUT
C:\Windows\system32>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 17 08:57:50 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL>
5. Adjust auxiliary so that it does not run production jobs upon opening in the development environment
alter system set job_queue_processes=0 scope=spfile;
OUTPUT
C:\Windows\system32>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 17 09:06:20 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1060585472 bytes
Fixed Size 2261960 bytes
Variable Size 612371512 bytes
Database Buffers 440401920 bytes
Redo Buffers 5550080 bytes
SQL> alter system set job_queue_processes=0 scope=spfile;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
C:\Windows\system32>
6. Duplicate the target to auxiliary using a recent warm backup from production
This is the part where the disks are put to work.
Because I am not duplicating directly from the target database but rather duplicating from a backup (datafiles, control files, archivelogfiles), I do not specify the TARGET parameter when connecting to RMAN. I also do not specify NOCATALOG because RMAN will default to NOCATALOG mode when catalog information is not specific in the RMAN connection command.
DB_FILE_NAME_CONVERT is used to automatically rename the restored backup files to a new location: considering the auxiliary environment is NOT the production environment and the drives are not the same, I have provided the convert to suit the destination environments layout.
BACKUP LOCATION is a neat little parameter that tells RMAN where to look for the backup files of TARGET. This does away with the need to CATALOG the backup pieces prior to duplication.
LOGFILE tells RMAN where to create the redo logfiles and their sizes.
rman auxiliary /
run {
DUPLICATE DATABASE <TARGET_DATABASE> to "<AUXILIARY_INSTANCE>"
DB_FILE_NAME_CONVERT='G:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','H:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','E:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','D:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','F:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>'
BACKUP LOCATION='C:\BACKUP\<TARGET_INSTANCE_FOLDER>'
LOGFILE
'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo01.log' size 10M,
'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo02.log' size 10M,
'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo03.log' size 10M;
}
exit
OUTPUT
RMAN> run {
2> DUPLICATE DATABASE <TARGET_DATABASE> to "<AUXILIARY_INSTANCE>"
3> DB_FILE_NAME_CONVERT='G:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','H:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','E:\ORACLE\ORADATA\G
DATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','F:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>'
4> BACKUP LOCATION='C:\BACKUP\<TARGET_INSTANCE_FOLDER>'
5> LOGFILE
6> 'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo01.log' size 10M,
7> 'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo02.log' size 10M,
8> 'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo03.log' size 10M;
9> }
Starting Duplicate Db at 17-JUN-16
contents of Memory Script:
{
sql clone "alter system set db_name =
''<TARGET_DATABASE>'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''<AUXILIARY_INSTANCE>'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from 'C:\BACKUP\<TARGET_INSTANCE_FOLDER>\<TARGET_DATABASE>_BRR7VP4C_1_1.RMAN';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''<TARGET_DATABASE>'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''<AUXILIARY_INSTANCE>'' comment= ''Modified by RMAN duplicate'' scope=spfile
executing Memory Script
sql statement: alter system set db_name = ''<TARGET_DATABASE>'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''<AUXILIARY_INSTANCE>'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1060585472 bytes
Fixed Size 2261960 bytes
Variable Size 612371512 bytes
Database Buffers 440401920 bytes
Redo Buffers 5550080 bytes
Starting restore at 17-JUN-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL01.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL02.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL03.CTL
Finished restore at 17-JUN-16
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 device type=DISK
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL01.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL02.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL03.CTL
Finished restore at 17-JUN-16
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 device type=DISK
........................................................
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\USERS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00005 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FINDATADM1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00006 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FINDATAUDIT1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00007 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FINDATINT1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00008 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FININDADM1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00009 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FININDINT1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00010 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\AUDIT_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00011 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATADMD1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00012 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATADMD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00013 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATADMS_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00014 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINTERFACE1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00015 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATOPSD1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00016 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATOPSD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00017 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATOPSS_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00018 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDADMD1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00019 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDADMD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00020 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDADMS_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00021 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDINTERFACE1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00022 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD1_1.DBF
channel ORA_AUX_DISK_1: restoring datafile 00023 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00024 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSS.DBF
channel ORA_AUX_DISK_1: restoring datafile 00025 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPUSERS1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00026 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\UNDOTBS02.DBF
channel ORA_AUX_DISK_1: reading from backup piece C:\BACKUP\<TARGET_INSTANCE_FOLDER>\<TARGET_DATABASE>_BQR7VNJU_1_1.RMAN
......................
input datafile copy RECID=20 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDINTERFACE1_01.DBF
datafile 22 switched to datafile copy
input datafile copy RECID=21 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD1_1.DBF
datafile 23 switched to datafile copy
input datafile copy RECID=22 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD2_01.DBF
datafile 24 switched to datafile copy
input datafile copy RECID=23 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSS.DBF
datafile 25 switched to datafile copy
input datafile copy RECID=24 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPUSERS1_01.DBF
datafile 26 switched to datafile copy
input datafile copy RECID=25 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\UNDOTBS02.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 17-JUN-16
RMAN>
While the database is restoring, I can monitor the duplication progress using v$session_longops using another database session. The RMAN process registers its block restoration effort in the v$session_longops dynamic view. How handy is that. In another cmd window, I run the following connected to the auxiliary instance
select message from v$session_longops;
OUTPUT
E:\oracle\glassfish4\bin>set ORACLE_SID=<AUXILIARY_INSTANCE>
E:\oracle\glassfish4\bin>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 17 09:43:06 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select message from v$session_longops;
MESSAGE
--------------------------------------------------------------------------------
RMAN: aggregate input: restore 421: 4915871 out of 0 Blocks done
RMAN: full datafile restore: Set Count 11642: 5212774 out of 5744384 Blocks done
SQL> /
MESSAGE
--------------------------------------------------------------------------------
RMAN: aggregate input: restore 421: 5260134 out of 0 Blocks done
RMAN: full datafile restore: Set Count 11642: 5306598 out of 5744384 Blocks done
SQL> /
MESSAGE
--------------------------------------------------------------------------------
RMAN: aggregate input: restore 421: 5260134 out of 0 Blocks done
RMAN: full datafile restore: Set Count 11642: 5319142 out of 5744384 Blocks done
SQL>
See how the block count increases, this is my proverbial progress bar for observation
7. Disable auxiliary jobs
Here I disable the production jobs before re-enabling the job parameter.
conn / as sysdba
BEGIN
FOR r_jobs IN (SELECT job
FROM dba_jobs
WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
)
LOOP
DBMS_JOB.BROKEN(r_jobs.job,TRUE);
END LOOP;
FOR r_scheduler_jobs IN (SELECT owner,
job_name
FROM SYS.dba_scheduler_jobs
WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
)
LOOP
DBMS_SCHEDULER.DISABLE('"' || r_scheduler_jobs.owner || '"."' || r_scheduler_jobs.job_name || '"',TRUE);
END LOOP;
END;
/
OUTPUT
SQL> BEGIN
2 FOR r_jobs IN (SELECT job
3 FROM dba_jobs
4 WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
5 )
6 LOOP
7 DBMS_JOB.BROKEN(r_jobs.job,TRUE);
8 END LOOP;
9
10 FOR r_scheduler_jobs IN (SELECT owner,
11 job_name
12 FROM SYS.dba_scheduler_jobs
13 WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
14 )
15 LOOP
16 DBMS_SCHEDULER.DISABLE('"' || r_scheduler_jobs.owner || '"."' || r_scheduler_jobs.job_name || '"',TRUE);
17 END LOOP;
18
19 END;
20 /
PL/SQL procedure successfully completed.
SQL> select owner, job_name, enabled from dba_scheduler_jobs where enabled = 'TRUE';
OWNER JOB_NAME ENABL
------------------------------ ------------------------------ -----
SYS PURGE_LOG TRUE
SYS ORA$AUTOTASK_CLEAN TRUE
SYS DRA_REEVALUATE_OPEN_FAILURES TRUE
ORACLE_OCM MGMT_CONFIG_JOB TRUE
ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE
SYS BSLN_MAINTAIN_STATS_JOB TRUE
SYS RSE$CLEAN_RECOVERABLE_SCRIPT TRUE
SYS SM$CLEAN_AUTO_SPLIT_MERGE TRUE
EXFSYS RLM$EVTCLEANUP TRUE
EXFSYS RLM$SCHDNEGACTION TRUE
SYS AUDIT_PURGE TRUE
11 rows selected.
SQL>
Only the system jobs are left running.
8. Adjust auxiliary to allow for the runnign of jobs after disabling the production jobs
Now that the jobs are disabled, I can go ahead and allow the system to run jobs again.
alter system set job_queue_processes=5 scope=both;
OUTPUT
SQL> alter system set job_queue_processes=5 scope=both;
System altered.
SQL>
All done.
Friday, 22 April 2016
Database Fever
The source of this post comes from my experience during a disaster at one of the sites of the company I am contracting to. 3 days ago, I left work early because I could feel a fever coming on.
I was getting sick. While shivering in bed, I received a message that the SAN supporting an entire site had a drive failure and all the production environments in a particular site crashed.
A brief overview of the site:
-The SAN Storage was configured for RAID 5 supporting a Oracle database production VM server and a SQL development VM server.
-The Oracle VM server was hosted to a number of integration mechanisms including 2 different FTP solutions supporting a number of big companies in the region.
-The information system hosted by the Oracle Database was key in transacting industrial logistics on a large scale. This means that system failure could potentially halt operations of national interest.
Shortly after the news, the site engineer attempted to rebuild the failed disk as the configuration was suited for this kind of transaction. However, the SAN declared itself in a degraded state owing to the lack of drives and no spares were available. What this meant was that for months, no one could resupply the disks after consecutive failures due to procurement policies. They had depleted the spare disks without replenishing the supply. What this also meant was that I would have to get up and out of bed and be ready for a new implementation of the site. Fortunately, the department manager negotiated a DR meeting for the next morning considering no transactions were planned for the next day and the site would not be used. I wasted little time and logged into the DR server for the lost system, storing and moving aside logs, backups, configurations and reading any documentation I could find on this particular system - there was little to none.
The following morning we held a video conference with all the managers and engineers involved. The plan was to not activate the DR site considering we had time before the next transaction took place. The administrators flattened the SAN and rebuilt storage minus one drive. They then planted a VM for me to work on. They also restored bits and pieces of drive backups from backup executive which fortunately had the Oracle application folder (to salvage config parts from and speed recovery) and a few other integration configuration files. No registry and no operating system could be restored. I began copying the most recent backup (sync to our DR server every night) and logs around midday and my colleague installed the database software while I planned for the specific moment when we would have everything we needed. At around 13:00 we had software, a backup and all the logs generated between the backup and the point of failure. Throughout the next few hours, my awareness of my condition was overridden by a small amount of adrenaline in my system.
The steps to install the instance service and restore and recover the database in Windows are below.
Fixing the database:
In Windows I need to install the database service using ORADIM which requires the pfile.
But to get the pfile, I need either a previous copy of that pfile or the spfile - neither of which was actively backed up except as part of the RMAN backup routine.
But I cannot restore the spfile using RMAN until I have the service (or at least to my knowledge at this point)
A catch 22.
I had little time to figure out the correct order of carrying this out so I did the following: I grabbed the spfile from backup executive restoration, opened it in a binary editor, scraped the contents out, cleaned up dynamic parameters (*.parameter_name) and remove the ascii artifacts in the contents and created a new pfile in the databases directory called 'init<instancename>.ora' using notepad.
I also verified the paths of the control_files parameter, log_archive_dest parameters and any other paths stated in the spfile to make sure they were real.
Create the service:
In command (as administrator) and using the hodgepodge pfile I created, I create the service as follows:
set ORACLE_SID=<instancename>
set ORACLE_HOME=e:\Oracle\product\11.2.0.3\dbhome_1
oradim -new -SID <instancename> -startmode manual -pfile 'E:\Oracle\Product\11.2.0.3\db_1\database\init<instancename>.ora'
REM echo.
REM echo use this if you've made a mistake in your service creation and want to start again
REM oradim -DELETE -SID MYDBSID
I started the service in services.msc once I created it. I then use RMAN to restore the spfile from the backup. You will have hopefully kept a log of which backup piece holds your spfile and control file (usually the same piece). In my case, I had the piece name after reviewing the RMAN backup log we kept for our system backup.
Restore the spfile:
rman target / nocatalog
shutdown immediate;
startup nomount;
restore spfile from 'H:\RMAN_Backups\<instancename>_2016_04_18\<instancename>_RIR3CALE_1_1.RMAN';
shutdown immediate;
exit
The above script restores the spfile to my %ORACLE_HOME%/database directory.
Once I have this file, I then recreate the pfile from the spfile.
sqlplus /nolog
conn / as sysdba
startup nomount;
create pfile='E:\Oracle\Product\11.2.0.3\db_1\database\init<instancename>.ora.fromspfile' from spfile;
shutdown immediate;
exit;
I review the pfile for any differences between what I scraped together and what was the last functioning spfile configuration. If there is a difference, I would delete the service and recreate it with a newly created pfile from spfile using the restored spfile. As there was no difference, I proceed to carry out the next important step.
Safe guard the system from running jobs the moment I open it:
sqlplus /nolog
conn / as sysdba
startup nomount;
alter system set job_queue_processes=0 scope=spfile;
shutdown immediate;
I do this to prevent the system from running real time jobs wildly when I've not setup the supporting non-oracle systems.
Restore the control files:
Next step is to restore the control files:
rman target / nocatalog
startup nomount
restore controlfile from 'H:\RMAN_Backups\<instancename>_2016_04_18\<instancename>_RIR3CALE_1_1.RMAN';
mount database;
exit
This succeeds as I've check that the destination directories of the control files are all valid.
The next step is to catalog the remaining backup pieces and archive logs so Oracle can restore and recover the database.
Catalog the remaining backup pieces:
rman target / nocatalog
catalog start with 'H:\RMAN_Backups\<instancename>_2016_04_18\';
exit;
This command will dig through the specified directory and sub directories hunting for RMAN backup
pieces to use in any restorative commands.
Restore and recover the database:
Finally, I restore and recover the database:
rman target / nocatalog
run {
restore database;
recover database;
}
exit;
The above step should open the backup pieces cataloged, restore the data files to their allocated drives and directories and lastly apply all the archive logs it can. The restore function will be successful but the recover function will eventually "fail" as the recovery process runs out of thread:
unable to find archive log.
archive log thread=1 sequence=###
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 20/04/2016 14:27:09
RMAN-06054: media recovery requesting unknown log: thread 1 seq ### lowscn ###
This is normal when performing an incomplete recovery. I believe that the only way around this is if I have a copy of the redologs, which are never part of an RMAN backup set.
If I had those, I wouldn't be restoring or recovering. So, once this message is received, I proceed to open the database using resetlogs.
Open the database:
The moment of truth.
sqlplus /nolog
conn / as sysdba
alter database open resetlogs;
exit;
The database opened for me and the quiet nervousness in the department subsided.
What resetlogs does is create a fork in the road for the database identity by scrubbing the scn information and incarnation number from all files and replacing it with a new set of values so all files are in sync, in other words, I am re-instantiating this database and also rendering all prior backups, archive logs, control and data files and DR solutions irrelevant. They become irrelevant because Oracle will not allow me to apply files from one incarnation to another as a safety mechanism against database corruption. It is like having a citizens id number (or social security number) changed: once the number is changed, I would think all previous societal facilities like banking and identity become ineffective as their details no longer match the respective systems - one would have to setup new accounts. This also means I need to create a new backup as soon as possible and recreate the DR site as soon as possible.
Note: I can still make use of the old backup files but again - I would need to open the database with resetlogs. RESETLOGS will only render old files from the previous incarnation irrelevant to my NEWLY opened database incarnation.
Re-enable the jobs:
I proceed to fix the integration solutions and switch the database jobs back on by running
sqlplus /nolog
conn / as sysdba
alter system set job_queue_processes=15 scope=both;
Also, I created the listener after I restored the database. I used the NETCA wizard to do this then I overwrote the contents with the restored listener.ora. I also reinstated the previous
tnsnames.ora and sqlnet.ora files from the backup executive backups. Lastly, I copied the old password file back and prior to starting the process of restoration, I copied the old diag directory into the new home. I then began working on the EDI solutions and worked to get working again.
Once this was all done, my joints were aching and I became aware of my condition - my head was burning and I was in the middle of a fever. I went home and slept. I went to work the following day to cleanup and attend any other new issues that I may have missed. Things are running smoothly again. Today, I took the day off to recover, post this post and prepare for my exams.
I was getting sick. While shivering in bed, I received a message that the SAN supporting an entire site had a drive failure and all the production environments in a particular site crashed.
A brief overview of the site:
-The SAN Storage was configured for RAID 5 supporting a Oracle database production VM server and a SQL development VM server.
-The Oracle VM server was hosted to a number of integration mechanisms including 2 different FTP solutions supporting a number of big companies in the region.
-The information system hosted by the Oracle Database was key in transacting industrial logistics on a large scale. This means that system failure could potentially halt operations of national interest.
Shortly after the news, the site engineer attempted to rebuild the failed disk as the configuration was suited for this kind of transaction. However, the SAN declared itself in a degraded state owing to the lack of drives and no spares were available. What this meant was that for months, no one could resupply the disks after consecutive failures due to procurement policies. They had depleted the spare disks without replenishing the supply. What this also meant was that I would have to get up and out of bed and be ready for a new implementation of the site. Fortunately, the department manager negotiated a DR meeting for the next morning considering no transactions were planned for the next day and the site would not be used. I wasted little time and logged into the DR server for the lost system, storing and moving aside logs, backups, configurations and reading any documentation I could find on this particular system - there was little to none.
The following morning we held a video conference with all the managers and engineers involved. The plan was to not activate the DR site considering we had time before the next transaction took place. The administrators flattened the SAN and rebuilt storage minus one drive. They then planted a VM for me to work on. They also restored bits and pieces of drive backups from backup executive which fortunately had the Oracle application folder (to salvage config parts from and speed recovery) and a few other integration configuration files. No registry and no operating system could be restored. I began copying the most recent backup (sync to our DR server every night) and logs around midday and my colleague installed the database software while I planned for the specific moment when we would have everything we needed. At around 13:00 we had software, a backup and all the logs generated between the backup and the point of failure. Throughout the next few hours, my awareness of my condition was overridden by a small amount of adrenaline in my system.
The steps to install the instance service and restore and recover the database in Windows are below.
Fixing the database:
In Windows I need to install the database service using ORADIM which requires the pfile.
But to get the pfile, I need either a previous copy of that pfile or the spfile - neither of which was actively backed up except as part of the RMAN backup routine.
But I cannot restore the spfile using RMAN until I have the service (or at least to my knowledge at this point)
A catch 22.
I had little time to figure out the correct order of carrying this out so I did the following: I grabbed the spfile from backup executive restoration, opened it in a binary editor, scraped the contents out, cleaned up dynamic parameters (*.parameter_name) and remove the ascii artifacts in the contents and created a new pfile in the databases directory called 'init<instancename>.ora' using notepad.
I also verified the paths of the control_files parameter, log_archive_dest parameters and any other paths stated in the spfile to make sure they were real.
Create the service:
In command (as administrator) and using the hodgepodge pfile I created, I create the service as follows:
set ORACLE_SID=<instancename>
set ORACLE_HOME=e:\Oracle\product\11.2.0.3\dbhome_1
oradim -new -SID <instancename> -startmode manual -pfile 'E:\Oracle\Product\11.2.0.3\db_1\database\init<instancename>.ora'
REM echo.
REM echo use this if you've made a mistake in your service creation and want to start again
REM oradim -DELETE -SID MYDBSID
I started the service in services.msc once I created it. I then use RMAN to restore the spfile from the backup. You will have hopefully kept a log of which backup piece holds your spfile and control file (usually the same piece). In my case, I had the piece name after reviewing the RMAN backup log we kept for our system backup.
Restore the spfile:
rman target / nocatalog
shutdown immediate;
startup nomount;
restore spfile from 'H:\RMAN_Backups\<instancename>_2016_04_18\<instancename>_RIR3CALE_1_1.RMAN';
shutdown immediate;
exit
The above script restores the spfile to my %ORACLE_HOME%/database directory.
Once I have this file, I then recreate the pfile from the spfile.
sqlplus /nolog
conn / as sysdba
startup nomount;
create pfile='E:\Oracle\Product\11.2.0.3\db_1\database\init<instancename>.ora.fromspfile' from spfile;
shutdown immediate;
exit;
I review the pfile for any differences between what I scraped together and what was the last functioning spfile configuration. If there is a difference, I would delete the service and recreate it with a newly created pfile from spfile using the restored spfile. As there was no difference, I proceed to carry out the next important step.
Safe guard the system from running jobs the moment I open it:
sqlplus /nolog
conn / as sysdba
startup nomount;
alter system set job_queue_processes=0 scope=spfile;
shutdown immediate;
I do this to prevent the system from running real time jobs wildly when I've not setup the supporting non-oracle systems.
Restore the control files:
Next step is to restore the control files:
rman target / nocatalog
startup nomount
restore controlfile from 'H:\RMAN_Backups\<instancename>_2016_04_18\<instancename>_RIR3CALE_1_1.RMAN';
mount database;
exit
This succeeds as I've check that the destination directories of the control files are all valid.
The next step is to catalog the remaining backup pieces and archive logs so Oracle can restore and recover the database.
Catalog the remaining backup pieces:
rman target / nocatalog
catalog start with 'H:\RMAN_Backups\<instancename>_2016_04_18\';
exit;
This command will dig through the specified directory and sub directories hunting for RMAN backup
pieces to use in any restorative commands.
Restore and recover the database:
Finally, I restore and recover the database:
rman target / nocatalog
run {
restore database;
recover database;
}
exit;
The above step should open the backup pieces cataloged, restore the data files to their allocated drives and directories and lastly apply all the archive logs it can. The restore function will be successful but the recover function will eventually "fail" as the recovery process runs out of thread:
unable to find archive log.
archive log thread=1 sequence=###
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 20/04/2016 14:27:09
RMAN-06054: media recovery requesting unknown log: thread 1 seq ### lowscn ###
This is normal when performing an incomplete recovery. I believe that the only way around this is if I have a copy of the redologs, which are never part of an RMAN backup set.
If I had those, I wouldn't be restoring or recovering. So, once this message is received, I proceed to open the database using resetlogs.
Open the database:
The moment of truth.
sqlplus /nolog
conn / as sysdba
alter database open resetlogs;
exit;
The database opened for me and the quiet nervousness in the department subsided.
What resetlogs does is create a fork in the road for the database identity by scrubbing the scn information and incarnation number from all files and replacing it with a new set of values so all files are in sync, in other words, I am re-instantiating this database and also rendering all prior backups, archive logs, control and data files and DR solutions irrelevant. They become irrelevant because Oracle will not allow me to apply files from one incarnation to another as a safety mechanism against database corruption. It is like having a citizens id number (or social security number) changed: once the number is changed, I would think all previous societal facilities like banking and identity become ineffective as their details no longer match the respective systems - one would have to setup new accounts. This also means I need to create a new backup as soon as possible and recreate the DR site as soon as possible.
Note: I can still make use of the old backup files but again - I would need to open the database with resetlogs. RESETLOGS will only render old files from the previous incarnation irrelevant to my NEWLY opened database incarnation.
Re-enable the jobs:
I proceed to fix the integration solutions and switch the database jobs back on by running
sqlplus /nolog
conn / as sysdba
alter system set job_queue_processes=15 scope=both;
Also, I created the listener after I restored the database. I used the NETCA wizard to do this then I overwrote the contents with the restored listener.ora. I also reinstated the previous
tnsnames.ora and sqlnet.ora files from the backup executive backups. Lastly, I copied the old password file back and prior to starting the process of restoration, I copied the old diag directory into the new home. I then began working on the EDI solutions and worked to get working again.
Once this was all done, my joints were aching and I became aware of my condition - my head was burning and I was in the middle of a fever. I went home and slept. I went to work the following day to cleanup and attend any other new issues that I may have missed. Things are running smoothly again. Today, I took the day off to recover, post this post and prepare for my exams.
Subscribe to:
Posts (Atom)