Rapid Guide – How to Repair a Failed Oracle Database Duplication

Posted on

Failed Duplication – Recovery Strategies

As an Oracle DBA, I’ve seen this time and again. During a massive duplication, something goes wrong and you don’t want to re-copy over the 6TBs of data you just spent the past day moving across the network. Here are a couple of techniques to get you repaired and back to setting up your new environment.

# if you don’t have the correct convert parameter in place

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/08/2015 13:57:46
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name +DATAC1/xx/datafile/undotbs2.753.87546xxx conflicts with a file used by the target database
RMAN-05001: auxiliary file name +DATAC1/xx/datafile/users.671.87546xxx3 conflicts with a file used by the target database
RMAN-05001: auxiliary file name +DATAC1/xx/datafile/undotbs1.752.xxx9 conflicts with a file used by the target database
RMAN-05001: auxiliary file name +DATAC1/xx/datafile/sysaux.751.875463xxx conflicts with a file used by the target database
RMAN-05001: auxiliary file name +DATAC1/xx/datafile/system.750.8754xxx conflicts with a file used by the target database

Fix:
SET DB_FILE_NAME_CONVERT ‘alpha’,’beta’
Should be…
SET DB_FILE_NAME_CONVERT ‘britt’,’beta’

 

# Failed during recovery phase due to missing archive log, here is how you repair it so you don’t have to re-copy the files.

… on TARGET

Oracle instance shut down
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/08/2015 15:22:48
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 21 and starting SCN of 1548373 found to restore

…. On AUX

SQL> startup mount

ORACLE instance started.

Total System Global Area 6062931968 bytes
Fixed Size 2264376 bytes
Variable Size 4596957896 bytes
Database Buffers 1442840576 bytes
Redo Buffers 20869120 bytes
ORA-01103: database name ‘BRITT’ in control file is not ‘BETA’

Fix:
Rebuild your Controlfile, find the missing log and catalog it in AUX and recover

Steps include:
NOTE: make sure your update your DATA FILES with the correct ones listed in ASMCMD. In regards to the REDO LOGS,
just add the +DATAC1, +RECOC1.

SAMPLE… please read carefully…
CREATE CONTROLFILE REUSE SET DATABASE “BETA” RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
‘+DATAC1′,
‘+RECOC1′
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
‘+DATAC1′,
‘+RECOC1′
) SIZE 50M BLOCKSIZE 512
DATAFILE
‘+DATAC1/beta/datafile/SYSAUX.744.876xx’,
‘+DATAC1/beta/datafile/SYSTEM.741.87656xx’,
‘+DATAC1/beta/datafile/UNDOTBS1.740.8765xx’,
‘+DATAC1/beta/datafile/UNDOTBS2.735.876xx’,
‘+DATAC1/beta/datafile/USERS.734.87656xx’
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 3 (
‘+DATAC1′,
‘+RECOC1′
) SIZE 50M BLOCKSIZE 512 REUSE,
GROUP 4 (
‘+DATAC1′,
‘+RECOC1′
) SIZE 50M BLOCKSIZE 512 REUSE;

# Here is how you can get to the problem/missing SCN. Most likely it is going to be an ARCHIVELOG missing.

ALTER DATABASE RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
SQL> alter database recover database using backup controlfile until cancel;
alter database recover database using backup controlfile until cancel
*
ERROR at line 1:
ORA-00279: change 1548245 generated at 04/07/2015 07:23:20 needed for thread 1
ORA-00289: suggestion : +RECOC1
ORA-15173: entry ‘ARCHIVELOG’ does not exist in directory ‘BETA’
ORA-00280: change 1548245 for thread 1 is in sequence #20
<<<

NOTE:
1. Fix the issue (find missing archivelog) and recatalog it so rman can find it. Once this is done, you can issue a recovery command.
2. Don’t need to do anything about the ORA-15173 entry ARCHIVELOG does not exist. This will be taken care of during the recovery phase automatically.

# recatalog either by single or folder… ex of folder
RMAN> CATALOG START WITH ‘path/folder of archivelogs';

