02.04. updating "COPY STANDBY" on demand

                  <<< Back   Next >>>
1. Startup database "COPY STANDBY"
[oracle]$
export ORACLE_SID=bankcopy
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/19.0/dbhome_1

$ORACLE_HOME/bin/rman target / nocatalog <<EOF
shutdown immediate;
startup mount;

EOF

2. Updating the date files for the nearest moment in time
$ORACLE_HOME/bin/rman target / nocatalog <<EOF
run{
allocate channel src01 type disk;
allocate channel src02 type disk;
allocate channel src03 type disk;
allocate channel src04 type disk;
RECOVER DATABASE FROM SERVICE bankstnb USING COMPRESSED BACKUPSET NOREDO SECTION SIZE 32g;
}
EOF

3. Сopy the fresh control file

$ORACLE_HOME/bin/rman target / nocatalog <<EOF
shutdown immediate;
startup nomount;
RESTORE STANDBY CONTROLFILE FROM SERVICE bankstnb;
alter database mount;
EOF

4. Droping STANDBY REDO

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

begin
  FOR z IN (SELECT group# from v\$logfile where TYPE in ('STANDBY'))
  LOOP
begin
    execute immediate 'ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP '||z.group#;
    execute immediate 'alter database drop standby logfile group '||z.group#;
    --exception when others then NULL;
end;
  END LOOP;
end;
/

EOF

5. Getting list data files which were added after last update

$ORACLE_HOME/bin/sqlplus '/ as sysdba' <<EOF
set lin 5000 pages 5000;
select ln_sql from (
select 'set newname for datafile '||file#||' to ''/u02/oradata/bank/'||substr(name,instr(name,'/',-1)+1)||''';' as ln_sql, creation_time from v\$datafile 
union
select 'RESTORE DATAFILE '||file#||' FROM SERVICE bankstnb SECTION SIZE 4g;' as ln_sql, creation_time from v\$datafile
) where creation_time>=sysdate-3 order by 1 desc;
EOF

6. Copying new data files

$ORACLE_HOME/bin/rman target / nocatalog <<EOF
run{
allocate channel src01 type disk;
allocate channel src02 type disk;
allocate channel src03 type disk;
allocate channel src04 type disk;

set newname for datafile 29 to '/u02/oradata/bank/undotbs03.dbf';

set newname for datafile 28 to '/u02/oradata/bank/system06.dbf';

set newname for datafile 27 to '/u02/oradata/bank/sysaux05.dbf';

set newname for datafile 26 to '/u02/oradata/bank/banktbs08.dbf';

set newname for datafile 25 to '/u02/oradata/bank/bankindx08.dbf';

RESTORE DATAFILE 29 FROM SERVICE bankstnb SECTION SIZE 4g;

RESTORE DATAFILE 28 FROM SERVICE bankstnb SECTION SIZE 4g;

RESTORE DATAFILE 27 FROM SERVICE bankstnb SECTION SIZE 4g;

RESTORE DATAFILE 26 FROM SERVICE bankstnb SECTION SIZE 4g;

RESTORE DATAFILE 25 FROM SERVICE bankstnb SECTION SIZE 4g;

}

exit;
EOF

### note, you can use

rman> set newname for database to '/u02/oradata/bank/%u';

### ......

rman> catalog start with '/u02/oradata/bank/' noprompt;

rman> switch datafile all;

5. Replacing paths for files to path: /u02/oradata/bank/
$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 name from v\$datafile
  union select name from v\$tempfile 
  union select member as name from v\$logfile
) order by 1)
  LOOP
begin
    execute immediate z.name;
exception when others then NULL;
end;
  END LOOP;
end;
/
EOF

6. С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 
) group by substr(replace(name,'\','/'),1,instr(replace(name,'\','/'),'/',-1))
order by 1;
EOF

7. Updating information about the archive logs

$ORACLE_HOME/bin/rman target / nocatalog <<EOF
crosscheck archivelog all;
delete force noprompt expired archivelog all;
EOF

8. Сhecking data file time
$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

9. Copying and applying archived logs for consistency
[oracle]$
$ORACLE_HOME/bin/sqlplus '/ as sysdba' <<EOF
select min(min_scn)-100 , max(max_scn)+100 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/***@bankstnb
CONNECT AUXILIARY sys/***@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 2176652 and 2178233 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

10. Сhecking data file time
$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

11. Shutting down database

$ORACLE_HOME/bin/rman target / nocatalog <<EOF
shutdown immediate;
EOF

                  <<< Back   Next >>>