Oracle backups and consistent datafiles

I saw this whilst trawling through the web today, looking for info on Oracle database;

"It is good practice to perform an orderly shutdown (NORMAL, TRANSACTIONAL, or IMMEDIATE) before performing the cold backup. However, you will still obtain a consistent cold backup if you perform a SHUTDOWN ABORT. And I have had no issues restoring from such a backup in the past. But just to be safe, I always recommend an orderly shutdown.

I have had some instances that take a very long time for an orderly shutdown. For those cases, I perform a SHUTDOWN ABORT, STARTUP RESTRICT and then a SHUTDOWN IMMEDIATE. This way, I can ensure an orderly shutdown has been performed, and the instance terminates much more quickly than if I did not do the SHUTDOWN ABORT."

The red italics are mine and I completely disagree with that part of the author's comment.  If the database was open to users, then a shutdown abort (in general) does NOT leave the datafiles in a consistent state (that's why it's so fast to abort the instance). 

The author then "had no issues restoring from such a backup in the past."  That's because he must have copied the online redo logs as well as the datafiles into the backup.  If the datafiles and online redo logs are then restored from the backup, starting up the instance will initiate auto-recovery using the restored online redo logs.  If there are enough redo log entries in the online logs to fully recover the datafiles, then the database will open.  So the fact that the database opens doesn't mean that the datafiles in the backup were consistent, only that Oracle had silently recovered the database during the startup process.  If the online redo logs were not restored from backup, then the datafiles would remain inconsistent and "alter database open" will fail.  (Unless you are very lucky)


Complicating this is Oracle's recommendation never to copy online redo logs as part of a backup strategy.  This is because, if you are performing an online backup (where the database is open to users throughout the duration of the backup), then you're supposed to backup the archived redo log files (along with the datafiles of course).  When properly done, all the redo generated during the time required to copy the datafiles are dumped into the archived logs, so that's why you don't copy the online logs.

But if you're performing a cold backup, there's no harm in backing up the lot. Just remember that if the database is in archivelog mode, and you want to use that backup (cold or hot) later on, say to recover from media failure holding the database files, then do not accidentally restore the online redo logs from the backup on top of (overwriting) your current, live online redo logs.  If you do that, you will likely lose the only copy of the latest redo data that you need to perform a full recovery all the way up to the point of failure.  In fact, many dba's cite this as yet another good reason not to copy the online redo logs during a backup.  Furthermore, if your cold backup is consistent (because the database was shutdown immediate, normal or transactional), the online redo logs can be recreated after restoring the database and opening it with reset logs.  So again, you do not need to copy the online redologs during backup.

Like the author, I used to cold-backup dev/test databases running in noarchivelog mode as,

Script 1
shutdown abort
startup
shutdown immediate
backup

But is it really necessary to restart the database and then shut it down with immediate?  Put it another way, is it really necessary to get a consistent backup of the datafiles in order to be able to restore and recover the backup later?  No, it isn't.  Consider: If the "startup" in script 1 always suceeds, then that proves that you can always recover from the shutdown abort.  So just backup the datafiles and redo logs straight away after the abort.  Later, if the backup is restored by the dba, all files will be in the state they were in after the abort.  Will the dba's startup work?  Yes, just as it did when the script ran.  In other words, the more you believe script 1 works, the more evidence you have that it can be replaced.  All you need to do is "shutdown abort" and copy all the files (with the redo logs).   But knowing dba's, they'll always do it the "safest" way, ie with script 1.

Is there ever a chance that an aborted, noarchivelog mode database, will fail to startup after an abort?  I've never seen it happen, but of course that doesn't mean it won't.

Apps Initialize in R12

In R12, the old fnd_client_info.set_org_context  no longer seems sufficient to query data from apps views:

SQL> show user
USER is "APPS"




SQL> select fnd_profile.value('ORG_ID') from dual;

FND_PROFILE.VALUE('ORG_ID')
--------------------------------------------------------------------------------
85

SQL> select USERENV ('CLIENT_INFO') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------



SQL> exec fnd_client_info.set_org_context(85);

PL/SQL procedure successfully completed.

SQL> select USERENV ('CLIENT_INFO') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
85

SQL>  select count(*) from ra_customer_trx_partial_v;

  COUNT(*)
----------
         0  <-- We know there are plenty of rows!


SQL> exit


One needs to run  fnd_global.apps_initialize and   mo_global.init  as follows ...

sqlplus as apps again and ...

Get parameters for apps security ...