Choose one of two

# recovery
RMAN> RECOVER DATABASE;

# recovery to scn (listed in error) last known good one.
RMAN> RECOVER DATABAES UNTIL SCN 1548245

EXAMPLE ….
RMAN> RECOVER DATABAES UNTIL SCN 1548245;
Starting recover at 09-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-APR-15
RMAN> ALTER DATABASE OPEN RESETLOGS;
database opened

# END

Rapid Guide – How to Duplicate an Oracle Database

Posted on Updated on

Purpose: A rapid guide through the oracle database duplication process. Oracle’s duplication process offers two main methods that include Backup-Based and Active Database.  The Backup-Based method is what we will be using in this guide.

Assumptions: You understand the basics of an Oracle Database environment.  This guide will be duplicating a single database with ASM storage.

Environmental Setup
# ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/dbhome_1

# Listener
/u01/app/12.1.0.2/grid/network/admin/listener.ora

# tnsnames
/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin

# pfiles etc.
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs

# For the rest of this document we will refer to these as follows:
TARGET = your source database/instance
AUXILIARY (AUX) = your destination database/instance

Create your adump directory (audit trail location)
mkdir /u01/app/oracle/admin/beta/adump

Password File
Copy the password file to the new location from TARGET to the AUX.

# Copy to Secondary
scp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwbeta oracle@server:
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs;

If you don’t already have a password file, create one…

# Create password file
orapwd file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwbeta password=<syspassword> entries=50

# View users in password file:
SELECT * FROM V$PWFILE_USERS;

 

Create Listener / tnsnames
# NOTE: These will be specific to your environment. The following are an example of what was used for testing only.

# Tnsnames.ora

ALPHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <ServerName)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = alpha)
)
)

# Listener.ora

(SID_DESC =
(GLOBAL_DBNAME=alpha)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME = alpha)
)

 

Startup empty AUXILIARY instance to duplicate into
Parameter Files

NOTE: create your new PFILE as follows. Pay specific attention around any existing RAC parameters is you are copying
from your existing PFILE.

# PFILE Template
$ORACLE_HOME/dbs/initbeta.ora

*.audit_file_dest=’/u01/app/oracle/admin/beta/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4.0′
*.control_files=’+DATAC1′,’+RECOC1′
*.db_create_file_dest=’+DATAC1′
*.db_block_size=8192
*.db_domain=”
*.db_name=’beta
*.db_recovery_file_dest=’+RECOC1′
*.db_recovery_file_dest_size=6005194752
*.pga_aggregate_target=3221225472
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=betaXDB)’
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile=’exclusive’
*.sessions=335
*.sga_target=7516192768
*.undo_tablespace=’UNDOTBS1′

# Add your instance to the ORATAB
beta:/u01/app/oracle/product/11.2.0.4/dbhome_1:N

# Alternate to ORATAB
Export ORACLE_SID=beta
Export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1

# start in nomount mode
STARTUP NOMOUNT;

Setup your Run Block for the duplicate command

# NOTES: there are two methods you can use for duplicating. I would recommend the time based since this is what the
business team will be giving us for a development environment. Either way it matches up to an SCN under the hood.

# SQL Queries to find your SCN

— Find SCN database level
select r.sequence#,r.set_stamp,p.handle,p.tag,p.start_time
,p.completion_time,r.first_change#,r.next_change#
from V$BACKUP_PIECE p, V$BACKUP_REDOLOG r
where r.set_stamp = p.set_stamp
and r.set_count = p.set_count
order by sequence# desc;

— Find using RMAN Catalog
select d.name,r.set_stamp,p.handle,p.tag,p.start_time,
p.completion_time,r.first_change#,r.next_change#
from RMAN.RC_BACKUP_PIECE p
join RMAN.RC_BACKUP_REDOLOG r
on r.set_stamp = p.set_stamp
and r.set_count = p.set_count
join RMAN.RC_DATABASE d
on d.db_key = r.db_key
where d.name like ‘targetdb‘ <<< ———— <your DBname>
order by sequence# desc;

