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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
7 comments
522 views