Tanmay DBA's Blog: March 2018

Wednesday, 28 March 2018

Partial Restore Of A Database

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 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

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 ---------------------------------------

Thursday, 22 March 2018

Tablespace Point-in-time Recovery

Steps:-

1. Actual requirement for this activity.
2. Existing Details for the Database.
3. Create a directory which is used by RMAN to restore a temporary database
4. Recover the tablespace Point-in-time using RMAN.
5. Bring the tablespace and datafile Online.
6. Check the table conunt it will be 12 rows.

----------------------------------------------------------

RMAN Inbuilt used in Step - 4 :-

recover tablespace INTRA until sequence 46 auxiliary destination '/u01/app/oracle/new_location_temp/';

# set requested point in time
# startup nomount a temporary database
# restore the controlfile
# mount the controlfile
# archive current online log
# avoid unnecessary autobackups for structural changes during TSPITR
# set requested point in time
# set destinations for recovery set and auxiliary set datafiles
# switch all tempfiles
# restore the tablespaces in the recovery set and the auxiliary set
# set requested point in time
# online the datafiles restored or switched
# recover and open resetlogs
# make read only the tablespace that will be exported
# create directory for datapump import
# create directory for datapump export
# Performing export of metadata using transportable tablespace
# shutdown clone before import
# drop target tablespaces before importing them back
# Performing import of metadata using transportable tablespace
# make read write and offline the imported tablespaces
# enable autobackups after TSPITR is finished
# Removing automatic instance

----------------------------------------------------------

1. Actual requirement for this activity.

Note:- { *** It will recover all the tables exist in the Tablespace to specified time. *** }

>> 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 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 ===

=== Database/Archive Log Full Backup ===

=== Final Database/Archive Log Backup Details ===

3. Create a directory which is used by RMAN to restore a temporary database

[oracle@servera ~]$
[oracle@servera ~]$ mkdir -p /u01/app/oracle/new_location_temp
[oracle@servera ~]$ cd /u01/app/oracle/new_location_temp
[oracle@servera new_location_temp]$ pwd
/u01/app/oracle/new_location_temp
[oracle@servera new_location_temp]$ ls -ltr
total 0
[oracle@servera new_location_temp]$

4. Recover the tablespace Point-in-time using RMAN.

[oracle@servera dbca]$ . oraenv
ORACLE_SID = [DB11G] ? LIGHT
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@servera new_location_temp]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 22 05:00:49 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: LIGHT (DBID=722849799)

RMAN> recover tablespace INTRA until sequence 46 auxiliary destination '/u01/app/oracle/new_location_temp/';

Starting recover at 22-MAR-2018 05:03:11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='tAlc'

initialization parameters used for automatic instance:
db_name=LIGHT
db_unique_name=tAlc_tspitr_LIGHT
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/u01/app/oracle/new_location_temp/
log_archive_dest_1='location=/u01/app/oracle/new_location_temp/'
#No auxiliary parameter file used

starting up automatic instance LIGHT

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until logseq 46 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 22-MAR-2018 05:03:26
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 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 /u01/app/oracle/fast_recovery_area/LIGHT/autobackup/2018_03_22/o1_mf_s_971403840_fc5km8sq_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIGHT/autobackup/2018_03_22/o1_mf_s_971403840_fc5km8sq_.bkp tag=TAG20180322T022400
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/new_location_temp/LIGHT/controlfile/o1_mf_fc5ty6lc_.ctl
Finished restore at 22-MAR-2018 05:03:27

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until logseq 46 thread 1;
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'INTRA' ||' offline immediate';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 8 to
"/u01/app/oracle/oradata/LIGHT/intra01.dbf";
set newname for datafile 9 to
"/u01/app/oracle/oradata/LIGHT/intra02.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 8, 9;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace INTRA offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/new_location_temp/LIGHT/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 22-MAR-2018 05:03:32
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/new_location_temp/LIGHT/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/LIGHT_20180322_3_1_FULL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/DB_Backup/LIGHT_20180322_3_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:04
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/new_location_temp/LIGHT/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/LIGHT/intra01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/LIGHT/intra02.dbf
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/LIGHT_20180322_2_1_FULL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/DB_Backup/LIGHT_20180322_2_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:37
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/new_location_temp/LIGHT/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/DB_Backup/LIGHT_20180322_1_1_FULL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/DB_Backup/LIGHT_20180322_1_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:05
Finished restore at 22-MAR-2018 05:05:18

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=971413518 file name=/u01/app/oracle/new_location_temp/LIGHT/datafile/o1_mf_system_fc5tzok4_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=971413518 file name=/u01/app/oracle/new_location_temp/LIGHT/datafile/o1_mf_undotbs1_fc5tyfcf_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=971413518 file name=/u01/app/oracle/new_location_temp/LIGHT/datafile/o1_mf_sysaux_fc5tyjn9_.dbf

