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.
5. Startup nomount the database in second server.
6. Ready create controlfile sql file using trace controlfile for required databafiles which are needs to be restore.
7. Run the create controlfile script it will automatially move the db to mount mode.
8. Catalog the Backups and check it properly fetched the backups.
9. Restore,Recover the database and open the database in resetlogs.
10. Check the table conunt it will be 12 rows.
11. 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:0722-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 200
col 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
-rwxrwxrwx 1 root root 6.6K Mar 21 22:24 tracecon.trc
tracecon.trc == Trace backup of controlfile using below command
[ alter database backup conntrolfile to trace as '/mnt/hgfs/DB_Backup/tracecon.trc'; ]
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
-rwxrwxrwx 1 root root 6.6K Mar 21 22:24 tracecon.trc
4. Copy the pfile/spfile to second server.
[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
LIGHT:/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 initLIGHT.ora
LIGHT.__db_cache_size=687865856
LIGHT.__java_pool_size=16777216
LIGHT.__large_pool_size=33554432
LIGHT.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
LIGHT.__pga_aggregate_target=671088640
LIGHT.__sga_target=989855744
LIGHT.__shared_io_pool_size=0
LIGHT.__shared_pool_size=234881024
LIGHT.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/LIGHT/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/LIGHT/control01.ctl','/u01/app/oracle/fast_recovery_area/LIGHT/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='LIGHT'
*.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=LIGHTXDB)'
*.memory_target=1653604352
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@serverb dbs]$ mkdir -p /u01/app/oracle/admin/LIGHT/adump /u01/app/oracle/oradata/LIGHT/ /u01/app/oracle/fast_recovery_area/LIGHT/
[oracle@serverb dbs]$
5. Startup nomount the database in second server.
[oracle@serverb ~]$ . oraenv
ORACLE_SID = [DB11G] ? LIGHT
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@LIGHT>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@LIGHT>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. Ready create controlfile sql file using trace controlfile for required databafiles which are needs to be restore.
[oracle@serverb DB_Backup]$ cat controlfromtrc.sql
CREATE CONTROLFILE REUSE DATABASE "LIGHT" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/LIGHT/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/LIGHT/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/LIGHT/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/LIGHT/system01.dbf',
'/u01/app/oracle/oradata/LIGHT/sysaux01.dbf',
'/u01/app/oracle/oradata/LIGHT/undotbs01.dbf',
'/u01/app/oracle/oradata/LIGHT/users01.dbf',
'/u01/app/oracle/oradata/LIGHT/intra01.dbf',
'/u01/app/oracle/oradata/LIGHT/intra02.dbf'
CHARACTER SET WE8MSWIN1252
;
7. Run the create control file script it will automatically move the db to mount mode.
[oracle@serverb DB_Backup]$ sq
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 22 03:56:48 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
03:56:48 SYS@LIGHT>@controlfromtrc.sql
Control file created.
03:56:50 SYS@LIGHT>select name,open_mode,log_mode,database_role from v$database;
NAME OPEN_MODE LOG_MODE DATABASE_ROLE
--------- -------------------- ------------ ----------------
LIGHT MOUNTE ARCHIVELOG PRIMARY
8. Catalog the Backups and check it properly fetched the backups.
[oracle@serverb DB_Backup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 22 03:57:47 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: LIGHT (DBID=722849799, not open)
RMAN> list backup summary;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> catalog start with '/mnt/hgfs/DB_Backup/';
searching for all files that match the pattern /mnt/hgfs/DB_Backup/
List of Files Unknown to the Database
=====================================
File Name: /mnt/hgfs/DB_Backup/controlfromtrc.sql
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_12_1_CONTROL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_14_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_15_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_16_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_18_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_19_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_1_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_20_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_21_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_22_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_23_1_CONTROL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_25_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_26_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_27_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_29_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_2_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_30_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_31_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_32_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_33_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_34_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_35_1_CONTROL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_3_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_9_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/tracecon.trc
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_12_1_CONTROL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_14_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_15_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_16_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_18_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_19_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_1_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_20_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_21_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_22_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_23_1_CONTROL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_25_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_26_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_27_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_29_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_2_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_30_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_31_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_32_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_33_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_34_1_ARCHIVE.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_35_1_CONTROL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_3_1_FULL.bkp
File Name: /mnt/hgfs/DB_Backup/LIGHT_20180322_9_1_ARCHIVE.bkp
List of Files Which Where Not Cataloged
=======================================
File Name: /mnt/hgfs/DB_Backup/controlfromtrc.sql
RMAN-07517: Reason: The file header is corrupted
File Name: /mnt/hgfs/DB_Backup/tracecon.trc
RMAN-07517: Reason: The file header is corrupted
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:23:58 1 1 NO DB_CONTROL
2 B F A DISK 22-MAR-2018 02:39:40 1 1 YES DB_FULL
3 B F A DISK 22-MAR-2018 02:39:40 1 1 YES DB_FULL
4 B F A DISK 22-MAR-2018 02:39:41 1 1 YES DB_FULL
5 B A A DISK 22-MAR-2018 02:40:49 1 1 NO DB_ARCHIVE
6 B A A DISK 22-MAR-2018 02:40:50 1 1 NO DB_ARCHIVE
7 B F A DISK 22-MAR-2018 02:19:41 1 1 YES DB_FULL
8 B A A DISK 22-MAR-2018 02:40:50 1 1 NO DB_ARCHIVE
9 B A A DISK 22-MAR-2018 02:40:50 1 1 NO DB_ARCHIVE
10 B A A DISK 22-MAR-2018 02:40:51 1 1 NO DB_ARCHIVE
11 B F A DISK 22-MAR-2018 02:40:52 1 1 NO DB_CONTROL
12 B F A DISK 22-MAR-2018 03:34:20 1 1 YES DB_FULL
13 B F A DISK 22-MAR-2018 03:34:21 1 1 YES DB_FULL
14 B F A DISK 22-MAR-2018 03:34:22 1 1 YES DB_FULL
15 B A A DISK 22-MAR-2018 03:36:02 1 1 NO DB_ARCHIVE
16 B F A DISK 22-MAR-2018 02:19:41 1 1 YES DB_FULL
17 B A A DISK 22-MAR-2018 03:36:02 1 1 NO DB_ARCHIVE
18 B A A DISK 22-MAR-2018 03:36:03 1 1 NO DB_ARCHIVE
19 B A A DISK 22-MAR-2018 03:36:03 1 1 NO DB_ARCHIVE
20 B A A DISK 22-MAR-2018 03:36:04 1 1 NO DB_ARCHIVE
21 B A A DISK 22-MAR-2018 03:36:04 1 1 NO DB_ARCHIVE
22 B F A DISK 22-MAR-2018 03:36:06 1 1 NO DB_CONTROL
23 B F A DISK 22-MAR-2018 02:19:43 1 1 YES DB_FULL
24 B A A DISK 22-MAR-2018 02:22:28 1 1 NO DB_ARCHIVE
9. Restore,Recover the database and open the database in resetlogs.
RMAN> run{
set until sequence 46;
restore database;
recover database;
alter database open resetlogs;
}2> 3> 4> 5> 6>
executing command: SET until clause
Starting restore at 22-MAR-2018 03:58:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
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
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 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
Finished restore at 22-MAR-2018 03:59:53
Starting recover at 22-MAR-2018 03:59:53
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=43
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=44
channel ORA_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/LIGHT_20180322_9_1_ARCHIVE.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/LIGHT_20180322_9_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/LIGHT/archivelog/2018_03_22/o1_mf_1_43_fc5q71rz_.arc thread=1 sequence=43
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/LIGHT/archivelog/2018_03_22/o1_mf_1_43_fc5q71rz_.arc RECID=2 STAMP=971409593
archived log file name=/u01/app/oracle/fast_recovery_area/LIGHT/archivelog/2018_03_22/o1_mf_1_44_fc5q71sf_.arc thread=1 sequence=44
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/LIGHT/archivelog/2018_03_22/o1_mf_1_44_fc5q71sf_.arc RECID=1 STAMP=971409593
channel ORA_DISK_1: starting archived log restore to default destination
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_30_1_ARCHIVE.bkp
channel ORA_DISK_1: piece handle=/mnt/hgfs/DB_Backup/LIGHT_20180322_30_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/LIGHT/archivelog/2018_03_22/o1_mf_1_45_fc5q73ng_.arc thread=1 sequence=45
unable to find archived log
archived log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/22/2018 03:59:57
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 935078
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2 2 LIGHT 722849799 PARENT 925702 21-MAR-2018 15:08:58
1 1 LIGHT 722849799 CURRENT 935078 22-MAR-2018 03:50:15
RMAN> alter database open restlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "resetlogs, ;"
RMAN-01008: the bad identifier was: restlogs
RMAN-01007: at line 1 column 21 file: standard input
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
10. Check the table conunt 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.
11. Export the table from second server db and import into target db with replace then we will achive required table data.
Server - B (Second Server)
[oracle@serverb ~]$ expdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_uintra_tab1.dmp LOGFILE=expdp_uintra_tab1.log TABLES=UINTRA.TAB1Server - A (Main Server)
[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