Source: Ora11g1:1521:TEST2
Target: Ora11g2:1521:TEST1
Follow the below steps to Keep in MM
Click on Targets->Databases Search with DB-NAME in left hand side search box->Click on Database->Left hand side there will be dropdown list "Oracle Databases->Control->Create Notification Blackout->Scroll Down and ther will be schedule option->Mention The Duration and Click on Submit
Follow the same steps for target and Source.
****************************************************************************************************************************************************************************
4. Take Consistant backup from source:
---------------------------------------
Once Blackout Window done, Create Directory in /backup location and check Free space in /backup directory and check the Database actual size
Note:-/backup file sysetm free space should be higher than DB actual Size(If we free free space not sufficient Remove old Backups from /backup directory)
Check Database actual size
SQL>select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST2 READ WRITE
SQL> SELECT 'Physical Size (GB):' ||TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99) FROM DBA_DATA_FILES union
SELECT 'Actual Size (GB) :' || TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99) FROM DBA_SEGMENTS union
SELECT 'SGA Size (GB):' || TO_CHAR(SUM(VALUE)/1024/1024/1024,9999999.99) FROM V$SGA; 2 3
'PHYSICALSIZE(GB):'||TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99)
--------------------------------------------------------------------------------
Actual Size (GB) : 59.48
Physical Size (GB): 162.72
SGA Size (GB): 15.00
[oracle@Ora11g10-TTESTTT] /backup > df -h .
Filsystem Storlek Använt Ledigt Anv% Monterat på
/dev/mapper/oraclevg-lv_backup 374G 281G 75G 79% /backup
[oracle@Ora11g10-TTESTTT] /backup >
Looks like /backup file system has only 75GB free space , Reclaim more free space by removing old backups
[oracle@Ora11g10-TTESTTT] /backup > du -sh *
58G TEST2_27FEB_CONSISTANT
18G TEST2_Basline_BKP
74G TEST2_consistent_bkp_28thJan
7,7G TEST2_feb-15
7,8G TEST2_feb-18
7,9G TEST2_feb-20
8,2G TEST2_feb-21
8,3G TEST2_feb-26
8,3G TEST2_feb-27
9,7G TEST2_jan-16
9,8G TEST2_jan-24
180K GDPR_TESTST
[oracle@Ora11g10-TTESTTT] /backup >rm -rf TEST2_jan-16 TEST2_jan-24
[oracle@Ora11g10-TTESTTT] /backup >du -sh *
58G TEST2_27FEB_CONSISTANT
18G TEST2_Basline_BKP
74G TEST2_consistent_bkp_28thJan
7,7G TEST2_feb-15
7,8G TEST2_feb-18
7,9G TEST2_feb-20
8,2G TEST2_feb-21
8,3G TEST2_feb-26
8,3G TEST2_feb-27
180K GDPR_TESTST
[oracle@Ora11g10-TTESTTT] /backup >du -h .
Filsystem Storlek Använt Ledigt Anv% Monterat på
/dev/mapper/oraclevg-lv_backup 374G 281G 95G 64% /backup
[oracle@Ora11g10-TTESTTT] /backup >
We have sufficient free space now, Create new directory in /backup location and keep DB in mount and start DB consistant backup
[oracle@Ora11g10-TEST2] /home/oracle > cd /backup/
[oracle@Ora11g10-TEST2] /backup > mkdir TEST2_27FEB_CONSISTANT
[oracle@Ora11g10-TEST2] /backup > cd TEST2_27FEB_CONSISTANT
[oracle@Ora11g10-TEST2] /backup/TEST2_27FEB_CONSISTANT > pwd
/backup/TEST2_27FEB_CONSISTANT
/backup/CARS_16MAR2020_CONSISTENTBKP
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size 7662000 bytes
Variable Size 1.5636E+10 bytes
Database Buffers 402653184 bytes
Redo Buffers 59445248 bytes
Database mounted.
[oracle@Ora11g10-TEST2] /home/oracle > cd /backup/
[oracle@Ora11g10-TEST2] /backup > cd TEST2_27FEB_CONSISTANT
[oracle@Ora11g10-TEST2] /backup/TEST2_27FEB_CONSISTANT > pwd
/backup/TEST2_27FEB_CONSISTANT
Change backup piece location and run below script from RMAN prompt to take backup.
RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/backup/CARS_16MAR2020_CONSISTENTBKP/backup_%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/backup/CARS_16MAR2020_CONSISTENTBKP/backup_%U';
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT '/backup/CARS_16MAR2020_CONSISTENTBKP/backup_%U';
BACKUP DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT '/backup/CARS_16MAR2020_CONSISTENTBKP/cntrl_%s_%p_%t';
RELEASE CHANNEL disk1;
RELEASE CHANNEL disk2;
RELEASE CHANNEL disk3;
}
Once backup completed check backup pieces
[oracle@Ora11g10-TEST2] /backup/TEST2_27FEB_CONSISTANT > ls -lrt
totalt 60612852
-rw-r----- 1 oracle 54321 5046747136 27 feb 09.36 backup_r3upir4q_1_1
-rw-r----- 1 oracle 54321 15743729664 27 feb 09.37 backup_r2upir4q_1_1
-rw-r----- 1 oracle 54321 41152315392 27 feb 09.37 backup_r1upir4q_1_1
-rw-r----- 1 oracle 54321 124747776 27 feb 09.37 cntrl_96100_1_1033465063
Exit from rman prompt and connect sql and start DB
sql>alter database open;
Once Backup completed from source sent email to app owner to release DB.
****************************************************************************************************************************************************************************
5. Take Consistant backup from Target:
---------------------------------------
There are tow ways to take target db backup A. consistant backup B. inconsistant backup
A. Consistant backup :
Once Blackout Window done, Create Directory in /backup location and check Free space in /backup directory and check the Database actual size
Note:-/backup file sysetm free space should be higher than DB actual Size(If we free free space not sufficient Remove old Backups from /backup directory)
Check Database actual size
SQL>select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST1 READ WRITE
SQL> SELECT 'Physical Size (GB):' ||TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99) FROM DBA_DATA_FILES union
SELECT 'Actual Size (GB) :' || TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99) FROM DBA_SEGMENTS union
SELECT 'SGA Size (GB):' || TO_CHAR(SUM(VALUE)/1024/1024/1024,9999999.99) FROM V$SGA; 2 3
'PHYSICALSIZE(GB):'||TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99)
--------------------------------------------------------------------------------
Actual Size (GB) : 59.48
Physical Size (GB): 162.72
SGA Size (GB): 15.00
[oracle@Ora11g2] /backup > df -h .
Filsystem Storlek Använt Ledigt Anv% Monterat på
/dev/mapper/vg_ora-lv_backup 492G 371G 96G 80% /backup
Seems we have sufficient free space in /backup file system, Create new directory in /backup location to keep target db backup files and keep DB in mount and start DB consistant backup
[oracle@Ora11g2] /backup > cd /backup/
[oracle@Ora11g2] /backup > mkdir TEST1_27FEB_CONSISTANT
[oracle@Ora11g2] /backup/TEST1_27FEB_CONSISTANT > pwd
/backup/TEST1_27FEB_CONSISTANT
[oracle@Ora11g2] /backup/TEST1_27FEB_CONSISTANT >
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.0737E+10 bytes
Fixed Size 7654304 bytes
Variable Size 9697231968 bytes
Database Buffers 1006632960 bytes
Redo Buffers 25899008 bytes
Database mounted.
Change backup piece location and run below script from RMAN prompt to take backup.
RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/backup/TEST1_27FEB_CONSISTANT/backup_%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/backup/TEST1_27FEB_CONSISTANT/backup_%U';
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT '/backup/TEST1_27FEB_CONSISTANT/backup_%U';
BACKUP DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT '/backup/TEST1_27FEB_CONSISTANT/cntrl_%s_%p_%t';
RELEASE CHANNEL disk1;
RELEASE CHANNEL disk2;
RELEASE CHANNEL disk3;
}
Once backup completed check backup pieces
[oracle@Ora11g2] /backup/TEST1_27FEB_CONSISTANT > ls -lrt
totalt 13167344
-rw-r-----. 1 oracle asmadmin 2863529984 27 feb 11.47 TEST03_95upj2fc_1_1_20200227_98597_1_Full
-rw-r-----. 1 oracle asmadmin 10488840192 27 feb 12.03 TEST03_94upj2fc_1_1_20200227_98596_1_Full
-rw-r-----. 1 oracle asmadmin 10488840192 27 feb 12.03 TEST03_93upj2fc_1_1_20200227_98596_1_Full
-rw-r-----. 1 oracle asmadmin 130973696 27 feb 12.03 TEST03_96upj3p7_1_1_20200227_CTRL
B. Inconsistant Backup:
For inconsistant backup we have already scheduled backup script as part of daily backup.
We can use the same backup script to take inconsistant backup
[oracle@Ora11g2] /u01/app > cd /u01/app
[oracle@Ora11g2] /u01/app > ls -lrt
totalt 40
drwxrwxr-x. 19 oracle oinstall 4096 6 feb 2018 diag
drwxr-xr-x. 2 oracle oinstall 4096 30 apr 2018 checkpoints
drwxr-xr-x. 3 grid oinstall 4096 3 jan 2019 log
drwxrwxr-x. 23 oracle oinstall 4096 10 apr 2019 oracle
drwxr-xr-x. 2 oracle oinstall 4096 3 jun 2019 BkpScript
drwxr-xr-x. 5 oracle oinstall 4096 17 dec 10.28 cfgtoollogs
drwxr-xr-x. 10 oracle oinstall 4096 17 dec 11.22 admin
drwxr-x---. 9 oracle asmadmin 4096 17 dec 11.39 audit
drwxr-xr-x. 43 oracle oinstall 4096 24 jan 12.38 Deployments
drwxrwx---. 7 grid oinstall 4096 28 feb 06.19 oraInventory
[oracle@Ora11g2] /u01/app > cd BkpScript
[oracle@Ora11g2] /u01/app/BkpScript > ls -lrt
totalt 8
-rwxr--r--. 1 oracle oinstall 4278 6 feb 2019 backup.sh
[oracle@Ora11g2] /u01/app/BkpScript > cat backup.sh
#!/bin/bash
###################################################################
#Script Name : backup.sh
######################## VERSION ##################################
###################################################################
echo "Below are the database's list running on this server"
db=`ps -ef | grep pmon | egrep -v 'ASM|grep' | awk '{print $8}' | cut -d_ -f3`
for ORACLE_SID in $db ; do
echo "*)$ORACLE_SID"
done
export choice_flag=1
while [ $choice_flag -eq 1 ]
do
read -e -p "Please provide the database name, you wanted to backed up: " DBNAME
read -e -p "Are you sure (Y/N): " ACTION
if [ $ACTION == "Y" ] ; then
export choice_flag=0
export ORACLE_SID=$DBNAME
export ORACLE_HOME=`cat /etc/oratab| grep -w "$ORACLE_SID"|cut -f2 -d ':'`
export PATH=$ORACLE_HOME/bin:$PATH
BACKUPDIR=/backup/${ORACLE_SID}_`date +%h-%d`
if [ ! -d "$BACKUPDIR" ] ;
then
mkdir -p $BACKUPDIR
echo 'Starting new Input file' >${BACKUPDIR}/Input.log
echo "Please provide the database name, you wanted to backed up: $DBNAME" >> ${BACKUPDIR}/Input.log
echo "Are you sure (Y/N): $ACTION" >> ${BACKUPDIR}/Input.log
echo "Backup directory Created: $BACKUPDIR" >> ${BACKUPDIR}/Input.log
else
rm ${BACKUPDIR}/*
echo 'Starting New Input file' >${BACKUPDIR}/Input.log
echo "Please provide the database name, you wanted to backed up: $DBNAME" >> ${BACKUPDIR}/Input.log
echo "Are you sure (Y/N): $ACTION" >> ${BACKUPDIR}/Input.log
echo "${BACKUPDIR} already exists" >> ${BACKUPDIR}/Input.log
echo "Deleted all the files under ${BACKUPDIR}" >> ${BACKUPDIR}/Input.log
fi
LOGFILE=${BACKUPDIR}/${ORACLE_SID}_RMAN_BKP.log
FORMAT_DATA="format '${BACKUPDIR}/%d_%U_%T_%s_%p_Full${BACKUP_TYPE}'"
FORMAT_CF="format '${BACKUPDIR}/%d_%U_%T_CTRL'"
FORMAT_ARCHIVE="format '${BACKUPDIR}/%d_%U_%T_ARCH'"
read -e -p "You want compressed backup?(Y/N): " COMPRESS
if [ $COMPRESS == "Y" ] ; then
echo "You want compressed backup?(Y/N): $ACTION" >> ${BACKUPDIR}/Input.log
COMPRESS='AS COMPRESSED BACKUPSET'
else
COMPRESS=''
fi
echo "Shutting down the database $ORACLE_SID on time $(date)" >> ${BACKUPDIR}/Input.log
sqlplus -s / as sysdba <<EOF 2>&1
shutdown immediate;
exit;
EOF
echo "Shutdown database $ORACLE_SID completed on time $(date)" >> ${BACKUPDIR}/Input.log
echo "Starting the backup of database $ORACLE_SID in mount mode on time $(date)" >> ${BACKUPDIR}/Input.log
rman target / log=$LOGFILE <<EOF 2>&1
run {
startup mount;
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK;
backup ${COMPRESS} database ${FORMAT_DATA};
BACKUP CURRENT CONTROLFILE ${FORMAT_CF};
RELEASE CHANNEL disk1;
RELEASE CHANNEL disk2;
alter database open;
}
exit;
EOF
if [ `egrep -w 'RMAN-|ORA-' ${BACKUPDIR}/${ORACLE_SID}_RMAN_BKP.log|wc -l` -gt 0 ];
then
echo "\nBackup failed, Check with DBA's";
echo "Backup for database $ORACLE_SID failed on time $(date)" >> ${BACKUPDIR}/Input.log
echo -e "Hi DBA's, \n\n Backup failed for database $ORACLE_SID. \n Please check Input File and backup log for more information and do the needful.\n\nThanks & Regards,\nInfosys Ettsak Infra Team" | mailx -s "Backup for Databases on server $HOSTNAME" -a ${BACKUPDIR}/${ORACLE_SID}_RMAN_BKP.log -a ${BACKUPDIR}/Input.log -r Infosys-Ettsak-Infra-Team@lansforsakringar.se Infosys-Ettsak-Infra-Team@lansforsakringar.se
else
echo "\nBackup taken successfully at location $BACKUPDIR";
echo "Backup of database $ORACLE_SID taken successfully on time $(date) at location ${BACKUPDIR}" >> ${BACKUPDIR}/Input.log
echo -e "Hi, \n\n Backup taken successfully for the database $ORACLE_SID. \n PFA for the Input File and backup log.\n\nThanks & Regards,\nInfosys Ettsak Infra Team" | mailx -s "Backup for Databases on server $HOSTNAME" -a ${BACKUPDIR}/${ORACLE_SID}_RMAN_BKP.log -a ${BACKUPDIR}/Input.log -r Infosys-Ettsak-Infra-Team@lansforsakringar.se Infosys-Ettsak-Infra-Team@lansforsakringar.se
fi
else
echo "Ohh Wrong info, Enter the details again"
fi
done
[oracle@Ora11g2] /u01/app/BkpScript >
[oracle@Ora11g2] /u01/app/BkpScript >./backup.sh
Supply runtime variables to take backup (Like SID, Compresed(Y/N)
Go to /backup location and monitor logfile and backup pieces
[oracle@Ora11g2] /home/oracle > cd /backup/TEST1_feb-27/
[oracle@Ora11g2] /backup/TEST1_feb-27 > ls -lrt
totalt 10594000
-rw-r-----. 1 oracle asmadmin 2887688192 27 feb 09.52 TEST1_mvupirmf_1_1_20200227_95967_1_Full
-rw-r-----. 1 oracle asmadmin 7835803648 27 feb 10.02 TEST1_n0upirmg_1_1_20200227_95968_1_Full
-rw-r-----. 1 oracle asmadmin 124747776 27 feb 10.02 TEST1_n1upismi_1_1_20200227_CTRL
-rw-r--r--. 1 oracle oinstall 3303 27 feb 10.03 TEST1_RMAN_BKP.log
-rw-r--r--. 1 oracle oinstall 552 27 feb 10.03 Input.log
[oracle@Ora11g2] /backup/TEST1_feb-27 > tail -20 TEST1_RMAN_BKP.log
including current control file in backup set
channel disk1: starting piece 1 at 27-FEB-20
channel disk1: finished piece 1 at 27-FEB-20
piece handle=/backup/TEST1_feb-27/TEST1_n1upismi_1_1_20200227_CTRL tag=TAG20200227T100258 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-FEB-20
Starting Control File and SPFILE Autobackup at 27-FEB-20
piece handle=+ARCHIVE/TEST1/AUTOBACKUP/2020_02_27/s_1033465513.1295.1033466581 comment=NONE
Finished Control File and SPFILE Autobackup at 27-FEB-20
released channel: disk1
released channel: disk2
Statement processed
RMAN>
Recovery Manager complete.
****************************************************************************************************************************************************************************
6. Once backup completed start DB and check SYS,SYSTEM and TEST schema password connectivity(Get Password from password sheet and keep it int handy to alter same password to users post restoration)
sys:hf674jgar39e
system:hf674jgar39e
TEST:JjxsTrWy4mCk395c
Connect SQL prompt start DB and test schema passwords.
SQL>alter database open;
SQL>conn sys/hf674jgar39e
connected.
SQL>show user
USER is "SYS"
SQL>conn system/hf674jgar39e
connected.
SQL>show user
USER is "SYSTEM"
SQL>conn TEST/JjxsTrWy4mCk395c
connected.
SQL>show user
USER is "TEST"
SQL>conn / as sysdba
connected.
SQL>show user
USER is "SYS"
As per above out put all passwords are working as expected.
****************************************************************************************************************************************************************************
7. Take backup of B* schemas metadata and assigned roles backup in target DB.
Steps:
A. Take backup of existing A or B id DB users from TESTINF and CFGDEV2 for your respective db
set linesize 200
set pages 2000
set long 5000
select dbms_metadata.get_ddl('USER', username) || ';' usercreate from dba_users where username like 'B%';
Sample output:
USERCREATE
--------------------------------------------------------------------------------
CREATE USER "ABC" IDENTIFIED BY VALUES 'S:84C641C9FE844478144CB83AB2997033
8C3DCD41E46ABE62D0F17C636F97;T:7D6D01EFBD025AF37D58B7EE044FBAE3A4388E6501DCCBC39
9962D20C11D4CFB226570CFB0DFC7651B3B07223C853DAAEEBE163167644DFE5615EF0BB6C03068E
ED6685E570FA9F06CDAF4EC3B455EE7;E8E5A9278888848D'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
4 rows selected.
B. Backup roles assigned to A or B ids
SELECT 'GRANT '||GRANTED_ROLE||' to '||GRANTEE||';' FROM DBA_ROLE_PRIVS WHERE GRANTEE like 'B%';
Sample Output:
SQL> SELECT 'GRANT '||GRANTED_ROLE||' to '||GRANTEE||';' FROM DBA_ROLE_PRIVS WHERE GRANTEE like 'B%';
'GRANT'||GRANTED_ROLE||'TO'||GRANTEE||';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GRANT CONNECT to ABC;
11 rows selected.
NOTE: Keep user creation and roles details in sepanotepad((Better send as email to yourself)
****************************************************************************************************************************************************************************
8. Create target DB pfile from SPFILE;
SQL>create pfile='/backup/initCARS.ora' from spfile;
File created.
****************************************************************************************************************************************************************************
9. Restart target DB in mount restriction mode and drop DB.
SQL> select name from v$database;
NAME
---------
TEST1
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict;
ORACLE instance started.
Total System Global Area 1.0737E+10 bytes
Fixed Size 7654304 bytes
Variable Size 9697231968 bytes
Database Buffers 1006632960 bytes
Redo Buffers 25899008 bytes
Database mounted.
SQL> drop database;
Database dropped.
****************************************************************************************************************************************************************************
10 .Create same directory in target server as same as source directory name in /backup file system.
This directory will be used for copying the files from source to destination
[oracle@Ora11g2] /home/oracle > cd /backup/
[oracle@Ora11g2] /backup > mkdir TEST2_27FEB_CONSISTANT
[oracle@Ora11g2] /backup/TEST2_27FEB_CONSISTANT > pwd
/backup/TEST2_27FEB_CONSISTANT
****************************************************************************************************************************************************************************
11. Copy backup files from source to target using scp utility
Go to source server
[oracle@Ora11g10-TTESTTT] /backup/TEST2_27FEB_CONSISTANT > ls -lrt
totalt 60612852
-rw-r----- 1 oracle 54321 5046747136 27 feb 09.36 backup_r3upir4q_1_1
-rw-r----- 1 oracle 54321 15743729664 27 feb 09.37 backup_r2upir4q_1_1
-rw-r----- 1 oracle 54321 41152315392 27 feb 09.37 backup_r1upir4q_1_1
-rw-r----- 1 oracle 54321 124747776 27 feb 09.37 cntrl_96100_1_1033465063
[oracle@Ora11g10-TTESTTT] /backup/TEST2_27FEB_CONSISTANT > scp backup_r1upir4q_1_1 oracle@Ora11g1:/backup/TEST2_27FEB_CONSISTANT
Password:
backup_r1upir4q_1_1 100% 38GB 15.8MB/s 41:19
[oracle@Ora11g10-TTESTTT] /backup/TEST2_27FEB_CONSISTANT > scp backup_r2upir4q_1_1 oracle@Ora11g1:/backup/TEST2_27FEB_CONSISTANT
Password:
backup_r2upir4q_1_1 100% 38GB 15.8MB/s 41:19
[oracle@Ora11g10-TTESTTT] /backup/TEST2_27FEB_CONSISTANT > scp backup_r3upir4q_1_1 oracle@Ora11g1:/backup/TEST2_27FEB_CONSISTANT
Password:
backup_r3upir4q_1_1 100% 38GB 15.8MB/s 41:19
[oracle@Ora11g10-TTESTTT] /backup/TEST2_27FEB_CONSISTANT > scp cntrl_96100_1_1033465063 oracle@Ora11g1:/backup/TEST2_27FEB_CONSISTANT
Password:
cntrl_96100_1_1033465063 100% 119MB 6.7MB/s 00:17
Go to Target Server and check copied files in respective directory location.
[oracle@Ora11g2] /home/oracle > cd /backup/
[oracle@Ora11g2] /backup > cd TEST2_27FEB_CONSISTANT
[oracle@Ora11g2] /backup/TEST2_27FEB_CONSISTANT > ls -lrt
totalt 60612852
-rw-r----- 1 oracle 54321 5046747136 27 feb 09.36 backup_r3upir4q_1_1
-rw-r----- 1 oracle 54321 15743729664 27 feb 09.37 backup_r2upir4q_1_1
-rw-r----- 1 oracle 54321 41152315392 27 feb 09.37 backup_r1upir4q_1_1
-rw-r----- 1 oracle 54321 124747776 27 feb 09.37 cntrl_96100_1_1033465063
Note: Till now all activities completed in source server(Disconnect from source server for safer practice)
****************************************************************************************************************************************************************************
From here all steps to be performed on target DB
12. Rename existing target DB Pfile and copy pfile file which is newly created pfile from spfile into $ORACLE_HOME/dbs location
Rename existing Pfile.
[oracle@Ora11g2] /home/oracle > echo $ORACLE_SID
TEST1
[oracle@Ora11g2] /home/oracle > cd $ORACLE_HOME/dbs
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs > ls -lrt initTEST1.ora
-rw-r--r--. 1 oracle oinstall 59 27 feb 14.18 initTEST1.ora
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs >mv initTEST1.ora initTEST1.ora_FEB27
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs > ls -lrt initTEST1*
-rw-r--r--. 1 oracle oinstall 59 27 feb 14.18 initTEST1.ora_FEB27
Copy Newly created pfile into
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs > cp /backup/initTEST1.ora /u01/app/oracle/product/12.1.0/dbhome_1/dbs
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs >cat initTEST1
TEST1.__data_transfer_cache_size=0
TEST1.__db_cache_size=3019898880
TEST1.__java_pool_size=469762048
TEST1.__large_pool_size=402653184
TEST1.__oracle_base='/u01/app'#ORACLE_BASE set from environment
TEST1.__pga_aggregate_target=5637144576
TEST1.__sga_target=10468982784
TEST1.__shared_io_pool_size=469762048
TEST1.__shared_pool_size=5905580032
TEST1.__streams_pool_size=134217728
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='+DATA/TEST1/CONTROLFILE/current.299.1030895633','+LOG/TEST1/CONTROLFILE/current.272.1030895633'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+LOG'
*.db_domain=''
*.db_name='TEST1'#Reset to original value by RMAN
*.db_recovery_file_dest_size=50G
*.db_recovery_file_dest='+ARCHIVE'
*.db_unique_name='TEST1'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='ARC%S_%R.%T'
*.memory_max_target=20G
*.memory_target=15G
*.open_cursors=300
*.processes=3000
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'
In pfile do the following changes
A. Remove all lines "starting with TEST1._*"
B. Remove controlfile completed path and keep only disk group name(+DATA,+LOG)
c. Change DB_NAME as Source DB Name
D. Do not change DB uniquename and Keep as it is (*.db_unique_name='TEST1')
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs >vi initTEST1
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs >cat initTEST1
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='+DATA','+LOG'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+LOG'
*.db_domain=''
*.db_name='TEST2'#Reset to original value by RMAN
*.db_recovery_file_dest_size=50G
*.db_recovery_file_dest='+ARCHIVE'
*.db_unique_name='TEST1'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='ARC%S_%R.%T'
*.memory_max_target=20G
*.memory_target=15G
*.open_cursors=300
*.processes=3000
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'
****************************************************************************************************************************************************************************
13. Startup target db in nomount state with modified pfile and restore controlfile from backup(Which is copied from source to target DB)
SQL> startup nomount pfile= /u02/app/oracle/product/12.1.0/dbhome_1/dbs/initTEST1.ora
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 7654304 bytes
Variable Size 1.5703E+10 bytes
Database Buffers 5704253440 bytes
Redo Buffers 59453440 bytes
[oracle@Ora11g2] /home/oracle > cd /backup/
[oracle@Ora11g2] /backup > cd TEST2_27FEB_CONSISTANT
[oracle@Ora11g2] /backup/TEST2_27FEB_CONSISTANT > ls -lrt
totalt 60612852
-rw-r----- 1 oracle 54321 5046747136 27 feb 09.36 backup_r3upir4q_1_1
-rw-r----- 1 oracle 54321 15743729664 27 feb 09.37 backup_r2upir4q_1_1
-rw-r----- 1 oracle 54321 41152315392 27 feb 09.37 backup_r1upir4q_1_1
-rw-r----- 1 oracle 54321 124747776 27 feb 09.37 cntrl_96100_1_1033465063
[oracle@Ora11g2] /backup/TEST2_27FEB_CONSISTANT > rman terget /
RMAN> run
{
allocate channel c1 device type disk;
restore controlfile from '/backup/TEST_backup_7Feb20/TEST_k5untott_1_1_20200207_CTL';
release channel c1;
}2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=2415 device type=DISK
Starting restore at 27-FEB-20
channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:00:01
output file name=+DATA/TEST1/CONTROLFILE/current.302.1033470943
output file name=+LOG/TEST1/CONTROLFILE/current.272.1033470943
Finished restore at 27-FEB-20
Note: Save Restore information in somewhere , We need those newly created controlfile location to update in pfile.
SQL> alter database mount;
Databse altered.
Shutdown DB and update controlfile details in pfile and start db in mount state.
SQL> shut immediate;
Database not mounted.
ORACLE instance shut down.
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs >vi initTEST1
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs >cat initTEST1
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='+DATA/TEST1/CONTROLFILE/current.302.1033470943','+LOG/TEST1/CONTROLFILE/current.272.1033470943'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+LOG'
*.db_domain=''
*.db_name='TEST2'#Reset to original value by RMAN
*.db_recovery_file_dest_size=50G
*.db_recovery_file_dest='+ARCHIVE'
*.db_unique_name='TEST1'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='ARC%S_%R.%T'
*.memory_max_target=20G
*.memory_target=15G
*.open_cursors=300
*.processes=3000
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'
SQL> startup mount pfile= /u02/app/oracle/product/12.1.0/dbhome_1/dbs/initTEST1.ora
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 7654304 bytes
Variable Size 1.5703E+10 bytes
Database Buffers 5704253440 bytes
Redo Buffers 59453440 bytes
Database mounted.
****************************************************************************************************************************************************************************
14. Restore DB and open db in Read write mode.
[oracle@Ora11g2] /backup/TEST2_27FEB_CONSISTANT > rman terget /
RMAN> CATALOG BACKUPPIECE '/backup/TEST_backup_7Feb20/TEST_k1untn6v_1_1_20200207_76417_1_Full';
cataloged backup piece
backup piece handle=/backup/TEST2_27FEB_CONSISTANT/backup_r3upir4q_1_1' RECID=38770 STAMP=1031741276
RMAN> CATALOG BACKUPPIECE '/backup/TEST_backup_7Feb20/TEST_k0untn6v_1_1_20200207_76416_1_Full';
cataloged backup piece
backup piece handle=/backup/TEST2_27FEB_CONSISTANT/backup_r2upir4q_1_1' RECID=38770 STAMP=1031741276
RMAN> CATALOG BACKUPPIECE '/backup/TEST2_27FEB_CONSISTANT/backup_r1upir4q_1_1';
cataloged backup piece
backup piece handle=/backup/TEST2_27FEB_CONSISTANT/backup_r1upir4q_1_1' RECID=38770 STAMP=1031741276
RUN {
allocate channel c1 device type disk;
set newname for database to '+DATA';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
release channel c1;
}
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=1 device type=DISK
Starting restore at 07-FEB-20
-----------------------------
-----------------------------
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:44:05
Finished restore at 07-FEB-20
released channel: c1
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST2 MOUNTED
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+LOG/TEST2/ONLINELOG/group_4.258.1031508679
+LOG/TEST2/ONLINELOG/group_3.257.1031508653
+LOG/TEST2/ONLINELOG/group_1.264.1031508701
+LOG/TEST2/ONLINELOG/group_2.263.1031508633
4. rows selected.
As per above output all redologfiles created in +LOG disk group no need to rename logfiles
Note: If redologfiles created with different name need to rename as per below reference.
SQL> alter database rename file '+LOG/TEST/ONLINELOG/group_4.258.1031508679' to '+LOG';
Database altered.
SQL> alter database rename file '+LOG/TEST/ONLINELOG/group_3.257.1031508653' to '+LOG';
Database altered.
SQL> alter database rename file '+LOG/TEST/ONLINELOG/group_1.264.1031508701' to '+LOG';
Database altered.
SQL> alter database rename file '+LOG/TEST/ONLINELOG/group_2.263.1031508633' to '+LOG';
Database altered.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+LOG
+LOG
+LOG
+LOG
4 rows selected.
SQL> alter database open resetlogs;
Database altered.
****************************************************************************************************************************************************************************
15. Shudown DB and mount with pfile and change DB name
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile= /u02/app/oracle/product/12.1.0/dbhome_1/dbs/initTEST1.ora
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 7654304 bytes
Variable Size 1.5703E+10 bytes
Database Buffers 5704253440 bytes
Redo Buffers 59453440 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Supply runtime variables and change DB name as below
Note: Crosscheck controle files then proceed with 'Y'
[oracle@Ora11g2] /u02/app/oracle/product/12.1.0/dbhome_1/bin > nid TARGET=sys/hf674jgar39e DBNAME=TEST1
DBNEWID: Release 12.1.0.2.0 - Production on Fri Feb 7 11:41:56 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to database TEST (DBID=1488347048)
Connected to server version 12.1.0
Control Files in database:
+DATA/TEST1/CONTROLFILE/current.302.1033470943
+LOG/TEST1/CONTROLFILE/current.272.1033470943
Change database ID and database name TEST to TEST1? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1488347048 to 433745540
Changing database name from TEST2 to TEST1
Control File +DATA/TEST1/CONTROLFILE/current.364.1031740917 - modified
Control File +LOG/TEST1/CONTROLFILE/current.271.1031740917 - modified
Datafile +DATA/TEST1/DATAFILE/system.357.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/sysaux.378.103174134 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/undotbs1.365.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/lobs.380.103174144 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/users.343.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/indx.344.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/TEST_migr_lf.368.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/undotbs1.265.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/undotbs1.350.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/TEST_hist_data.367.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/undotbs2.256.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/TEST_mds.379.103174138 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/gg_data.377.103174137 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/undotbs2.341.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/TEST.371.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/TEST_core.360.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/TEST_jms.376.103174138 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/TEST_core.338.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/TEST_core.354.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/TEST_core.352.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/DATAFILE/TEST_core.261.103174130 - dbid changed, wrote new name
Datafile +DATA/TEST1/TEMPFILE/temp.382.103174434 - dbid changed, wrote new name
Datafile +DATA/TEST1/TEMPFILE/tempmig01.381.103174434 - dbid changed, wrote new name
Datafile +DATA/TEST1/TEMPFILE/temp1.383.103174434 - dbid changed, wrote new name
Control File +DATA/TEST1/CONTROLFILE/current.364.1031740917 - dbid changed, wrote new name
Control File +LOG/TEST1/CONTROLFILE/current.271.1031740917 - dbid changed, wrote new name
Instance shut down
Database name changed to TEST1.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST1 changed to 433745540.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
Note: Once we set new DBNAME, DB will shutdown automaticall after completion
Now change DB_NAME parameter in pfile and start with modified pfile.
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs >vi initTEST1
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs >cat initTEST1
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='+DATA/TEST1/CONTROLFILE/current.302.1033470943','+LOG/TEST1/CONTROLFILE/current.272.1033470943'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+LOG'
*.db_domain=''
*.db_name='TEST1'#Reset to original value by RMAN
*.db_recovery_file_dest_size=50G
*.db_recovery_file_dest='+ARCHIVE'
*.db_unique_name='TEST1'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='ARC%S_%R.%T'
*.memory_max_target=20G
*.memory_target=15G
*.open_cursors=300
*.processes=3000
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'
SQL> startup mount pfile= /u02/app/oracle/product/12.1.0/dbhome_1/dbs/initTEST1.ora
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 7654304 bytes
Variable Size 1.5703E+10 bytes
Database Buffers 5704253440 bytes
Redo Buffers 59453440 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST1 READ WRITE
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+LOG/TEST1/ONLINELOG/group_4.258.1031508679
+LOG/TEST1/ONLINELOG/group_3.257.1031508653
+LOG/TEST1/ONLINELOG/group_1.264.1031508701
+LOG/TEST1/ONLINELOG/group_2.263.1031508633
SQL> create spfile='+DATA' from pfile='/u02/app/oracle/product/12.1.0/dbhome_1/dbs/initTEST1.ora';
File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
As a grid user check newly created spfile location and update in pfile as oracle user.
[grid@Ora11g2] /home/grid > ps -ef|grep pmon
grid 30406 1 0 2019 ? 00:13:04 asm_pmon_+ASM
oracle 30738 1 0 Feb27 ? 00:00:15 ora_pmon_TEST1
[grid@Ora11g2] /home/grid > . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /g01/app
[grid@Ora11g2] /home/grid > asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 512000 490467 0 490467 0 N ARCHIVE/
MOUNTED EXTERN N 512 4096 4194304 1024000 240172 0 240172 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 10240 6873 0 6873 0 N LOG/
MOUNTED EXTERN N 512 4096 1048576 10240 10187 0 10187 0 N OCR/
ASMCMD> cd DATA/
ASMCMD> ls
ASM/
TEST1/
REPOSDB/
orapwasm
ASMCMD> cd TEST1/
ASMCMD> ls
CONTROLFILE/
DATAFILE/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd PARAMETERFILE/
ASMCMD> ls
spfile.303.1033481659
ASMCMD> pwd
+DATA/TEST1/PARAMETERFILE
[oracle@Ora11g2] /backup > cd $ORACLE_HOME
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1 > cd dbs
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs > vi initTEST1.ora
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs > cat initTEST1.ora
spfile='+DATA/TEST1/PARAMETERFILE/spfile.303.1033481659'
startup DB now with spfile
SQL> startup ;
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 7654304 bytes
Variable Size 1.5703E+10 bytes
Database Buffers 5704253440 bytes
Redo Buffers 59453440 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST1 READ WRITE
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ------ --------
spfile string +DATA/TEST1/PARAMETERFILE/spfile.303.1033481659
****************************************************************************************************************************************************************************
16. Change SYSTEM,TEST* schemas passwords.( Passwords saves as a part of Step.6 Use those passwords here)
SQL> alter user system identified by hf674jgar39e account unlock;
User altered.
SQL> set lines 200
SQL> set pages 200
SQL> select 'alter user ' || username || ' identified by JjxsTrWy4mCk395c account unlock;' from dba_users where username like '%USER%';
'ALTERUSER'||USERNAME||'IDENTIFIEDBYJjxsTrWy4mCk395cACCOUNTUNLOCK;'
-------------------------------------------------------------------------------
alter user USER_EA identified by JjxsTrWy4mCk395c account unlock;
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST1 READ WRITE
****************************************************************************************************************************************************************************
17. Create B* users users doesn't exists(User creation script and role captured as part of step-7)
SQL>CREATE USER "ABC" IDENTIFIED BY VALUES 'S:18C5E7AC76FB0B238F91F7E19CF5FCEE4B770D80FDB8EE030FE2BE48F8D2;T:B9FACD94E6A7B703394EB7771D932E1D3168EA2666402CF886E0EE9C47D134674FC141F48955329AED00C2D3A7BF238EF00B109CDAC94367813E0E7238B9CFFC30A415A452C8124D6D02A661D688E1A4;F91875BFD21BA5B9' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";
User created.
SQL>CREATE USER "ABC" IDENTIFIED BY VALUES 'S:1F5B16B386FD0544134981A7903F67FA753314FF986312E809D9D4E83656;T:DE219DDA44C21BCCFB5706A9A8D3F91310F97D3A3CE063E7AD48790576C88B66B571B99C151506D0972A18C3B1D2E28BDF93A51C713484AB2D9E7B84EE27CD4C21C1A75403E5433DCB693F921806ED9A;F4EEFF22834D3E61' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";
ORA-01920: user name 'string' conflicts with another user or role name
Cause: There is already a user or role with that name.
Action: Specify a different user name.
Looks like User already exists in DB . Just change the password for users if already exists.
SQL>Alter USER "ABC" IDENTIFIED BY VALUES 'S:B239D07E9452EC65B5C168D319CC4F4BDD8A286C4D6726350DF119B2DDE7;T:4DD62A61C00C830CBFFB1FCE4ACC0DE2F1153A374C84AF4A59F36366112F2E5C4A37ABFE9D97DDA726253F067B8BF5200C04156933204E9EA9C03543EAC3B18B7791DE2B263657E8D38C5B382AAC5E6E;55902BC7819CD76E' account unlock;
User altered.
Grant roles to B* users (Privs metadata captured as a part of ste-7)
SELECT 'GRANT '||GRANTED_ROLE||' to '||GRANTEE||';' FROM DBA_ROLE_PRIVS WHERE GRANTEE like 'B%';
Sample Output:
SQL>GRANT CONNECT to ABC;
grant successed.
Ensure scheduled DB backups are running fine as expected in target DB.
[grid@Ora11g2] /home/grid > cd /backup/
[grid@Ora11g2] /backup > cd TEST1_feb-27
[grid@Ora11g2] /backup/TEST1_feb-27 > ls -lrt
total 10594000
-rw-r-----. 1 oracle asmadmin 2887688192 Feb 27 09:52 TEST1_mvupirmf_1_1_20200227_95967_1_Full
-rw-r-----. 1 oracle asmadmin 7835803648 Feb 27 10:02 TEST1_n0upirmg_1_1_20200227_95968_1_Full
-rw-r-----. 1 oracle asmadmin 124747776 Feb 27 10:02 TEST1_n1upismi_1_1_20200227_CTRL
-rw-r--r--. 1 oracle oinstall 3303 Feb 27 10:03 TEST1_RMAN_BKP.log
-rw-r--r--. 1 oracle oinstall 552 Feb 27 10:03 Input.log
-rw-r--r--. 1 oracle oinstall 0 Feb 28 08:21 cd
-rw-r--r--. 1 oracle oinstall 0 Feb 28 08:21 ls
[grid@Ora11g2Ora11g2] /backup/TEST1_feb-27 > tail -20 TEST1_RMAN_BKP.log
including current control file in backup set
channel disk1: starting piece 1 at 27-FEB-20
channel disk1: finished piece 1 at 27-FEB-20
piece handle=/backup/TEST1_feb-27/TEST1_n1upismi_1_1_20200227_CTRL tag=TAG20200227T100258 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-FEB-20
Starting Control File and SPFILE Autobackup at 27-FEB-20
piece handle=+ARCHIVE/TEST1/AUTOBACKUP/2020_02_27/s_1033465513.1295.1033466581 comment=NONE
Finished Control File and SPFILE Autobackup at 27-FEB-20
released channel: disk1
released channel: disk2
Statement processed
RMAN>
Recovery Manager complete.
Note:
=====
If we are restoring any DB from RAC to single instance, We can drop thread 2 logs and UNDOTBS 2(it should not be the default undo tablespace)
It will free more storage on ASM disk
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 YES INACTIVE
3 YES INACTIVE
4 NO CURRENT
5 YES UNUSED
6 YES UNUSED
7 YES UNUSED
8 YES UNUSED
9 YES UNUSED
10 YES UNUSED
11 YES UNUSED
GROUP# ARC STATUS
---------- --- ----------------
12 YES UNUSED
12 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+LOG/CARS/ONLINELOG/group_4.277.1035194293
+LOG/CARS/ONLINELOG/group_3.280.1035194293
+LOG/CARS/ONLINELOG/group_1.279.1035194291
+LOG/CARS/ONLINELOG/group_2.281.1035194293
+LOG/CARS/ONLINELOG/group_9.275.1035194297
+LOG/CARS/ONLINELOG/group_5.271.1035194295
+LOG/CARS/ONLINELOG/group_6.274.1035194295
+LOG/CARS/ONLINELOG/group_7.273.1035194295
+LOG/CARS/ONLINELOG/group_8.272.1035194297
+LOG/CARS/ONLINELOG/group_10.256.1035194299
+LOG/CARS/ONLINELOG/group_11.257.1035194299
MEMBER
--------------------------------------------------------------------------------
+LOG/CARS/ONLINELOG/group_12.258.1035194299
12 rows selected.
SQL> alter database disable thread 2;
Database altered.
SQL>
SQL>
SQL>
SQL>
SQL> set linesize 100
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 YES INACTIVE
3 YES INACTIVE
4 NO CURRENT
5 YES UNUSED
6 YES UNUSED
7 YES UNUSED
8 YES UNUSED
9 YES UNUSED
10 YES UNUSED
11 YES UNUSED
GROUP# ARC STATUS
---------- --- ----------------
12 YES UNUSED
12 rows selected.
SQL>
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
SQL> alter database drop logfile group 8;
Database altered.
SQL> alter database drop logfile group 9;
Database altered.
SQL> alter database drop logfile group 10;
Database altered.
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 12;
Database altered.
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 YES INACTIVE
3 YES INACTIVE
4 NO CURRENT
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
+LOG/CARS/ONLINELOG/group_4.277.1035194293
+LOG/CARS/ONLINELOG/group_3.280.1035194293
+LOG/CARS/ONLINELOG/group_1.279.1035194291
+LOG/CARS/ONLINELOG/group_2.281.1035194293
***********************************************************************************************************************************************************************
##################################################################Issues Faced During DB Restoration###################################################################
Issue: After controlfile restoration mounted Database without any issues,During DB Restoration got below errors.
------
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/27/2020 13:11:48
RMAN-06026: some targets not found - aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile 12
RMAN-06100: no channel to restore a backup or copy of datafile 11
RMAN-06100: no channel to restore a backup or copy of datafile 10
RMAN-06100: no channel to restore a backup or copy of datafile 9
RMAN-06100: no channel to restore a backup or copy of datafile 8
Cause: After two hours of searching for answers and performing more tests I ran to the Metalink document 965122.1.
The problem were those autobackups in FRA.
That files belonged to different incarnation than the available backups current incarnation.
[From Metalink document]
If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined,
RMAN execute and implicit crosscheck and catalog of all the objects in the Flash Recovery Area.
RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURRENT incarnation in the controlfile then changes controlfile CURRENT incarnation to the one found in the file that is being cataloged.
This prevents database from restoring backups that belong to old CURRENT incarnation.
RMAN considers backup availble for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.
So We’ve decided to remove old archivelogs, Autobackups,Controfiles and redologfile .
Solution: Shutdown DB and startup mount state with restriction mode and drop DB
Once DB Dropped restore controlfile and start DB Restoration.
SQL> select name from v$database;
NAME
---------
TEST2
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict;
ORACLE instance started.
Total System Global Area 1.0737E+10 bytes
Fixed Size 7654304 bytes
Variable Size 9697231968 bytes
Database Buffers 1006632960 bytes
Redo Buffers 25899008 bytes
Database mounted.
SQL> drop database;
Database dropped.
[grid@Ora11g2] /home/grid > . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /g01/app
[grid@Ora11g2] /home/grid > asmcmd
ASMCMD> ls
ARCHIVE/
DATA/
LOG/
OCR/
1. Remove Old archive logs/Autobackups related to Target DB
ASMCMD> cd ARCHIVE/
ASMCMD> ls
TEST01/
TEST03/
TEST1/
REPOSDB/
ASMCMD> cd TEST1/
ASMCMD> ls
AUTOBACKUP/
ASMCMD> cd AUTOBACKUP/
ASMCMD> ls
2020_02_27/
ASMCMD> pwd
+ARCHIVE/TEST1/AUTOBACKUP
ASMCMD> ls
2. Remove controlfile
ASMCMD> cd DATA/
ASMCMD> ls
ASM/
TEST01/
TEST02/
TEST03/
TEST1/
REPOSDB/
orapwasm
ASMCMD> cd TEST1/
ASMCMD> ls
CONTROLFILE/
DATAFILE/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd CONTROLFILE/
ASMCMD> ls
current.299.1033480381
ASMCMD> pwd
+DATA/TEST1/CONTROLFILE
ASMCMD>rm -rf current.299.1033480381
ASMCMD> cd LOG/
ASMCMD> ls
AUDITTEST/
TEST01/
TEST02/
TEST03/
TEST1/
REPOSDB/
ASMCMD> cd TEST1/
ASMCMD> ls
CONTROLFILE/
ONLINELOG/
ASMCMD> cd CONTROLFILE/
ASMCMD> pwd
+LOG/TEST1/CONTROLFILE
ASMCMD> ls
current.273.1033480381
ASMCMD>rm -rf current.273.1033480381
3. Remove spfile
ASMCMD> cd DATA/
ASMCMD> ls
ASM/
TEST01/
TEST02/
TEST03/
TEST1/
REPOSDB/
orapwasm
ASMCMD> cd TEST1/
ASMCMD> ls
CONTROLFILE/
DATAFILE/
PARAMETERFILE/
ASMCMD> cd PARAMETERFILE/
ASMCMD> ls
spfile.303.1033481659
ASMCMD>rm -rf spfile.303.1033481659
4. Remove online redologs.
ASMCMD> ls
ARCHIVE/
DATA/
LOG/
OCR/
ASMCMD> cd LOG/
ASMCMD> ls
AUDITTEST/
TEST01/
TEST02/
TEST03/
TEST1/
REPOSDB/
ASMCMD> cd TEST1/
ASMCMD> ls
CONTROLFILE/
ONLINELOG/
ASMCMD> cd ONLINELOG/
ASMCMD> ls
group_1.272.1033481607
group_2.276.1033481607
group_3.275.1033481607
group_4.274.1033481607
ASMCMD>rm -rf group_1.272.1033481607 group_2.276.1033481607 group_3.275.1033481607 group_4.274.1033481607
SQL> startup nomount pfile= /u02/app/oracle/product/12.1.0/dbhome_1/dbs/initTEST1.ora
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 7654304 bytes
Variable Size 1.5703E+10 bytes
Database Buffers 5704253440 bytes
Redo Buffers 59453440 bytes
[oracle@Ora11g2] /home/oracle > cd /backup/
[oracle@Ora11g2] /backup > cd TEST2_27FEB_CONSISTANT
[oracle@Ora11g2] /backup/TEST2_27FEB_CONSISTANT > ls -lrt
totalt 60612852
-rw-r----- 1 oracle 54321 5046747136 27 feb 09.36 backup_r3upir4q_1_1
-rw-r----- 1 oracle 54321 15743729664 27 feb 09.37 backup_r2upir4q_1_1
-rw-r----- 1 oracle 54321 41152315392 27 feb 09.37 backup_r1upir4q_1_1
-rw-r----- 1 oracle 54321 124747776 27 feb 09.37 cntrl_96100_1_1033465063
[oracle@Ora11g2] /backup/TEST2_27FEB_CONSISTANT > rman terget /
RMAN> run
{
allocate channel c1 device type disk;
restore controlfile from '/backup/TEST2_27FEB_CONSISTANT/cntrl_96100_1_1033465063';
release channel c1;
}2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=2415 device type=DISK
Starting restore at 27-FEB-20
channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:00:01
output file name=+DATA/TEST1/CONTROLFILE/current.302.1033470943
output file name=+LOG/TEST1/CONTROLFILE/current.272.1033470943
Finished restore at 27-FEB-20
Note: Save Restore information in somewhere , We need those newly created controlfile location to update in pfile.
SQL> alter database mount;
Databse altered.
Shutdown DB and update controlfile details in pfile and start db in mount state.
SQL> shut immediate;
Database not mounted.
ORACLE instance shut down.
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs >vi initTEST1
[oracle@Ora11g2] /u01/app/oracle/product/12.1.0/dbhome_1/dbs >cat initTEST1
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='+DATA/TEST1/CONTROLFILE/current.302.1033470943','+LOG/TEST1/CONTROLFILE/current.272.1033470943'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+LOG'
*.db_domain=''
*.db_name='TEST2'#Reset to original value by RMAN
*.db_recovery_file_dest_size=50G
*.db_recovery_file_dest='+ARCHIVE'
*.db_unique_name='TEST1'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='ARC%S_%R.%T'
*.memory_max_target=20G
*.memory_target=15G
*.open_cursors=300
*.processes=3000
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'
SQL> startup mount pfile= /u02/app/oracle/product/12.1.0/dbhome_1/dbs/initTEST1.ora
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 7654304 bytes
Variable Size 1.5703E+10 bytes
Database Buffers 5704253440 bytes
Redo Buffers 59453440 bytes
Database mounted.
Restore DB and open db in Read write mode.
[oracle@Ora11g2] /backup/TEST2_27FEB_CONSISTANT > rman terget /
RMAN> CATALOG BACKUPPIECE '/backup/TEST2_27FEB_CONSISTANT/backup_r3upir4q_1_1';
cataloged backup piece
backup piece handle=/backup/TEST2_27FEB_CONSISTANT/backup_r3upir4q_1_1' RECID=38770 STAMP=1031741276
RMAN> CATALOG BACKUPPIECE '/backup/TEST2_27FEB_CONSISTANT/backup_r2upir4q_1_1';
cataloged backup piece
backup piece handle=/backup/TEST2_27FEB_CONSISTANT/backup_r2upir4q_1_1' RECID=38770 STAMP=1031741276
RMAN> CATALOG BACKUPPIECE '/backup/TEST2_27FEB_CONSISTANT/backup_r1upir4q_1_1';
cataloged backup piece
backup piece handle=/backup/TEST2_27FEB_CONSISTANT/backup_r1upir4q_1_1' RECID=38770 STAMP=1031741276
RUN {
allocate channel c1 device type disk;
set newname for database to '+DATA';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
RECOVER DATABASE;
release channel c1;
}
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=1 device type=DISK
Starting restore at 07-FEB-20
-----------------------------
-----------------------------
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:44:05
Finished restore at 07-FEB-20
released channel: c1
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST2 MOUNTED
SQL> alter database open resetlogs;
Database altered.
#################################################################Doccumented By Sreenu###################################################################################
No comments:
Post a Comment