DBCA “Recovery manager failed to restore datafiles”

Hi all,

If that’s the 5th Blog Post with the same title one open, don’t close, READ THIS ONE!

This one is different.

It’s actually another case about Oracle throwing generic errors for DBCA where 99% of times it’s the same error, so all blogs are different but the same in essence, and none resolve your problem. So, let’s go by parts:

The Error from Client:

DBCA_Error

 

Generic Case (if this is the first blog you open about the subject):

  • Make sure the file $ORACLE_HOME/bin/oracle has privilege set to chmod 6751 in both (ASM and DB) homes. it should look like this:
[oracle@PROD01 bin]$ cd /u01/app/oracle/product/19c/db/bin
[oracle@PROD01 bin]$
[oracle@PROD01 bin]$ ls -ltr oracle*
-rwsr-s--x. 1 oracle asmadmin 441253104 Aug 27 22:29 oracle
  • If you are not sure, set it accordingly:
cd $ORACLE_HOME/bin && chmod 6751 oracle
  • Not yet? Check the disks assigned to ASM privileges and groups:
kfod status=TRUE asm_diskstring='/dev/asm*' disk=ALL
  • Ater all this, still not working? Go for the atypical case below:

 

Atypical Case (Exception)

After some struggle and no success, I started validating everything I could. Some piece of words before the silver bullet here:

Noticed I knew you do have ASM? How come that? Well, most likely the error below the ASM happens at the point the DBCA runs a RMAN restore to create the database base files and metadata. This most likely happens at the time the write is happening on the ASM, once this is the most unstable process involved.

By looking deeper on the installation logs I could see:

[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=channel ORA_DISK_1: restoring datafile 00001 to +DATA
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=channel ORA_DISK_1: reading from backup piece /ora01/app/oracle/product/19c/db/assistants/dbca/templates/Seed_Database.dfb
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=channel ORA_DISK_1: ORA-19870: error while restoring backup piece /ora01/app/oracle/product/19c/db/assistants/dbca/templates/Seed_Database.dfb
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=ORA-19504: failed to create file "+DATA"
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=ORA-17502: ksfdcre:4 Failed to create file +DATA
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=ORA-15001: diskgroup "DATA" does not exist or is not mounted
[Thread-527] [ 2020-08-27 23:50:04.942 PDT ] [RMANUtil$RMANUtilErrorListener.handleError:1386] ERROR=ORA-01017: invalid username/password; logon denied

Bingo, so it’s a password issue?

Well, I’m creating the database and this actually matches with all the chmod 6751 thing…

What then?

Well, after a while going crazy validating passwd files and so one, realized something about the oracle user:

[oracle@PROD01 bin]$ id -a
uid=500(oracle) gid=501(oinstall) groups=501(oinstall),10(wheel),203(dba),503(asmadmin),504(asmoper),525(madhoc) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@PROD01 bin]$ grep oracle /etc/group
wheel:x:10:oracle
asmadmin:x:503:oracle
asmoper:x:504:oracle
madhoc:x:525:oracle

Can you see the oracle user is part of the oinstall group but not really appearing on /etc/group? Also not in osusergroup dba.

Well, let’s force it?

[oracle@PROD01 bin]$ sudo su -
Last login: Fri Aug 28 14:13:21 PDT 2020 on pts/3
[root@DMSDB1PA ~]# usermod -g oinstall -G oinstall,dba,asmadmin,asmoper,madhoc oracle
[root@PROD01 ~]# id oracle
uid=500(oracle) gid=501(oinstall) groups=501(oinstall),10(wheel),203(dba),503(asmadmin),504(asmoper),525(madhoc)
[root@PROD01 ~]# grep oracle /etc/group
wheel:x:10:oracle
dba:x:203:oracle
asmadmin:x:503:oracle
asmoper:x:504:oracle
oinstall:x:501:oracle
madhoc:x:525:oracle
[root@PROD01 ~]#

Well done!

Now try running DBCA again. That was a very tricky issue to find.

Know something else? At the point I was writing this I decided to have another look and ended up finding this MOS note: “ORA-17502 /ORA-01017: invalid username/password; logon denied ” While Creating 19c Database (Doc ID 2545858.1). We have a bug for it: BUG:29821687 – ORA-17502 /ORA-01017: INVALID USERNAME/PASSWORD; LOGON DENIED ” WHILE CREATING 19C DATABASE

You have the workaround already though. Go champs!

Hope it helps you, cheers!

