copy of the database on another machine, it was the same as conventional backup & recovery. Copying a database on the same machine, there is little difference because in one machine may not have 2 (or more) the same database; a step to change the name of the database.
I have 1 database development, database name is ts2. For test purposes, I need another database on the development machine. New database content is the same as the previous database. How do I make it, there are two ways:
1. Creating a new database, export from the old database, then import into new database.
2. Copying the old database to the new one, as if the backup and recovery
The first way, of course, takes a long time, especially if a large enough data. Then I choose the latter; my first database backup, then I raise (restore & recovery) with a different name (I named tsrep). Here are the steps:
1. Prepare (copy) init file. Examples here, I use UNIX. In Windows, the location of the file init in% ORACLE_HOME% \ database
cd $ORACLE_HOME/dbs
cp -rp initts2.ora inittsrep.ora
Suppose inittsrep.ora content is the following:
### Parameter ini perlu disesuaikan ###
audit_file_dest='/data1/oracle/admin/ts2/adump'
background_dump_dest='/data1/oracle/admin/ts2/bdump'
core_dump_dest='/data1/oracle/admin/ts2/cdump'
user_dump_dest='/data1/oracle/admin/ts2/udump'
control_files='/oradata/oracle/ts2/control01.ctl', '/oradata/oracle/ts2/control02.ctl', '/oradata/oracle/ts2/control03.ctl'
log_archive_dest_1='LOCATION=/oradata/oracle/ts2/arc'
db_name='ts2'
####################################
log_archive_dest_state_1=enable
log_archive_format=%s_%t_%r.arc
compatible='10.2.0.3.0'
db_block_size=8192
db_domain=''
pga_aggregate_target=209715200
sga_target=1610612736
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
remote_login_passwordfile='EXCLUSIVE'
I adjust some parameters, especially db_name and related directories. Here are the parameters I have been customized:
audit_file_dest='/data1/oracle/admin/tsrep/adump'
background_dump_dest='/data1/oracle/admin/tsrep/bdump'
core_dump_dest='/data1/oracle/admin/tsrep/cdump'
user_dump_dest='/data1/oracle/admin/tsrep/udump'
control_files='/oradata/oracle/tsrep/control01.ctl', '/oradata/oracle/tsrep/control02.ctl', '/oradata/oracle/tsrep/control03.ctl'
log_archive_dest_1='LOCATION=/oradata/oracle/tsrep/arc'
db_name='tsrep'
Prepare a directory for files and other data
mkdir /data1/oracle/admin/tsrep
mkdir /data1/oracle/admin/tsrep/adump
mkdir /data1/oracle/admin/tsrep/bdump
mkdir /data1/oracle/admin/tsrep/cdump
mkdir /data1/oracle/admin/tsrep/udump
mkdir /oradata/oracle/tsrep
mkdir /oradata/oracle/tsrep/arc
Prepare script to create database
In the old database (source):
SQL> alter database backup controlfile to trace resetlogs;
See trace file in the directory user_dump_dest, / data1/oracle/admin/ts2/udump
SQL> sho parameter user_dump_dest
Copy the trace file
cd /data1/oracle/admin/ts2/udump
cp ts2_ora_18762.trc /oradata/oracle/tsrep/crdbtsrep.sql
Edit crdbtsrep.sql file, remove "trace file entry", to be like this
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TS2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 4 '/oradata/oracle/ts2/redo04.log' SIZE 5M,
GROUP 5 '/oradata/oracle/ts2/redo05.log' SIZE 5M,
GROUP 6 '/oradata/oracle/ts2/redo06.log' SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'/oradata/oracle/ts2/system01.dbf',
'/oradata/oracle/ts2/undotbs01.dbf',
'/oradata/oracle/ts2/sysaux01.dbf',
'/oradata/oracle/ts2/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
Edit crdbtsrep.sql file again.
Replace this entry
CREATE CONTROLFILE REUSE DATABASE “TS2″ RESETLOGS ARCHIVELOG
Become
CREATE CONTROLFILE SET DATABASE “TS2REP” RESETLOGS ARCHIVELOG
Adjust the directory-related directory. In this example I changed
/oradata/oracle/ts2/
become
/oradata/oracle/tsrep/
After some editing, crdbtsrep.sql files will be
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TSREP" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 4 '/oradata/oracle/tsrep/redo04.log' SIZE 5M,
GROUP 5 '/oradata/oracle/tsrep/redo05.log' SIZE 5M,
GROUP 6 '/oradata/oracle/tsrep/redo06.log' SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'/oradata/oracle/tsrep/system01.dbf',
'/oradata/oracle/tsrep/undotbs01.dbf',
'/oradata/oracle/tsrep/sysaux01.dbf',
'/oradata/oracle/tsrep/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
Copy (backup and restore) a database source
If NOARCHIVELOG database, do a cold (off line) backups. Because ts2 ARCHIVELOG database, then I can do is hot (on line) backups. In this example I use hotbackup.Lihat list datafile and tempfile that needs to be copied
select name as file_name from
(select name from v$tempfile union
select name from v$datafile);
/oradata/oracle/ts2/sysaux01.dbf
/oradata/oracle/ts2/system01.dbf
/oradata/oracle/ts2/temp01.dbf
/oradata/oracle/ts2/undotbs01.dbf
/oradata/oracle/ts2/users01.dbf
See tablespace to the backup
SQL> select distinct tablespace_name from dba_data_files;
TABLESPACE_NAME
------------------------------
SYSTEM
USERS
SYSAUX
UNDOTBS1
Before running BEGIN backup, see "Current log sequence". This information we need to see any archived logs are later required to raise the database. In this instance, "Current log sequence" is 17.
SQL> archive log list
Run BEGIN BackUp-tablespaces in the tablespace (if cold backup, this step is replaced by a database shutdown):
SQL> alter tablespace SYSTEM begin backup;
SQL> alter tablespace USERS begin backup;
SQL> alter tablespace SYSAUX begin backup;
SQL> alter tablespace UNDOTBS1 begin backup;
Copy datafile and tempfile
cd /oradata/oracle/ts2/
cp -rp sysaux01.dbf /oradata/oracle/tsrep
cp -rp system01.dbf /oradata/oracle/tsrep
cp -rp temp01.dbf /oradata/oracle/tsrep
cp -rp undotbs01.dbf /oradata/oracle/tsrep
cp -rp users01.dbf /oradata/oracle/tsrep
After a datafile copy process is complete, run the BackUp END tablespace tablespace-related (if cold backup, this step is replaced with a startup database):
SQL> alter tablespace SYSTEM end backup;
SQL> alter tablespace USERS end backup;
SQL> alter tablespace SYSAUX end backup;
SQL> alter tablespace UNDOTBS1 end backup;
When finished END backups, see the value of "Current log sequence". In this example the value was 18.
SQL> archive log list
Run "archive log current" to create archived logs from sequence 18.
SQL> alter system archive log current
Archived logs required to raise the database is archived log sequence before BEGIN END seteleh backups and backups. Thus, sequences 17 and 18.
Further copies of the required archived log is
cd /oradata/oracle/ts2/arc/
cp -rp 17_1_658171224.arc /oradata/oracle/tsrep/arc
cp -rp 18_1_658171224.arc /oradata/oracle/tsrep/arc
Preparation before raising database
Setting ORACLE_SID, in csh shell
setenv ORACLE_SID tsrep
Ksh shell
export ORACLE_SID=tsrep
In Windows
set ORACLE_SID=tsrep
Especially in Windows, for instance (service) oradim use. Make sure that the file was created inittsrep.ora.
cd %ORACLE_HOME%\database
oradim -NEW -SID tsrep
Raise (create) the database
SQL> @/oradata/oracle/tsrep/crdbtsrep.sql
Perform recovery. With automatic specify, Oracle will search for archived log files alone.
SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE;
In this example, the required archived log is sequence 17 to 18. After a sequence of 18-apply, Oracle still asking squence 19. Because the sequence 19 does not exist, then the error recovery. Ignore these errors. Next:
SQL> RECOVER DATABASE until cancel;
If the database in sync, the above command should work. If Oracle is still asked archived logs for recovery, copy it again from its source databases. If the source database archived log has not been create, yes run "alter system archive log current", or if you want, just stop using the logfile recover the source database (not archived log). Suppose still required archived log sequence 19, while sequence is not dicreate archived logs. Use the following command to get logfilenya
SQL> -- di database source (ts2)
SQL> select member from v$logfile where GROUP# in
(select GROUP# from v$log where SEQUENCE#=19);
Next, use the logfile above for recovery in tsrepSetelah database recovery process is successful, open the database:
SQL> ALTER DATABASE OPEN RESETLOGS;
Next, add the temp files:
SQL> ALTER TABLESPACE TEMP ADD
TEMPFILE '/oradata/oracle/tsrep/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND OFF;
Posted by audio at 2:21 PM 0 comments
Labels: copy Database to the Same Machine
PHP with Oracle Connection 2: Sample Program
In this tutorial, we will use the following oracle table as its data source. Table name is test.
nama
kota
Prothelord
New York
Strawberi
Bangkok
Our goal is to display the name and city data from the table. But before you start, you need to seek prior information about the Oracle login information is usually called the TNS name (you can compare with the user, password and IP dariMySQL server). TNS name consists of:
- IP Server Oracle
- Port is in use (if MySQL 3306, remember?)
- SID
- User
- Password
Try asking the same Oracle admin at your place on such information.
If it can, try the following programs to edit the configuration TNS Name which you have from your Oracle admin. In this example, we use the TNS name configuration as follows:
- IP: 10.2.2.2
- Port: 1523
- SID: TEST
- User: your name
- Password: pwd
Here's an example program to access the Oracle tables:
$ db = "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.2.2.2) (PORT = 1523))) (CONNECT_DATA = (SID = TEST)))";
$ c1 = ocilogon ( "name", "pwd", $ db);
$ code = "select * from test";
$ stmt = ociparse ($ c1, $ code);
ociexecute ($ stmt, OCI_DEFAULT);
/ / Displaying data from test table
while (ocifetch ($ stmt)) (
ociresult echo ($ stmt, 0 ).",". ociresult ($ stmt, 1);
echo ( "
");
)
ocilogoff ($ c1);
?>
When finished, save the name and call coba_oracle.php from the browser. You should see the following results:
Prothelord, New York
Strawberry, Bangkok
Bye, cuman kok gitu doang. Easy yach?
Good luck, and hopefully useful.
Best regards.
Prothelord.