## Using TIME
run {
allocate channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
SET UNTIL TIME “to_date(‘April 07 2015 13:00:00′,
‘Mon DD YYYY HH24:MI:SS’)”;
DUPLICATE TARGET DATABASE to beta
SPFILE
SET CLUSTER_DATABASE=”FALSE”
SET db_name=’beta’
SET CONTROL_FILES=’+DATAC1′
SET DB_FILE_NAME_CONVERT ‘britt’,’beta’
SET db_recovery_file_dest_size ’10G’
SET db_recovery_file_dest=’+RECOC1′;
release channel c1;
release channel c2;
}

## Using SCN last before failure

run {
allocate channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
DUPLICATE TARGET DATABASE to beta
UNTIL SCN 1542189
SPFILE
SET CLUSTER_DATABASE=”FALSE”
SET db_name=’beta’
SET CONTROL_FILES=’+DATAC1′
SET DB_FILE_NAME_CONVERT ‘britt’,’beta’
SET db_recovery_file_dest_size ’10G’
SET db_recovery_file_dest=’+RECOC1′;
release channel c1;
release channel c2;
}

Catalog into RMAN

NOTES: Make sure you catalog the .snapshot folder into your TARGET database first.

** Important – make sure you have a backup of SPFILE & CONTROLFILE ** as this is two of the first things RMAN looks
for since we are modifying the spfile during the process. You should have RMAN configured as such: CONFIGURE CONTROLFILE AUTOBACKUP ON; This backups up both spfile and controlfile.

[XXX xxx_snap]$ ls
_data_D-XXXX_I-431051115_TS-SYSTEM_FNO-1_04q34a1f
_data_D-XXXX_I-431051115_TS-SYSAUX_FNO-2_05q34a1u

….

# By folder – recommended way
Catalog start with ‘/u03/.snapshot/xxx_snap/xxx_20150330’;

# Individually
catalog datafilecopy ‘/u03/andrew_exadata_poc/.snapshot/xxx_snap/xxx_20150330/_data_D-xxx_I-
431051115_TS-SYSTEM_FNO-1_04q34a1f';

….

# If you ever have to Uncatalog
RMAN> change datafilecopy | controfilecopy ‘<path/file>’ uncatalog;
# Connect to your TARGET first
[$] rman target / catalog rman/password@rmancatalog

….

connected to target database: BRITT (DBID=xxx)
connected to recovery catalog database

# PLEASE READ, use this VALIDATE and PREVIEW command on your TARGET. If it doesn’t work here, you will fail on your
duplicate.

The Duplication Process

# Be sure to connect to your TARGET then AUX
[oracle@hsdleddb01 dbs]$ rman target / catalog rman/password@rmancatalog
connected to target database: BRITT (DBID=xxxx)
RMAN> connect auxiliary sys/Password@beta
connected to auxiliary database: BETA (not mounted)

RMAN> run {
allocate channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
SET UNTIL TIME “to_date(‘April 07 2015 13:00:00′,
‘Mon DD YYYY HH24:MI:SS’)”;
DUPLICATE TARGET DATABASE to beta
SPFILE
SET CLUSTER_DATABASE=”FALSE”
SET db_name=’beta
SET CONTROL_FILES=’+DATAC1′
SET DB_FILE_NAME_CONVERT ‘britt’,’beta’
SET db_recovery_file_dest_size ’10G’
SET db_recovery_file_dest=’+RECOC1′;
release channel c1;
release channel c2;
}

….

# NOTE: This output can be lengthy, only the important pieces will be listed.

#### OUTPUT FROM TARGET ####

….

# OUTPUT – starts with SPFILE COPY and UPDATE

contents of Memory Script:
{
set until scn 1548402;
restore clone spfile to ‘/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebeta.ora';
sql clone “alter system set spfile= ”/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebeta.ora””;
}
….

sql statement: alter system set spfile= ”/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebeta.ora”

….

# OUTPUT – Sets the SCN under the hood and brings over the controlfile