SQL> select user_id,responsibility_id,responsibility_application_id, security_group_id
from fnd_user_resp_groups
where user_id = (

    select user_id from fnd_user where user_name = 'ROBERT')
    and responsibility_id = (select responsibility_id from         fnd_responsibility_vl where responsibility_name =
    'Purchasing Super User')

;


USER_ID RESPONSIBILITY_ID RESPONSIBILITY_APPLICATION_ID SECURITY_GROUP_ID
---------- ----------------- ----------------------------- -----------------
     11787             20707                           201                 0

SQL> exec fnd_global.apps_initialize(11787,20707,201);

PL/SQL procedure successfully completed.

SQL> exec mo_global.init ('PO');

PL/SQL procedure successfully completed.

SQL> select USERENV ('CLIENT_INFO') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
85                                                    0

 
And test again ...


SQL> select count(*) from ra_customer_trx_partial_v;

  COUNT(*)
----------
     14857

Logging in as someone else with proxy users

Example of proxy users in Oracle 10g (and probably 9i), where one can login to a different database account without knowing the password to the other account:

SQL> show user
USER is "SYSTEM"
SQL> create user lm1 identified by pass1;

User created.

SQL> create user lm2 identified by pass2;

User created.

SQL> grant connect to lm1, lm2;

Grant succeeded.

SQL> alter user lm2 grant connect through lm1;

User altered.

SQL> connect lm1/pass1
Connected.
SQL> show user
USER is "LM1"
SQL> connect lm1[lm2]/pass1
Connected.
SQL> show user
USER is "LM2"  

... so lm1 was able to connect to lm2 without using the password pass2 for lm2.

This superceeds the "alter user identified by values" method in older releases (which is still valid).

To reverse,

SQL> alter user lm2 revoke connect through lm1;

User altered.

SQL> conn lm1/pass1
Connected.
SQL> show user
USER is "LM1"
SQL> connect lm1[lm2]/pass1
ERROR:
ORA-28150: proxy not authorized to connect as client
Warning: You are no longer connected to ORACLE.

Multi-column partitioning

Simple example of table with more than 1 partition column.

create table xp (a int, b int, c int, data varchar2(20))
partition by range (a,b)(
  partition part1 values less than (1,1),
  partition part2 values less than (2,2),
  partition part3 values less than (3,3),
  partition part4 values less than (MAXVALUE,MAXVALUE)

);

Check:

select * from user_part_key_columns;

NAME                           OBJEC COLUMN_NAME     COLUMN_POSITION
------------------------------ ----- --------------- ---------------
XP                             TABLE A                             1
XP                             TABLE B                             2

select partition_name, high_value, partition_position
from user_tab_partitions
where table_name='XP';


PARTITION_NAME                 HIGH_VALUE           PARTITION_POSITION
------------------------------ -------------------- ------------------
PART1                          1, 1                                  1
PART2                          2, 2                                  2
PART3                          3, 3                                  3
PART4                          MAXVALUE, MAXVALUE                    4


Now insert some values:

create or replace procedure popxp
(imax in int, jmax in int)
is
begin
for i in 0..imax loop
  for j in 0..jmax loop
    insert into xp values (i, j, null, null);
  end loop;
end loop;
end;

/

Run it:

exec popxp(3,3);

And look at the values by partition:
select * from xp partition (part1) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         0          0
         0          1
         0          2
         0          3
         1          0


select * from xp partition (part2) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         1          1
         1          2
         1          3
         2          0
         2          1

select * from xp partition (part3) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         2          2
         2          3
         3          0
         3          1
         3          2

select * from xp partition (part4) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         3          3

So in determining the partition for a row, the value of the first column over-rides that of the 2nd column.   For e.g. (0,3) lies in PART1 because a=0 satisfies the condition a<1 for PART1.  Although b=3 violates the condition b<1, this fact is irrelevant.  

The 2nd column is only considered when the value of the 1st column lies on a partition boundary.  For e.g., (2,0), (2,1), (2,2) and (2,3) all lie on partition boundaries for column "a" of PART2.  For (2,0) and (2,1) the "b" values are the deciding factor in placing these rows in PART2.

For (2,2) "b" is on the boundary for PART2, so "a" again determines the partition for the row.  For (2,3) "b" is now a boundary value for PART3, and since a=2 satisfies the condition for PART3, the row goes there (regardless of "b").


In summary,

