Environment:-
Source Database:- DB11GSource DB Instance:- DB11G1,DB11G2
Source Server:- rac1,rac2
Target Database:- SNAP
Target Server:- ora11gr2
--------------------------------------
Steps:-
1. Check the Database,dbfiles server details along with pfile
2. 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. Create password file and Startup nomount the database
7. Make entry in tnsnames.ora
8. Make entry in listener.ora
9. Check the connection using rman
10. Start Duplicate target database to new db name
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
NAME OPEN_MODE LOG_MODE DATABASE_ROLE
--------- -------------------- ------------ ----------------
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)
cat initDB11G.ora
---------------------------------
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:-
[oracle@rac1 ~]$ rman target /
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
Step:-4
Target Pfile (Standalone pfile)
cat initSNAP.ora
---------------------------------
#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
SNAP.__db_cache_size=620756992
SNAP.__java_pool_size=16777216
SNAP.__large_pool_size=33554432
SNAP.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
SNAP.__pga_aggregate_target=671088640
SNAP.__sga_target=989855744
SNAP.__shared_io_pool_size=0
SNAP.__shared_pool_size=301989888
SNAP.__streams_pool_size=0
#*.audit_file_dest='/u02/app/oracle/admin/DB11G/adump'
*.audit_file_dest='/u01/app/oracle/admin/SNAP/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/SNAP/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='SNAP'
*.db_recovery_file_dest_size=10737418240
#*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest='/u01/app/oracle/SNAP/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'
*.db_file_name_convert='+DATA/db11g/datafile/','/u01/app/oracle/SNAP/datafile/','+DATA/db11g/tempfile/','/u01/app/oracle/SNAP/tempfile/'
*.log_file_name_convert='+DATA/db11g/onlinelog/','/u01/app/oracle/SNAP/logfile/'
undo_tablespace='UNDOTBS1'
After Modification Target Pfile (Standalone pfile)
===================================================
cat initSNAP.ora
---------------------------------
SNAP.__db_cache_size=620756992
SNAP.__java_pool_size=16777216
SNAP.__large_pool_size=33554432
SNAP.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
SNAP.__pga_aggregate_target=671088640
SNAP.__sga_target=989855744
SNAP.__shared_io_pool_size=0
SNAP.__shared_pool_size=301989888
SNAP.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/SNAP/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/SNAP/controlfile/control01.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/db11g/datafile/','/u01/app/oracle/SNAP/datafile/','+DATA/db11g/tempfile/','/u01/app/oracle/SNAP/tempfile/'
*.db_name='SNAP'#Reset to original value by RMAN
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u01/app/oracle/SNAP/recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.log_file_name_convert='+DATA/db11g/onlinelog/','/u01/app/oracle/SNAP/logfile/'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
Step:-5
5. Create all the required file system paths
mkdir -p /u01/app/oracle/SNAP/datafile
mkdir -p /u01/app/oracle/SNAP/controlfile
mkdir -p /u01/app/oracle/SNAP/logfile
mkdir -p /u01/app/oracle/SNAP/tempfile
Step:-6
Create password file and Startup nomount the database
[oracle@ora11gr2 dbs]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/dbs
[oracle@ora11gr2 dbs]orapwd file=orapwSNAP password=password entries=10
[oracle@ora11gr2 dbs]$ sq
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 14 18:52:06 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
18:52:07 SYS@SNAP>create spfile from pfile;
File created.
18:52:11 SYS@SNAP>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
Make entry in tnsnames.ora
[oracle@ora11gr2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
SNAP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11gr2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SNAP)
)
)
Step:-8
Make entry in listener.ora
[oracle@ora11gr2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11gr2.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TANMAY)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = SNAP)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Step:-9
Check the connection using rman
[oracle@ora11gr2 DB_Backup]$ rman target sys/password@DB11G auxiliary sys/password@SNAP
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 14 18:56:41 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB11G (DBID=421833570)
connected to auxiliary database: SNAP (not mounted)
RMAN>
Step:-10
Start Duplicate target database to new db name
[oracle@ora11gr2 DB_Backup]$ rman target sys/password@DB11G auxiliary sys/password@SNAP
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 14 18:56:41 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB11G (DBID=421833570)
connected to auxiliary database: SNAP (not mounted)
RMAN> duplicate target database to SNAP;
Starting Duplicate Db at 14-MAR-2018 18:57:00
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''DB11G'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''SNAP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''DB11G'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''SNAP'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
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
Starting restore at 14-MAR-2018 18:57:11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DATA/db11g/autobackup/2018_03_14/s_970776131.280.970776135
channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece +DATA/db11g/autobackup/2018_03_14/s_970776131.280.970776135
ORA-19505: failed to identify file "+DATA/db11g/autobackup/2018_03_14/s_970776131.280.970776135"
ORA-17503: ksfdopn:2 Failed to open file +DATA/db11g/autobackup/2018_03_14/s_970776131.280.970776135
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgrou
failover to previous backup
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_21_1_CONTROL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_21_1_CONTROL.bkp tag=DB_CONTROL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
output file name=/u01/app/oracle/SNAP/controlfile/control01.ctl
Finished restore at 14-MAR-2018 18:57:20
database mounted
contents of Memory Script:
{
set until scn 1052346;
set newname for datafile 1 to
"/u01/app/oracle/SNAP/datafile/system.256.952163783";
set newname for datafile 2 to
"/u01/app/oracle/SNAP/datafile/sysaux.257.952163785";
set newname for datafile 3 to
"/u01/app/oracle/SNAP/datafile/undotbs1.258.952163787";
set newname for datafile 4 to
"/u01/app/oracle/SNAP/datafile/users.259.952163787";
set newname for datafile 5 to
"/u01/app/oracle/SNAP/datafile/example.264.952164051";
set newname for datafile 6 to
"/u01/app/oracle/SNAP/datafile/undotbs2.265.952164501";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 14-MAR-2018 18:57:27
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/SNAP/datafile/undotbs1.258.952163787
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/SNAP/datafile/example.264.952164051
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_11_1_FULL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_11_1_FULL.bkp tag=DB_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/SNAP/datafile/sysaux.257.952163785
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/SNAP/datafile/users.259.952163787
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_10_1_FULL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_10_1_FULL.bkp tag=DB_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/SNAP/datafile/system.256.952163783
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/SNAP/datafile/undotbs2.265.952164501
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_9_1_FULL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_9_1_FULL.bkp tag=DB_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 14-MAR-2018 19:01:35
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=970772496 file name=/u01/app/oracle/SNAP/datafile/system.256.952163783
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=970772496 file name=/u01/app/oracle/SNAP/datafile/sysaux.257.952163785
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=970772496 file name=/u01/app/oracle/SNAP/datafile/undotbs1.258.952163787
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=970772496 file name=/u01/app/oracle/SNAP/datafile/users.259.952163787
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=970772496 file name=/u01/app/oracle/SNAP/datafile/example.264.952164051
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=970772496 file name=/u01/app/oracle/SNAP/datafile/undotbs2.265.952164501
contents of Memory Script:
{
set until scn 1052346;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 14-MAR-2018 19:01:37
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_17_1_ARCHIVE.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_17_1_ARCHIVE.bkp tag=DB_ARCHIVE
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=/u01/app/oracle/SNAP/recovery_area/SNAP/archivelog/2018_03_14/o1_mf_1_12_fbl9246k_.arc thread=1 sequence=12
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=7
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=8
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=13
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/DB11G_20180314_18_1_ARCHIVE.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/DB_Backup/DB11G_20180314_18_1_ARCHIVE.bkp tag=DB_ARCHIVE
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/SNAP/recovery_area/SNAP/archivelog/2018_03_14/o1_mf_2_7_fbl92lvo_.arc thread=2 sequence=7
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/SNAP/recovery_area/SNAP/archivelog/2018_03_14/o1_mf_2_7_fbl92lvo_.arc RECID=17 STAMP=970772522
archived log file name=/u01/app/oracle/SNAP/recovery_area/SNAP/archivelog/2018_03_14/o1_mf_2_8_fbl92lwc_.arc thread=2 sequence=8
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/SNAP/recovery_area/SNAP/archivelog/2018_03_14/o1_mf_1_12_fbl9246k_.arc RECID=16 STAMP=970772515
archived log file name=/u01/app/oracle/SNAP/recovery_area/SNAP/archivelog/2018_03_14/o1_mf_1_13_fbl92lw4_.arc thread=1 sequence=13
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/SNAP/recovery_area/SNAP/archivelog/2018_03_14/o1_mf_1_13_fbl92lw4_.arc RECID=18 STAMP=970772522
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/SNAP/recovery_area/SNAP/archivelog/2018_03_14/o1_mf_2_8_fbl92lwc_.arc RECID=19 STAMP=970772522
media recovery complete, elapsed time: 00:00:07
Finished recover at 14-MAR-2018 19:02:11
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
contents of Memory Script:
{
sql clone "alter system set db_name =
''SNAP'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''SNAP'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
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
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SNAP" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/SNAP/logfile/group_1.261.952164009' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/SNAP/logfile/group_2.262.952164013' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/SNAP/datafile/system.256.952163783'
CHARACTER SET AL32UTF8
sql statement: ALTER DATABASE ADD LOGFILE
INSTANCE 'i2'
GROUP 3 ( '/u01/app/oracle/SNAP/logfile/group_3.266.952164647' ) SIZE 50 M REUSE,
GROUP 4 ( '/u01/app/oracle/SNAP/logfile/group_4.267.952164651' ) SIZE 50 M REUSE
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/SNAP/tempfile/temp.263.952164033";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/SNAP/datafile/sysaux.257.952163785",
"/u01/app/oracle/SNAP/datafile/undotbs1.258.952163787",
"/u01/app/oracle/SNAP/datafile/users.259.952163787",
"/u01/app/oracle/SNAP/datafile/example.264.952164051",
"/u01/app/oracle/SNAP/datafile/undotbs2.265.952164501";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/SNAP/tempfile/temp.263.952164033 in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/SNAP/datafile/sysaux.257.952163785 RECID=1 STAMP=970772557
cataloged datafile copy
datafile copy file name=/u01/app/oracle/SNAP/datafile/undotbs1.258.952163787 RECID=2 STAMP=970772557
cataloged datafile copy
datafile copy file name=/u01/app/oracle/SNAP/datafile/users.259.952163787 RECID=3 STAMP=970772557
cataloged datafile copy
datafile copy file name=/u01/app/oracle/SNAP/datafile/example.264.952164051 RECID=4 STAMP=970772557
cataloged datafile copy
datafile copy file name=/u01/app/oracle/SNAP/datafile/undotbs2.265.952164501 RECID=5 STAMP=970772557
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=970772557 file name=/u01/app/oracle/SNAP/datafile/sysaux.257.952163785
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=970772557 file name=/u01/app/oracle/SNAP/datafile/undotbs1.258.952163787
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=970772557 file name=/u01/app/oracle/SNAP/datafile/users.259.952163787
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=970772557 file name=/u01/app/oracle/SNAP/datafile/example.264.952164051
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=970772557 file name=/u01/app/oracle/SNAP/datafile/undotbs2.265.952164501
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 14-MAR-2018 19:03:01
RMAN> exit
Recovery Manager complete.
Step:-11
Disable thread 2 redolog group and drop unnecessary redolog groups after creating new groups
16:28:15 SYS@DB11G>select THREAD#,STATUS,ENABLED,GROUPS,INSTANCE from gV$THREAD;
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
INST_ID GROUP# THREAD# SIZE_MB MEMBERS STATUS
---------- ---------- ---------- ---------- ---------- ----------------
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
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
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
NAME OPEN_MODE LOG_MODE DATABASE_ROLE
--------- -------------------- ------------ ----------------
SNAP READ WRITE ARCHIVELOG PRIMARY
INSTANCE_NAME LOGINS HOST_NAME DB Startup Time
---------------- ---------- -------------------- -----------------------------
SNAP ALLOWED ora11gr2.localdomain 14-MAR-2018 19:38:41
NAME
--------------------------------------------------------------
/u01/app/oracle/SNAP/controlfile/control01.ctl
Tablespace Used MB Free MB Total MB Pct. Free PCT_USED
------------------------------ ----------- ----------- ----------- ---------- ----------
SYSAUX 527 33 560 5.89 94.11
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
------------------------- ---------- ------------------------------------------------------------ ---------- ---
SYSTEM 1 /u01/app/oracle/SNAP/datafile/system.256.952163783 .732421875 YES
SYSAUX 2 /u01/app/oracle/SNAP/datafile/sysaux.257.952163785 .546875 YES
UNDOTBS1 3 /u01/app/oracle/SNAP/datafile/undotbs1.258.952163787 .092773438 YES
USERS 4 /u01/app/oracle/SNAP/datafile/users.259.952163787 .004882813 YES
EXAMPLE 5 /u01/app/oracle/SNAP/datafile/example.264.952164051 .305786133 YES
TABLESPACE GB_TOTAL GB_USED GB_FREE
------------------------------ ---------- ---------- ----------
TEMP .03125 0 .03125
TABLESPACE_NAME FILE_ID FILE_NAME SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
TEMP 1 /u01/app/oracle/SNAP/tempfile/temp.263.952164033 .03125 YES
INST_ID GROUP# THREAD# SIZE_MB MEMBERS STATUS
---------- ---------- ---------- ---------- ---------- ----------------
1 1 1 30 1 INACTIVE
1 3 1 30 1 INACTIVE
1 2 1 30 1 CURRENT
INST_ID GROUP# MEMBER
---------- ---------- ------------------------------------------------------------
1 1 /u01/app/oracle/SNAP/logfile/redolog01.log
1 2 /u01/app/oracle/SNAP/logfile/redolog02.log
1 3 /u01/app/oracle/SNAP/logfile/redolog03.log
No comments:
Post a Comment