Amazon RDS: How to perform RMAN operations?

Hi all,

Need to performa any RMAN Operation from Amazon RDS? Maybe something like validating backups?

Well, we have some options under by using RDSADMIN_RMAN_UTIL package. See below an example for validate backup:

BEGIN
 rdsadmin.rdsadmin_rman_util.validate_database(
 p_validation_type => 'PHYSICAL+LOGICAL',
 p_parallel => 4,
 p_section_size_mb => 10,
 p_rman_to_dbms_output => FALSE);
END;
/

More info about it: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.RMAN.html

Hope it helps, cheers!

Adding new PDB to a CDB with Standby

On Oracle 12c we got a new feature on RMAN which allows you to restore database files, over the network, from a physical standby database by using the FROM SERVICE clause of the RESTORE command.

So in a Data Guard environment in case you add a new Pluggable database (PDB) you don’t need to duplicate the entire Container database (CDB) again. You can restore only the new PDB to the physical standby. Cool right, less work, faster work. I would prefer this as RMAN would do all its magic and we would not need to manual copies to renames.

On the practical side, this would be done in case you need to add a new PDB in your Data Guard configuration.

Stop the archive log apply on the standby using DG broker

validate database verbose PRIMARY;
validate database verbose STANDBY;
edit database 'STANDBY' set state='apply-off';
On the standby database restore the new PDB

rman target /
run{
allocate channel prim1 type disk;
set newname for pluggable database NEW_PDB to new;
restore pluggable database NEW_PDB from service PRIMARY;
switch datafile all;
}

– Enable recovery on the new PDB, if you running an Active DataGuard you need to stop it and start the standby database in mount, also if you get ORA-1113 make sure you are in MOUNT stage.

sqlplus / as sysdba
shtudown immediate
startup mount
alter session set container=NEW_PDB;
alter pluggable database enable recovery;
exit

– Enable archive log apply

edit database 'STANDBY' set state='apply-on';
validate database verbose PRIMARY;
validate database verbose STANDBY;

More information you can find on the MOS Note Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant (Doc ID 1916648.1)

Hope it helps,
Elisson Almeida

/bin/rm: cannot execute [Argument list too long]

Hey all!

Just a quickie and useful thing today. How many times you found this?

/bin/rm: cannot execute [Argument list too long]

Ok, so, first thing: Is it related to Oracle logs? If so, you may want to ADCRI. Check this post for more info: ADRCI Retention Policy and Ad-Hoc Purge Script for all Bases.

If not, you may solve this using find with rm. Ok, but want to keep the most recent files?

Some examples for you, removing audit files:

# Remove older then 1 day:

find /oracle/greporadb/admin/greporadb/adump -name "*.aud" -mtime +1 -exec rm {} \;

# Remove older then 1 hour:

find /oracle/greporadb/admin/greporadb/adump -name "*.aud" -cmin +60 -exec rm {} \;

More“/bin/rm: cannot execute [Argument list too long]”

GGATE ABBENDED: ORA-00308: Cannot Open Archived Log

Hi all!
Ok, so this is one of the most common issues for GGate administration. How to solve it? Easy thing.

First let’s understand what it means: It means the redologs don’t have the required information (assuming integrated) and you have already deleted archivelogs the extract needs. Why? Probably because you already backed up those archivelogs and they were not needed for the database anymore.

Unfortunately we don’t have any kind of ARCHIVELOG DELETION POLICY to guarantee extracts had already read it, like we have for Dataguard. So, what can we do?

Restore the missing archivelogs.

But first let’s confirm on the errors. Some examples:

ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, ext1.prm: Opening ASM file +ARCH/2_11055_742755632.dbf in DBLOGREADER mode: (308) ORA-00308: cannot open archived log '+ARCH/2_11055_742755632.dbf' ORA-17503.

or

ERROR OGG-01028 Oracle GoldenGate Capture for Oracle,ext1.prm: Getting attributes for ASM file +ARCH/2_86720_716466928.dbf,

SQL : (15056)

ORA-15056: additional error message ORA-15173: entry '2_86720_716466928.dbf' does not exist in directory '/...


SOLUTION
:

Restore all archive logs starting from recovery checkpoint until the current checkpoint and restart the extract:
More“GGATE ABBENDED: ORA-00308: Cannot Open Archived Log”

Recover Standby Using an Incremental Backup

Hey all,
I know is there is a lot of posts about it in the internet, but I’m doing mine about it. The main reason is that the other has lots of steps and outputs and I miss simplicity. Hope you enjoy this one as well.

