ssh in001i97@10.217.45.86
welcomejune@2022
ssh ibm_admin@172.22.8.53
ibm_admin
ssh 10.242.98.25
ibm_admin
MX10OR90BSAB/ 10.242.98.25 SID: BTPROPQ1 Port: 1529
===================Source Side ===================
@db_status.sql
select dbid from v$database;
col file_name format a90
select file_name from dba_data_files order by file_name;
col member for a90
select member from v$logfile;
show parameter controlfile
show parameter spfile;
SQL> create pfile='/home/oracle/initCALCOPQ1.ora' from spfile;
col "Database Size" format a20
col "Free space" format a20
select round(sum(used.bytes) / 1024 / 1024 /1024) || ' GB' "Database Size"
, round(free.p / 1024 / 1024 /1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p
/
connect to catalog database.
rman target / catalog rman12/r3c0v3ry@MXRCAT
list backup of controlfile;
list backup of database;
list backup summary;
list backup of controlfile completed between "to_date('08-AUG-2021 00:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('08-AUG-2021 11:00:00','DD-MON-YYYY HH24:MI:SS')";
list backup of database completed between "to_date('12/07/2021','DD/MM/YYYY')" and "to_date('15/07/2021','DD/MM/YYYY')";
===================Target side =================
cp -p initGEMTOPQ1.ora initCALCOPQ1.ora
[oracle@mx1orrt50bsab dbs]$ cat initGEMTOPQ1.ora
*.audit_file_dest='/u01/app/oracle/admin/GEMTOPQ1/adump'
*.audit_trail='db'
*.compatible='12.2.0.1.0'
*.control_files='/fra/GEMTOPQ1/controlfiles/control01.ctl'
*.core_dump_dest='/u01/app/oracle/admin/GEMTOPQ1/cdump'
*.db_block_size=8192
*.db_create_file_dest='/fra/GEMTOPQ1/datafiles'
*.db_domain=''
*.DB_FILE_NAME_CONVERT='/datafiles/oradata/GEMTOPQ1/','/fra/GEMTOPQ1/datafiles/'
*.db_name='GEMTOUT1'
*.db_recovery_file_dest='/fra/GEMTOPQ1/archivelog'
*.db_recovery_file_dest_size=100G
*.diagnostic_dest='/u01/app/oracle'
*.LOG_FILE_NAME_CONVERT='/datafiles/oradata/GEMTOPQ1/','/fra/GEMTOPQ1/datafiles/'
*.open_cursors=300
*.pga_aggregate_target=204M
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=3072M
*.sga_target=3072M
*.undo_tablespace='UNDOTBS1'
[oracle@mx1orrt50bsab dbs]$
%s/GEMTOPQ1/CALCOPQ1/g
mkdir -p /fra/oradata/CALCOPQ1/controlfiles
mkdir -p /u01/app/oracle/admin/CALCOPQ1/cdump
mkdir -p /fra/oradata/CALCOPQ1/datafiles
mkdir -p /fra/oradata/CALCOPQ1/archivelog
ls -ld /fra/oradata/CALCOPQ1/controlfiles /u01/app/oracle/admin/CALCOPQ1/cdump /fra/oradata/CALCOPQ1/datafiles /fra/oradata/CALCOPQ1/archivelog /u01/app/oracle/admin/GEMTOPQ1/adump
export ORACLE_SID=BTOT02PQ1
echo $ORACLE_HOME
echo $PATH
sqlplus / as sysdba
startup nomount
rman target / catalog rman12/r3c0v3ry@MXRCAT
set DBID=2079546165;
run
{
allocate channel ch1 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/BTOT02PQ1/tdpo_btot02pq1.opt)';
restore controlfile from tag 'TAG20210425T030255';
release channel ch1;
}
run
{
allocate channel ch1 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/CALCOPQ1/tdpo_calcopq1.opt)';
allocate channel ch2 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/CALCOPQ1/tdpo_calcopq1.opt)';
allocate channel ch3 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/CALCOPQ1/tdpo_calcopq1.opt)';
set until time "to_date('28-APR-21 10:00:00','DD-MON-YY HH24:MI:SS')";
set newname for database to '+DG_BTOT01P_DATA';
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
release channel ch1;
release channel ch2;
release channel ch3;
}
======= or else =====
run
{
allocate channel ch1 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/FIDUCUQ1/tdpo_FIDUCUQ1.opt)';
set until time "to_date('08-AUG-21 21:00:00','DD-MON-YY HH24:MI:SS')";
set newname for database to '/fra/oradata/FIDUCUQ1/datafiles/%b';
restore database;
switch datafile all;
recover database;
release channel ch1;
}
=============================================If the db size is very big use the below way========================================================================
vi restore12aug21.cmd
run
{
allocate channel ch1 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/BTOT02PQ1/tdpo_btot02pq1.opt)';
allocate channel ch2 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/BTOT02PQ1/tdpo_btot02pq1.opt)';
allocate channel ch3 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/BTOT02PQ1/tdpo_btot02pq1.opt)';
set until time "to_date('28-APR-21 10:00:00','DD-MON-YY HH24:MI:SS')";
set newname for database to '+DG_BTOT01P_DATA';
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
release channel ch1;
release channel ch2;
release channel ch3;
}
exit;
EOF
chmod 775 restore12aug21.cmd
nohup rman target / catalog rman12/r3c0v3ry@MXRCAT cmdfile=restore12aug21.cmd log=restore12aug21.log &
jobs
tail -10f restore12aug21.log
================================================================================================================================
alter database rename file '/datafiles/oradata/CALCOPQ1/redo01.log' to '/fra/oradata/CALCOPQ1/redo01.log';
alter database rename file '/datafiles/oradata/CALCOPQ1/redo02.log' to '/fra/oradata/CALCOPQ1/redo02.log';
alter database rename file '/datafiles/oradata/CALCOPQ1/redo03.log' to '/fra/oradata/CALCOPQ1/redo03.log';
ALTER DATABASE clear logfile GROUP 1;
ALTER DATABASE clear logfile GROUP 2;
ALTER DATABASE clear logfile GROUP 3;
/u01/app/oracle/product/12.2.0.1/dbs
=============================== Post steps ============================================================
orapwd file=/u01/app/oracle/product/12.2.0.1/dbs/orapwCALCOPQ1 password=fIducpq#9 entries=10 FORCE=Y
mv spfile to spfilebak
shut immediate;
startup mount;
nid TARGET=sys/fIducpq#9 DBNAME=BTPROPQ1 SETNAME=Y
ORA-01103: database name 'BTPROPQ1' in control file is not 'BTOT01P' chnage the db name in pfile;
COMIPQ1:/u01/app/oracle/product/12.2.0.1:Y
change the db name in pfile and startup with pfile
starup;
Keep the network files .
cd $ORACLE_HOME/network/admin
ls -ltr
keep the listener
keep the tns entry.
lsnrctl status listener
tnsping tnsname
shutdown immediate;
staru mount;
alter datbase noarchivelog;
===========================================================================================================
SQL> select group_number,name,round(total_mb/1024,2) "Total_GB",round(free_mb/1024,2) "Free_GB",round(((free_mb/total_mb)*100),2) "Available%" from v$asm_diskgroup where name='DG_BT01ACEP_DATA';
list backup of controlfile completed between "to_date('08-AUG-2021 00:00:00','DD-MON-YYYY HH24:MI:SS')" and "to_date('08-AUG-2021 11:00:00','DD-MON-YYYY HH24:MI:SS')";
ALTER DATABASE ACTIVATE STANDBY DATABASE;
rman target / catalog rman12/r3c0v3ry@MXRCAT
run
{
allocate channel ch1 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/BTOT02PQ1/tdpo_btot02pq1.opt)';
RESTORE ARCHIVELOG FROM SEQUENCE 128458 UNTIL SEQUENCE 128510;
RELEASE CHANNEL ch1;
}
recover database using backup controfile until cancel;
===================
Restore database PTS01PQ1 with rman backup pieces from 28/06/2021 10:30:00 PM to the server AIX MX10OR90BSAB/ 10.242.98.25
Please execute on Wednesday 18-Aug-2021 Mexico Time.
Source: mx10or01bsab/ 10.242.18.11 SID: PTS01PQ1
Target: MX10OR90BSAB/ 10.242.98.25 SID: PTS01UT1
Port: 1549
========================================================================
prod side
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PTS01P READ WRITE
SQL> select sum(bytes/1024/1024/1024) from dba_data_files;
SUM(BYTES/1024/1024/1024)
-------------------------
21.3603516
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DG_PTS01P_DATA/PTS01P/spfilep
ts01pq1.ora
SQL> !date;hostname
Wed Aug 18 10:13:06 CDT 2021
MX10OR01BSAB
=====================================================================================================================================
PTS01PQ1:/u01/app/oracle/product/12.1.0/db_1:N
Restore database PTS01PQ1 with rman backup pieces from 28/06/2021 10:30:00 PM to the server AIX MX10OR90BSAB/ 10.242.98.25
Please execute on Wednesday 18-Aug-2021 Mexico Time.
Source: mx10or01bsab/ 10.242.18.11 SID: PTS01PQ1
Target: MX10OR90BSAB/ 10.242.98.25 SID: PTS01UT1
Port: 1549
on prod side:
=====================================================
. oraenv
PTS01PQ1
env|grep ORA
rman target / catalog rman12/r3c0v3ry@MXRCAT
RMAN> list backup of database;
FILE_NAME FILE_ID
---------------------------------------------------------------------- ----------
+DG_PTS01P_DATA/PTS01P/DATAFILE/system.266.1049030721 1
+DG_PTS01P_DATA/PTS01P/DATAFILE/sysaux.260.1049030777 3
+DG_PTS01P_DATA/PTS01P/DATAFILE/undotbs1.269.1049030821 4
+DG_PTS01P_DATA/PTS01P/DATAFILE/users.264.1049030823 6
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/treball.274.1049031925 5
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/tivoliorts.273.1049031925 2
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/ibm_auditorias.271.1051141161 7
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/ts_pts_idx.270.1051141359 8
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/ts_channelmanager_dat.259.1049031927 9
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/ts_mmprodat_dat.267.1051141265 10
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/ts_pts_dat.279.1049031931 15
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/ts_pts_dat.256.1049031931 14
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/ts_pts_dat.268.1049031929 13
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/ts_pts_dat.257.1049031929 12
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/ts_pts_dat.258.1049031927 11
+DG_PTS01P_DATA/PTS01PQ1/DATAFILE/audit_trail_ts.280.1051543423 16
SQL> select file_name,FILE_ID from dba_temp_files;
FILE_NAME FILE_ID
---------------------------------------------------------------------- ----------
+DG_PTS01P_DATA/PTS01P/TEMPFILE/temp.277.1049030875 1
+DG_PTS01P_DATA/PTS01PQ1/TEMPFILE/tivoliortempts.272.1049031927 2
SQL> select sum(bytes/1024/1024/1024) from dba_data_files;
SUM(BYTES/1024/1024/1024)
-------------------------
21.3603516
SQL> select name,open_mode,dbid from v$database;
NAME OPEN_MODE DBID
--------- -------------------- ----------
PTS01P READ WRITE 2821103701
SQL> !date;hostname
Wed Aug 18 12:49:37 CDT 2021
MX10OR01BSAB
/usr/tivoli/tsm/client/oracle/bin64/PTS01PQ1/tdpo_pts01pq1.opt
take OPT format from PROD:
RMAN> run {
2> ALLOCATE CHANNEL T1 DEVICE TYPE 'sbt_tape' PARMS "ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/PTS01PQ1/tdpo_pts01pq1.opt)" FORMAT 'PTS01PQ1_ARCHLOG_%U.arc';
3> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FILESPERSET 15 TAG "ARCH_BACKUP_18-AUG-2021_12-05";
4> RELEASE CHANNEL T1;
5> }
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG_PTS01P_DATA/PTS01P/CONTROL
FILE/current.263.1049030869, +
DG_PTS01P_FRA/PTS01P/CONTROLFI
LE/current.256.1049030869
SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 4391 INACTIVE
2 1 4392 CURRENT
3 1 4390 INACTIVE
==============================================On target side ===========
ASM disk group free space
create the PFILE;
MX10OR90BSAB:/u01/app/oracle/product/12.1.0/db_1/dbs >
vi initPTS01UT1.ora
*.audit_file_dest='/u01/app/oracle/admin/PTS01UT1/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='+DG_BTOT01P_DATA'
*.core_dump_dest='/u01/app/oracle/diag/rdbms/PTS01UT1/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DG_BTOT01P_DATA'
*.db_recovery_file_dest='+DG_DATA'
*.db_recovery_file_dest_size=50G
*.db_domain=''
*.db_name='PTS01PQ1'
*.diagnostic_dest='/u01/app/oracle'
*.DB_FILE_NAME_CONVERT='+DG_PTS01P_DATA','+DG_BTOT01P_DATA'
*.LOG_FILE_NAME_CONVERT='+DG_PTS01P_DATA','+DG_BTOT01P_DATA'
*.open_cursors=300
*.pga_aggregate_target=1024M
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3072M
*.undo_tablespace='UNDOTBS1'
keep in oratab (same as prod like only instance name) and also in pfile same as prod dbname also instance name
PTS01PQ1:/u01/app/oracle/product/12.1.0/db_1:N
. oraenv
PTS01PQ1
sqlplus / as sysdba
startup nomount;
ps -ef|grep smon ===check the
create these directories in test server
mkdir -p /u01/app/oracle/admin/PTS01UT1/adump
mkdir -p /u01/app/oracle/diag/rdbms/PTS01UT1/cdump
export ORACLE_SID=PTS01PQ1
echo $ORACLE_HOME
echo $PATH
sqlplus / as sysdba
startup nomount
rman target / catalog rman12/r3c0v3ry@MXRCAT
set DBID=2821103701;
run
{
allocate channel ch1 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/PTS01PQ1/tdpo_pts01pq1.opt)';
restore controlfile from tag 'WEEKLY_FULL0_26-JUN-2021_21-01';
release channel ch1;
}
==========Here we will get controlfile name keep that in pfile ===============
sqlplus / as sysdba
alter database mount;
exit
hostname;date
shut immediate;
cd $ORACLE_HOME/dbs
vi initPTS01PQ1.ora
.control_files='+DG_BTOT01P_DATA/PTS01P/CONTROLFILE/current.299.1080921079'
sqlplus / as sysdba
startup nomount;
SQL> create spfile from pfile;
SQL> startup mount;
vi restore18aug21.cmd
run
{
allocate channel ch1 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/BTOT02PQ1/tdpo_btot02pq1.opt)';
allocate channel ch2 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/BTOT02PQ1/tdpo_btot02pq1.opt)';
allocate channel ch3 type 'sbt_tape' parms 'ENV=(BLKSIZE=104857600,TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/BTOT02PQ1/tdpo_btot02pq1.opt)';
set until time "to_date('28-APR-21 10:00:00','DD-MON-YY HH24:MI:SS')";
set newname for database to '+DG_BTOT01P_DATA';
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
release channel ch1;
release channel ch2;
release channel ch3;
}
exit;
EOF
chmod 775 restorePTSO118aug21.cmd
nohup rman target / catalog rman12/r3c0v3ry@MXRCAT cmdfile=restorePTSO118aug21.cmd log=restorePTSO118aug21.cmd &
nid TARGET=sys/sys123 DBNAME=SFPROD SETNAME=Y
sqlplus / as sysdba
startup
add TNS entry in tns.ora file
lsnrctl status LISTENER_SFPROD
shut immediate
startup mount
alter database noarchivelog;
rename tempfiles
select * from dba_temp_files;
Comments
Post a Comment