Environment:-
Source Database:- DB11GSource DB Instance:- DB11G1,DB11G2
Source Server:- rac1,rac2
Target Database:- DB11G
Target Server:- ora11gr2
--------------------------------------
Steps:-
1. Check the Database,dbfiles server details along with pfile2. Take backup of source RAC database
3. Transfer the backups and pfile to target server
4. Modify the pfile as per requirement
5. Create all the required file system paths
6. Startup nomount
7. Set DBID and Restore the controlfile
8. Mount the database
9. Restore and Recover the database until last sequence after renaming all the datafiles,tempfiles (last sequence from list backup of source db)
10. Rename the old redolog files to new files and open the database in resetlogs mode
11. Disable thread 2 redolog group and drop unnecessary redolog groups after creating new groups
12. Drop unused second undo tablespace
13. Check all the database files and status
==================================================================================================================================================
Step:-1
Source Database Files
--------- -------------------- ------------ ----------------
DB11G READ WRITE ARCHIVELOG PRIMARY
INSTANCE_NAME LOGINS HOST_NAME DB Startup Time
---------------- ---------- -------------------- -----------------------------
DB11G1 ALLOWED rac1.oracle.com 14-MAR-2018 18:15:14
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME STATUS NAME OPEN_MODE LOG_MODE DATABASE_ROLE
--------------- ---------------- ----------------------------------- ------------ --------- --------------- ------------ ----------------
1 DB11G1 rac1.oracle.com OPEN DB11G READ WRITE ARCHIVELOG PRIMARY
2 DB11G2 rac2.oracle.com OPEN DB11G READ WRITE ARCHIVELOG PRIMARY
Tablespace Used MB Free MB Total MB Pct. Free PCT_USED
------------------------------ ----------- ----------- ----------- ---------- ----------
SYSAUX 523 27 550 4.91 95.09
UNDOTBS1 91 4 95 4.21 95.79
USERS 3 2 5 40 60
SYSTEM 746 4 750 .53 99.47
EXAMPLE 309 4 313 1.28 98.72
UNDOTBS2 32 18 50 36 64
6 rows selected.
TABLESPACE GB_TOTAL GB_USED GB_FREE
------------------------------ ---------- ---------- ----------
TEMP .03125 0 .03125
TABLESPACE_NAME FILE_ID FILE_NAME SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
USERS 4 +DATA/db11g/datafile/users.259.952163787 .004882813 YES
UNDOTBS1 3 +DATA/db11g/datafile/undotbs1.258.952163787 .092773438 YES
SYSAUX 2 +DATA/db11g/datafile/sysaux.257.952163785 .537109375 YES
SYSTEM 1 +DATA/db11g/datafile/system.256.952163783 .732421875 YES
EXAMPLE 5 +DATA/db11g/datafile/example.264.952164051 .305786133 YES
UNDOTBS2 6 +DATA/db11g/datafile/undotbs2.265.952164501 .048828125 YES
6 rows selected.
TABLESPACE_NAME FILE_ID FILE_NAME SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
TEMP 1 +DATA/db11g/tempfile/temp.263.952164033 .03125 YES
INST_ID GROUP# THREAD# SIZE_MB MEMBERS STATUS
---------- ---------- ---------- ---------- ---------- ----------------
1 1 1 50 1 INACTIVE
1 2 1 50 1 CURRENT
1 3 2 50 1 INACTIVE
1 4 2 50 1 CURRENT
2 1 1 50 1 INACTIVE
2 2 1 50 1 CURRENT
2 3 2 50 1 INACTIVE
2 4 2 50 1 CURRENT
8 rows selected.
INST_ID GROUP# MEMBER
---------- ---------- ------------------------------------------------------------
1 1 +DATA/db11g/onlinelog/group_1.261.952164009
1 2 +DATA/db11g/onlinelog/group_2.262.952164013
1 3 +DATA/db11g/onlinelog/group_3.266.952164647
1 4 +DATA/db11g/onlinelog/group_4.267.952164651
2 1 +DATA/db11g/onlinelog/group_1.261.952164009
2 2 +DATA/db11g/onlinelog/group_2.262.952164013
2 3 +DATA/db11g/onlinelog/group_3.266.952164647
2 4 +DATA/db11g/onlinelog/group_4.267.952164651
8 rows selected.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/db11g/spfiledb11g.ora
NAME
-------------------------------------------------
+DATA/db11g/controlfile/current.260.952164003
+DATA/db11g/controlfile/current.260.952164003
Source Pfile (RAC db pfile)
---------------------------------
DB11G1.__db_cache_size=620756992
DB11G2.__db_cache_size=620756992
DB11G1.__java_pool_size=16777216
DB11G2.__java_pool_size=16777216
DB11G1.__large_pool_size=33554432
DB11G2.__large_pool_size=33554432
DB11G1.__oracle_base='/u02/app/oracle'#ORACLE_BASE set from environment
DB11G1.__pga_aggregate_target=671088640
DB11G2.__pga_aggregate_target=671088640
DB11G1.__sga_target=989855744
DB11G2.__sga_target=989855744
DB11G1.__shared_io_pool_size=0
DB11G2.__shared_io_pool_size=0
DB11G1.__shared_pool_size=301989888
DB11G2.__shared_pool_size=301989888
DB11G1.__streams_pool_size=0
DB11G2.__streams_pool_size=0
*.audit_file_dest='/u02/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/db11g/controlfile/current.260.952164003'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='DB11G'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='+DATA'
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
DB11G1.instance_number=1
DB11G2.instance_number=2
*.memory_target=1654652928
*.open_cursors=300
*.processes=150
*.remote_listener='rac-scan.oracle.com:1521'
*.remote_login_passwordfile='exclusive'
DB11G2.thread=2
DB11G1.thread=1
DB11G2.undo_tablespace='UNDOTBS2'
DB11G1.undo_tablespace='UNDOTBS1'
Step:-2
RMAN Backup of Source Database:-
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 14 19:25:40 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB11G (DBID=421833570)
RMAN> run {
sql 'alter system archive log current';
sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
configure controlfile autobackup on;
set command id to 'DBOnlineBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag DB_FULL format '/mnt/hgfs/DB_Backup/%d_%T_%s_%p_FULL.bkp' ;
sql 'alter system archive log current';
backup tag DB_ARCHIVE format '/mnt/hgfs/DB_Backup/%d_%T_%s_%p_ARCHIVE.bkp' archivelog all;
backup tag DB_CONTROL current controlfile format '/mnt/hgfs/DB_Backup/%d_%T_%s_%p_CONTROL.bkp';
delete noprompt obsolete;
delete noprompt expired backup;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20>
using target database control file instead of recovery catalog
sql statement: alter system archive log current
sql statement: alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
executing command: SET COMMAND ID
allocated channel: c1
channel c1: SID=58 instance=DB11G1 device type=DISK
allocated channel: c2
channel c2: SID=49 instance=DB11G1 device type=DISK
allocated channel: c3
channel c3: SID=54 instance=DB11G1 device type=DISK
allocated channel: c4
channel c4: SID=62 instance=DB11G1 device type=DISK
Starting backup at 14-MAR-18
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/db11g/datafile/system.256.952163783
input datafile file number=00006 name=+DATA/db11g/datafile/undotbs2.265.952164501
channel c1: starting piece 1 at 14-MAR-18
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/db11g/datafile/sysaux.257.952163785
input datafile file number=00004 name=+DATA/db11g/datafile/users.259.952163787
channel c2: starting piece 1 at 14-MAR-18
channel c3: starting compressed full datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/db11g/datafile/example.264.952164051
input datafile file number=00003 name=+DATA/db11g/datafile/undotbs1.258.952163787
channel c3: starting piece 1 at 14-MAR-18
channel c3: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_11_1_FULL.bkp tag=DB_FULL comment=NONE
channel c3: backup set complete, elapsed time: 00:01:16
channel c2: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_10_1_FULL.bkp tag=DB_FULL comment=NONE
channel c2: backup set complete, elapsed time: 00:01:56
channel c1: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_9_1_FULL.bkp tag=DB_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:02:58
Finished backup at 14-MAR-18
Starting Control File and SPFILE Autobackup at 14-MAR-18
piece handle=+DATA/db11g/autobackup/2018_03_14/s_970774135.280.970774139 comment=NONE
Finished Control File and SPFILE Autobackup at 14-MAR-18
sql statement: alter system archive log current
Starting backup at 14-MAR-18
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=1 STAMP=970772793
channel c1: starting piece 1 at 14-MAR-18
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=2 sequence=2 RECID=2 STAMP=970772803
channel c2: starting piece 1 at 14-MAR-18
channel c3: starting archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=3 STAMP=970772803
input archived log thread=2 sequence=3 RECID=4 STAMP=970772812
input archived log thread=1 sequence=8 RECID=5 STAMP=970772812
input archived log thread=2 sequence=4 RECID=7 STAMP=970773758
channel c3: starting piece 1 at 14-MAR-18
channel c4: starting archived log backup set
channel c4: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=6 STAMP=970773754
input archived log thread=1 sequence=10 RECID=8 STAMP=970773880
input archived log thread=2 sequence=5 RECID=9 STAMP=970773882
input archived log thread=1 sequence=11 RECID=10 STAMP=970773946
channel c4: starting piece 1 at 14-MAR-18
channel c3: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_15_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c3: backup set complete, elapsed time: 00:00:08
channel c3: starting archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=2 sequence=6 RECID=11 STAMP=970773948
input archived log thread=1 sequence=12 RECID=13 STAMP=970774157
channel c3: starting piece 1 at 14-MAR-18
channel c4: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_16_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c4: backup set complete, elapsed time: 00:00:04
channel c4: starting archived log backup set
channel c4: specifying archived log(s) in backup set
input archived log thread=2 sequence=7 RECID=12 STAMP=970774152
input archived log thread=2 sequence=8 RECID=14 STAMP=970774179
input archived log thread=1 sequence=13 RECID=15 STAMP=970774180
channel c4: starting piece 1 at 14-MAR-18
channel c1: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_13_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:11
channel c2: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_14_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c2: backup set complete, elapsed time: 00:00:18
channel c4: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_18_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c4: backup set complete, elapsed time: 00:00:07
channel c3: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_17_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c3: backup set complete, elapsed time: 00:00:16
Finished backup at 14-MAR-18
Starting backup at 14-MAR-18
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 14-MAR-18
channel c1: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_19_1_CONTROL.bkp tag=DB_CONTROL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:15
Finished backup at 14-MAR-18
Starting Control File and SPFILE Autobackup at 14-MAR-18
piece handle=+DATA/db11g/autobackup/2018_03_14/s_970774249.285.970774251 comment=NONE
Finished Control File and SPFILE Autobackup at 14-MAR-18
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log 1 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_1_seq_6.269.970772787
Archive Log 2 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_2_seq_2.270.970772795
Archive Log 3 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_1_seq_7.271.970772801
Archive Log 4 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_2_seq_3.272.970772811
Archive Log 5 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_1_seq_8.273.970772811
Archive Log 6 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_1_seq_9.274.970773755
Archive Log 7 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_2_seq_4.275.970773757
Archive Log 8 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_1_seq_10.276.970773879
Archive Log 9 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_2_seq_5.277.970773881
Archive Log 10 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_1_seq_11.278.970773945
Archive Log 11 14-MAR-18 +DATA/db11g/archivelog/2018_03_14/thread_2_seq_6.279.970773947
Backup Set 4 14-MAR-18
Backup Piece 4 14-MAR-18 +DATA/db11g/autobackup/2018_03_14/s_970774135.280.970774139
Backup Set 7 14-MAR-18
Backup Piece 7 14-MAR-18 /mnt/hgfs/DB_Backup/DB11G_20180314_13_1_ARCHIVE.bkp
Backup Set 9 14-MAR-18
Backup Piece 9 14-MAR-18 /mnt/hgfs/DB_Backup/DB11G_20180314_14_1_ARCHIVE.bkp
Backup Set 5 14-MAR-18
Backup Piece 5 14-MAR-18 /mnt/hgfs/DB_Backup/DB11G_20180314_15_1_ARCHIVE.bkp
Backup Set 6 14-MAR-18
Backup Piece 6 14-MAR-18 /mnt/hgfs/DB_Backup/DB11G_20180314_16_1_ARCHIVE.bkp
Backup Set 11 14-MAR-18
Backup Piece 11 14-MAR-18 /mnt/hgfs/DB_Backup/DB11G_20180314_19_1_CONTROL.bkp
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_1_seq_6.269.970772787 RECID=1 STAMP=970772793
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_2_seq_2.270.970772795 RECID=2 STAMP=970772803
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_1_seq_7.271.970772801 RECID=3 STAMP=970772803
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_2_seq_3.272.970772811 RECID=4 STAMP=970772812
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_1_seq_8.273.970772811 RECID=5 STAMP=970772812
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_1_seq_9.274.970773755 RECID=6 STAMP=970773754
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_2_seq_4.275.970773757 RECID=7 STAMP=970773758
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_1_seq_10.276.970773879 RECID=8 STAMP=970773880
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_2_seq_5.277.970773881 RECID=9 STAMP=970773882
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_1_seq_11.278.970773945 RECID=10 STAMP=970773946
deleted archived log
archived log file name=+DATA/db11g/archivelog/2018_03_14/thread_2_seq_6.279.970773947 RECID=11 STAMP=970773948
deleted backup piece
backup piece handle=+DATA/db11g/autobackup/2018_03_14/s_970774135.280.970774139 RECID=4 STAMP=970774139
deleted backup piece
backup piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_13_1_ARCHIVE.bkp RECID=7 STAMP=970774182
deleted backup piece
backup piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_14_1_ARCHIVE.bkp RECID=9 STAMP=970774183
deleted backup piece
backup piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_15_1_ARCHIVE.bkp RECID=5 STAMP=970774188
deleted backup piece
backup piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_16_1_ARCHIVE.bkp RECID=6 STAMP=970774188
deleted backup piece
backup piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_19_1_CONTROL.bkp RECID=11 STAMP=970774234
Deleted 17 objects
specification does not match any backup in the repository
released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN> exit
Recovery Manager complete.
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cd -
/mnt/hgfs/DB_Backup
[oracle@rac1 DB_Backup]$
[oracle@rac1 DB_Backup]$ ls -ltr
total 331160
-rwxrwxrwx. 1 root root 23560192 Mar 14 13:57 DB11G_20180314_11_1_FULL.bkp
-rwxrwxrwx. 1 root root 76824576 Mar 14 13:58 DB11G_20180314_10_1_FULL.bkp
-rwxrwxrwx. 1 root root 209158144 Mar 14 13:59 DB11G_20180314_9_1_FULL.bkp
-rwxrwxrwx. 1 root root 88576 Mar 14 14:00 DB11G_20180314_18_1_ARCHIVE.bkp
-rwxrwxrwx. 1 root root 29473280 Mar 14 14:00 DB11G_20180314_17_1_ARCHIVE.bkp
RMAN> backup tag DB_CONTROL current controlfile format '/mnt/hgfs/DB_Backup/%d_%T_%s_%p_CONTROL.bkp';
Starting backup at 14-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 instance=DB11G1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 14-MAR-18
channel ORA_DISK_1: finished piece 1 at 14-MAR-18
piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_21_1_CONTROL.bkp tag=DB_CONTROL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 14-MAR-18
Starting Control File and SPFILE Autobackup at 14-MAR-18
piece handle=+DATA/db11g/autobackup/2018_03_14/s_970776131.280.970776135 comment=NONE
Finished Control File and SPFILE Autobackup at 14-MAR-18
RMAN> exit
[oracle@rac1 DB_Backup]$ ls -ltr
total 331160
-rwxrwxrwx. 1 root root 23560192 Mar 14 13:57 DB11G_20180314_11_1_FULL.bkp
-rwxrwxrwx. 1 root root 76824576 Mar 14 13:58 DB11G_20180314_10_1_FULL.bkp
-rwxrwxrwx. 1 root root 209158144 Mar 14 13:59 DB11G_20180314_9_1_FULL.bkp
-rwxrwxrwx. 1 root root 88576 Mar 14 14:00 DB11G_20180314_18_1_ARCHIVE.bkp
-rwxrwxrwx. 1 root root 29473280 Mar 14 14:00 DB11G_20180314_17_1_ARCHIVE.bkp
-rwxrwxrwx. 1 root root 18546688 Mar 14 14:32 DB11G_20180314_21_1_CONTROL.bkp
Step:-3
3. Transfer the backups and pfile to target server using scp
Step:-4
Target Pfile (Standalone pfile)
---------------------------------
#DB11G1.__db_cache_size=620756992
#DB11G2.__db_cache_size=620756992
#DB11G1.__java_pool_size=16777216
#DB11G2.__java_pool_size=16777216
#DB11G1.__large_pool_size=33554432
#DB11G2.__large_pool_size=33554432
#DB11G1.__oracle_base='/u02/app/oracle'#ORACLE_BASE set from environment
#DB11G1.__pga_aggregate_target=671088640
#DB11G2.__pga_aggregate_target=671088640
#DB11G1.__sga_target=989855744
#DB11G2.__sga_target=989855744
#DB11G1.__shared_io_pool_size=0
#DB11G2.__shared_io_pool_size=0
#DB11G1.__shared_pool_size=301989888
#DB11G2.__shared_pool_size=301989888
#DB11G1.__streams_pool_size=0
#DB11G2.__streams_pool_size=0
DB11G.__db_cache_size=620756992
DB11G.__java_pool_size=16777216
DB11G.__large_pool_size=33554432
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=671088640
DB11G.__sga_target=989855744
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=301989888
DB11G.__streams_pool_size=0
#*.audit_file_dest='/u02/app/oracle/admin/DB11G/adump'
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
#*.cluster_database=true
*.compatible='11.2.0.4.0'
#*.control_files='+DATA/db11g/controlfile/current.260.952164003'
*.control_files='/u01/app/oracle/DB11G_DB/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='DB11G'
*.db_recovery_file_dest_size=10737418240
#*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest='/u01/app/oracle/DB11G_DB/recovery_area'
#*.diagnostic_dest='/u02/app/oracle'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
#DB11G1.instance_number=1
#DB11G2.instance_number=2
#*.memory_target=1654652928
#*.memory_target=1G
*.open_cursors=300
*.processes=150
#*.remote_listener='rac-scan.oracle.com:1521'
*.remote_login_passwordfile='exclusive'
#DB11G2.thread=2
#DB11G1.thread=1
#DB11G2.undo_tablespace='UNDOTBS2'
#DB11G1.undo_tablespace='UNDOTBS1'
After Modification Target Pfile (Standalone pfile)
---------------------------------
DB11G.__db_cache_size=620756992
DB11G.__java_pool_size=16777216
DB11G.__large_pool_size=33554432
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=671088640
DB11G.__sga_target=989855744
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=301989888
DB11G.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/DB11G_DB/controlfile/control01.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='DB11G'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u01/app/oracle/DB11G_DB/recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
Step:-5
5. Create all the required file system paths
Step:-6
Startup nomount Target DB
ORACLE_SID = [DB11G] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora11gr2 dbs]$ sq
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 14 14:26:21 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
14:26:21 SYS@DB11G>startup nomount
ORACLE instance started.
Total System Global Area 221331456 bytes
Fixed Size 2251856 bytes
Variable Size 163578800 bytes
Database Buffers 50331648 bytes
Redo Buffers 5169152 bytes
Step:-7,8,9
7. Set DBID and Restore the controlfile8. Mount the database9. Restore and Recover the database until last sequence after renaming all the datafiles,tempfiles (last sequence from list backup of source db)
[oracle@ora11gr2 DB_Backup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 14 16:01:46 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB11G (not mounted)
RMAN> set dbid=421833570;
restore controlfile from '/mnt/hgfs/DB_Backup/DB11G_20180314_21_1_CONTROL.bkp';
executing command: SET DBID
RMAN>
Starting restore at 14-MAR-2018 16:01:53
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/DB11G_DB/controlfile/control01.ctl
Finished restore at 14-MAR-2018 16:01:55
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> run {
set newname for datafile 1 to '/u01/app/oracle/DB11G_DB/datafile/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/DB11G_DB/datafile/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/DB11G_DB/datafile/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/DB11G_DB/datafile/users01.dbf';
set newname for datafile 5 to '/u01/app/oracle/DB11G_DB/datafile/example01.dbf';
set newname for datafile 6 to '/u01/app/oracle/DB11G_DB/datafile/undotbs02.dbf';
set newname for tempfile 1 to '/u01/app/oracle/DB11G_DB/tempfile/temp01.dbf';
set until sequence 13;
restore database;
switch datafile all;
switch tempfile all;
recover database;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET until clause
Starting restore at 14-MAR-2018 16:02:07
Starting implicit crosscheck backup at 14-MAR-2018 16:02:07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 14-MAR-2018 16:02:08
Starting implicit crosscheck copy at 14-MAR-2018 16:02:08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-MAR-2018 16:02:08
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/DB11G_DB/datafile/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/DB11G_DB/datafile/example01.dbf
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_11_1_FULL.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_11_1_FULL.bkp tag=DB_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/DB11G_DB/datafile/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/DB11G_DB/datafile/users01.dbf
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_10_1_FULL.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_10_1_FULL.bkp tag=DB_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:58
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/DB11G_DB/datafile/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/DB11G_DB/datafile/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_9_1_FULL.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_9_1_FULL.bkp tag=DB_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
Finished restore at 14-MAR-2018 16:05:07
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=970761908 file name=/u01/app/oracle/DB11G_DB/datafile/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=970761908 file name=/u01/app/oracle/DB11G_DB/datafile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=970761908 file name=/u01/app/oracle/DB11G_DB/datafile/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=970761908 file name=/u01/app/oracle/DB11G_DB/datafile/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=970761908 file name=/u01/app/oracle/DB11G_DB/datafile/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=970761908 file name=/u01/app/oracle/DB11G_DB/datafile/undotbs02.dbf
renamed tempfile 1 to /u01/app/oracle/DB11G_DB/tempfile/temp01.dbf in control file
Starting recover at 14-MAR-2018 16:05:10
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_17_1_ARCHIVE.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_17_1_ARCHIVE.bkp tag=DB_ARCHIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=/u01/app/oracle/DB11G_DB/recovery_area/DB11G/archivelog/2018_03_14/o1_mf_1_12_fbkyq121_.arc thread=1 sequence=12
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=8
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_18_1_ARCHIVE.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_18_1_ARCHIVE.bkp tag=DB_ARCHIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/DB11G_DB/recovery_area/DB11G/archivelog/2018_03_14/o1_mf_2_7_fbkyq7y0_.arc thread=2 sequence=7
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/DB11G_DB/recovery_area/DB11G/archivelog/2018_03_14/o1_mf_2_7_fbkyq7y0_.arc RECID=17 STAMP=970761919
archived log file name=/u01/app/oracle/DB11G_DB/recovery_area/DB11G/archivelog/2018_03_14/o1_mf_2_8_fbkyq7yo_.arc thread=2 sequence=8
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/DB11G_DB/recovery_area/DB11G/archivelog/2018_03_14/o1_mf_1_12_fbkyq121_.arc RECID=16 STAMP=970761918
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/DB11G_DB/recovery_area/DB11G/archivelog/2018_03_14/o1_mf_2_8_fbkyq7yo_.arc RECID=18 STAMP=970761919
media recovery complete, elapsed time: 00:00:03
Finished recover at 14-MAR-2018 16:05:24
RMAN> exit
Recovery Manager complete.
[oracle@ora11gr2 DB_Backup]$
Step:-10
Rename the old redolog files to new files and open the database in resetlogs mode
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 14 16:07:09 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
16:07:10 SYS@DB11G>select GROUP#,MEMBER from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
2 +DATA/db11g/onlinelog/group_2.262.952164013
1 +DATA/db11g/onlinelog/group_1.261.952164009
3 +DATA/db11g/onlinelog/group_3.266.952164647
4 +DATA/db11g/onlinelog/group_4.267.952164651
16:07:11 SYS@DB11G>alter database rename file '+DATA/db11g/onlinelog/group_1.261.952164009' to '/u01/app/oracle/DB11G_DB/logfile/redo01a.log';
alter database rename file '+DATA/db11g/onlinelog/group_2.262.952164013' to '/u01/app/oracle/DB11G_DB/logfile/redo02a.log';
alter database rename file '+DATA/db11g/onlinelog/group_3.266.952164647' to '/u01/app/oracle/DB11G_DB/logfile/redo03a.log';
alter database rename file '+DATA/db11g/onlinelog/group_4.267.952164651' to '/u01/app/oracle/DB11G_DB/logfile/redo04a.log';
Database altered.
16:07:25 SYS@DB11G>
Database altered.
16:07:25 SYS@DB11G>
Database altered.
16:07:25 SYS@DB11G>
Database altered.
16:07:25 SYS@DB11G>
16:07:26 SYS@DB11G>select GROUP#,MEMBER from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
2 /u01/app/oracle/DB11G_DB/logfile/redo02a.log
1 /u01/app/oracle/DB11G_DB/logfile/redo01a.log
3 /u01/app/oracle/DB11G_DB/logfile/redo03a.log
4 /u01/app/oracle/DB11G_DB/logfile/redo04a.log
16:07:29 SYS@DB11G>alter database open resetlogs;
Database altered.
16:16:56 SYS@DB11G>select INST_ID,GROUP#,THREAD#,BYTES/1024/1024 SIZE_MB,MEMBERS,STATUS from gv$log order by INST_ID;
INST_ID GROUP# THREAD# SIZE_MB MEMBERS STATUS
---------- ---------- ---------- ---------- ---------- ----------------
1 1 1 50 1 CURRENT
1 4 2 50 1 UNUSED
1 3 2 50 1 INACTIVE
1 2 1 50 1 UNUSED
16:16:56 SYS@DB11G>col MEMBER for A60
16:16:56 SYS@DB11G>select INST_ID,GROUP#,MEMBER from gv$logfile order by INST_ID,GROUP#;
INST_ID GROUP# MEMBER
---------- ---------- ------------------------------------------------------------
1 1 /u01/app/oracle/DB11G_DB/logfile/redo01a.log
1 2 /u01/app/oracle/DB11G_DB/logfile/redo02a.log
1 3 /u01/app/oracle/DB11G_DB/logfile/redo03a.log
1 4 /u01/app/oracle/DB11G_DB/logfile/redo04a.log
Step:-11
Disable thread 2 redolog group and drop unnecessary redolog groups after creating new groups
THREAD# STATUS ENABLED GROUPS INSTANCE
---------- ------ -------- ---------- --------------------------------------------------------------------------------
1 OPEN PUBLIC 4 DB11G
2 CLOSED PUBLIC 2 DB11G2
16:28:36 SYS@DB11G>alter database disable thread 2;
Database altered.
16:29:44 SYS@DB11G>select THREAD#,STATUS,ENABLED,GROUPS,INSTANCE from gV$THREAD;
THREAD# STATUS ENABLED GROUPS INSTANCE
---------- ------ -------- ---------- --------------------------------------------------------------------------------
1 OPEN PUBLIC 4 DB11G
2 CLOSED DISABLED 2 DB11G2
Afetr Adding SOme logfile group and Droping unnecessary redolog groups below is the final stattus
---------- ---------- ---------- ---------- ---------- ----------------
1 1 1 20 1 CURRENT
1 3 1 20 1 INACTIVE
1 2 1 20 1 INACTIVE
Step:-12
Drop unused second undo tablespace
------------------------------ ----------- ----------- ----------- ---------- ----------
SYSAUX 524 36 560 6.43 93.57
UNDOTBS1 94 1 95 1.05 98.95
USERS 3 2 5 40 60
SYSTEM 746 4 750 .53 99.47
EXAMPLE 309 4 313 1.28 98.72
UNDOTBS2 32 18 50 36 64
6 rows selected.
16:11:31 SYS@DB11G>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
16:12:46 SYS@DB11G>drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
Tablespace Used MB Free MB Total MB Pct. Free PCT_USED
------------------------------ ----------- ----------- ----------- ---------- ----------
SYSAUX 524 36 560 6.43 93.57
UNDOTBS1 94 1 95 1.05 98.95
USERS 3 2 5 40 60
SYSTEM 746 4 750 .53 99.47
EXAMPLE 309 4 313 1.28 98.72
TABLESPACE_NAME FILE_ID FILE_NAME SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
USERS 4 /u01/app/oracle/DB11G_DB/datafile/users01.dbf .004882813 YES
UNDOTBS1 3 /u01/app/oracle/DB11G_DB/datafile/undotbs01.dbf .092773438 YES
SYSAUX 2 /u01/app/oracle/DB11G_DB/datafile/sysaux01.dbf .546875 YES
SYSTEM 1 /u01/app/oracle/DB11G_DB/datafile/system01.dbf .732421875 YES
EXAMPLE 5 /u01/app/oracle/DB11G_DB/datafile/example01.dbf .305786133 YES
Step:-13
Check all the database files and status
--------- -------------------- ------------ ----------------
DB11G READ WRITE ARCHIVELOG PRIMARY
INSTANCE_NAME LOGINS HOST_NAME DB Startup Time
---------------- ---------- -------------------- -----------------------------
DB11G ALLOWED ora11gr2.localdomain 14-MAR-2018 16:25:20
NAME
-----------------------------------------------------
/u01/app/oracle/DB11G_DB/controlfile/control01.ctl
Tablespace Used MB Free MB Total MB Pct. Free PCT_USED
------------------------------ ----------- ----------- ----------- ---------- ----------
SYSAUX 524 36 560 6.43 93.57
UNDOTBS1 94 1 95 1.05 98.95
USERS 3 2 5 40 60
SYSTEM 747 3 750 .4 99.6
EXAMPLE 309 4 313 1.28 98.72
TABLESPACE_NAME FILE_ID FILE_NAME SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
USERS 4 /u01/app/oracle/DB11G_DB/datafile/users01.dbf .004882813 YES
UNDOTBS1 3 /u01/app/oracle/DB11G_DB/datafile/undotbs01.dbf .092773438 YES
SYSAUX 2 /u01/app/oracle/DB11G_DB/datafile/sysaux01.dbf .546875 YES
SYSTEM 1 /u01/app/oracle/DB11G_DB/datafile/system01.dbf .732421875 YES
EXAMPLE 5 /u01/app/oracle/DB11G_DB/datafile/example01.dbf .305786133 YES
TABLESPACE GB_TOTAL GB_USED GB_FREE
------------------------------ ---------- ---------- ----------
TEMP .01953125 0 .01953125
TABLESPACE_NAME FILE_ID FILE_NAME SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
TEMP 1 /u01/app/oracle/DB11G_DB/tempfile/temp01.dbf .01953125 YES
INST_ID GROUP# THREAD# SIZE_MB MEMBERS STATUS
---------- ---------- ---------- ---------- ---------- ----------------
1 1 1 20 1 INACTIVE
1 3 1 20 1 CURRENT
1 2 1 20 1 INACTIVE
INST_ID GROUP# MEMBER
---------- ---------- ------------------------------------------------------------
1 1 /u01/app/oracle/DB11G_DB/logfile/redo01a.log
1 2 /u01/app/oracle/DB11G_DB/logfile/redo02a.log
1 3 /u01/app/oracle/DB11G_DB/logfile/redo03a.log
No comments:
Post a Comment