02.01. creation of "COPY STANDBY"

                 <<< Back   Next >>>

1. Configuring the environment

-- /etc/hosts

[oracle]$ cat /etc/hosts

192.168.1.71 ora-db01.oracak.com ora-db01

192.168.1.72 ora-db02.oracak.com ora-db02

192.168.1.77 ora-acfs01.oracak.com ora-acfs01

-- tnsnames.ora

[oracle]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

bank=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora-db01)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=bank)))

bankstnb=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora-db02)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=bankstnb)))

bankcopy=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora-acfs01)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=bankcopy)))

-- initbankcopy.ora

[oracle]$ cat $ORACLE_HOME/dbs/initbankcopy.ora

*.db_file_name_convert='/u02/oradata/bankstnb01/','/u02/oradata/bank/','/u02/oradata/bankstnb02/','/u02/oradata/bank/','/u02/oradata/bankstnb03/','/u02/oradata/bank/'

*.log_file_name_convert='/u02/oradata/bankstnb01/','/u02/oradata/bank/'

*.standby_file_management='MANUAL'

-- note

select distinct substr(replace(name,'\','/'),1,instr(replace(name,'\','/'),'/',-1)) from (

select name from gv$datafile union

select name from gv$tempfile union

select member as name from gv$logfile

) order by 1;

2. Starting duplication

[oracle]$

export ORACLE_HOME=/u01/app/oracle/19.0/dbhome_1

export ORACLE_SID=bankcopy

$ORACLE_HOME/bin/sqlplus '/ as sysdba' <<EOF

shutdown abort;

startup nomount;

exit;

EOF

$ORACLE_HOME/bin/rman nocatalog <<EOF

CONNECT TARGET sys/Qw123456@bankstnb

CONNECT AUXILIARY sys/Qw123456@bankcopy

RUN{

ALLOCATE CHANNEL a01 DEVICE TYPE DISK;

ALLOCATE AUXILIARY CHANNEL aux01 DEVICE TYPE DISK;

ALLOCATE AUXILIARY CHANNEL aux02 DEVICE TYPE DISK;

ALLOCATE AUXILIARY CHANNEL aux03 DEVICE TYPE DISK;

ALLOCATE AUXILIARY CHANNEL aux04 DEVICE TYPE DISK;

DUPLICATE TARGET DATABASE 

FOR STANDBY

  FROM ACTIVE DATABASE

  DORECOVER nofilenamecheck USING COMPRESSED BACKUPSET

  DB_FILE_NAME_CONVERT=

                      ( '/u02/oradata/bank/','/u02/oradata/bank/',

                        '/u02/oradata/bankstnb01/','/u02/oradata/bank/',

                        '/u02/oradata/bankstnb02/','/u02/oradata/bank/',

                        '/u02/oradata/bankstnb03/','/u02/oradata/bank/');

}

exit;

EOF

3. Copying and applying archived logs for consistency

[oracle]$

$ORACLE_HOME/bin/sqlplus '/ as sysdba' <<EOF

select min(min_scn)-50 , max(max_scn)+50 from

(select current_scn as min_scn, 0 as max_scn from v\$database

 union select min(checkpoint_change#) as min_scn,max(checkpoint_change#) as max_scn from v\$datafile_header);

exit;

EOF

$ORACLE_HOME/bin/rman nocatalog <<EOF

CONNECT TARGET sys/Qw123456@bankstnb

CONNECT AUXILIARY sys/Qw123456@bankcopy

run{

 allocate channel src0 type disk;

 allocate channel src1 type disk;

 allocate channel src2 type disk;

 allocate channel src3 type disk;

 backup as copy reuse archivelog scn between 1771299 and 1773460 auxiliary format "/u02/fra/BANKCOPY/o1_mf_1_%s_%u_.arc";

}

exit;

EOF

$ORACLE_HOME/bin/rman target / nocatalog <<EOF

CATALOG START WITH '/u02/fra/BANKCOPY/' noprompt;

alter database flashback off;

recover database until available redo;

exit;

EOF

4. Replacing paths for files

[oracle]$

$ORACLE_HOME/bin/sqlplus '/ as sysdba' <<EOF

begin

  FOR z IN

(select 'alter database rename file '''||name||''' to ''/u02/oradata/bank/'||substr(name,instr(name,'/',-1)+1)||'''' name from

 (select member name from v\$logfile) order by 1)

  LOOP

begin

    execute immediate z.name;

end;

  END LOOP;

end;

/

EOF

5. Сhecking file location

$ORACLE_HOME/bin/sqlplus '/ as sysdba' <<EOF

set lin 5000;

select distinct substr(replace(name,'\','/'),1,instr(replace(name,'\','/'),'/',-1)),count(*) from (

select name from v\$datafile union

select name from v\$tempfile union

select member from v\$logfile union

select name from v\$controlfile 

union select name from V\$FLASHBACK_DATABASE_LOGFILE

) group by substr(replace(name,'\','/'),1,instr(replace(name,'\','/'),'/',-1))

order by 1;

EOF

6. Сhecking file date

$ORACLE_HOME/bin/sqlplus '/ as sysdba' <<EOF

set lin 5000 pages 5000;

select

    to_char( sysdate ,'dd.mm.yyyy hh24:mi:ss' ) as date_time_now,

    to_char( min( checkpoint_time ) ,'dd.mm.yyyy hh24:mi:ss' ) as checkpoint_min,

    to_char( max( checkpoint_time ) ,'dd.mm.yyyy hh24:mi:ss' ) as checkpoint_max

from v\$datafile_header;

EOF

                 <<< Back   Next >>>