connect to a pluggable database without password .. 

connect to a pluggable database without password 

This note explains how one can configure SEPS ( Secure External Password Store) to connect to a pluggable database. 

This is useful when creating passwordless connections for expdp. 

on sqlnet.ora

Example ...

WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY =<wallet location directory>)

)

)

SQLNET.WALLET_OVERRIDE = TRUE

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

---- 

WALLET_LOCATION = (SOURCE =(METHOD = FILE) (METHOD_DATA = (DIRECTORY =/u01/app/oracle/wallet)))

SQLNET.WALLET_OVERRIDE = TRUE

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

 mkstore -wrl . -create

Oracle Secret Store Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter password:

Enter password again:

mkstore -wrl "/u01/app/oracle/wallet" -createCredential vihaan anuj vihaan

mkstore -wrl "<wallet location directory>" -createCredential <Pluggable DB service name> <username> <password>

mkstore -wrl "/u01/app/oracle/wallet" -createCredential vihaan anuj vihaan

Oracle Secret Store Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Create credential oracle.security.client.connect_string1

on tnsnames.ora

Example ..

<Pluggable DB service name> =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port#>))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = <Pluggable DB SID>)

)

)

vihaan =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.71)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = vihaan)

      (SERVER = dedicated)

    )

  )

[oracle@ora-prim wallet]$ tnsping vihaan

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 08-DEC-2016 17:40:47

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.71)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vihaan) (SERVER = dedicated)))

OK (10 msec)

[oracle@ora-prim admin]$ sqlplus /@vihaan

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 8 17:41:57 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Dec 08 2016 17:34:26 +00: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

expdp /@vihaan dumpfile=INT1.DMP directory=anujdir logfile=INT1.LOG tables='INTERVAL_SALES' CONSISTENT=Y  ESTIMATE=STATISTICS reuse_dumpfiles=y

Export: Release 12.1.0.2.0 - Production on Thu Dec 8 18:20:00 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

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

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "consistent=TRUE" Location: Command Line, Replaced with: "flashback_time=TO_TIMESTAMP('2016-12-08 18:20:00', 'YYYY-MM-DD HH24:MI:SS')"

Legacy Mode has set reuse_dumpfiles=true parameter.

