Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

help "cloning" a database

Rich Holland

2004-05-18

Replies:
I'm trying to make a copy of a database (running SAP, Oracle 9.2.0.4 64-bit,
AIX 5.1) and running into some difficulty with the system tablespace. It
doesn't seem to matter if I shut the database down before the copy (offline) or
put everything in backup mode first (online) -- I get the same end result
either way. Maybe some oracle guru can steer me in the right direction.

SAP lays out the database with two mirrored redo log groups, log_archive_dest
to /oracle/<SID>/oraarch, and all the data files in /oracle/<SID>/sapdata<##>
(e.g. sapdata1, sapdata2, etc).

Here's what I'm doing to copy the system:

On the source system (SRC), I create a SQL copy of the control file:
 SQL> alter database backup controlfile to trace;

For all tablespaces except TEMP, I do:
 SQL> alter tablespace <TS> begin backup;

Then I copy the file systems from SRC to DST system:
 $ cd /oracle/SRC
 $ tar -cf -./sapdata* |  cd (/oracle/DST ; tar -xvpf - )

Then I force log switches (four, just to be safe):
 SQL> alter system archive log current;
 SQL> alter system archive log current;
 SQL> alter system archive log current;
 SQL> alter system archive log current;

Then I copy the redo & archived redo logs:
 $ cd /oracle/SRC
 $ tar -cf - ./mirr* ./orig* ./ora* | (cd /oracle/DST ; tar -xvpf -)

At this point it should be safe to take the source system out of backup mode,
so I do that. I also fix the ownership of all the copied files:

 $ cd /oracle/DST
 $ find . -user orasrc -exec chown oradst {} ';'

Now I copy the control file backup I made, and edit out everything but the
piece to rebuild the control file from set #2 (the backup creates two sets, one
with 'noresetlogs' and one with 'resetlogs'), which looks sort of like this:

 STARTUP NOMOUNT
 CREATE CONTROLFILE REUSE DATABASE "SRC" RESETLOGS ARCHIVELOG
 -- SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 254
    MAXINSTANCES 50
    MAXLOGHISTORY 1134
 [... LOGFILE info ...]
 DATAFILE
  '/oracle/SRC/sapdata1/system_1/system.data1',
 [... rest of the datafiles ...]
 CHARACTER SET WE8DEC
 ;

So I change all occurrences of 'SRC' to 'DST' in the file, and change 'REUSE'
to 'SET' in the create statement, so it looks like:
 CREATE CONTROLFILE SET DATABASE "DST" RESETLOGS ARCHIVELOG

I then run the controlfile creation script in the target system:
 oradst$ sqlplus '/ as sysdba'
 SQL> @/tmp/cntrl.sql

This creates the control files successfully. I realize there will likely be
some media recovery needed, so I make sure the archived redo logs are all
there:
 oradst$ cd /oracle/DST/oraarch
 oradst$ for i in SRC*
 > do
 > ln $i `echo $i |sed -e 's/SRC/DST/'`
   > done

This creates hard links so for example, SRCarch1_392.dbf can also be referenced
as DSTarch1_392.dbf. This just saves some typing during the recovery:

 SQL> recover database using backup controlfile until CANCEL;

At this point I'm prompted for the first archived redo log from the log
switches I did earlier:

 ORA-00279: change 83231342 generated at 05/18/2004 10:31:41 needed for
 thread 1
 ORA-00289: suggestion : /oracle/DST/oraarch/DSTarch1_397.dbf
 ORA-00280: change 83231342 for thread 1 is in sequence #397
 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

So I press <RET> to take it's suggestion and it goes to the next one; it does
this all the way until it rolls through log 400, which was the last of the 4 I
created with the 'alter system archive log current' commands earlier. When it
asks for 401, there isn't anything to give it, so I CANCEL the recovery, but I
see this error:

 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get
error
 below
 ORA-01195: online backup of file 1 needs more recovery to be consistent
 ORA-01110: data file 1: '/oracle/DST/sapdata1/system_1/system.data1'

Now that seems odd that the system tablespace would need further recovery,
especially in light of the fact that it's current SCN number matches all the
other files:

 SQL> set pagesize 40
 SQL> col name format A45
 SQL> col fn format 99
 SQL> select a.file# FN, b.change#, a.name
  2> from v$datafile a, v$recover_file b
  3> where a.file# = b.file#;

 FN   CHANGE# NAME
 --- ---------- ---------------------------------------------
  1  83232063 /oracle/DST/sapdata1/system_1/system.data1
  2  83232063 /oracle/DST/sapdata2/roll_1/roll.data1
  3  83232063 /oracle/DST/sapdata4/sol_1/src.data1
  4  83232063 /oracle/DST/sapdata4/sol_2/src.data2
  5  83232063 /oracle/DST/sapdata4/sol_3/src.data3
  6  83232063 /oracle/DST/sapdata4/sol_4/src.data4
  7  83232063 /oracle/DST/sapdata4/sol_5/src.data5
  8  83232063 /oracle/DST/sapdata3/sol620_1/src620.data1
  9  83232063 /oracle/DST/sapdata3/sol620_2/src620.data2
  10  83232063 /oracle/DST/sapdata3/sol620_3/src620.data3
  11  83232063 /oracle/DST/sapdata1/solusr_1/srcusr.data1

So how come if the SCN numbers are the same in all the files, Oracle thinks it
needs media recovery on the system data file? And why can't I open the
database with resetlogs?

 SQL> alter database open resetlogs;
 alter database open resetlogs
 *
 ERROR at line 1:
 ORA-01195: online backup of file 1 needs more recovery to be consistent
 ORA-01110: data file 1: '/oracle/DST/sapdata1/system_1/system.data1'

Help! What am I overlooking here? I could swear I've done it this way in
previous releases with no problems....

Thanks!
Rich

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------