Loading...

Follow Laurent Schneider – Oracle Certified Master on Feedspot

Continue with Google
Continue with Facebook
or

Valid

A common frustration with standby is that your database is doing nothing else than applying logs. One may want to run some reports on it.

Usually, the database is MOUNTED and not OPEN. This means, apart from selecting from DUAL and performance views like v$$managed_standby or v$session, there is little you can do.

Possibly, you can cancel the recovery and open in read only mode.
SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
or
Dataguard edit database db01_sb set state='APPLY-OFF'

Now we can open the database

SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
---------------
READ ONLY

Now we can query the database

SQL> select count(*) from dba_objects;

COUNT(*)
----------
22783

but not write

SQL> create table t(x number);
create table t(x number)
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

if you need to write, there is more than one way to do it. Either logical standby, or snapshot standby, or create an additional database and create database links and synonyms. My preferred option would be golden gate. But this is beyond the scope of this post.

A good option is to open it without stopping the apply process…

DGMGRL> edit database db01_sb set state='APPLY-ON';
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

This good but is bounded to the Active Dataguard licensing option (EE).

There are also a few differences

One is that you cannot compile view on the fly.

Primary

SQL> create or replace force view bar as select * from foo;
Warning: View created with compilation errors.
SQL> create table foo(x number);
Table created.
SQL> select status from user_objects where object_name='BAR';
STATUS
---------------
INVALID

The view is invalid, but a select would compile. But not on standby read only
Standby

SQL> select * from bar;
select * from bar
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of BAR
ORA-16000: database or pluggable database open for read-only access

Primary

SQL> select * from bar;
no rows selected


SQL> select * from bar;
no rows selected

A more worrying issuing is security. On your main system, you have failed login attempts

Primary

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 3;
Profile altered.
SQL> create user u identified by p;
User created.
SQL> conn u/a@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/b@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/c@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/d@db01_prim
ERROR:
ORA-28000: the account is locked
SQL> conn / as sysdba
Connected.
SQL> alter user u account unlock;
User altered.

On the standby, since it is read only, the last tentatives are not recorded.

SQL> conn u/e@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/f@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/g@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/h@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied

which allows you unlimited login attempts

As well, audit records are not generated.
Primary

SQL> audit session;

Standby

SQL> conn u/xxx@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> SELECT USERNAME,ACTION_NAME,TIMESTAMP,RETURNCODE FROM DBA_AUDIT_TRAIL ORDER BY TIMESTAMP DESC;
no rows selected

No audit record from ORA-01017

Primary

SQL> conn u/xxx@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> SELECT USERNAME,ACTION_NAME,TIMESTAMP,RETURNCODE FROM DBA_AUDIT_TRAIL ORDER BY TIMESTAMP DESC
USER ACTION TIMESTAMP RETURNCODE
---- ------ ------------------- ----------
U LOGON 2019-06-14_15:39:05 1017

On primary, audit records are saved as expected. There are many other things/tools that won’t work the same way. Because it is read-only. Use with care if you are entitled too.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

many ignore the difference meaning of .. (dot dot) as an argument when used with ls or cd

this leads to buggy coding and wrong parsing of arguments in scripts

let’s start with an example

$ mkdir $HOME/test $HOME/test/physical $HOME/test/foo 
$ cd $HOME/test/foo
$ ln -s ../physical logical
$ cd logical
$ ls -l ..
total 8
drwxr-xr-x. 2 oracle dba 18:01 foo
drwxr-xr-x. 2 oracle dba 18:01 physical
$ cd ..; ls -l
total 0
lrwxrwxrwx. 1 oracle dba 18:01 logical -> ../physical
$ 

Wait… how could cd ..; ls and ls .. have a different output?

Most programs except cd use the physical path in arguments.

If you are in the physical directory $HOME/test/physical and you issue

program argument

it will behave the same as if you were in the logical path. This is somehow consistent, but confusing

Let’s try

$ cd $HOME/test/physical
$ ls -l ..
total 8
drwxr-xr-x. 2 oracle dba 4096 May 21 18:01 foo
drwxr-xr-x. 2 oracle dba 4096 May 21 18:01 physical
$ cd $HOME/test/foo/logical
$ ls -l ..
total 8
drwxr-xr-x. 2 oracle dba 4096 May 21 18:01 foo
drwxr-xr-x. 2 oracle dba 4096 May 21 18:01 physical
$ 

