Home » SQL & PL/SQL » SQL & PL/SQL » Getting the SCN as a variable (Oracle Enterprise RAC, 10.2.0, AIX 5.3)
Getting the SCN as a variable [message #276325] Wed, 24 October 2007 20:36 Go to next message
seethem
Messages: 41
Registered: September 2007
Member
Background Info
====================

* We are running an Oracle RAC 10g
* 2 nodes
* AIX 5.3
* The RMAN client invokes a backup from a BACKUP BLADE.
* Part of the script, executes a "create restore point command"


Question
==============
* I want to ask sqlplus from this client server to pass the current SCN number as a parameter that is associated with this restore point.
* I want to run an export of a schema when I perform the RMAN backup.
* I want to pass this SCN variable to the FLASHBACK_SCN flag that either EXP or EXPDP uses.


I truly hope I was lucid in this explanation, can you please let me know if any area requires more explanation.

Thanks for your time and consideration...
Seethem
Re: Getting the SCN as a variable [message #276329 is a reply to message #276325] Wed, 24 October 2007 21:24 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>* I want to ask sqlplus from this client server to pass the current SCN number as a parameter that is associated with this restore point.

Sounds reasonable. Please proceed to do so.

>* I want to run an export of a schema when I perform the RMAN backup.

Sounds reasonable. Please proceed to do so.

>* I want to pass this SCN variable to the FLASHBACK_SCN flag that either EXP or EXPDP uses.

Sounds reasonable. Please proceed to do so.
Re: Getting the SCN as a variable [message #276333 is a reply to message #276329] Wed, 24 October 2007 21:50 Go to previous messageGo to next message
seethem
Messages: 41
Registered: September 2007
Member
So after you spent all your time heckling a newbie...

What say you?

Do you even know how to do it?
Re: Getting the SCN as a variable [message #276336 is a reply to message #276325] Wed, 24 October 2007 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>What say you?

I said the plan was a reasonable one; unlike many "problems/solutions" which are posted here.

>Do you even know how to do it?
I do know how to do it.
We believe in using both belts & suspenders (aka braces).
I have two different scripts which ensure archived redo log files are being successfully applied to standby databases;
both scripts validate status based upon SCN values.

It is not exactly rocket science.

I hope the following SQL will provide you a clue; since it appear you are desperate for one.

select table_name, column_name from dba_tab_columns where column_name like '%SCN%'

Re: Getting the SCN as a variable [message #276369 is a reply to message #276325] Thu, 25 October 2007 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1525228

1 row selected.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1525229

1 row selected.

Regards
Michel
Re: Getting the SCN as a variable [message #276902 is a reply to message #276369] Fri, 26 October 2007 13:12 Go to previous messageGo to next message
seethem
Messages: 41
Registered: September 2007
Member
So how do I retrieve that SCN number back into a SQL Script?

I want to do the following:

sqlplus -s $user/$pass <<EOF
select current_scn from v$database;
exit
EOF

expdp $user/$pass@$db flashback_scn=$current_scn full=y


How do I extract that current_scn?


Thanks in advance,
Phillip
Re: Getting the SCN as a variable [message #276904 is a reply to message #276325] Fri, 26 October 2007 13:17 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>How do I extract that current_scn?
This question really has NOTHING to do with Oracle.
Solutions for this situation are taught in *nix Shell Scripting 101 class.
One approach would be to SPOOL results to a file & the use the contents of the file containing the SCN value.

The standing joke if that when you have 3 *nix Aces in a room & gave them this problem, you'd have at least 5 different solutions within 5 minutes.
Previous Topic: I want to insert only integer values in table (merged)
Next Topic: which "AND" get priority when statement is executed?
Goto Forum:
  


Current Time: Sat Dec 03 22:09:46 CST 2016

Total time taken to generate the page: 0.07864 seconds