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>