contents of Memory Script:
{
# set requested point in time
set until logseq 46 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 8 online";
sql clone "alter database datafile 9 online";
# recover and open resetlogs
recover clone database tablespace "INTRA", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 8 online

sql statement: alter database datafile 9 online

Starting recover at 22-MAR-2018 05:05:19
using channel ORA_AUX_DISK_1

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 for thread 1 with sequence 44 is already on disk as file /u01/app/oracle/fast_recovery_area/LIGHT/archivelog/2018_03_22/o1_mf_1_44_fc5kj9vr_.arc
archived log for thread 1 with sequence 45 is already on disk as file /u01/app/oracle/fast_recovery_area/LIGHT/archivelog/2018_03_22/o1_mf_1_45_fc5lckpf_.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
archived log file name=/u01/app/oracle/fast_recovery_area/LIGHT/archivelog/2018_03_22/o1_mf_1_44_fc5kj9vr_.arc thread=1 sequence=44
archived log file name=/u01/app/oracle/fast_recovery_area/LIGHT/archivelog/2018_03_22/o1_mf_1_45_fc5lckpf_.arc thread=1 sequence=45
media recovery complete, elapsed time: 00:00:02
Finished recover at 22-MAR-2018 05:05:22

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace INTRA read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/new_location_temp/''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/new_location_temp/''";
}
executing Memory Script

sql statement: alter tablespace INTRA read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/new_location_temp/''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/new_location_temp/''

Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_tAlc":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_tAlc" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_tAlc is:
EXPDP> /u01/app/oracle/new_location_temp/tspitr_tAlc_11681.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace INTRA:
EXPDP> /u01/app/oracle/oradata/LIGHT/intra01.dbf
EXPDP> /u01/app/oracle/oradata/LIGHT/intra02.dbf
EXPDP> Job "SYS"."TSPITR_EXP_tAlc" successfully completed at Thu Mar 22 05:06:04 2018 elapsed 0 00:00:29
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace INTRA including contents keep datafiles cascade constraints';
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

sql statement: drop tablespace INTRA including contents keep datafiles cascade constraints

Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_tAlc" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_tAlc":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_tAlc" successfully completed at Thu Mar 22 05:06:27 2018 elapsed 0 00:00:03
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace INTRA read write';
sql 'alter tablespace INTRA offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace INTRA read write

sql statement: alter tablespace INTRA offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/new_location_temp/LIGHT/datafile/o1_mf_temp_fc5v1zgc_.tmp deleted
auxiliary instance file /u01/app/oracle/new_location_temp/LIGHT/onlinelog/o1_mf_3_fc5v1xt4_.log deleted
auxiliary instance file /u01/app/oracle/new_location_temp/LIGHT/onlinelog/o1_mf_2_fc5v1wpf_.log deleted
auxiliary instance file /u01/app/oracle/new_location_temp/LIGHT/onlinelog/o1_mf_1_fc5v1v7m_.log deleted
auxiliary instance file /u01/app/oracle/new_location_temp/LIGHT/datafile/o1_mf_sysaux_fc5tyjn9_.dbf deleted
auxiliary instance file /u01/app/oracle/new_location_temp/LIGHT/datafile/o1_mf_undotbs1_fc5tyfcf_.dbf deleted
auxiliary instance file /u01/app/oracle/new_location_temp/LIGHT/datafile/o1_mf_system_fc5tzok4_.dbf deleted
auxiliary instance file /u01/app/oracle/new_location_temp/LIGHT/controlfile/o1_mf_fc5ty6lc_.ctl deleted
Finished recover at 22-MAR-2018 05:06:29