Row		PART1	->	PART2	->	PART3	->	PART4
(2,0)		a is too big, so go to next partition.		a is boundary.  So consider b. b=0 satisfies condition for PART2, so put the row there.		-		-
(2,1)		a is too big, so go to next partition.		a is boundary.  So consider b. b=1 satisfies condition for PART2, so put the row here.		-		-
(2,2)		a is too big, so go to next partition.		Both a and b lie on boundaries.  So a dominates again.  Since a=2 is too big for PART2, go to the next partition.		a=2 satisfies condition for PART3 so put the row here.		-
(2,3)		a is too big, so go to next partition.		b is too big for this partition.  So a dominates again.  Since a=2 is too big for PART2, go to the next partition.		a=2 satisfies condition for PART3 so put the row here.		-

Multi-table insert first

Example

user1@orcl92> select * from tt;

         X          Y Z
---------- ---------- --------------------
         1        100 aa
         2        200 bb
         3        300 cc
         4        400 dd


user1@orcl92> select * from tt1, tt2;

no rows selected

user1@orcl92> insert first
when x=3 then into tt1
when x>0 then into tt2
select * from tt;

4 rows created.

user1@orcl92> select * from tt1;

         X          Y Z
---------- ---------- --------------------
         3        300 cc

user1@orcl92> select * from tt2;

         X          Y Z
---------- ---------- --------------------
         1        100 aa
         2        200 bb
         4        400 dd   <-- No X=3 row.


The row with x=3 is missing from tt1 because it is excluded during the evaluation of the second "where" clause due to this row satisfying the first clause "x=3".

OSB 10.3.1 and xulrunner 1.9

After Oracle bought BEA Systems in January 2008, BEA's Weblogic has been incorporated into Oracle Service Bus.  I tried to run OSB 10.3.1 on Oracle Enterprise Linux 5 (32 bit) and quickly ran into problems with xulrunner.

Symptoms:
Run $BEA_HOME/wlserver_10.3/common/bin/quickstart.sh.
Click on "Launch Oracle Workshop for Weblogic".
Get: pop up message with "JVM terminated. Exit code 1"  etc.

According to the stack dump, the problem is with Eclipse on xulrunnner 1.9.

Solution:
Download xulrunner 1.8 e.g. from, http://releases.mozilla.org/pub/mozilla.org/xulrunner/releases/1.8.1.3/contrib/linux-i686

Unzip xulrunner-1.8.1.3.en-US.linux-i686-20080128.tar.gz into /usr/lib.  Note that the current version is in /usr/lib/xulrunner-1.9

This creates a directory called "xulrunner".  Rename this to "xulrunner-1.8".

