Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Duplicate database on the same host

P.HuangFeb 6 2020 — edited Feb 7 2020

Hi, I am trying to see if there is way to have multiple instances of the same database that running on the same host.

Setup: Standalone instance with ASM (ACFS) running.

1. use ACFS snapshot to duplicate the database datafile, i.e., (using OMF, all datafile are under /u01/app/oracle/acfs_df)

[grid2@devracdb100 acfs_df]$ /sbin/acfsutil snap create -w db65jr /u01/app/oracle/acfs_df

acfsutil snap create: Snapshot operation is complete.

[grid2@devracdb100 acfs_df]$

2. create the pfile and control file

create pfile='/tmp/65pfile' from spfile;

alter database backup controlfile to trace as '/tmp/65control';

3. update the pfile to include the instance_name clause

SQL> !cat /tmp/65pfile

*._diag_adr_trace_dest='/u01/app/oracle/diag/rdbms/db65/db65jr/trace'

*.archive_lag_target=0

*.audit_file_dest='/u01/app/oracle/admin/db65jr/adump'

*.audit_trail='DB'

*.compatible='12.1.0.2.0'

*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value

*.control_files='/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/control01.ctl','/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/control0                                                                                                    2.ctl'#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr'

*.db_domain='athenahealth.com'

*.db_name='DB65'#Reset to original value by RMAN

*.dg_broker_start=TRUE

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=db65jrXDB)'

*.filesystemio_options='setall'

*.local_listener='LISTENER_DB65JR'

*.log_archive_format='%t_%s_%r.dbf'

*.log_archive_max_processes=4

*.log_archive_min_succeed_dest=1

*.log_buffer=9280K# log buffer update

*.open_cursors=300

*.optimizer_dynamic_sampling=2

*.optimizer_mode='ALL_ROWS'

*.pga_aggregate_target=2049M

*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora

*.processes=300

*.query_rewrite_enabled='TRUE'

*.remote_login_passwordfile='EXCLUSIVE'

*.result_cache_max_size=31552K

*.sga_target=6160M

*.skip_unusable_indexes=TRUE

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.instance_name=db65jr   <------------------------------------------- here

SQL>

4. update control trace file directory to the snapshot

startup nomount pfile='/tmp/65pfile';

CREATE CONTROLFILE REUSE DATABASE "DB65" RESETLOGS FORCE LOGGING ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/onlinelog/o1_mf_1_h3rtxm34_.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/onlinelog/o1_mf_2_h3rtxm6s_.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/onlinelog/o1_mf_3_h3rtxm9z_.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/datafile/o1_mf_indx_h3rtvhrv_.dbf',

'/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/datafile/o1_mf_loc_temp_h3rtvfdz_.dbf',

'/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/datafile/o1_mf_loc_temp_h3rtvjys_.dbf',

'/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/datafile/o1_mf_sysaux_h3rttno8_.dbf',

'/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/datafile/o1_mf_system_h3rttd9c_.dbf',

'/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/datafile/o1_mf_undo_h3rttmh2_.dbf',

'/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/datafile/o1_mf_undotbs1_h3rttvvj_.dbf',

'/u01/app/oracle/acfs_df/.ACFS/snaps/db65jr/DB65/datafile/o1_mf_users_h3rtvb7k_.dbf'

CHARACTER SET US7ASCII

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/12.1.0/dbhome_2/dbs/arch1_1_1030367384.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/12.1.0/dbhome_2/dbs/arch1_1_1031589282.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

5. start the database.

However, given the lock, i.e., $ORACLE/dbs/lkDB65, I can't have both database running, i.e., if I start the 2nd database, I got the following:

SQL>  startup pfile='/tmp/65pfile';

Total System Global Area 6459228160 bytes

Fixed Size                  6871000 bytes

Variable Size            1778386984 bytes

Database Buffers         4664066048 bytes

Redo Buffers                9904128 bytes

ORA-01102: cannot mount database in EXCLUSIVE mode

SQL>

Comments
Post Details
Added on Feb 6 2020
9 comments
604 views