But why?
Sometimes if the standby database fails for any reason – for example when doing NOLOGGING actions in the primary database – then it can be necessary to recover the datafiles from the incremental backup as the archive logs will not contain the right information.

The recovery of the standby is relatively simple.

1. Take a cumulative incremental backup on the primary

Example

Either take a level 1 backup

backup as compressed backupset incremental level 1 cumulative database filesperset 15;

Or get the scn to recover from on the standby using the lower value from following SQLs

select min(f.fhscn) from x$kcvfh f, v$datafile d where f.hxfil =d.file# and d.enabled != 'READ ONLY';
SELECT CURRENT_SCN FROM V$DATABASE;

2. Run the backup using the SCN

backup as compressed backupset incremental from scn &scn;

3. Create a copy of controlfile for Standby

alter database create standby controlfile as '/tmp/newctfl.ctl';

4. Copy incremental backup files and controlfile to standby

5. Restore copied Controlfile

Example

SQL> shutdown abort; 
SQL> startup nomount; 
RMAN> restore controlfile from '/tmp/newctfl.ctl'; 
SQL> alter database mount;

6. Catalog the backup files in the standby controlfile

Example

catalog start with '/tmp/rmanbackup/';

7. Recover the database with no redo

Example

recover database noredo;

8. Start the managed recovery

Example

alter database recover managed standby database using current logfile disconnect from session;

This is also an easy alternative to the lot more complex described in MOS Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes (Doc ID 958181.1).

Hope you enjoy!
Cheers!

DFS Lock Handle During RMAN Backup on Standby Database

Hi all!
Some time ago I faced some session on wait event DFS Lock Handle during a RMAN Backup on Standby Database. Btw, running backup on a Standby is a very interesting approach to avoid running backups on primary, so all nodes can be fully dedicated to application ends.

Turn that in my situation I noticed there was a lock with my apply process. The fix? Quite simple: Cancel apply process, run backup, and restart apply again. In my case, using DG Broker:

dgmgrl
connect / 
show configuration
show database 
edit database  set state = 'apply-off';

— Run Backup

edit database set state = 'apply-on';

 

Hope it helps!
Cheers!

ORA-00001: unique constraint (RMAN.CKP_U1) violated

Hey,
Don’t create so much expectations on this post.

This is because I don’t exactly fixed the issue, but workarounded…
The thing is: This error is caused in catalog database, so the workaround is simple: do a RMAN-nocatalog, I mean, simply don’t connect in catalog to perform the backup.

After completing the backup, I’d suggest you to force a synchronization with command “RESYNC CATALOG“. In worst case, on next execution the implicit resync will fix everything. ūüôā

There is no bigger explanations on this, but you can same workaround in MOS Bug 12588237 – RMAN-3002 ORA-1: unique constraint (ckp_u1) violated after dataguard switchover (Doc ID 12588237.8).

And this is it for today!
See you next week!

RMAN Raise ORA-19913 ORA-28365 On Restore from Cloud Backup

First I think was some error with Database Backup To Cloud, when testing. Then I realized it was a simple mistake by myself.

Let me show you. First trying to restore datafile:

[oracle@mydbsrvr archivelogs]$ rman target /
RMAN> restore datafile 6;
Starting restore at 03-MAY-2016 20:00:30
using channel ORA_SBT_TAPE_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=178 device type=DISK
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00006 to /db/u1001/test/cloud_test/test_restore.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0sr4mdun_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/03/2016 20:00:34
ORA-19870: error while restoring backup piece 0sr4mdun_1_1
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

Ok, it might happen because I forgot to set encryption password:

RMAN> SET ENCRYPTION ON IDENTIFIED BY "matheusdba" only;
executing command: SET encryption
RMAN> restore datafile 6;
Starting restore at 03-MAY-2016 20:00:30
using channel ORA_SBT_TAPE_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=178 device type=DISK
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00006 to /db/u1001/test/cloud_test/test_restore.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0sr4mdun_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/03/2016 20:00:34
ORA-19870: error while restoring backup piece 0sr4mdun_1_1
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

It hapen again?
This point I suspect some kind of bug… But it was my mistake and is not related to Cloud, but to Encryption use. To undestand:
For Backup: Use ENCRYPTION
For Restore/Recover: Use DECRYPTION

Obviously, but take me a minute to realize…

Setting decryption, and problem solved:

