Monday, 8 May 2017

Oracle: Standby on same host

Source : Turn on force logging on and auto file management on Standby
initBLOK.ora   # this is standby pfile created to mount in NOMOUNT mode

racle@localhost:/u01/app/oracle/product/11.2.0/dbs$ cat initBLOK.ora
BLOK.__db_cache_size=603979776
BLOK.__java_pool_size=16777216
BLOK.__large_pool_size=33554432
BLOK.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
BLOK.__pga_aggregate_target=637534208
BLOK.__sga_target=939524096
BLOK.__shared_io_pool_size=0
BLOK.__shared_pool_size=251658240
BLOK.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/BLOK/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='ALOK'
*.instance_name='BLOK'
*.db_unique_name='BLOK'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BLOKXDB)'
*.memory_target=1572864000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/oradata/ALOK','/oradata/BLOK'
*.log_file_name_convert='/oradata/ALOK','/oradata/BLOK'
control_files='/oradata/BLOK/control01.ctl','/oradata/BLOK/control02.ctl'

Note: Critical one in bold.

RMAN script:

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  NOFILENAMECHECK;

Run log:
RMAN> DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  NOFILENAMECHECK;2> 3> 4>

Starting Duplicate Db at 08-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=10 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=135 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbs/orapwALOK' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbs/orapwBLOK'   ;
}
executing Memory Script

Starting backup at 08-MAY-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=147 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=24 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=143 device type=DISK
Finished backup at 08-MAY-17

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/oradata/BLOK/control01.ctl';
   restore clone controlfile to  '/oradata/BLOK/control02.ctl' from
 '/oradata/BLOK/control01.ctl';
}
executing Memory Script

Starting backup at 08-MAY-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oradata/backup/ALOK/snapcf_alok.f tag=TAG20170508T072157 RECID=12 STAMP=943428119
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 08-MAY-17

Starting restore at 08-MAY-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_4: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 08-MAY-17

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/oradata/BLOK/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oradata/BLOK/system01.dbf";
   set newname for datafile  2 to
 "/oradata/BLOK/sysaux01.dbf";
   set newname for datafile  3 to
 "/oradata/BLOK/undotbs01.dbf";
   set newname for datafile  4 to
 "/oradata/BLOK/users01.dbf";
   set newname for datafile  5 to
 "/oradata/BLOK/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/oradata/BLOK/system01.dbf"   datafile
 2 auxiliary format
 "/oradata/BLOK/sysaux01.dbf"   datafile
 3 auxiliary format
 "/oradata/BLOK/undotbs01.dbf"   datafile
 4 auxiliary format
 "/oradata/BLOK/users01.dbf"   datafile
 5 auxiliary format
 "/oradata/BLOK/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/BLOK/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 08-MAY-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oradata/ALOK/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=/oradata/ALOK/sysaux01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00005 name=/oradata/ALOK/example01.dbf
channel ORA_DISK_4: starting datafile copy
input datafile file number=00003 name=/oradata/ALOK/undotbs01.dbf
output file name=/oradata/BLOK/undotbs01.dbf tag=TAG20170508T072215
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_4: starting datafile copy
input datafile file number=00004 name=/oradata/ALOK/users01.dbf
output file name=/oradata/BLOK/example01.dbf tag=TAG20170508T072215
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:01:22
output file name=/oradata/BLOK/users01.dbf tag=TAG20170508T072215
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:55
output file name=/oradata/BLOK/sysaux01.dbf tag=TAG20170508T072215
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:02:52
output file name=/oradata/BLOK/system01.dbf tag=TAG20170508T072215
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:12
Finished backup at 08-MAY-17

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=943428328 file name=/oradata/BLOK/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=943428328 file name=/oradata/BLOK/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=943428328 file name=/oradata/BLOK/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=943428328 file name=/oradata/BLOK/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=943428328 file name=/oradata/BLOK/example01.dbf
Finished Duplicate Db at 08-MAY-17

RMAN> exit

Post this just adjust archive dest and fal and you are done.

on my primary:
alter system set log_archive_dest_3='SERVICE=BLOK LGWR ASYNC=20480 reopen=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=BLOK' scope=both;

on standby:
SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string

fal_server                           string      alok

and started mrp.

No comments:

Post a Comment