Wednesday, May 25, 2016

RMAN ORA-19606: Cannot copy or restore to snapshot control file

When RMAN needs to resynchronize the recovery catalog with a read-consistent version of the control file, it creates a temporary snapshot control file. RMAN needs a snapshot control file when resynchronizing with the recovery catalog or when making a backup of the current control file.
The default location for the snapshot control file is platform-specific and depends on the Oracle home of each target database. For example, the default filename on some Linux platforms is $ORACLE_HOME/dbs/snapcf_@.f


RMAN> delete obsolete;

starting full resync of recovery catalog
full resync complete
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=188 device type=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     1123728 2016-JAN-19 14:33:21 /software/oratest/product/11.2.0/dbs/snapcf_TESTDB.f
Backup Set           1123715 2015-NOV-06 10:55:47

Do you really want to delete the above objects (enter YES or NO)? yes
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_1 channel at 05/25/2016 11:57:24
ORA-19606: Cannot copy or restore to snapshot control file


SOLUTION

Not sure why this happens, but the solution is to configure a different snapshot controlfile name so that RMAN can use that, thus allowing you to remove the “old” one. Then configure back to what you had:
configure snapshot controlfile name to '/tmp/it.f';
crosscheck controlfilecopy "/oracle/product/11.2.0.2/dbs/snapcf_P10AC.f";
delete expired controlfilecopy "/oracle/product/11.2.0.2/dbs/snapcf_P10AC.f";
delete noprompt obsolete;
configure snapshot controlfile name to '/oracle/product/11.2.0.2/dbs/snapcf_P10AC.f';

ORA-19806: cannot make duplex backups in recovery area

when running "backup filesperset 10 archivelog all not backed up 2 times;", encountered below error :

channel ORA_DISK_1: starting piece 1 at 2016-MAY-25 14:59:18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/25/2016 14:59:18
ORA-19806: cannot make duplex backups in recovery area

Initially thought FRA is full, but it is not.

SYS@PELD> @@chk_fra_usage

NAME
------------------------------------------------------------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/dbfra/testdb01d
 5.3687E+10  959854592                 0              20



FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                       1.79                         0              20
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.


Googled and found below in http://www.allinterview.com/showanswers/41216/ora-19806-cannot-make-duplex-backups-in-recovery-area.html


this is because you might have set the parametes
1)configure datafile backup copies for device type disk to
2)configure archivelog backup copies for device type disk to

now the solution is to clear the above two configuration
settings i,e...
A)
1)configure datafile backup copies for device type disk to
clear;
2)configure archivelog backup copies for device type disk to
clear;
now it will work ................... 


Tried to reset back to 1 copy, and it did works for me now. 


RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK clear;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
RMAN configuration parameters are successfully reset to default value




RMAN can duplex backups to either disk or tape, but cannot duplex backups to tape and disk simultaneously. When backing up to tape, ensure that the number of copies does not exceed the number of available tape devices. 

This is could be related to I am using FRA as backup destination.

Monday, May 23, 2016

simpler error message for RMAN tested against NOARCHIVELOG in 12c

Before actual cold backup for development database, I skip the shutdown command to test the script deployment, without actually bring down the database (during office hour).

I expected to see below error message prior to 12c.

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/04/2016 17:28:14
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run

However, this is repeated for each backup set while travelling all data files.

channel ORA_DISK_1: SID=244 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=251 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/04/2016 17:28:14
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/04/2016 17:28:14
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/04/2016 17:28:14
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/04/2016 17:28:14
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/04/2016 17:28:14
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/04/2016 17:28:15
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/04/2016 17:28:15
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_2 channel at 05/04/2016 17:28:15
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

Recovery Manager complete.


Good to see , it is getting simpler in 12c as shown below, just one word.

Starting backup at 2016-MAY-20 17:53:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=368 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=250 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=366 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=131 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 05/20/2016 17:53:13
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

Recovery Manager complete.


reduce AWR retention but hit ORA-13541

Need to reduce AWR retention due to no disk space in this development database.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from dba_hist_wr_control;

      DBID