Starting "ANUJ"."SYS_EXPORT_TABLE_01":  /********@vihaan dumpfile=INT1.DMP directory=anujdir logfile=INT1.LOG tables=INTERVAL_SALES flashback_time=TO_TIMESTAMP('2016-12-08 18:20:00', 'YYYY-MM-DD HH24:MI:SS') ESTIMATE=STATISTICS reuse_dumpfiles=y

Estimate in progress using STATISTICS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

.  estimated "ANUJ"."INTERVAL_SALES":"P0"                4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"P1"                4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"P2"                4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"P3"                4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P261"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P262"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P263"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P264"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P265"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P266"          4.683 KB

.  estimated "ANUJ"."INTERVAL_SALES":"SYS_P267"          4.683 KB

Total estimation using STATISTICS method: 51.51 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported "ANUJ"."INTERVAL_SALES":"P0"                7.773 KB       0 rows

. . exported "ANUJ"."INTERVAL_SALES":"P1"                7.773 KB       0 rows

. . exported "ANUJ"."INTERVAL_SALES":"P2"                7.773 KB       0 rows

. . exported "ANUJ"."INTERVAL_SALES":"P3"                7.773 KB       0 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P261"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P262"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P263"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P264"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P265"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P266"          7.812 KB       1 rows

. . exported "ANUJ"."INTERVAL_SALES":"SYS_P267"          7.812 KB       1 rows

Master table "ANUJ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for ANUJ.SYS_EXPORT_TABLE_01 is:

  /home/oracle/INT1.DMP

Job "ANUJ"."SYS_EXPORT_TABLE_01" successfully completed at Thu Dec 8 18:21:07 2016 elapsed 0 00:00:51

How to use SYSBACKUP privilege to perform backup and recovery tasks without accessing data....

SYSBACKUP  role 

create OS user

useradd -c "Oracle backup user"  orabackup -p root123

[root@ora-prim ~]# su - orabackup

[orabackup@ora-prim ~]$ pwd

/home/orabackup

 cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/

[root@ora-prim admin]# ls -ltr

total 16

-rw-r--r-- 1 oracle oinstall 373 Oct 31  2013 shrept.lst

drwxr-xr-x 2 oracle oinstall  61 Nov 27 19:49 samples

-rw-r--r-- 1 oracle oinstall 460 Dec  3 22:51 listener.ora

-rw-r--r-- 1 oracle oinstall 601 Dec  8 17:40 tnsnames.ora

-rw-r--r-- 1 oracle oinstall 177 Dec  8 17:41 sqlnet.ora

[root@ora-prim admin]# cp tnsnames.ora /home/orabackup

[root@ora-prim orabackup]# chown orabackup:orabackup tnsnames.ora

[root@ora-prim orabackup]# pwd

/home/orabackup

[root@ora-prim orabackup]# cp /home/oracle/.bash_profile .

[orabackup@ora-prim ~]$ cat .bash_profile

cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export ORACLE_UNQNAME=prim

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export ORACLE_SID=prim

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export TNS_ADMIN=/home/orabackup   

[orabackup@ora-prim ~]$ sqlplus anuj/vihaan@vihaan

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 10 08:00:43 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Dec 10 2016 06:14:29 +00: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> !id

uid=1001(orabackup) gid=1001(orabackup) groups=1001(orabackup)

SQL> grant sysbackup to orabackup ;

grant sysbackup to orabackup

                   *

ERROR at line 1:

ORA-01917: user or role 'ORABACKUP' does not exist

good news ...

Now creating new user 

SQL> alter session set container=vihaan ;

Session altered.

SQL> create user ORABACKUP identified by root123 ;

User created.

SQL> grant sysbackup to orabackup ;

Grant succeeded.

[root@ora-prim orabackup]# su - orabackup

Last login: Sat Dec 10 07:58:34 GMT 2016 on pts/0

[orabackup@ora-prim ~]$ sqlplus ORABACKUP/root123@vihaan as sysbackup

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 10 08:13:49 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

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 * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

------------------------------ ----- ----- ----- ----- ----- ----- ----------

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

ORABACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          3

set pages 200 linesize 200 

col grantee      format a25

col granted_role format a25

select * from dba_sys_privs where grantee = 'SYSBACKUP';

col current_schema format a20

col session_user format a20

select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

CURRENT_SCHEMA       SESSION_USER

-------------------- --------------------

SYS                  SYSBACKUP

 SQL> show user

USER is "SYSBACKUP"

RMAN> connect target ORABACKUP/root123@vihaan as sysbackup

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found "as": expecting one of: "newline, ;"

RMAN-01007: at line 1 column 41 file: standard input

use like this ...

RMAN> connect target 'ORABACKUP/root123@vihaan as sysbackup'

connected to target database: PRIM (DBID=4221955747)

RMAN> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

using target database control file instead of recovery catalog

CURRENT_SCHEMA

--------------------------------------------------------------------------------

SESSION_USER

--------------------------------------------------------------------------------

SYS

SYSBACKUP

RMAN> list backup;

specification does not match any backup in the repository

RMAN> backup current controlfile;

Starting backup at 10-DEC-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=62 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 10-DEC-16

channel ORA_DISK_1: finished piece 1 at 10-DEC-16

piece handle=/u01/app/oracle/fast_recovery_area/PRIM/42C8A9F4DB2B18EBE0534700A8C0C45C/backupset/2016_12_10/o1_mf_ncnnf_TAG20161210T082630_d4qh1v9r_.bkp tag=TAG20161210T082630 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09

Finished backup at 10-DEC-16

Starting Control File and SPFILE Autobackup at 10-DEC-16

piece handle=/u01/app/oracle/fast_recovery_area/PRIM/autobackup/2016_12_10/o1_mf_s_930212804_d4qh247h_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 10-DEC-16

RMAN>  list backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

2       Full    17.23M     DISK        00:00:06     10-DEC-16

        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20161210T082630

        Piece Name: /u01/app/oracle/fast_recovery_area/PRIM/42C8A9F4DB2B18EBE0534700A8C0C45C/backupset/2016_12_10/o1_mf_ncnnf_TAG20161210T082630_d4qh1v9r_.bkp

  Control File Included: Ckp SCN: 2518618      Ckp time: 10-DEC-16

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

3       Full    17.27M     DISK        00:00:00     10-DEC-16

        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20161210T082644

        Piece Name: /u01/app/oracle/fast_recovery_area/PRIM/autobackup/2016_12_10/o1_mf_s_930212804_d4qh247h_.bkp

  SPFILE Included: Modification time: 10-DEC-16

  SPFILE db_unique_name: PRIM

  Control File Included: Ckp SCN: 2518644      Ckp time: 10-DEC-16