8.1.6 cloned with 8.1.7 database; v$ views and tables missing or corrupt

Perhaps you need to create a new 8.1.7 ORACLE_HOME for your Dev database to run under, then try re-cloning from PROD again.

Can you tell me how to go about doing this ? Do you know for sure that this will work ?

The simplest way is to install Oracle 8.1.7 to the Dev environment or patch the current 8.1.6 install to 8.1.7.  Either way this will give you a working 8.1.7 environment in Dev.  Then you select the backup you want from PROD and restore into DEV.  Please consult your DBA.  This is a very simple operation.

We have no DBA. I am the DBA :(
Thanks for your input. I will get back to you as soon as I have something going. In the meantime if you know of any documents which have the step-by-step procedure to do this stuff, that would be great.
masaimara

Oracle installation guides should have the info you are after.

A quick work-around, provided the DEV and PROD servers are same hardware and OS, would be to copy the whole 8.1.7 Oracle Home from Prod to Dev.
Then re-link all Oracle executables - $ORACLE_HOME/bin/relink
Then repeat your clone process

I am trying to take a backup of 8.1.6 Home (before copying 8.1.7) and it is taking too long because of the trc files under bdump. Are these files important ? Can I skip them ?

Hi,
I got the copy of the tar file done but while trying to untar it it gave me a lot of errors saying "cannot create" and "cannot set time on: not owner". Is there something I am missing ?
Thanks,
masaimara

You can forget about the trace files - these will be specific to PROD database.

You need to have permission to create the directories on the DEV server. Ask your Unix admin person to either give you permissions ir get him/her to untar file for you.
BTW , you will need to create them as the ORACLE user

I tried to do the upgrade using u080106.sql. It didn't work as the systems tables/views are corrupted. Is there a way I can replace this database with a new database ? May be copy data files, log files from other database ?

I did the tar and untar as the ORACLE user with all the permissions - I think... I will check with the UNIX admin. Since no patch or sql are working against the database, the only hope I have is to replace this database with a new one. Could you please tell me if there is a way to replace files from another database ?

Sorry, I may have misinterpreted your original posting. I missed the 'accidentally' cloned bit.

However, it is good practice to have your Development database running under the same Oracle (and OS) versions as your Prod database. That way, there will be no surprises when you promote (move) application code from Dev to Prod. So if you have the 8.1.7 Oracle Home on your Dev Server, then to complete the copy process use the following steps.

1) On your Prod Database issue the following command
alter database backup controlfile to trace ; (This will produce a trace file in your UDUMP directory. )

Thank you! This is what I needed. After I copy PROD files, I should delete DEV's old DATA and LOG files right ?

One other thing I saw was, when I check the value for $ORACLE_HOME it is a different directory - /u1/ora/devora/8.0.6, but the actual ORACLE_HOME is /u1/ora/devdb/8.1.6. Is this because of the APPS setup ? Do you know ?

Thanks again,
masaimara

I forgot to tell you that in appreciation of your responses, I doubled the points.

Sorry for posting so many questions. Disregard my previous 2 postings - this is a cumulative one...

Thank you for the step-by-step procedure that you've put out. You're awesome!

These are the questions I have:

1. After I copy PROD files, I should delete DEV's old DATA and LOG files right ?

2. Does it matter what PROD Backup (what date) I use ?

3. One other thing I saw was, when I check the value for $ORACLE_HOME it is a different directory - /u1/ora/devora/8.0.6, but the actual ORACLE_HOME is /u1/ora/devdb/8.1.6. Is this because of the APPS setup ? Do you know ?

FYI, in appreciation of your responses, I doubled the points.

masaimara

Hi.

1)  You would copy the PROD files over the top of ther Dev files (ensuring that the filenames - full pathname - match those in the controlfile.sql)
2)  Latest backup would be best
3) Sorry , not totally familiar with APPS set-up so unsure if Db Oracle Hoem should be same as APPS.


(Of course if you wanted to get back to square 1 - another option is to restore the DEV database from a backup)

I am still in the process of doing it. Just wanted to keep you posted.

CallumT,

I have the control files from DEV and PROD. They both look very different and so I would like your opinion on these. Can I email you these files so that you can look at them ? I don't mean to dump this stuff on you. I figured since you're an expert you might just need couple of mins to figure things out.

I really appreciate it.
masaimara

No problems.

But I don't think a comparison of the control files will help. What stage did you get up to with the clone of the database ???
Did you manage to start the DEV db ?

I copied PROD's 8.1.7 Oracle Home and got the trace file. I wanted to take your opinion before proceeding further. What is your email address ?

I'd rather not post emal address in forums  ;-)
Howver, if you don'r mind posting yours I''ll contact you tomoorow(Monday New Zaland time) when I get into the office .

In the meantime your controlfile should look something like this after editing all the comments out ..


STARTUP NOMOUNT pfile=$DBA/pfile/initaasp.ora
CREATE CONTROLFILE SET DATABASE "AASP" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 1022
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/db/aasp/redo/aasp_redo_1a.dbf'  SIZE 10M,
  GROUP 2 '/db/aasp/redo/aasp_redo_2a.dbf'  SIZE 10M,
  GROUP 3 '/db/aasp/redo/aasp_redo_3a.dbf'  SIZE 10M,
  GROUP 4 '/db/aasp/redo/aasp_redo_4a.dbf'  SIZE 10M
