2

We have a local development database. Our customer has a production database.

Both databases started the same - same tablespaces, tables, users, schemas, indexes, views, etc.

Months went by. They put data in their database. We put different data in our database. But all the structure stuff should be the same. The OS and database software is also the same.

We want to bring the two databases into sync, so that our development database has all the data from their production database.

They've provided us with 76 .BKP files. Those files are now sitting on a drive connected to the server that hosts our development database. How do we actually get the data out of these files and into our database? This is all oracle 12c, if that's important.

Looking online I saw people talking about rman. I tried starting it up like this:

$ rman target=/

It appears to have properly connected to the existing database.

All of the .BKP files are located in /u01/dumpfilesgohere. In Oracle SQL Developer, under the SYS account for the database, there's a directory named IMPORT_FILES for that, thus why I thought it would be a good place to put the files.

But when I run SHOW ALL; in rman, it doesn't show that directory anywhere:

RMAN> SHOW ALL;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ECLS are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/db_1/dbs/snapcf_ECLS.f'; # default

Edit: Here's the list of names of files I was provided with:

 9 files matching: O1_MF_ANNNN_TAG20161218T020128_D5CV????_.BKP
 3 files matching: O1_MF_ANNNN_TAG20161218T042945_D5D45???_.BKP
63 files matching: O1_MF_NNND0_TAG20161218T021024_D5??????_.BKP
 1 unique file:    O1_MF_S_930889816_D5D475W3_.BKP

Edit #2: After trying to get the DBID and DB Name of the backup files by attempting to catalog them with RMAN, I decided to try changing the DBID and DB Name by following this article. The actual steps I did are below:

  1. $ sqlplus / as sysdba
  2. SQL> shutdown immediate
  3. SQL> startup open read only
  4. SQL> @/u01/change_dbid.sql This file has all the code of section 2 and 3 from the article above. It seems to have run successfully on my server, despite the instructions being written for Oracle 11 and me using Oracle 12c.
  5. SQL> create pfile from spfile;
  6. I made a copy of the file at /u01/app/oracle/product/12.1.0/db_1/dbs/initOLDNAME.ora with OLDNAME changed to match the name from the backup, and I changed the line *.db_name='OLDNAME' to have the name from the backup. I changed nothing else about the file.
  7. SQL> shutdown immediate
  8. SQL> startup mount pfile=initNEWNAME.ora
  9. SQL> alter database open resetlogs;
  10. SQL> create spfile from pfile='initNEWNAME.ora;
  11. SQL> startup force
  12. SQL> exit
  13. $ rman target=/
  14. RMAN> catalog start with '/u01/dumpfilesgohere/'; It listed those 76 .BKP files, I told it to go, and it told me it successfully cataloged all the files.
  15. RMAN> restore database;

Here's the output from step #15:

Starting restore at 26-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3 device type=DISK

creating datafile file number=1 name=/u01/app/oracle/oradata/OLDNAME/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/26/2017 10:50:46
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/OLDNAME/system01.dbf'

I'm stuck again - I'm not sure how to proceed from here.

Maybe changing the DB ID and name was a mistake...

Improve this question
3
  • Are these RMAN backups or datapump exports ? Do you have any backup logs accompanying those .BKP files ?
    – A_V
    Commented Jan 25, 2017 at 15:40
  • @A_V - I believe these are RMAN backups. Historically, we've copied data between our environments by using datapumps, but it would take several days. They told us that they wanted to use these files going forward because it only takes them hours instead of days to produce them.
    – ArtOfWarfare
    Commented Jan 25, 2017 at 15:53
  • dba.stackexchange.com/questions/124327/…
    – atokpas
    Commented Jan 28, 2017 at 16:06

1 Answer 1

Reset to default
2

You're on the right track. You just need to catalog the files so that rman is aware of them.

At a rman prompt:

CATALOG START WITH '/u01/dumpfilesgohere/';

(the trailing slash is important).

Improve this answer
7
  • Okay - thank you for that. Now I'm hitting this issue - it's not cataloging any of the files. For every file, it spat out this error message: RMAN-07518: Reason: Foreign database file DBID: 1345519754 Database Name: COOVZP. How can I fix this? If possible, I'd rather not change our database SID... we already have dozens of machines successfully connecting to the database and I don't want to have to go through and change them all. I'm fine with changing the DBID... I don't think that's used anywhere.
    – ArtOfWarfare
    Commented Jan 25, 2017 at 15:58
  • @ArtOfWarfare You need to restore the controlfile first. Provided the production environment is taking a control file autobackup, have them send you that backup (they may have already sent you that piece, or the controlfile backup could have been included with the database backup), then: RMAN> shutdown immediate; startup nomount; restore controlfile from '<full path of controlfile backup>'; alter database mount; catalog start with... ;
    – Kris Johnston
    Commented Jan 25, 2017 at 17:40
  • @KrisJohnston - I edited my question to include the names of all the files I was provided with at the end. Do you think any of those might be the control file?
    – ArtOfWarfare
    Commented Jan 25, 2017 at 17:45
  • @KrisJohnston - Also, what would restoring the controlfile actually do? Would that change the SID of the database?
    – ArtOfWarfare
    Commented Jan 25, 2017 at 17:52
  • @ArtOfWarfare There is no way to tell from the filenames. If you can get the other team to do a RMAN> show all; from the production environment, that will tell you how the CF autobackup is named: from the show all line: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT... The CF autobackup is usually pretty small, so odds are, it would be the smallest file. If the CF backup is part of the database backup, it may be included in the last file (based on the last write date/time).
    – Kris Johnston
    Commented Jan 25, 2017 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.