Kevin Kempf's Blog

September 3, 2015

More fun with 12c: Online datafile moves

Filed under: 12c — kkempf @ 1:50 pm

A long time coming

I think I’ve been wondering why I couldn’t do this since 9i

SQL> select name, enabled from v$datafile where name like '%vertex%';
NAME                                        ENABLED
--------------------------------------------------------------------------- ----------
/u05/appdev/devdata/vertex01.dbf                        READ WRITE


SQL> alter database move datafile '/u05/appdev/devdata/vertex01.dbf' to '/u06/appdev/devdata/vertex01.dbf';

Database altered.

SQL> select name, enabled from v$datafile where name like '%vertex%';
NAME                                        ENABLED
--------------------------------------------------------------------------- ----------
/u06/appdev/devdata/vertex01.dbf                        READ WRITE


SQL>

Oracle RDBMS 12c table restore

Filed under: 12c, RMAN — kkempf @ 7:37 am

Database 12.1.0.2

So I’m playing with 12c in a development environment with EBS 12.2.3, trying to understand some of the new features.  I don’t use FRA, because as an EBS DBA, there’s not much logic in restoring only one table back, or (worse) flashing the whole ERP back.  That said, this new RMAN feature is pretty cool, and best of all, it requires no effort on my part.

Custom Table

Nothing special here, just a custom schema table (yes, I removed the schema name references from this post so as to “anonymize” it a bit).  I get a row count, the current SCN, and then truncate the table

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 2 16:35:16 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Enter password:
 Last Successful login time: Wed Sep 02 2015 16:34:56 -04:00
Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select count(*) from label_data;
COUNT(*)
 ----------
 232
SQL> column current_scn format 999999999999999
 SQL> select sysdate, current_scn from v$database;
SYSDATE        CURRENT_SCN
 --------- ----------------
 02-SEP-15    5982678044431
SQL> truncate table label_data;
Table truncated.

RMAN Restore/Recover of a Table

Now let's fix it with the new RMAN 12c feature, doing the work in /u05/appdev/restore and renaming the table to restored_label_data.  
The long and short of it is that RMAN does all the work for you.  It brings up a minimal instance, restores the table, then exports it 
into the restored table you specified, then wipes out the minimal instance.  It took awhile, about 30 minutes, but the main database 
was available the whole time.  I've removed some messages to save space, but you get the gist of it.  I suppose in some abstract and 
sampling manner, this also serves to test your RMAN backup to a degree.

RMAN> recover table label_data until scn 5982678044431 auxiliary destination '/u05/appdev/restore' remap table label_data:restored_label_data;
Starting recover at 02-SEP-15
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=425 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 APPS_UNDOTS1
Creating automatic instance, with SID='degz'
initialization parameters used for automatic instance:
 db_name=DEV
 db_unique_name=degz_pitr_DEV
 compatible=12.1.0
 db_block_size=8192
 db_files=1000
 diagnostic_dest=/u01/appdev/oracle/devdb/12.1.0.2/log
 _system_trig_enabled=FALSE
 sga_target=2560M
 processes=200
 db_create_file_dest=/u05/appdev/restore
 p log_archive_dest_1='location=/u05/appdev/restore' #No auxiliary parameter file used
starting up automatic instance DEV
Oracle instance started
Total System Global Area    2684354560 bytes
Fixed Size                     3714440 bytes
 Variable Size                654312056 bytes
 Database Buffers            2013265920 bytes
 Redo Buffers                  13062144 bytes
 Automatic instance created
contents of Memory Script:
 {
 # set requested point in time
 set until  scn 5982678044431;
 # 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';
 }
 executing Memory Script
executing command: SET until clause
Starting restore at 02-SEP-15
 allocated channel: ORA_AUX_DISK_1
 channel ORA_AUX_DISK_1: SID=2 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 /usr/local/oracle/backup/RMAN/DEV/c-3959101112-20150902-01
 channel ORA_AUX_DISK_1: piece handle=/usr/local/oracle/backup/RMAN/DEV/c-3959101112-20150902-01 tag=TAG20150902T012532
 channel ORA_AUX_DISK_1: restored backup piece 1
 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
 output file name=/u05/appdev/restore/DEV/controlfile/o1_mf_bygqo9w1_.ctl Finished restore at 02-SEP-15
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
 {
 # set requested point in time
 set until  scn 5982678044431;
 # set destinations for recovery set and auxiliary set datafiles
 set newname for clone datafile  1 to new;
 set newname for clone datafile  2 to new;
 set newname for clone datafile  3 to new;
 set newname for clone datafile  4 to new;
 set newname for clone datafile  5 to new;
 set newname for clone datafile  295 to new;
 set newname for clone datafile  314 to new;
 set newname for clone datafile  8 to new;
 set newname for clone datafile  11 to new;
 set newname for clone datafile  288 to new;
 set newname for clone datafile  392 to new;
 set newname for clone datafile  20 to new;
 set newname for clone datafile  106 to new;
 set newname for clone tempfile  1 to new;
 set newname for clone tempfile  2 to new;
 set newname for clone tempfile  3 to new;
 set newname for clone tempfile  5 to new;
 # switch all tempfiles
 switch clone tempfile all;
 # restore the tablespaces in the recovery set and the auxiliary set
 restore clone datafile  1, 2, 3, 4, 5, 295, 314, 8, 11, 288, 392, 20, 106;
switch clone datafile all;
 }
 executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u05/appdev/restore/o1_mf_temp_%u_.tmp in control file 