RMAN> set DECRYPTION identified by "matheusdba";
executing command: SET decryption
RMAN> restore datafile 6;
Starting restore at 03-MAY-2016 20:00:58
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00006 to /db/u1001/test/cloud_test/test_restore.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0sr4mdun_1_1
channel ORA_SBT_TAPE_1: piece handle=0sr4mdun_1_1 tag=TAG20160503T193239
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
Finished restore at 03-MAY-2016 20:01:02

See ya!
Matheus.

Oracle Database: Compression Algorithms for Cloud Backup

Hi all!
Again talking about cloud backups for on-premise databases: An important aspect is to compress the data, so network consumption might be reduced once less data is being transfered.Compress_PDF
It’s also important to evaluate CPU consumption. As higher compress algorithm is, as much CPU it uses. So, pay attention!

Now, how to choose the compression algorithm? Here the options Oracle give us:

SQL> col ALGORITHM_NAME for a15
set line 200
SQL> select ALGORITHM_NAME,INITIAL_RELEASE,TERMINAL_RELEASE,ALGORITHM_DESCRIPTION,ALGORITHM_COMPATIBILITY  from v$rman_compression_algorithm;
ALGORITHM_NAME INITIAL_RELEASE    TERMINAL_RELEASE   ALGORITHM_DESCRIPTION                                            ALGORITHM_COMPATIB
-------------- ------------------ ------------------ ---------------------------------------------------------------- ------------------
BZIP2          10.0.0.0.0         11.2.0.0.0         good compression ratio                                           9.2.0.0.0
BASIC          10.0.0.0.0                            good compression ratio                                           9.2.0.0.0
LOW            11.2.0.0.0                            maximum possible compression speed                               11.2.0.0.0
ZLIB           11.0.0.0.0         11.2.0.0.0         balance between speed and compression ratio                      11.0.0.0.0
MEDIUM         11.2.0.0.0                            balance between speed and compression ratio                      11.0.0.0.0
HIGH           11.2.0.0.0                            maximum possible compression ratio                               11.2.0.0.0

How to identify our compression algorithm?

RMAN> show COMPRESSION ALGORITHM;
RMAN configuration parameters for database with db_unique_name EZM_PRFL are:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

And how to change it?

RMAN> CONFIGURE COMPRESSION ALGORITHM 'HIGH';
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored
RMAN> show COMPRESSION ALGORITHM;
RMAN configuration parameters for database with db_unique_name EZM_PRFL are:
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

Ok,
But how to evaluate my compression ratio?

See the difference between INPUT_BYTES_DISPLAY and OUTPUT_BYTES_DISPLAY columns from the query:

prddb> col STATUS for a10
prddb> col INPUT_BYTES_DISPLAY for a15
prddb> col OUTPUT_BYTES_DISPLAY for a15
prddb> col TIME_TAKEN_DISPLAY for a20
prddb> SELECT SESSION_KEY,
2         INPUT_TYPE,
3         STATUS,
4         TO_CHAR(START_TIME, 'mm/dd/yy hh24:mi') start_time,
5         TO_CHAR(END_TIME, 'mm/dd/yy hh24:mi') end_time,
6  --       ELAPSED_SECONDS / 3600 hrs,
7         COMPRESSION_RATIO,
8         INPUT_BYTES_DISPLAY,
9         OUTPUT_BYTES_DISPLAY,
10         TIME_TAKEN_DISPLAY
11    FROM V$RMAN_BACKUP_JOB_DETAILS
12    where input_type like 'DB%'
13   ORDER BY SESSION_KEY
14  /SESSION_KEY INPUT_TYPE    STATUS     START_TIME     END_TIME       COMPRESSION_RATIO INPUT_BYTES_DIS OUTPUT_BYTES_DI TIME_TAKEN_DISPLAY
----------- ------------- ---------- -------------- -------------- ----------------- --------------- --------------- --------------------
          2 DB FULL       COMPLETED  04/22/16 12:59 04/22/16 13:06        6,84838963     4.26G         636.50M       00:06:57
          9 DB FULL       COMPLETED  04/22/16 13:47 04/22/16 13:54        6,83764706     4.26G         637.50M       00:06:37
         14 DB FULL       COMPLETED  04/22/16 16:26 04/22/16 16:33        6,84189878     4.26G         637.25M       00:06:48

KB: https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmconfa.htm#BRADV89466

Done?
If you have any question, please let me know in the comments! ūüėČ
Matheus.