set until scn 1548402;
sql clone “alter system set control_files =
”+DATAC1/beta/controlfile/current.745.876496xxxxxx” comment=
”Set by RMAN” scope=spfile”;
sql clone “alter system set db_name =
”BRITT” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”BETA” comment=

# OUTPUT – Watch to make sure It’s coming from the correct directory. In our case, .snapshot etc.. This will
automatically convert your datafiles into ASM.

….
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk
group only.
contents of Memory Script:
{
set until scn 1548402;
set newname for datafile 1 to
….
input datafile copy RECID=334 STAMP=876496944 file
name=/u03/andrew_exadata_poc/.snapshot/tues10/britt_20150330/_NFSFULL_data_D-BRITT_I-
431051115_TS-SYSAUX_FNO-2_8vq3nc05
destination for restore of datafile 00002: +datac1
channel c2: copied datafile copy of datafile 00002
output file name=+DATAC1/beta/datafile/sysaux.743.876496947
channel c2: restoring datafile 00003
….

# OUTPUT – Recovery Phase, very important to watch this to make sure it’s getting all of its archivelogs if not it will fail

( see recovery strategies in this document).
contents of Memory Script:
{
set until time “to_date(‘APR 07 2015 13:00:00′, ‘MON DD YYYY HH24:MI:SS’)”;
recover
clone database
delete archivelog;
}
executing Memory Script
….

# OUTPUT – sets the name back to what the AUXILIARY is after everything is completed.

sql statement: alter system set db_name = ”BETA” comment= ”Reset to original value by RMAN” scope=spfile
sql statement: alter system reset db_unique_name scope=spfile

# OUTPUT – END

 

Remove the old cataloged backups from your TARGET.

NOTE: You will need to do this for each datafile copy

# Uncatalog
RMAN> change datafilecopy ‘<path/file>’ uncatalog;

# Script to help, make sure you’re in correct directory
$> pwd

/u03/andrew_exadata_poc/.snapshot/tues11

# Script
ls | while read x; do echo “change datafilecopy ‘/u03/andrew_exadata_poc/.snapshot/tues11/$x’ uncatalog;”; done

# End Post

PowerShell (script) – Get SQL Server Backups Listing

Posted on Updated on

As a DBA there are many times I want to target a SQL Server without logging in and checking logs and running queries to get a list of backups.

I’ve put this Powershell Script together to help make my life a little easier since my current environment has 100’s of databases.

Place the .Ps1 file in an easy location to call, typically C:\Scripts or something similar.

The call:

Dialog:

Example results:
Name LastBackupDate LastLogBackupDate
—————– ————– —————–
AdventureWorks 1/12/2013 12:00:00 AM 1/12/2013 12:00:00 AM
AdventureWorksLT 1/12/2013 12:00:00 AM 1/12/2013 12:00:00 AM

Powershell Script

A special note for larger environments, there is a toggle you can use to switch to GRID VIEW which is a great way to sort the returned data. Notice I have it in the main code commented out. I don’t use it often but it is nice when I’m making a larger call. Remove the comment # for Out-Gridview and add # to the Format-Table -autosize.

Preview:

GridView

Hope this can help someone. It’s a great starter script for so many other related items.

–Commit Tran
–Andrew Brittain

PowerShell (script) – Get Disk Space and Free Space on a Windows Server

Posted on

This is a staple in my DBA scripts. I can’t tell you how many times this comes in handy. This will quickly give you the Disk Space levels on a server. Doesn’t need much more explaining than that.

The call:

Dialog:

Powershell Script:

Hope this can help make your day a bit easier

–COMMIT TRAN
–Andrew Brittain

PowerShell (script) – Run SQL Query in Database

Posted on Updated on

This post is a nice simple way to connect into a SQL Server and run a quick SQL query. I have purposely left it simple so anyone can use it as a template to build on and add features. I use it all the time for sp_configure, sys.database etc…

Also for bringing in a list of servers to iterate just substitute the (foreach) “see example” with a file that lists the names of the servers you want to target. Don’t forget to create the file…

Hope this can help make your day a bit easier!
COMMIT TRAN
–ANDREW