script a whole database 2

[fgeorge]

how can i script a whole oracle 8i db?
i want to re create it on another server..
can i script into an .sql file?
thx

 

[alexhu]

use export and then run import with SHOW=Y

SHOW
Default: n

When SHOW=y, the contents of the export file are listed to the display and not imported. The SQL statements contained in the export are displayed in the order in which Import will execute them.

The SHOW parameter can be used only with the FULL=y, FROMUSER, TOUSER, or TABLES parameter.

Alex

 

[carp]

However, since import requires you to already have an Oracle database up and running on the target server, you will need to have the Oracle server, instance and basic database already set up.

 

[fgeorge]

but is it possible to script the db to a txt file and run the txt file on another server to re create the db?
thx

 

[fgeorge]

but is it possible to script the db to a txt file and run the txt file on another server to re create the db?
just like sql server??
thx

 

[carp]

It's possible, but it would be an awful lot of work!

You might want to go to metalink and do a search on cloning databases. There are several good white papers on the subject. Also, take a look at RMAN documentation.

 

[sybaseguru]

This is the answer that I have already given to a similar request in Oracle9i forum. I believe it will work for you as well. Try it and good luck

The best way of cloning the database in my opinion is as follows. If you want to create both databases on the same host you need to be aware of total RAM etc you have. You can easily have two instances each with an SGA of 2GB on a host with say 6GB of max shared memory. On Solaris you can find out the max shared memory by looking at  file /etc/system. The parameter Set shmsys:shminfo_shmmax will give the total amount of shared memory in bytes. On Linux you can do ipcs -lm to get the max seg size in KB. For example


oracle@linux:/u001/oracle/admin/mydb/udump% ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 2097151
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1


the line you are interested is 'max seg size (kbytes)'

Either way on the same host or different hosts you can clone the database as follows:
Decide on the name (SID) of the new database and create an associated parameter file to define the Oracle instance. Remember, the parameter file should reside in $ORACLE_HOME/dbs and have an init<$ORACLE_SID>.ora filename. For example, if the name of the new database is to be NEWDB, then the associated parameter file must be called initNEWDB.ora for the database. When you create the parameter file, be sure to change the parameter DB_NAME to contain the new name of the database, and change CONTROL_FILES to contain the location of the new associated Oracle controlfiles. This is very important
1. Using SQL*Plus or anything else connect to the source database and issue the following command:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This command creates a text-based copy of the Database controlfile in the Database trace files.
2. Cleanly shutdown the source database as below:


Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


3. Now that you have shutdown the source instance, go and find the text-based copy of your controlfile which will be saved in the trace directory. This directory will be contained in the directory pointed to by USER_DUMP_DEST in the database initialization parameter file. 
Once you are in the directory do the following:

ls -t *.trc | head -1

and this will identify the trace file you want. 

You'll have to edit this file and perform the following changes 

(a) Delete all lines at the top of the trace file up to but NOT INCLUDING STARTUP NOMOUNT 
(b) Delete all lines that start with '#', which are comments. Just do 


cat xxxx_ora_3426.trc | grep -v '^#' > clone.sql


(c) On the line that starts with CREATE CONTROLFILE, change 'REUSE' to 'SET' Change the old database name to the new one, and change 'NORESETLOGS' to 'RESETLOGS' 
(d) Change the locations of all logfiles and datafiles to the location of the datafiles and logfiles for the new database. 
(e) Delete the line 'RECOVER DATABASE' 
(f) Change the line 'ALTER DATABASE OPEN' to 'ALTER DATABASE OPEN RESETLOGS' 

Now you have the clone.sql ready 

4. At this point, the source database should still be shut down. Before you start it back up, copy all logfiles + datafiles associated with this database to the new location (local or on another host). These files will eventually comprise your destination database.
5. Once you have copied all the associated datafiles, you are ready to create the new database. The actual cloning process simply creates a new set of Oracle controlfiles for the new database. You have all the Oracle environment variables set in the new host etc. Set your $ORACLE_SID to point to the name of the new database.
6. Use SQL*Plus to connect to an idle instance as follows


sqlplus &quot;/ as sysdba&quot;
Connected to an idle instance.
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initNEWDB.ora
SQL> show sga
SQL> @clone.sql 
Statement processed.


7. If you have no errors, you now have another copy of your database. As a general rule, you may want to shutdown/startup this new database just to make sure there are no problems.

8. You can now go back and restart the original database.

Hope this helps and good luck

 

[fgeorge]

the databases are on 2 different boxes..

 