In $BEA_HOME/workshop_10.3/workshop.ini, add this line (or modify it if it's already there):
-Dorg.eclipse.swt.browser.XULRunnerPath=/usr/lib/xulrunner-1.8

Click again on "Launch Oracle Workshop for Weblogic" from Quicklaunch and the "Workshop for Weblogic 10gR3" should pop up. 

Verify that all other products requiring xulrunner 1.9 (e.g. firefox 3) still work.

Note: There is an initialization file for eclipse in $BEA_HOME/osb/tools/eclipse_pkgs/2.0/eclipse_3.3.2/eclipse/eclipse.ini

However, adding -Dorg.eclipse.swt.browser.XULRunnerPath=/usr/lib/xulrunner-1.8 to eclipse.ini made no difference.  The stack dump in $BEA_HOME/utils/quickstart/jrockit.xxxx.dump clearly shows the 1.9 version of xulrunner among all the other Eclipse startup parameters.

Oracle Support were very responsive on this issue, though it was the workshop.ini update that finally fixed the problem.

Certification of E-Business Suite on virtual Windows guest

Anyone noticed this in Metalink?

Note 464754.1 has "Oracle E-Business Suite Release 12 (12.0.4 or higher and 12.1.1 or higher) is certified on Windows Server 2003 (32-bit) running as a guest on Oracle VM using Windows PV driver version 1.0.8 or higher".

Note 465915.1 says "Windows guest OS'es in Paravirtualized or Hardware Virtualized environments are not currently certified with Oracle E-Business Suite".

Which is true?  Also does anyone have any experiences to share for R11i on virtualized Windows guest OS?

role privileges, plsql and invokers rights

Some observations on role privileges, plsql and invokers rights
===============================================================
Oracle 9.2.0.4

As tuser1, create table t as select * from dba_data_files; to test privileges accessing this table.

sys@orcl92> create user tuser2 identified by tuser2 default tablespace users temporary tablespace temp;

User created.

sys@orcl92> grant connect, resource to tuser2;

Grant succeeded.

sys@orcl92> @conn tuser2/tuser2
Connected.

tuser2@orcl92> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE

tuser2@orcl92> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TUSER2                         CONNECT                        NO  YES NO
TUSER2                         RESOURCE                       NO  YES NO

tuser2 cannot select from tuser1.t:

tuser2@orcl92> select count(*) from tuser1.t;
select count(*) from tuser1.t
                            *
ERROR at line 1:
ORA-00942: table or view does not exist


Now create a package-protected secure application role as tuser1:

tuser1@orcl92> !cat sar-cr.sql
@conn tuser1/tuser1

drop role sar1;

create role sar1 identified using tuser1.saradmin;

grant select on tuser1.t to sar1;

create or replace package saradmin
authid current_user
as
procedure letmein;
end;
/

create or replace package body saradmin
as
procedure letmein
is
  begin
    dbms_session.set_role('sar1');
  end;
end;
/

tuser1@orcl92> @sar-cr
Connected.

Role dropped.


Role created.


Grant succeeded.


Package created.


Package body created.


Now grant the role sar1 to tuser2:
tuser1@orcl92> grant sar1 to tuser2;

Grant succeeded.

####  At this stage, tuser2 gets the role as a default role, meaning it is automatically set upon login.  So logging off and loging in again gives the privilege WITHOUT EXECUTING THE PACKAGE:

tuser2@orcl92> select count(*) from tuser1.t;
select count(*) from tuser1.t
                            *
ERROR at line 1:
ORA-00942: table or view does not exist

tuser2@orcl92> @conn tuser1/tuser1
Connected.

tuser1@orcl92> select count(*) from tuser1.t;

  COUNT(*)
----------
        11

... which defeats the purpose of a security procedure that can check the user's credentials. 

To get around this, just disable the role after it is granted:

sys@orcl92> alter user tuser2 default role all except sar1;

User altered.

If tuser2 IS STILL LOGGED IN, THE PRIVILEGE IS STILL ENABLED:

tuser2@orcl92> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TUSER2                         CONNECT                        NO  YES NO
TUSER2                         RESOURCE                       NO  YES NO
TUSER2                         SAR1                           NO  NO  NO  <- NOT DEFAULT ANYMORE

tuser2@orcl92> select count(*) from tuser1.t;

  COUNT(*)
----------
        11

That's because default roles are enabled/disabled UPON LOGIN:

tuser2@orcl92> @conn tuser2/tuser2
Connected.

tuser2@orcl92> select count(*) from tuser1.t;
select count(*) from tuser1.t
                            *
ERROR at line 1:
ORA-00942: table or view does not exist

=> So remember to disable a protected role after granting it.  Note that " ... default role all except ..." may enable OTHER roles that the tuser2 is not supposed to have as default.


tuser1@orcl92> grant execute on saradmin to tuser2;

Grant succeeded.

tuser2@orcl92> execute tuser1.saradmin.letmein;

PL/SQL procedure successfully completed.

(Without the execute grant on saradmin, tuser2 cannot run letmein)

Finally, the table is accessible from sqlplus:
tuser2@orcl92> select count(*) from tuser1.t;

  COUNT(*)
----------
        11


But from PLSQL, the table is NOT accessible:
tuser2@orcl92> create or replace procedure sar1test
  2  is
  3  x int;
  4  begin
  5    select count(*) into x from tuser1.t;
  6    --execute immediate 'select count(*) from dual';
  7  end;
  8
  9  /

Warning: Procedure created with compilation errors.

tuser2@orcl92> show error
Errors for PROCEDURE SAR1TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
5/38     PL/SQL: ORA-00942: table or view does not exist


But this works:
tuser2@orcl92> ed
Wrote file afiedt.buf

  1  create or replace procedure sar1test
  2  is
  3  x int;
  4  begin
  5    --select count(*) into x from tuser1.t;
  6    execute immediate 'select count(*) from tuser1.t';
  7* end;
  8  /

Procedure created.

... but it does not run ...

tuser2@orcl92> exec sar1test
BEGIN sar1test; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "TUSER2.SAR1TEST", line 6
ORA-06512: at line 1



Using AUTHID CURRENT_USER does help:

  1  create or replace procedure sar1test
  2  authid current_user
  3  is
  4  x int;
  5  begin
  6    execute immediate 'select count(*) from tuser1.t';
  7* end;

tuser2@orcl92> /

Procedure created.

tuser2@orcl92> exec sar1test;

PL/SQL procedure successfully completed.

However, using AUTHID CURRENT_USER does not help if the execute immediate is replaced by select ... into:

  1  create or replace procedure sar1test
  2  authid current_user
  3  is
  4  x int;
  5  begin
  6    select count(*) into x from tuser1.t;
  7    --execute immediate 'select count(*) from tuser1.t';
  8* end;
tuser2@orcl92> /

Warning: Procedure created with compilation errors.

tuser2@orcl92> show errors
Errors for PROCEDURE SAR1TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3      PL/SQL: SQL Statement ignored
6/38     PL/SQL: ORA-00942: table or view does not exist

So here we are back to the ORA-942.




We know that "Within an invoker-rights routine, roles are enabled ..."

What does this mean?

It does NOT mean that, using authid current_user, tuser2 can run it's own procedure and reference tuser1's table using a privilege granted through a role (except for the execute immediate case).  Trying this, tuser2 would not even be able to compile the procedure, as shown above.

What it DOES mean is that tuser2 can run a procedure owned by tuser1 with privileges from a role granted by tuser1 to tuser2:


tuser1@orcl92>

  1  create or replace procedure sar1test
  2  authid current_user
  3  is
  4  x int;
  5  begin
  6    select count(*) into x from tuser1.t;
  7    --execute immediate 'select count(*) from tuser1.t';
  8    dbms_output.put_line(x);
  9* end;
tuser1@orcl92> /

Procedure created.


tuser2@orcl92> exec tuser1.sar1test
11

PL/SQL procedure successfully completed.


However, this means tuser2 is running a procedure that is selecting from a table in the procedures' schema (tuser1).  So then, definers' rights will do just as well!  Just remove authid current_user and tuser2 can execute tuser1.sar1test successfully.

The real use of authid current_user would be when the stored procedure references a table in the invokers' schema, for which no explicit grant is required because both code and table are in the same (invoking) schema.  In this way, a "code-base" in tuser1 can be built up, which during run-time, references objects (e.g. tables) in the invokers' schema.  In addition, role-based privileges can also be used to reference objects in the definers' schema as shown above.

So,
temporarily grant direct privilege for tuser2 to create a duplicate of table t:

tuser1@orcl92> grant select on t to tuser2;

Grant succeeded.

tuser2@orcl92> create table t as select * from tuser1.t where rownum<5;

Table created.

tuser1@orcl92> revoke select on t from tuser2;

Revoke succeeded.



Redefine the procedure with invokers' rights:

tuser1@orcl92>

  1  create or replace procedure sar1test
  2  authid current_user
  3  is
  4  x int;
  5  y int;
  6  begin
  7    select count(*) into x from t;  -- unqualified, so becomes t in invokers' schema.
  8    select count(*) into y from tuser1.t;
  9    dbms_output.put_line('num lines in invoker''s schema: '||x);
 10    dbms_output.put_line('num lines in  tuser1''s schema: '||y);
 11* end;

tuser1@orcl92> /

Procedure created.

Connect as tuser2, set role and run the proc:

tuser2@orcl92> exec tuser1.saradmin.letmein

PL/SQL procedure successfully completed.

tuser2@orcl92>  select * from session_roles;

ROLE
------------------------------
SAR1

tuser2@orcl92> exec tuser1.sar1test;
num lines in invoker's schema: 4  <-- from invokers' schema tuser2.
num lines in  tuser1's schema: 11 <-- from fixed schema tuser1.

PL/SQL procedure successfully completed.

Just to be sure, verify that removing authid means the unqualified schema becomes tuser2:

tuser1@orcl92> ed
Wrote file afiedt.buf

  1  create or replace procedure sar1test
  2  -- authid current_user
  3  is
  4  x int;
  5  y int;
  6  begin
  7    select count(*) into x from t;
  8    select count(*) into y from tuser1.t;
  9    dbms_output.put_line('num lines in invoker''s schema: '||x);
 10    dbms_output.put_line('num lines in  tuser1''s schema: '||y);
 11* end;
tuser1@orcl92> /

Procedure created.

tuser2@orcl92> exec tuser1.sar1test;
num lines in invoker's schema: 11
num lines in  tuser1's schema: 11

PL/SQL procedure successfully completed.


To summarize, this shows the use of invokers' rights in a stored procedure to access 2 schemas using role privileges, instead of directly granted privileges.