RMAN> exit

Recovery Manager complete.
[oracle@servera new_location_temp]$

5. Bring the tablespace and datafile Online.

05:07:22 SYS@LIGHT>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
HELLO ONLINE
LILLO ONLINE
INTRA OFFLINE

8 rows selected.

05:11:09 SYS@LIGHT>select FILE#,NAME,BYTES/1024/1024,STATUS from v$datafile;

FILE# NAME BYTES/1024/1024 STATUS
---------- ------------------------------------------------- --------------- -------
1 /u01/app/oracle/oradata/LIGHT/system01.dbf 740 SYSTEM
2 /u01/app/oracle/oradata/LIGHT/sysaux01.dbf 500 ONLINE
3 /u01/app/oracle/oradata/LIGHT/undotbs01.dbf 30 ONLINE
4 /u01/app/oracle/oradata/LIGHT/users01.dbf 5 ONLINE
5 /u01/app/oracle/oradata/LIGHT/hello01.dbf 30 ONLINE
6 /u01/app/oracle/oradata/LIGHT/lillo01.dbf 26 ONLINE
7 /u01/app/oracle/oradata/LIGHT/lillo02.dbf 10 ONLINE
8 /u01/app/oracle/oradata/LIGHT/intra02.dbf 0 OFFLINE
9 /u01/app/oracle/oradata/LIGHT/intra01.dbf 0 OFFLINE

9 rows selected.

05:11:10 SYS@LIGHT>alter tablespace INTRA online;

Tablespace altered.

05:11:26 SYS@LIGHT>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
HELLO ONLINE
LILLO ONLINE
INTRA ONLINE

8 rows selected.

05:11:30 SYS@LIGHT>select FILE#,NAME,BYTES/1024/1024,STATUS from v$datafile;

FILE# NAME BYTES/1024/1024 STATUS
---------- ------------------------------------------------- --------------- -------
1 /u01/app/oracle/oradata/LIGHT/system01.dbf 740 SYSTEM
2 /u01/app/oracle/oradata/LIGHT/sysaux01.dbf 500 ONLINE
3 /u01/app/oracle/oradata/LIGHT/undotbs01.dbf 30 ONLINE
4 /u01/app/oracle/oradata/LIGHT/users01.dbf 5 ONLINE
5 /u01/app/oracle/oradata/LIGHT/hello01.dbf 30 ONLINE
6 /u01/app/oracle/oradata/LIGHT/lillo01.dbf 26 ONLINE
7 /u01/app/oracle/oradata/LIGHT/lillo02.dbf 10 ONLINE
8 /u01/app/oracle/oradata/LIGHT/intra02.dbf 25 ONLINE
9 /u01/app/oracle/oradata/LIGHT/intra01.dbf 20 ONLINE

9 rows selected.

05:11:34 SYS@LIGHT>select table_name from dba_tables where tablespace_name='INTRA';

TABLE_NAME
------------------------------
TAB1

6. 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 servera.oracle.com 22-MAR-2018 02:08:49

SUM(BYTES)/1024/1024/1024
-------------------------
1.34375

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
HELLO ONLINE
LILLO ONLINE
INTRA ONLINE

8 rows selected.

FILE# NAME BYTES/1024/1024 STATUS
---------- ------------------------------------------------- --------------- -------
1 /u01/app/oracle/oradata/LIGHT/system01.dbf 740 SYSTEM
2 /u01/app/oracle/oradata/LIGHT/sysaux01.dbf 500 ONLINE
3 /u01/app/oracle/oradata/LIGHT/undotbs01.dbf 30 ONLINE
4 /u01/app/oracle/oradata/LIGHT/users01.dbf 5 ONLINE
5 /u01/app/oracle/oradata/LIGHT/hello01.dbf 30 ONLINE
6 /u01/app/oracle/oradata/LIGHT/lillo01.dbf 26 ONLINE
7 /u01/app/oracle/oradata/LIGHT/lillo02.dbf 10 ONLINE
8 /u01/app/oracle/oradata/LIGHT/intra02.dbf 25 ONLINE
9 /u01/app/oracle/oradata/LIGHT/intra01.dbf 20 ONLINE

9 rows selected.

05:11: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.

--------------------------------------- END ---------------------------------------