Creating RMAN Backup Catalog

It can soud repetitive, but always good to have notes about 🙂

  1. Create Schema for Catalog on CatalogDB:
-- Create the user
create user RMAN_MYDB identified by &PASS;
-- Grant/Revoke role privileges
grant recovery_catalog_owner to RMAN_MYDB;
-- Grant/Revoke system privileges
grant create session to RMAN_MYDB;

2. Create catalog and register database:

-- Conected to target Database via RMAN
RMAN> connect catalog rman_mydb/password@catdb.grepora.net:1521/catalogdb

connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Well done!
Matheus.

DDBoost: sbtbackup: dd_rman_connect_to_backup_host failed

A common error. It happens when the datadomain host or mtree is unreachable.
For the first situation, contact the OS/Network administrator. Is can be a firewall limitation, DNS miss (if using DNS hosting) or, in some cases, networks physically unreachable.

For the second case, try to [re]send user/pass to access datadomain:

Starting backup at 24-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=191 instance=almdbdw_1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Domain Boost API
allocated channel: ORA_SBT_TAPE_2
input datafile file number=00001 name=+DGMYDB/almdbdw/datafile/system.267.849463017
channel ORA_SBT_TAPE_1: starting piece 1 at 22-JUL-15
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 10/24/2015 10:03:50
ORA-19506: failed to create sequential file, name="a4qcme1l_1_1", parms=""
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
 sbtbackup: dd_rman_connect_to_backup_host failed
channel ORA_SBT_TAPE_1 disabled, job failed on it will be run on another channel

Sending user/password to acess data domain as follow and, after that, re-run the your action.

RUN {
ALLOCATE CHANNEL t1 TYPE SBT_TAPE PARMS 'BLKSIZE=1048576, SBT_LIBRARY=$ORACLE_HOME/lib/libddobk.so,ENV=(STORAGE_UNIT=$STORAGE_UNIT,BACKUP_HOST=$DATADOMAIN_HOST,ORACLE_HOME=$ORACLE_HOME)' FORMAT '%U-%d';
send 'set username $DDBOOST_USER password $PASSWORD servername $DATADOMAIN_HOST';
RELEASE CHANNEL t1;
}

Hugs!

Matheus.

Date Format in RMAN: Making better!

I know…
The date format on RMAN it’s not good, but it’s to make it better. Take a look:

db-server>rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 12 11:00:59 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=1286311368)
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
541 Incr 1 17.80M DISK 00:00:01 12-AUG-15
BP Key: 541 Status: AVAILABLE Compressed: NO Tag: BKPINCR_LV1_20150812_0923
Piece Name: +DGFRA/MYDB/backupset/2015_08_12/ncnnn1_bkpincr_lv1_20150812_0923_0.4613.887534683
Control File Included: Ckp SCN: 7301745 Ckp time: 12-AUG-15
RMAN> exit
Recovery Manager complete.

I’ts a simple NLS export on SO before access RMAN:

db-server>export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss';
db-server>rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 12 11:05:57 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=1286311368)
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
541 Incr 1 17.80M DISK 00:00:01 2015/08/12 09:24:42
BP Key: 541 Status: AVAILABLE Compressed: NO Tag: BKPINCR_LV1_20150812_0923
Piece Name: +DGFRA/MYDB/backupset/2015_08_12/ncnnn1_bkpincr_lv1_20150812_0923_0.4613.887534683
Control File Included: Ckp SCN: 7301745 Ckp time: 2015/08/12 09:24:41

Matheus.

(12c) RMAN-07539: insufficient privileges to create or upgrade the catalog schema

Another “The problem -> the fix” post. 🙂

# KB:
Upgrade Recovery Catalog fails with RMAN-07539: insufficient privileges (Doc ID 1915561.1)
Unpublished Bug 17465689 – RMAN-6443: ERROR UPGRADING RECOVERY CATALOG

# Problem

[oracle@databasesrvr dbs]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 21 14:17:09 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYDB (not mounted)
RMAN> connect catalog catalog_mydb/catalog_mydb@catalogdb
connected to recovery catalog database
PL/SQL package CATALOG_MYDB.DBMS_RCVCAT version 11.02.00.03 in RCVCAT database is too old
RMAN> upgrade catalog
RMAN-00571: =========================================================
RMAN-00569: ============== ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: =========================================================
RMAN-07539: insufficient privileges to create or upgrade the catalog schema
RMAN> exit

# Solution
– Connect on the catalog database with the 12c (local) OH:
(and don’t worry about the error on alter session).