----------
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
TOPNSQL        CON_ID
---------- ----------
2723810418
+00000 01:00:00.0
+00042 00:00:00.0
DEFAULT             0




SQL> begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(11520,60); end;
  2
  3  /
begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(11520,60); end;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (3628800) greater than retention
(691200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 198
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 246
ORA-06512: at line 1


SQL> SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW';


MOVING_WINDOW_SIZE
------------------
                42

SQL>
SQL> exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

PL/SQL procedure successfully completed.

SQL> SELECT moving_window_size
FROM   dba_hist_baseline
  2    3  WHERE  baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
                 7

SQL>
SQL> begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(11520,60); end;
  2  /

PL/SQL procedure successfully completed.

SQL>  select * from dba_hist_wr_control;

      DBID
----------
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
TOPNSQL        CON_ID
---------- ----------
2723810418
+00000 01:00:00.0
+00008 00:00:00.0
DEFAULT             0


References:
http://www.sqlpanda.com/2013/02/ora-13541-system-moving-window-baseline.html

rman shell script but not connecting to recovery catalog

I have one RMAN backup script works fine , but when running for a 12c.1 database, it keeps showing me below in logfile

"using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DEV"


And this the working fine shell function.


func_cold_db_backup() {
${ORACLE_HOME}/bin/rman target /  cmdfile='/oracle/UAT/ops/exec/cold_backup.rman' log=$logfile <>$logfile 2>&1
${ORACLE_HOME}/bin/rman target /   log=$logfile <>$logfile 2>&1
connect catalog rman/pwdxxxxx@RCAT
@/oracle/UAT/ops/exec/cold_backup.rman
EOF


When manually try the command , it works fine and shows me connected to RCAT.

${ORACLE_HOME}/bin/rman target /  catalog rman@RCAT
connected to target database: UAT (DBID=27281018)
connected to recovery catalog database


So the only difference is cmdfile parameter.

As such, I rewrite the function and works "better" now for this database.


func_cold_db_backup() {
${ORACLE_HOME}/bin/rman target /   log=$logfile <>$logfile 2>&1
connect catalog rman/pwdxxx@RCAT
@/oracle/UAT/ops/exec/cold_backup.rman
EOF

Friday, May 13, 2016

two ways to decommission a database backed up by RMAN

1. If follow proper procedure, backups should be deleted before unregistrating from recovery catalog

Either using with RMAN command "delete backup", or
during dropping database with "drop database including backups"

2. However, if we forget to follow above sequence, we still have manual way to achieve the same.

2.1  Manually delete backups from DISK, usually they should be under FRA.
2.2 Connect to catalog database without specifying target

     rman rman/password@RCAT

    > set dbid=
    > unregister database;



e.g.




> rman  catalog rman@RCAT

Recovery Manager: Release 11.2.0.3.0 - Production on Fri May 13 15:20:03 2016

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

recovery catalog database Password:
connected to recovery catalog database

RMAN> set dbid=649569281;

executing command: SET DBID
database name is "CTCG" and DBID is 649569281

RMAN> unregister database;

database name is "CTCG" and DBID is 649569281

Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog

RMAN> exit



double-quoted not accept in 10g rman, but not 11g

In my cold backup shell script, there is below line, which works fine in 11g.

A double quote inside single quote , to pass ORACLE_HOME path  to &1 of cold_backup.rman

${ORACLE_HOME}/bin/rman target /  cmdfile='./cold_backup.rman "/software/oraabc/product/11.2.0"' log=$logfile <>$logfile 2>&1
connect catalog rman/xxxxxx@RCAT1
EOF

But it is giving me below error , when running for 10g database.


Both single and double quotes (' or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00552: syntax error in command line arguments
RMAN-01009: syntax error: found "double-quoted-string": expecting one of: "append, at, auxiliary, catalog, cmdfile, clone, checksyntax, debug, log, msglog, mask, msgno, nocatalog, pipe, rcvcat, script, slaxdebug, send, target, timeout, trace"
RMAN-01007: at line 0 column 52 file: command line arguments
Recovery Manager: Release 10.2.0.2.0 - Production on Wed May 11 15:57:27 2016

This is a funny finding.