Table Restore Using CommVault

[Scott Canaan]

Has anyone done a table restore using CommVault? I'm trying to figure out how to do it using the command line interface, as I want to change the table name and the GUI doesn't appear to allow that.

I've created a user, test, with a table, test_table to use for this. I put 10 rows in the table the other day and 10 more today. I want to restore a copy of the table to another table from the other day.

I've created a script, using the usual CommVault stuff at the beginning and ending and added the recover table clause in the middle, but RMAN doesn't like it.

This is what I have so far:


run {

allocate channel notarget1 device type 'sbt_tape'

PARMS 'SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576

ENV=(CV_mmsApiVsn=2,CV_channelPar=notarget1)';

Recover table test.test_table until time = "to_date('11/04/2018 00:00:00','mm/dd/yyyy hh24:mi:ss')"

Auxiliary destination '/oracle/data/TEST'

Remap table 'TEST'.'TEST_TABLE':'TEST_TABLE2';

release channel notarget1;

}

RMAN keeps complaining about 'test'. When I took that out, it complained about 'until'. It keeps telling me there is a syntax error and I can't figure out why. The recover table statement is based on the Oracle RMAN documentation.

Scott Canaan '88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: [phone] | f: [phone]
***@rit.edu<mailto:***@rit.edu> | c: [phone]

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

[Leng Kaing]

Test is an Oracle keyword, especially with "recover x test”. Try renaming the table/schema from test to something else first and see if that helps.

I’m assuming this is 12c, right? Try a backup and table restore from disk first. If that works then you can troubleshoot the commvault bit. To troubleshoot the commvault bit I’d try a backup and restore of archive log.

Once you get the two bits working you can put them together.

Has anyone done a table restore using CommVault? I’m trying to figure out how to do it using the command line interface, as I want to change the table name and the GUI doesn’t appear to allow that.
I’ve created a user, test, with a table, test_table to use for this. I put 10 rows in the table the other day and 10 more today. I want to restore a copy of the table to another table from the other day.
I’ve created a script, using the usual CommVault stuff at the beginning and ending and added the recover table clause in the middle, but RMAN doesn’t like it.
run {
allocate channel notarget1 device type 'sbt_tape'
PARMS 'SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576
ENV=(CV_mmsApiVsn=2,CV_channelPar=notarget1)';
Recover table test.test_table until time = “to_date(‘11/04/2018 00:00:00’,’mm/dd/yyyy hh24:mi:ss’)”
Auxiliary destination ‘/oracle/data/TEST’
Remap table ‘TEST’.’TEST_TABLE’:’TEST_TABLE2’;
release channel notarget1;
}
RMAN keeps complaining about ‘test’. When I took that out, it complained about ‘until’. It keeps telling me there is a syntax error and I can’t figure out why. The recover table statement is based on the Oracle RMAN documentation.
Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: [phone] | f: [phone]
CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

[Mladen Gogala]

Hi Scott,

You need to put the word "test" in double quotes. Here is a version that
will work:

run {

allocate channel notarget1 device type 'sbt_tape'

 PARMS 'SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
Recover table "TEST"."TEST_TABLE" until time  = “to_date(‘11/04/2018
00:00:00’,’mm/dd/yyyy hh24:mi:ss’)”

Auxiliary destination ‘/oracle/data/TEST’

Remap table ‘TEST’.’TEST_TABLE’:’TEST_TABLE2’;

release  channel notarget1;

}

BTW, for this to work, your database version must be 12.1 or higher.

Regards

Has anyone done a table restore using CommVault?  I’m trying to figure
out how to do it using the command line interface, as I want to change
the table name and the GUI doesn’t appear to allow that.
I’ve created a user, test, with a table, test_table to use for this. 
I put 10 rows in the table the other day and 10 more today.  I want to
restore a copy of the table to another table from the other day.
I’ve created a script, using the usual CommVault stuff at the
beginning and ending and added the recover table clause in the middle,
but RMAN doesn’t like it.
run {
allocate channel notarget1 device type 'sbt_tape'
 PARMS 'SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576
ENV=(CV_mmsApiVsn=2,CV_channelPar=notarget1)';
Recover table test.test_table until time  = “to_date(‘11/04/2018
00:00:00’,’mm/dd/yyyy hh24:mi:ss’)”
Auxiliary destination ‘/oracle/data/TEST’
Remap table ‘TEST’.’TEST_TABLE’:’TEST_TABLE2’;
release channel notarget1;
}
RMAN keeps complaining about ‘test’.  When I took that out, it
complained about ‘until’.  It keeps telling me there is a syntax error
and I can’t figure out why.  The recover table statement is based on
the Oracle RMAN documentation.
*Scott Canaan ‘88**
*/Sr Database Administrator
/Information & Technology Services
Finance & Administration **
*Rochester Institute of Technology
*o: [phone] | f: [phone]**
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it
is addressed and may contain confidential and/or privileged material.
Any review, retransmission, dissemination or other use of, or taking
of any action in reliance upon this information by persons or entities
other than the intended recipient is prohibited. If you received this
in error, please contact the sender and destroy any copies of this
information.