renamed tempfile 2 to /u05/appdev/restore/o1_mf_temp_%u_.tmp in control file 
renamed tempfile 3 to /u05/appdev/restore/o1_mf_temp_%u_.tmp in control file 
renamed tempfile 5 to /u05/appdev/restore/o1_mf_temp_%u_.tmp in control file
Starting restore at 02-SEP-15
 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
...
contents of Memory Script:
{
# set requested point in time
set until  scn 5982678044431;
# online the datafiles restored or switched
sql clone "alter database datafile  354 online";
# recover and open resetlogs
recover clone database tablespace  "DATA", "SYSTEM", "APPS_UNDOTS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  354 online

Starting recover at 02-SEP-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting incremental datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00354: /u05/appdev/restore/TQFR_PITR_DEV/datafile/o1_mf_data_bygtnmkg_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /usr/local/oracle/backup/RMAN/DEV/6sqfujuo_1_1
channel ORA_AUX_DISK_1: piece handle=/usr/local/oracle/backup/RMAN/DEV/6sqfujuo_1_1 tag=BACKUP_DEV_INCR_083115010001
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting incremental datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00354: /u05/appdev/restore/TQFR_PITR_DEV/datafile/o1_mf_data_bygtnmkg_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /usr/local/oracle/backup/RMAN/DEV/93qg197k_1_1
channel ORA_AUX_DISK_1: piece handle=/usr/local/oracle/backup/RMAN/DEV/93qg197k_1_1 tag=BACKUP_DEV_INCR_090115010003
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting incremental datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00354: /u05/appdev/restore/TQFR_PITR_DEV/datafile/o1_mf_data_bygtnmkg_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /usr/local/oracle/backup/RMAN/DEV/baqg3so8_1_1
channel ORA_AUX_DISK_1: piece handle=/usr/local/oracle/backup/RMAN/DEV/baqg3so8_1_1 tag=BACKUP_DEV_INCR_090215010002
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 4156 is already on disk as file /usr/local/oracle/archive/DEV1880762303__4156.log
archived log for thread 1 with sequence 4157 is already on disk as file /usr/local/oracle/archive/DEV1880762303__4157.log
archived log for thread 1 with sequence 4158 is already on disk as file /usr/local/oracle/archive/DEV1880762303__4158.log
archived log for thread 1 with sequence 4159 is already on disk as file /usr/local/oracle/archive/DEV1880762303__4159.log
archived log for thread 1 with sequence 4160 is already on disk as file /usr/local/oracle/archive/DEV1880762303__4160.log
archived log for thread 1 with sequence 4161 is already on disk as file /usr/local/oracle/archive/DEV1880762303__4161.log
archived log file name=/usr/local/oracle/archive/DEV1880762303__4156.log thread=1 sequence=4156
archived log file name=/usr/local/oracle/archive/DEV1880762303__4157.log thread=1 sequence=4157
archived log file name=/usr/local/oracle/archive/DEV1880762303__4158.log thread=1 sequence=4158
archived log file name=/usr/local/oracle/archive/DEV1880762303__4159.log thread=1 sequence=4159
archived log file name=/usr/local/oracle/archive/DEV1880762303__4160.log thread=1 sequence=4160
archived log file name=/usr/local/oracle/archive/DEV1880762303__4161.log thread=1 sequence=4161
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-SEP-15
...
ORA-06512: at "SYS.DBMS_METADATA", line 10261
   EXPDP> . . exported "LABEL_DATA"                    207.4 KB     232 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_tqFr_jxbe" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_tqFr_jxbe is:
   EXPDP>   /u05/appdev/restore/tspitr_tqFr_90139.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_tqFr_jxbe" completed with 10 error(s) at Wed Sep 2 17:35:20 2015 elapsed 0 00:01:31
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_tqFr_Bipd" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_tqFr_Bipd":  
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "RESTORED_LABEL_DATA"           207.4 KB     232 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_tqFr_Bipd" successfully completed at Wed Sep 2 17:36:40 2015 elapsed 0 00:00:48
Import completed

sqlplus apps@dev Enter password: 
Last Successful login time: Thu Sep 03 2015 08:15:09 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count(*) from RESTORED_LABEL_DATA;

  COUNT(*)
----------
       232

SQL>



Blog at WordPress.com.