[sybaseguru]

The procedure should apply to dabases being on different hosts as well. Actually it will be much easier in that case as you would not have any problem with having enough shared memory etc. follow the line from

Either way on the same host or different hosts you can clone the database as follows:

 

[fgeorge]

will this work in windows?
i see linux at the top of ur code..
i'm a new guy to this oracle stuff..
thanks..

 

[sybaseguru]

Yes it should. The platform would not matter. But if you have Unix simulator on windows something like MKS tool life will be easier. Anyway just try it and see how it goes. You should be able to copy files easily across two servers in Windows.

Good luck

 

[greekgirl]

I tried this, because I need to clone my database, too. I get to step 6: 6. Use SQL*Plus to connect to an idle instance as follows

sqlplus "/ as sysdba"
Connected to an idle instance.

And this is where I get errors. ORA-12560 TNSrotocol adapter error. Should the entry for the NEWDB be found in tnsnames.ora? I have put the entry in for the NEWDB.

What's a idle instance? Does this mean that I already have to create a instance?

Please help.

 

[SantaMufasa]

I hope you all do not mind if I jump into the fray. Fine and workable solutions appear previously. But I hope the steps below represent a very simple method to produce a truly CLONED database.

Assumptions:
1) The new database will reside on a different machine from the original database.
2) The new database will reside under the same operating system (example: old and new both on Unix, or both on Windows, et cetera) as the original database.
3) The new database will run under the same version of Oracle as the original database.

Steps:
1) Ensure that you have installed the same version of Oracle software on the target machine as on the original machine.
2) On the target machine, create proper operating-system entries to identify your new instance. On Unix, ensure that there is a proper entry in the /var/opt/oracle/oratab or /etc/oratab file; on Windows, ensure that you have executed an
&quot;oradim -new -sid <sid> -intpwd <password> -startmode <a|m>&quot;
entry to create a Windows service; or whatever you normally do on your operating system to establish support for your instance.
3) Document the names of database files in your original database:
select name from v$controlfile;
select member from v$logfile;
select file_name from dba_data_files;
also, identify the location and name of your init<SID>.ora file.
4) Gracefully shutdown your original Oracle database:
shutdown immediate
5) Duplicate all files from Step 3, above, to the target clone machine: &quot;ftp&quot; is an example of a good copying mechanism. On the target/cloned machine, place all files in directories identical to the directories of the files on the original machine. Rename the init<SID>.ora file to match your new SID name. (Note: if you want to place the files in differently named filesystems or paths, you may do so, but it requires your doing a &quot;startup mount&quot;, followed by an &quot;alter database rename file...&quot; command for any files that do not reside in the same locations as the original database. Then do an &quot;ALTER DATABASE OPEN;&quot.
6) Startup your databases/instances: Use whatever software you normally use [example: &quot;svrmgrl&quot; or &quot;sqlplus /nolog&quot;] to bring up both your original database and the cloned database.

This method is lightyears faster than export/import methodologies, and it is truly a clone of your original database. (I believe our Tek-Tips buddy, &quot;Carp&quot; has also successfully used this methodology.)

Any additional questions, please reply.

 

[greekgirl]

Thanks so much for the info.
Part of my problem is that the duplicate database is on the SAME machine.

I did a ALTER DATABASE BACKUP CONTROLFILE TO TRACE and edited this file to create the controlfile for my new database. Would I do the following step BEFORE OR AFTER I create the controlfile?

...doing a &quot;startup mount&quot;, followed by an &quot;alter database rename file...&quot; command for any files that do not reside in the same locations as the original database. Then do an &quot;ALTER DATABASE OPEN;&quot.

Thanks for your help.

 

[hubud]

If you've created a new control file following the backup to trace command and changed the information to reflect the new locations of the files within this file then I am not sure why you would need to rename any files to change their locations.

 

[SantaMufasa]

Greekgirl, I agree with Hubud: if you have already made file-location changes to the text in the &quot;backup controlfile to trace&quot; file, then you don't need to do any additional renaming. Making changes to the text version of the backup control file is IN LIEU OF doing the &quot;startup mount&quot;, &quot;alter database rename file...&quot; steps.

 

[greekgirl]

Thank you all for clarifing that I only needed to create the controlfile with the changes.

Also, the control files (.ctl) should not be copied when changing the database name and issuing the create controlfile script. Since I was on the same machine and needed a new sid, I did not need to copy the control files.

Therefore combining SantaMufasa and Sybaseguru's instructions gave me the details to successfully create the cloned database.