• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1005
  • Last Modified:

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

Hi,
We have 2 environments DEV and PROD.

DEV    8.1.6
PROD  8.1.7

DEV (target) was accidentally cloned with PROD (source). Now DEV's database is in a mess. I cannot run catalog.sql or catproc.sql to fix the system tables/views. They just hang. My questions:

1. Is there a way to fix system's tables/views ?

I tried to run hcheck diagnostic script and I get the following error:

SQL> execute hcheck.full
BEGIN hcheck.full; END;

*
ERROR at line 1:
ORA-06544: PL/SQL: internal error, arguments: [78601], [], [], [], [], [], [],
[]
ORA-06553: PLS-801: internal error [78601]
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.HOUT", line 18
ORA-06512: at "SYS.HCHECK", line 1078
ORA-06512: at line 1

2. Is there a way I can copy/clone DEV again or can I just replace DEV's  DB files with PROD's ?

I just want to fix DEV database so that I can upgrade the application from 11.5.3 to 11.5.7.

Thanks,
masaimara
0
masaimara
Asked:
masaimara
  • 19
  • 13
1 Solution
 
CallumTCommented:
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.

0
 
masaimaraAuthor Commented:
Can you tell me how to go about doing this ? Do you know for sure that this will work ?
0
 
taiskCommented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
masaimaraAuthor Commented:
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
0
 
CallumTCommented:
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
0
 
masaimaraAuthor Commented:
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 ?
0
 
masaimaraAuthor Commented:
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
0
 
CallumTCommented:
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
0
 
masaimaraAuthor Commented:
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 ?
0
 
masaimaraAuthor Commented:
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 ?
0
 
CallumTCommented:
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. )
0
 
CallumTCommented:
2) Edit this trace file to create a sql file that will create a control-file for your DEV database. E.G. call it controlfile.sql


Remove all the comments  (for example)


Dump file /db/arpt/dba/udump/arpt_ora_24395.trc
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
ORACLE_HOME = /opt/oracle/8.1.7.4
System name:    SunOS
Node name:      aklxd051
Release:        5.8
Version:        Generic_108528-22
Machine:        sun4u
Instance name: arpt
Redo thread mounted by this instance: 1
Oracle process number: 14
Unix process pid: 24395, image: oracle@aklxd051 (TNS V1-V3)

*** SESSION ID:(13.2756) 2004-04-19 22:07:51.763
*** 2004-04-19 22:07:51.763
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.

First line should be the STARTUP
Change the second line to read somethinglike :

CREATE CONTROLFILE SET DATABASE "XXXX" RESETLOGS NOARCHIVELOG  (where XXXX is the name of your DEV database)

Then change all the path-names for the database files to match what they will be called on your DEV server. SAve this File and then copy over to DEV Server.
So Control file will look something like..

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARPT" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 1022
    MAXINSTANCES 1
    MAXLOGHISTORY 32672
LOGFILE
  GROUP 1 '/db/arpt/redolog/arpt_redo_1a.dbf'  SIZE 4M,
  GROUP 2 '/db/arpt/redolog/arpt_redo_2a.dbf'  SIZE 4M,
  GROUP 3 '/db/arpt/redolog/arpt_redo_3a.dbf'  SIZE 4M,
  GROUP 4 '/db/arpt/redolog/arpt_redo_4a.dbf'  SIZE 4M
DATAFILE
  '/db/arpt/system/arpt_system_01.dbf',
  '/db/arpt/rollback/arpt_rollback_01.dbf',
  '/db/arpt/temp/arpt_temp_01.dbf',......
.......
  '/db/arpt/index/arpt_usage_raw_200402_ind_01.dbf',
  '/db/arpt/data/arpt_usage_raw_200402_data_01.dbf'
CHARACTER SET US7ASCII
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;


From the latest COLD backup of your PROD database (or if you can afford an outage - shutdown the PROD database) and copy all the files from PROD to DEV to the directories that have been defined in your newly edited controlfile.sql

Make sure your DEV environment is running under the correct ORACLE_HOME.
If necessary, copy the PROD pfile to DEV.

Start SQLPLUS and connect as sysdba, then execute your controlfile.sql.

So then you will have a copy of PROD running as DEV under the correct Oracle Home
0
 
masaimaraAuthor Commented:
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
0
 
masaimaraAuthor Commented:
I forgot to tell you that in appreciation of your responses, I doubled the points.
0
 
masaimaraAuthor Commented:
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


0
 
CallumTCommented:
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)
0
 
masaimaraAuthor Commented:
I am still in the process of doing it. Just wanted to keep you posted.
0
 
masaimaraAuthor Commented:
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
0
 
CallumTCommented:
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 ?
0
 
masaimaraAuthor Commented:
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 ?
0
 
CallumTCommented:
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 .
0
 
CallumTCommented:
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;
0
 
masaimaraAuthor Commented:
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
0
 
masaimaraAuthor Commented:
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.
0
 
masaimaraAuthor Commented:
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
0
 
masaimaraAuthor Commented:
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 ?
0
 
CallumTCommented:
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)

0
 
CallumTCommented:
..and yes you will need the tempfiles too.

There should be a bit in the conrtrolfile.sql that references the TEMP data-files
0
 
masaimaraAuthor Commented:
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;
0
 
masaimaraAuthor Commented:
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.
0
 
CallumTCommented:
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
0
 
masaimaraAuthor Commented:
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
0
 
CallumTCommented:
Great..

One last thing..make sure you get a good backup of the new Dev environment   ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 19
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now