Topic: Database Administration >> cloning fo database
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: cloning fo database |
| Neeraj |
Posted: Jan 25, 2008 06:30:39 AM |
Total Post: 228
Joined: Jan, 2007
|
hi,
i got the script on dbapool abt cloning of database , i folowed all the steps define in the script
MY QUESTION IS THAT HOW CAN V SET ORACLE_SID UNLESS I GET THAT DATABSE & HOW COME V CAN CONNECT NEW DATABASE .
& AFTER RUNNING CREATE_CONTROL SCRIPT IT GIVES ME ERROR
UNKNOWN COMMAND "DATAFILE .....REST OF LINE IGNORE....
IF ITS RIGHT SCRIPT SO TELL ME Y I M GETTING THIS ERROR
**************************************
Step: 1
Check the database Connect to the PROD database and make sure you are in the right database. You can use this statement to find database name.
SQL> select name from v$database;
NAME --------- PROD
This is good practice to check the database name when you are working on different databases at a time.
Step: 2
Copy initPROD.ora file and rename it to initTEST.ora Copy initPROD.ora file from PROD database to the new location where you are planning to copy TEST database. Rename this file to initTEST.ora file. Now edit initTEST.ora file and change the value of the ?db_name? parameter to TEST. Also change the value of following parameter. audit_file_dest background_dump_dest control_files core_dump_dest log_archive_dest user_dump_dest All these parameters are important parameters, so make sure you are not missing anyone.
Step: 3
Copy all datafiles and place them in the TEST directory Copy all datafiles to new location. Find these files by using the following query. First, locate the database files from PROD database by using this statement.
SQL> select name from v$datafile;
NAME ---------------------------------------
/u01/app/oradata/PROD/system01.dbf
/u02/app/oradata/PROD/rbs01.dbf
/u02/app/oradata/PROD/temp01.dbf
/u02/app/oradata/PROD/users01.dbf
/ u02/app/oradata/PROD/indx01.dbf
/u02/app/oradata/PROD/tools01.dbf
Before copying these files in new location, create a directory TEST in oradata. All file should be inside TEST same as they are in PROD directory in PROD database.
Step: 4
Generate the script to create the control file On Prod database use these statements. SVRMGR> connect internal Connected.
SVRMGR> alter database backup controlfile to trace;
Statement processed.
This statement will create a trace file in USER_DUMP_DEST directory. You can find this file as per the value of USER_DUMP_DEST parameter in initPROD.ora file. Once you find the trace file, rename it to create_control.sql and edit it as follows:
Remove everything up to the "START NOMOUNT" statement and everything after the semicolon at the end of the "CREATE CONTROLFILE" statement. Edit the line starting with "CREATE CONTROLFILE" and insert the word "SET" right before the keyword DATABASE. On the same line, modify the database name changing it from PROD to TEST. On the same line, change the keyword NORESETLOGS to RESETLOGS. The final script should be like this.
Script: create_control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 10
MAXDATAFILES 500
MAXINSTANCES 5
MAXLOGHISTORY 1000
LOGFILE
GROUP 1 ( '/u03/app/oradata/TEST/redo01a.log', '/u04/app/oradata/TEST/redo01b.log', '/u05/app/oradata/TEST/redo01c.log' ) SIZE 100K,
GROUP 2 ( '/u03/app/oradata/TEST/redo02a.log', '/u04/app/oradata/TEST/redo02b.log', '/u05/app/oradata/TEST/redo02c.log' ) SIZE 100K,
GROUP 3 ( '/u03/app/oradata/TEST/redo03a.log', '/u04/app/oradata/TEST/redo03b.log', '/u05/app/oradata/TEST/redo03c.log' ) SIZE 100K DATAFILE ?/u01/app/oradata/TEST/system01.dbf? ?/u02/app/oradata/TEST/rbs01.dbf? ?/u02/app/oradata/TEST/temp01.dbf? ?/u02/app/oradata/TEST/users01.dbf? ?/ u02/app/oradata/TEST/indx01.dbf? ?/u02/app/oradata/TEST/tools01.dbf?
CHARACTER SET WE8ISO8859P1 ;
If the TEST database is on a different machine move this file to that machine.
Step: 5
Create the new controlfile for TEST Set Oracle environment variable "ORACLE_SID" is set to TEST by this command export ORACLE_SID=TEST Now connect to SVRMGR and the CREATE CONTROLFILE script (create_control.sql) to create your controlfile for TEST: SVRMGR> connect internal Connected to an idle instance.
SVRMGR> @create_control
ORACLE instance started.
Total System Global Area 73701404 bytes
Fixed Size 75804 bytes
Variable Size 56770560 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Statement processed.
Step: 6
Open the TEST database You will need to perform incomplete recovery before you open the database. After recovery you can open the database using the RESETLOGS option, as it is shown below.
SVRMGR> alter database recover database until cancel using backup controlfile;
SVRMGR> alter database recover cancel;
Statement processed.
SVRMGR> alter database open resetlogs;
Statement processed.
TEST database is ready, you can verify it by querying
SQL> select name from v$database;
NAME --------- TEST
|
|
|
 Vinod |
| Posted: Jan 25, 2008 09:01:39 AM |  |
|
Total Post: 54
Joined: Oct, 2007
|
Hi ,
It looks like the database is aleardy brought up ,if you have any difficulty in connecting to the database you could make use of $ORACLE_HOME/bin/setenv
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
LIB_PATH=$ORACLE_HOME/lib
export LIB_PATH
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_PATH=:$ORACLE_HOME/bin:/bin:/usr/bin:.:
export ORACLE_PATH
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
NLS_LANG=AMERICAN_AMERICA.UTF8; export NLS_LANG
Vinod
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 814 |
| Mohammed Taj | 694 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| positive fanatic | 361 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 239 |
| neeraj sharma | 228 |
| Ramesh Jois | 226 |
| snehalatha p | 169 |
|
|