BINGO! I got the same output

Got it? The arguments are parsed using the physical path.

This does not relate to absolute or relative path. While most bug occurs with relative path, a relative path is neither logical nor physical. It is relative. Whether it relates to a physical or it relates to a logical path is the scope of this post.

Okay, we get now that most programs use the “filename” as if you were in the physical path.

Path Logical Physical
/home/user01/test/physical /home/user01/test/physical /home/user01/test/physical
/home/user01/test/foo/logical /home/user01/test/foo/logical /home/user01/test/physical

In most case, it makes no difference (which render the bugs less evident to trap). For instance browsing paths in dbca doesn’t do it right, but it is just a side note.

If you use /physicalpath/file or /logicalpath/file or ./file, it really doesn’t matter. It’s relevant with symbolic links on directories and relative path to parents.

So for instance if you want to change to the directory of the first argument it would be wrong to do


cd $(dirname $1)

because cd does use logical path and your program should NOT (to make it symlink-independent).

a not-properly documented (missing for instance on aix 7.2 cd manpage) way is to use the -P option

In Linux there is also a -e option which gives you a non-zero error code on non-existent current working directory (if you are in a path that does not exits, the cd -P won’t work but return 0 by default), but for now, just stick to -P

Let’s see

$ pwd
/home/user01/test/foo/logical
$ cd -P ..
$ pwd
/home/user01/test

wait, you change to .. and went two step back? this is not the default behavior of cd. The default behavior is -L


$ pwd
/home/user01/test/foo/logical
$ cd -L ..
$ pwd
/home/user01/test/foo

hmm… is that not easier? No way! this is just fine for cd (where you navigate to parent regarding to the logical working directory). But it is not the way the arguments are interpreted.

Apart from cd , there is another command that deals with symlink path hassle : pwd. Again, it is not really well documented (missing in Solaris 10 pwd manpage), but it has always been there.


$ pwd
/u01/users/oracle/test/foo/logical
$ pwd -L
/u01/users/oracle/test/foo/logical
$ pwd -P
/u01/users/oracle/test/physical
$

next time you use cp, ls, cat with a .. and symlinks, remember this post !

Last note, one may like to try the long option. Don’t!

$ man pwd | grep -- -P
-P, --physical
$ cd -P .
$ cd --physical .
-bash: cd: --: invalid option
cd: usage: cd [-L|-P] [dir]
$

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

There is no privileges strong enough for you to view all objects in all databases

Let’s try

as sys:

SQL> select con_id, count(*) from cdb_objects group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         1      22749
         3      22721

as non-sys

SQL> create user c##u identified by ***;
User created.
SQL> grant create session, select any dictionary to c##u;
Grant succeeded.
SQL> conn c##u/x
Connected.
SQL> select con_id, count(*) from cdb_objects group by con_id;
    CON_ID   COUNT(*)
---------- ----------
         1      22749

You can try to grant and grant and grant, it won’t help

SQL> conn / as sysdba
Connected.
SQL> grant dba, cdb_dba, pdb_dba, all privileges, sysdba to c##u with admin option container=all;
Grant succeeded.
SQL> conn c##u/x
Connected.
SQL> select con_id, count(*) from cdb_objects group by con_id;
    CON_ID   COUNT(*)
---------- ----------
         1      22749

This is not what you are missing…

SQL> revoke dba, cdb_dba, pdb_dba, all privileges, sysdba from c##u container=all;
Revoke succeeded.
SQL> grant create session, select any dictionary to c##u;
Grant succeeded.

you need container data

SQL> alter user c##u set container_data=all container=current;
User altered.
SQL> conn c##u/x
Connected.
SQL> select con_id, count(*) from cdb_objects group by con_id;
    CON_ID   COUNT(*)
---------- ----------
         1      22749
         3      22721

Here you go …

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

One of the today’s challenge, since Oracle 12c deprecated non-cdb, is to make the dba scripts CDB-aware.

If you are lucky enough to have no 11g around, you can mostly replace DBA_* by CDB_*

OLD:

SQL> select count(*) from dba_users;
  COUNT(*)
----------
       121

NEW: non-cdb

SQL> select con_id, count(*) from cdb_users group by con_id;

CON_ID   COUNT(*)
------ ----------
     0        121