DATAFILE
  '/db/aasp/system/aasp_system_01.dbf',
  '/db/aasp/rollback/aasp_rollback_01.dbf',
  '/db/aasp/temp/aasp_temp_01.dbf',
  '/db/aasp/data1/aasp_tools_01.dbf',
  '/db/aasp/data1/aasp_users_01.dbf',
  '/db/aasp/data1/aasp_aas_data_01.dbf',
  '/db/aasp/data1/aasp_aas_index_01.dbf'
CHARACTER SET AL24UTFFSS
;
ALTER DATABASE OPEN RESETLOGS;

You are right. It is not a good idea to ask for email addresses - that was stupid of me :)

I am in the process of copying all of the dbf files (in backup controlfile) from PROD to DEV. Everything is going well as per your plan so far. I will let you know as soon as I get it done.

Thanks,
masaimara

I just want to make sure that I have to say "CREATE CONTROLFILE SET DATABASE "AASP" RESETLOGS NOARCHIVELOG" and not REUSE DATABASE in the new controlfile. Please let me know.
Thanks.

I used CREATE CONTROLFILE REUSE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG to create control files. I get the following error:

CREATE CONTROLFILE REUSE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : '/u1/oradev/data/log01a.dbf'

This is what is see (below) after I copied all the files from PROD backup. How do I go about fixing this issue ?

Also, do I need the temp dbf files for the new db ?

lrwxrwxrwx   1 root     dba           23 Apr 26 17:01 log1a.dbf -> .log01a.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:32 log1b.dbf -> .log01b.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:32 log2a.dbf -> .log02a.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log2b.dbf -> .log02b.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log3a.dbf -> .log03a.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log3b.dbf -> .log03b.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log4a.dbf -> .log04a.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log4b.dbf -> .log04b.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log5a.dbf -> .log05a.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log5b.dbf -> .log05b.dbf::cdev:vxfs:
-rwxr-xr-x  1 root     dba      104865792 Apr 23 21:10 temp1.dbf
-rw-r-----   1 root     dba      1153425408 Apr 23 21:10 temp2.dbf

Thanks,
masaimara

Sorry for all these comments. I did manage to get rid of the links and create physical files but I still get this error with REUSE SET and just SET:

CREATE CONTROLFILE REUSE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : '/u1/oradev/data/log1a.dbf'


SQL> @initDEV.sql
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : '/u1/oradev/data/log1a.dbf'

FYI
When I do $ file log1a.dbf
this is what I see
log1a.dbf:     data

Any ideas ?

It looks like you have got symbolic links pointing to the real redo logs. If you are restorng from Cold Backup, then you don't need the redo logs. The create_controlfile will create them for you.

Can you post the first few lines of youer controlfile.sql (include a few lines after the DATAFILE line)

..and yes you will need the tempfiles too.

There should be a bit in the conrtrolfile.sql that references the TEMP data-files

You mean I can delete the log files from the directory ?

Create controlfile attached below. Please let me know how I should proceed from here.

STARTUP NOMOUNT pfile=/u3/oracle/devdb/8.1.7/dbs/initTEST.ora
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 500
    MAXINSTANCES 1
    MAXLOGHISTORY 1815
LOGFILE
  GROUP 1 (
    '/u3/oracle/devdata/log01a.dbf',
    '/u3/oracle/devdata/log01b.dbf'
  ) SIZE 20979200,
  GROUP 2 (
    '/u3/oracle/devdata/log02a.dbf',
    '/u3/oracle/devdata/log02b.dbf'
  ) SIZE 20979200,
  GROUP 3 (
    '/u3/oracle/devdata/log03a.dbf',
    '/u3/oracle/devdata/log03b.dbf'
  ) SIZE 20979200,
  GROUP 4 (
    '/u3/oracle/devdata/log04a.dbf',
    '/u3/oracle/devdata/log04b.dbf'
  ) SIZE 20979200,
  GROUP 5 (
    '/u3/oracle/devdata/log05a.dbf',
    '/u3/oracle/devdata/log05b.dbf'
  ) SIZE 20979200
DATAFILE
  '/u3/oracle/devdata/system01.dbf',
  '/u3/oracle/devdata/system02.dbf',
  '/u3/oracle/devdata/system03.dbf',
  '/u3/oracle/devdata/system04.dbf',
  '/u3/oracle/devdata/system05.dbf',
  '/u5/oracle/devdata/abmd01.dbf',
  '/u5/oracle/devdata/abmx01.dbf',
  ...
  ...
CHARACTER SET WE8ISO8859P1
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

FYI, In the error above it might show '/u1/oradev/data/log1a.dbf'. I changed it to the right directory later on '/u3/oracle/devdata/log1a.dbf' and I still get the error.

Check that the db_block_size in the DEV file is the same as the db_block_size of your PROD database.

Copy the redologs from th Cold-Backup to the directory specified in the control_file.sql (just to be on the safe side0

CallumT,

It worked !!! I had one of the logfiles under the Datafiles section of the Create Controlfile script. I could create the controlfile once I got rid of that. Even though DEV was restored from a cold backup it asked for arch files during recovery. I applied the required logs and it was recovered.

Thanks and Regards,
masaimara

Great..

One last thing..make sure you get a good backup of the new Dev environment   ;-)