[oracle@databasesrvr dbs]$ sqlplus sys/magicpass@catalogdb as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 21 14:21:02 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @?/rdbms/admin/dbmsrmansys.sql
alter session set "_ORACLE_SCRIPT" = true
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
alter session set "_ORACLE_SCRIPT" = false
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

– Then try to upgrade catalog again:

[oracle@databasesrvr dbs]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 21 14:21:27 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (not mounted)
RMAN> connect catalog catalog_mydb/catalog_mydb@catalogdb
connected to recovery catalog database
PL/SQL package CATALOG_MYDB.DBMS_RCVCAT version 11.02.00.03 in RCVCAT database is too old
RMAN> upgrade catalog;
recovery catalog owner is CATALOG_MYDB
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> upgrade catalog;
recovery catalog upgraded to version 12.01.00.02
DBMS_RCVMAN package upgraded to version 12.01.00.02
DBMS_RCVCAT package upgraded to version 12.01.00.02.

Matheus.

ORA-19751: could not create the change tracking file

Let’s make it simple to solve the problem:

# Error:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file: '+DGDATA/mydb/changetracking/ctf.470.859997781'
ORA-17502: ksfdcre:1 Failed to create file +DGDATA/mydb/changetracking/ctf.470.859997781
ORA-17501: logical block size 4294967295 is invalid
ORA-15001: diskgroup "DGDATA" does not exist or is not mounted
ORA-17503: ksfdopn:2 Failed to open file +DGDATA/mydb/changetracking/ctf.470.859997781
ORA-15001: diskgroup "DGDATA" does not exist or is not mounted
ORA-15001: diskgroup "DGDATA" does not exist or is not mounted

# Solution:

SQL> alter database disable BLOCK CHANGE TRACKING;
Database altered.
SQL> alter database open;
Database altered.

Then, after everything be OK, you fix the situation recrating a BCTF:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DGDATANEW';

MTFBWU!

Matheus.

Database Migration/Move with RMAN: Are you sure nothing is missing?

Forced by the destiny to make a migration using backup/restore (with a little downtime), how to be sure nothing will be lost during the migration?
Here is a way: Create your own data just before migrating. 🙂

Seems like a kludge and it is.. haha.. But it works. Take a look:

# Original Database

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 2689060864 bytes
Fixed Size 2229520 bytes
Variable Size 1996491504 bytes
Database Buffers 671088640 bytes
Redo Buffers 19251200 bytes
Database mounted.
Database opened.
SQL> create table matheus_boesing.migration (text varchar2(10));
Table created.
SQL> insert into matheus_boesing.migration values ('well done!');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> shu immediate;
SQL> exit;
$ rman target /
connect catalog rman_mydb/password@catalogdb
run { backup archivelog all;}

# Destination Database

$ rman target /
connect catalog rman_mydb/password@catalogdb
run { recover database;}
$ sqlplus / as sysdba
SQL> select count(1), to_char(CHECKPOINT_TIME, 'DD/MM/YYYY HH24:MI:SS') from V$DATAFILE_HEADER t
group by to_char(CHECKPOINT_TIME, 'DD/MM/YYYY HH24:MI:SS') order by 2;
COUNT(1) TO_CHAR(CHECKPOINT_
---------- -------------------
51 27/06/2015 22:15:28
-- All datafiles with synchronized headers...
SQL> alter database open read only; 
-- If needed, you can do more recover, this way...
Database altered.
SQL> select * from matheus_boesing.migration;
TEXT
----------
well done!
-- Means no more recover is needed :)
SQL> shutdown immediate;
SQL> alter database open resetlogs;
Database altered.

And be Happy! 😀

Matheus.

RMAN-06059: expected archived log not found

# Error

RMAN-03002: failure of backup command at 06/28/2015 14:56:30
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file +DGFRA/corpdb/archivelog/2015_06_27/thread_1_seq_20198.1192.883524615
ORA-17503: ksfdopn:2 Failed to open file +DGFRA/corpdb/archivelog/2015_06_27/thread_1_seq_20198.1192.883524615
ORA-15012: ASM file '+DGFRA/corpdb/archivelog/2015_06_27/thread_1_seq_20198.1192.883524615' does not exist

# Solution
First of all, you need to know which files exists or not:

RMAN> CROSSCHECK ARCHIVELOG ALL;

Then, clear the missing and run another backup.

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

It’s hardly recommended that you make a full backup after that, to ensure you have a recoverable state.

Matheus.