Steps:-
1. Actual requirement for this activity.
2. Existing Details for the Database.
3. Prepare the Second Server where we can restore the database with same file system and tarnsfer the backups taken from source server.
4. Copy the pfile/spfile to second server and rename db_name and db_unique_name and all values.
5. Startup nomount the database in second server.
6. Set DBID and Restore the Controlfile from old backup.
7. Restore,Recover all the required tablespaces in database and open the database with resetlogs.
8. Check the table conunt it will be 12 rows.
9. Export the table from second server db and import into target db with replace then we will achive required table data.
----------------------------------------------------------
1. Actual requirement for this activity.
>> We need to recover this below table to 12 rows time= 22-MAR-2018 02:28:07
22-MAR-2018 02:28:07 >> select * from uintra.tab1; >>> 12 rows (Sequence = 45 , SCN = 935136)
22-MAR-2018 03:33:17 >> select * from uintra.tab1; >>> 5 rows (Sequence = 64 , SCN = 937496)
2. Existing Details for the Database
Database Details (LIGHT):-
set lines 200 pages 200col HOST_NAME for A20
select name,open_mode,log_mode,database_role from v$database;
SELECT instance_name,LOGINS,host_name,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM v$instance;
NAME OPEN_MODE LOG_MODE DATABASE_ROLE
--------- -------------------- ------------ ----------------
LIGHT READ WRITE ARCHIVELOG PRIMARY
INSTANCE_NAME LOGINS HOST_NAME DB Startup Time
---------------- ---------- -------------------- --------------------
LIGHT ALLOWED servera.oracle.com 22-MAR-2018 02:08:49
TABLESPACE_NAME size MB free MB % used
---------------------------------------- ------------ ----------- ------
HELLO 30 29 3
INTRA 45 43 4
LILLO 36 34 5
USERS 5 4 26
UNDOTBS1 30 2 95
SYSAUX 470 8 98
SYSTEM 740 6 99
TEMP **TEMP** 20 0 100
8 rows selected.
TABLESPACE_NAME FILE_ID FILE_NAME SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
USERS 4 /u01/app/oracle/oradata/LIGHT/users01.dbf .004882813 YES
UNDOTBS1 3 /u01/app/oracle/oradata/LIGHT/undotbs01.dbf .029296875 YES
SYSAUX 2 /u01/app/oracle/oradata/LIGHT/sysaux01.dbf .458984375 YES
SYSTEM 1 /u01/app/oracle/oradata/LIGHT/system01.dbf .72265625 YES
HELLO 5 /u01/app/oracle/oradata/LIGHT/hello01.dbf .029296875 NO
LILLO 6 /u01/app/oracle/oradata/LIGHT/lillo01.dbf .025390625 NO
LILLO 7 /u01/app/oracle/oradata/LIGHT/lillo02.dbf .009765625 NO
INTRA 8 /u01/app/oracle/oradata/LIGHT/intra01.dbf .01953125 NO
INTRA 9 /u01/app/oracle/oradata/LIGHT/intra02.dbf .024414063 NO
9 rows selected.
SUM(BYTES)/1024/1024/1024
-------------------------
1.34375
Table and Backup Details:-
=== Database Full Backup ===
05:11:34 SYS@LIGHT>select table_name from dba_tables where tablespace_name='INTRA';
TABLE_NAME
------------------------------
TAB1
02:28:07 UINTRA@LIGHT>select * from tab1;
EMN NAME
---------- --------------------
999 Why
998 Haha
987 Down
986 Downnew
100 Hari
304 Loli
608 liha
906 Tat
404 Error
101 Ram
105 Cool
111 Final
12 rows selected.
02:28:20 SYS@LIGHT>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 43
Next log sequence to archive 45
Current log sequence 45
02:38:56 SYS@LIGHT>select dbid,name,current_scn from v$database;
DBID NAME CURRENT_SCN
---------- --------- -----------
722849799 LIGHT 935136
=== Database/Archive Log Full Backup ===
03:33:17 UINTRA@LIGHT>select * from tab1;
EMN NAME
---------- --------------------
987 Down
100 Hari
304 Loli
906 Tat
105 Cool
5 rows selected.
03:33:40 SYS@LIGHT>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 62
Next log sequence to archive 64
Current log sequence 64
03:33:44 SYS@LIGHT>select dbid,name,current_scn from v$database;
DBID NAME CURRENT_SCN
---------- --------- -----------
722849799 LIGHT 937496
=== Final Database/Archive Log Backup Details ===
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1 B F A DISK 22-MAR-2018 02:19:49 1 1 YES DB_FULL
2 B F A DISK 22-MAR-2018 02:21:39 1 1 YES DB_FULL
3 B F A DISK 22-MAR-2018 02:22:14 1 1 YES DB_FULL
8 B A A DISK 22-MAR-2018 02:22:29 1 1 NO DB_ARCHIVE
11 B F A DISK 22-MAR-2018 02:22:43 1 1 NO TAG20180322T022241
12 B F A DISK 22-MAR-2018 02:24:00 1 1 NO DB_CONTROL
13 B F A DISK 22-MAR-2018 02:24:01 1 1 NO TAG20180322T022400
14 B F A DISK 22-MAR-2018 02:39:43 1 1 YES DB_FULL
15 B F A DISK 22-MAR-2018 02:40:18 1 1 YES DB_FULL
16 B F A DISK 22-MAR-2018 02:40:47 1 1 YES DB_FULL
17 B F A DISK 22-MAR-2018 02:40:48 1 1 NO TAG20180322T024047
18 B A A DISK 22-MAR-2018 02:40:50 1 1 NO DB_ARCHIVE
19 B A A DISK 22-MAR-2018 02:40:50 1 1 NO DB_ARCHIVE
20 B A A DISK 22-MAR-2018 02:40:50 1 1 NO DB_ARCHIVE
21 B A A DISK 22-MAR-2018 02:40:51 1 1 NO DB_ARCHIVE
22 B A A DISK 22-MAR-2018 02:40:51 1 1 NO DB_ARCHIVE
23 B F A DISK 22-MAR-2018 02:40:54 1 1 NO DB_CONTROL
24 B F A DISK 22-MAR-2018 02:40:57 1 1 NO TAG20180322T024056
25 B F A DISK 22-MAR-2018 03:34:24 1 1 YES DB_FULL
26 B F A DISK 22-MAR-2018 03:35:05 1 1 YES DB_FULL
27 B F A DISK 22-MAR-2018 03:35:50 1 1 YES DB_FULL
28 B F A DISK 22-MAR-2018 03:35:58 1 1 NO TAG20180322T033557
29 B A A DISK 22-MAR-2018 03:36:02 1 1 NO DB_ARCHIVE
30 B A A DISK 22-MAR-2018 03:36:03 1 1 NO DB_ARCHIVE
31 B A A DISK 22-MAR-2018 03:36:03 1 1 NO DB_ARCHIVE
32 B A A DISK 22-MAR-2018 03:36:04 1 1 NO DB_ARCHIVE
33 B A A DISK 22-MAR-2018 03:36:04 1 1 NO DB_ARCHIVE
34 B A A DISK 22-MAR-2018 03:36:05 1 1 NO DB_ARCHIVE
35 B F A DISK 22-MAR-2018 03:36:09 1 1 NO DB_CONTROL
36 B F A DISK 22-MAR-2018 03:36:12 1 1 NO TAG20180322T033610
[oracle@servera DB_Backup]$ pwd
/mnt/hgfs/DB_Backup/
[oracle@servera DB_Backup]$ ls -ltrh
total 841M
-rwxrwxrwx 1 root root 5.5M Mar 21 20:49 LIGHT_20180322_3_1_FULL.bkp
-rwxrwxrwx 1 root root 69M Mar 21 20:51 LIGHT_20180322_2_1_FULL.bkp
-rwxrwxrwx 1 root root 193M Mar 21 20:52 LIGHT_20180322_1_1_FULL.bkp
-rwxrwxrwx 1 root root 3.2M Mar 21 20:52 LIGHT_20180322_9_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 9.7M Mar 21 20:54 LIGHT_20180322_12_1_CONTROL.bkp
-rwxrwxrwx 1 root root 5.7M Mar 21 21:09 LIGHT_20180322_16_1_FULL.bkp
-rwxrwxrwx 1 root root 70M Mar 21 21:10 LIGHT_20180322_15_1_FULL.bkp
-rwxrwxrwx 1 root root 193M Mar 21 21:11 LIGHT_20180322_14_1_FULL.bkp
-rwxrwxrwx 1 root root 3.2M Mar 21 21:11 LIGHT_20180322_18_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 562K Mar 21 21:11 LIGHT_20180322_19_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 7.0K Mar 21 21:11 LIGHT_20180322_20_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 4.5K Mar 21 21:11 LIGHT_20180322_21_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 7.0K Mar 21 21:11 LIGHT_20180322_22_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 9.7M Mar 21 21:11 LIGHT_20180322_23_1_CONTROL.bkp
-rwxrwxrwx 1 root root 1.3M Mar 21 22:04 LIGHT_20180322_27_1_FULL.bkp
-rwxrwxrwx 1 root root 70M Mar 21 22:05 LIGHT_20180322_26_1_FULL.bkp
-rwxrwxrwx 1 root root 193M Mar 21 22:06 LIGHT_20180322_25_1_FULL.bkp
-rwxrwxrwx 1 root root 3.2M Mar 21 22:06 LIGHT_20180322_29_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 563K Mar 21 22:06 LIGHT_20180322_30_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 8.5K Mar 21 22:06 LIGHT_20180322_31_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 126K Mar 21 22:06 LIGHT_20180322_33_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 2.5K Mar 21 22:06 LIGHT_20180322_34_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 3.6M Mar 21 22:06 LIGHT_20180322_32_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 9.7M Mar 21 22:06 LIGHT_20180322_35_1_CONTROL.bkp
3. Prepare the Second Server where we can restore the database with same file system and transfer the backups taken from source server.
[oracle@serverb DB_Backup]$ pwd
/mnt/hgfs/DB_Backup/
[oracle@serverb DB_Backup]$ ls -ltrh
total 841M
-rwxrwxrwx 1 root root 5.5M Mar 21 20:49 LIGHT_20180322_3_1_FULL.bkp
-rwxrwxrwx 1 root root 69M Mar 21 20:51 LIGHT_20180322_2_1_FULL.bkp
-rwxrwxrwx 1 root root 193M Mar 21 20:52 LIGHT_20180322_1_1_FULL.bkp
-rwxrwxrwx 1 root root 3.2M Mar 21 20:52 LIGHT_20180322_9_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 9.7M Mar 21 20:54 LIGHT_20180322_12_1_CONTROL.bkp
-rwxrwxrwx 1 root root 5.7M Mar 21 21:09 LIGHT_20180322_16_1_FULL.bkp
-rwxrwxrwx 1 root root 70M Mar 21 21:10 LIGHT_20180322_15_1_FULL.bkp
-rwxrwxrwx 1 root root 193M Mar 21 21:11 LIGHT_20180322_14_1_FULL.bkp
-rwxrwxrwx 1 root root 3.2M Mar 21 21:11 LIGHT_20180322_18_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 562K Mar 21 21:11 LIGHT_20180322_19_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 7.0K Mar 21 21:11 LIGHT_20180322_20_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 4.5K Mar 21 21:11 LIGHT_20180322_21_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 7.0K Mar 21 21:11 LIGHT_20180322_22_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 9.7M Mar 21 21:11 LIGHT_20180322_23_1_CONTROL.bkp
-rwxrwxrwx 1 root root 1.3M Mar 21 22:04 LIGHT_20180322_27_1_FULL.bkp
-rwxrwxrwx 1 root root 70M Mar 21 22:05 LIGHT_20180322_26_1_FULL.bkp
-rwxrwxrwx 1 root root 193M Mar 21 22:06 LIGHT_20180322_25_1_FULL.bkp
-rwxrwxrwx 1 root root 3.2M Mar 21 22:06 LIGHT_20180322_29_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 563K Mar 21 22:06 LIGHT_20180322_30_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 8.5K Mar 21 22:06 LIGHT_20180322_31_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 126K Mar 21 22:06 LIGHT_20180322_33_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 2.5K Mar 21 22:06 LIGHT_20180322_34_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 3.6M Mar 21 22:06 LIGHT_20180322_32_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root 9.7M Mar 21 22:06 LIGHT_20180322_35_1_CONTROL.bkp
4. Copy the pfile/spfile to second server and rename db_name and db_unique_name and all values.
[oracle@serverb ~]$ cat /etc/oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
DB11G:/u01/app/oracle/product/11.2.0.4/db_1:N
DARK:/u01/app/oracle/product/11.2.0.4/db_1:N
[oracle@serverb ~]$ cd /u01/app/oracle/product/11.2.0.4/db_1/dbs
[oracle@serverb dbs]$ ls
hc_DB11G.dat initLIGHT.ora init.ora lkDB11G orapwDB11G spfileDB11G.ora spfileLIGHT.ora
[oracle@serverb dbs]$ cat initDARK.ora
DARK.__db_cache_size=687865856
DARK.__java_pool_size=16777216
DARK.__large_pool_size=33554432
DARK.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DARK.__pga_aggregate_target=671088640
DARK.__sga_target=989855744
DARK.__shared_io_pool_size=0
DARK.__shared_pool_size=234881024
DARK.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DARK/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/DARK/control01.ctl','/u01/app/oracle/fast_recovery_area/DARK/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='LIGHT'
*.db_unique_name='DARK'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DARKXDB)'
#*.memory_target=1653604352
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@serverb dbs]$
5. Startup nomount the database in second server.
[oracle@serverb ~]$ . oraenv
ORACLE_SID = [DB11G] ? DARK
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@serverb DB_Backup]$ sq
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 22 03:19:16 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
03:19:16 SYS@DARK>startup nomount
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
03:19:42 SYS@DARK>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
6. Set DBID and Restore the Controlfile from old backup.
==========================================================
[oracle@serverb DB_Backup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 28 23:00:31 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: LIGHT (not mounted)
RMAN> set dbid=722849799;
executing command: SET DBID
RMAN> restore controlfile from '/mnt/hgfs/DB_Backup/LIGHT_20180322_23_1_CONTROL.bkp';
Starting restore at 28-MAR-2018 23:04:08
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:07
output file name=/u01/app/oracle/oradata/DARK/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/DARK/control02.ctl
Finished restore at 28-MAR-2018 23:04:17
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1 B F A DISK 22-MAR-2018 02:19:49 1 1 YES DB_FULL
2 B F A DISK 22-MAR-2018 02:21:39 1 1 YES DB_FULL
3 B F A DISK 22-MAR-2018 02:22:14 1 1 YES DB_FULL
8 B A A DISK 22-MAR-2018 02:22:29 1 1 NO DB_ARCHIVE
11 B F A DISK 22-MAR-2018 02:22:43 1 1 NO TAG20180322T022241
12 B F A DISK 22-MAR-2018 02:24:00 1 1 NO DB_CONTROL
13 B F A DISK 22-MAR-2018 02:24:01 1 1 NO TAG20180322T022400
14 B F A DISK 22-MAR-2018 02:39:43 1 1 YES DB_FULL
15 B F A DISK 22-MAR-2018 02:40:18 1 1 YES DB_FULL
16 B F A DISK 22-MAR-2018 02:40:47 1 1 YES DB_FULL
17 B F A DISK 22-MAR-2018 02:40:48 1 1 NO TAG20180322T024047
18 B A A DISK 22-MAR-2018 02:40:50 1 1 NO DB_ARCHIVE
19 B A A DISK 22-MAR-2018 02:40:50 1 1 NO DB_ARCHIVE
20 B A A DISK 22-MAR-2018 02:40:50 1 1 NO DB_ARCHIVE
21 B A A DISK 22-MAR-2018 02:40:51 1 1 NO DB_ARCHIVE
22 B A A DISK 22-MAR-2018 02:40:51 1 1 NO DB_ARCHIVE
RMAN> report schema;
Starting implicit crosscheck backup at 28-MAR-2018 23:05:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 16 objects
Finished implicit crosscheck backup at 28-MAR-2018 23:05:14
Starting implicit crosscheck copy at 28-MAR-2018 23:05:14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 28-MAR-2018 23:05:14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/DARK/autobackup/2018_03_28/o1_mf_s_971943747_fcp0vd55_.bkp
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name DARK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 740 SYSTEM *** /u01/app/oracle/oradata/LIGHT/system01.dbf
2 490 SYSAUX *** /u01/app/oracle/oradata/LIGHT/sysaux01.dbf
3 30 UNDOTBS1 *** /u01/app/oracle/oradata/LIGHT/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/LIGHT/users01.dbf
5 30 HELLO *** /u01/app/oracle/oradata/LIGHT/hello01.dbf
6 26 LILLO *** /u01/app/oracle/oradata/LIGHT/lillo01.dbf
7 10 LILLO *** /u01/app/oracle/oradata/LIGHT/lillo02.dbf
8 20 INTRA *** /u01/app/oracle/oradata/LIGHT/intra01.dbf
9 25 INTRA *** /u01/app/oracle/oradata/LIGHT/intra02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/LIGHT/temp01.dbf
7. Restore,Recover all the required tablespaces in database and open the database with resetlogs.
RMAN> run {
2> set until sequence 46;
3> restore tablespace SYSTEM,SYSAUX,UNDOTBS1,USERS,INTRA;
4> recover database skip tablespace HELLO,LILLO;
5> alter database open resetlogs;
6> }
executing command: SET until clause
Starting restore at 28-MAR-2018 23:14:35
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/oradata/LIGHT/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/LIGHT_20180322_3_1_FULL.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/LIGHT_20180322_3_1_FULL.bkp tag=DB_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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/oradata/LIGHT/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/LIGHT/intra01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/LIGHT/intra02.dbf
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/LIGHT_20180322_2_1_FULL.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/LIGHT_20180322_2_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 00001 to /u01/app/oracle/oradata/LIGHT/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/LIGHT/users01.dbf
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/LIGHT_20180322_1_1_FULL.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/LIGHT_20180322_1_1_FULL.bkp tag=DB_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 28-MAR-2018 23:15:39
Starting recover at 28-MAR-2018 23:15:39
using channel ORA_DISK_1
Executing: alter database datafile 5 offline
Executing: alter database datafile 6 offline
Executing: alter database datafile 7 offline
starting media recovery
archived log for thread 1 with sequence 43 is already on disk as file /u01/app/oracle/fast_recovery_area/LIGHT/archivelog/2018_03_22/o1_mf_1_43_fc5kj9pp_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/LIGHT/archivelog/2018_03_22/o1_mf_1_43_fc5kj9pp_.arc thread=1 sequence=43
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=44
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=45
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/LIGHT_20180322_19_1_ARCHIVE.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/LIGHT_20180322_19_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/fast_recovery_area/DARK/archivelog/2018_03_28/o1_mf_1_44_fcqo65bt_.arc thread=1 sequence=44
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/DARK/archivelog/2018_03_28/o1_mf_1_44_fcqo65bt_.arc RECID=67 STAMP=971997341
archived log file name=/u01/app/oracle/fast_recovery_area/DARK/archivelog/2018_03_28/o1_mf_1_45_fcqo6530_.arc thread=1 sequence=45
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/DARK/archivelog/2018_03_28/o1_mf_1_45_fcqo6530_.arc RECID=66 STAMP=971997341
media recovery complete, elapsed time: 00:00:00
Finished recover at 28-MAR-2018 23:15:42
database opened
RMAN> exit
Recovery Manager complete.
8. Check the table count it will be 12 rows.
NAME OPEN_MODE LOG_MODE DATABASE_ROLE
--------- -------------------- ------------ ----------------
LIGHT READ WRITE ARCHIVELOG PRIMARY
INSTANCE_NAME LOGINS HOST_NAME DB Startup Time
---------------- ---------- -------------------- --------------------
LIGHT ALLOWED serverb.oracle.com 22-MAR-2018 03:53:52
SUM(BYTES)/1024/1024/1024
-------------------------
1.27929688
TABLESPACE_NAME size MB free MB % used
---------------------------------------- ------------ ----------- ------
INTRA 45 43 4
USERS 5 4 26
SYSAUX 490 27 94
UNDOTBS1 30 1 98
SYSTEM 740 6 99
HELLO 0 100
LILLO 0 100
7 rows selected.
TABLESPACE_NAME FILE_ID FILE_NAME SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
INTRA 9 /u01/app/oracle/oradata/LIGHT/intra02.dbf .024414063 NO
INTRA 8 /u01/app/oracle/oradata/LIGHT/intra01.dbf .01953125 NO
USERS 4 /u01/app/oracle/oradata/LIGHT/users01.dbf .004882813 YES
UNDOTBS1 3 /u01/app/oracle/oradata/LIGHT/undotbs01.dbf .029296875 YES
SYSAUX 2 /u01/app/oracle/oradata/LIGHT/sysaux01.dbf .478515625 YES
SYSTEM 1 /u01/app/oracle/oradata/LIGHT/system01.dbf .72265625 YES
HELLO 5 /u01/app/oracle/product/11.2.0.4/db_1/dbs/MISSING00005
LILLO 6 /u01/app/oracle/product/11.2.0.4/db_1/dbs/MISSING00006
LILLO 7 /u01/app/oracle/product/11.2.0.4/db_1/dbs/MISSING00007
9 rows selected.
05:12:57 SYS@LIGHT>select * from uintra.tab1;
EMN NAME
---------- --------------------
999 Why
998 Haha
987 Down
986 Downnew
100 Hari
304 Loli
608 liha
906 Tat
404 Error
101 Ram
105 Cool
111 Final
12 rows selected.
9. Export the table from second server db and import into target db with replace then we will achive required table data.
[oracle@serverb ~]$ expdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_uintra_tab1.dmp LOGFILE=expdp_uintra_tab1.log TABLES=UINTRA.TAB1
[oracle@servera ~]$ impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_uintra_tab1.dmp LOGFILE=expdp_uintra_tab1.log TABLES=UINTRA.TAB1 TABLE_EXISTS_ACTION=REPLACE
--------------------------------------- END ---------------------------------------
No comments:
Post a Comment