Solved

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

Posted on 2004-04-17
33
931 Views
Last Modified: 2007-12-19
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
Comment
Question by:masaimara
  • 19
  • 13
33 Comments
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
Can you tell me how to go about doing this ? Do you know for sure that this will work ?
0
 
LVL 3

Expert Comment

by:taisk
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
 
LVL 2

Accepted Solution

by:
CallumT earned 250 total points
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
I forgot to tell you that in appreciation of your responses, I doubled the points.
0
 

Author Comment

by:masaimara
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:masaimara
Comment Utility
I am still in the process of doing it. Just wanted to keep you posted.
0
 

Author Comment

by:masaimara
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
..and yes you will need the tempfiles too.

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

Author Comment

by:masaimara
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
 

Author Comment

by:masaimara
Comment Utility
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
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
Great..

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now