NEW: single-tenant

SQL> select con_id, count(*) from cdb_users group by con_id;
    CON_ID   COUNT(*)
---------- ----------
         1         23
         3         39

As mentioned in a white paper :
The set container privilege has certain restrictions in PL/SQL
multitenant-security-concepts-12c-2402462.pdf

Sometimes the certain restrictions will puzzle you

SQL> set feed off serverout on
SQL> exec dbms_output.put_line('root')
root
SQL> alter session set container=dora1;
SQL> sho serverout
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL> exec dbms_output.put_line('dora1');
SQL> -- NO OUTPUT WTF !!!!
SQL> set serveroutput ON 
SQL> exec dbms_output.put_line('dora1');
dora1
SQL> 

The security model prevents you from using alter session (with execute immediate or like in the previous example) to execute plsql.

Now you know…

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Let’s start with some basics. The basics works as well on Unix, Linux and Windows. Later techniques only work on linux/unix

$ ls -l hosts          
-rw-r--r--. 1 root root 211 Oct  5  2015 hosts
$ ls -l xxx  
ls: cannot access xxx: No such file or directory
$ read x
foo
$ 

Outpout and error are displayed on screen and input is read from your keyboard

The output is kwown as file-descriptor-1 or stdout. Sometimes, depending on your OS, it may be exposed as /dev/fd/1 or /dev/stdout. But not all *nix have this.
The error is kwown as file-descriptor-2 or stderr.
The input is known as file-descriptor-0 or stdin.

Instead of keyboard and screen, it could be a file or any other devices, e.g. /dev/null or just a simple file.

$ ls -l hosts 1>file1
$ ls -l xxx 2>file2          
$ read x 0<file3

0 and 1 are optional here.

If is also possible to redirect stdout and vice versa

$ ls -l hosts 1>&2         
-rw-r--r--. 1 root root 211 Oct  5  2015 hosts
$ ls -l xxx 2>&1 
ls: cannot access xxx: No such file or directory

It is possible to close the file descriptor.

sleep 1 1>&- 2>&- 0&-     
bash: echo: write error: Bad file descriptor
$ echo $?        
1
$ (echo bar 1>&2) 2>&-      
$ echo $?             
1

if you want to redirect stdin to stdout and stdout to stdin, you better use a new file descriptor

$ (ls -l hosts xxx 1>&2 2>&3) 3>&1            
xxx not found
-rw-rw-r-- 1 root system 2133 Jun 22 2017 hosts

An old trick is to use additional file descriptor to find a return code of command before the pipe.

$ ((((ls hosts; echo $? >&3) | tr '[a-z]' '[A-Z]' >&4) 3>&1) | (read rc; exit $rc)) 4>&1       
HOSTS
$ echo $?
0
$ ((((ls xxx; echo $? >&3) | tr '[a-z]' '[A-Z]' >&4) 3>&1) | (read rc; exit $rc)) 4>&1        
ls: cannot access xxx: No such file or directory
$ echo $?
2

As I know the trick for so long that I could not credit the author, only found some 21st century posts

If you want to redirect all your outputs to a logfile, you can use exec

#!/bin/ksh
exec 1>>/tmp/mylog
exec 2>>/tmp/mylog
cd /etc 
ls -l hosts          
ls -l xxx
exit

If you want to be able to still use your stdout / stderr, again, open new descriptors

#!/bin/ksh
exec 3>&1
exec 4>&2
exec 1>>/tmp/mylog
exec 2>>/tmp/mylog
cd /etc 
ls -l hosts          
ls -l xxx
echo INFO >&3
echo ERROR >&4
exec 3>&-
exec 4>&-
exit

Bash has also one shortcut

ls xxx host &>log

& redirect both 1 and 2 in one step. Doesn't work on ksh.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
19c
Laurent Schneider – Oracle Certified Mas.. by Laurent Schneider - 4M ago

19c is a mini-release. Remember it is a new name for the second 12cR2 patchset, after 12.2.0.2/18c

https://mikedietrichde.com/2019/02/13/oracle-database-19-2-for-exadata-is-now-available-for-download/ was the first to mention it. By looking up in the doc I found

. Distinct listagg

. Desupport sqlplus product profile

. listener.log log rotation

Go to the doc to find more https://docs.oracle.com/en/database/oracle/oracle-database/19/whats-new.html

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview