Connecting to Another Schema using a Proxy Connection

In certain situations the Oracle DBA still needs to connect to the database using application schemas.  activities such as adding or dropping database links , execution of query etc.

One common workaround that DBAs have commonly used involves temporarily changing the user password, connecting, and then changing the password back using the previously recorded password hash.  However there are numerous problems with this approach:

1.             The schema may be locked

2.             The password may be controlled by a PROFILE that may also need to be adjusted

3.             Account intrusion detection tools may detect the connection

4.             The connection may not be properly audited via Oracle or external autditing tools

5.             The application may unsuccessfully try to connect while the password is temporarily changed causing an application failure!

Solution

The proxy connection allows the user to connect to another schema using their own username and password.  The schema being proxied is simply provided in square brackets. proxy connection can be made the GRANT CONNECT THROUGH privilege is required.

The following example illustrates how a proxy connection is configured and used:

in my e.g i am creating one user with name  admin who want to access all object of scott user by logging admin ID with his own password.

SQL>

SQL> create user admin identified by admin;    

User created.

SQL> grant create session to admin;

Grant succeeded.

SQL> conn admin[scott]/admin  //method to login with proxy account in [] you have to pass the user ID.

ERROR:

ORA-28150: proxy not authorized to connect as client

Warning: You are no longer connected to ORACLE.

SQL> conn /as sysdba

Connected.

SQL> alter user scott grant connect through admin;

User altered.

SQL> conn admin[scott]/admin   //pass the schema name while logging with your ID.

Connected.

SQL> show user                 //now u ca see even u have pass the credentials for admin but showing as scott

USER is "SCOTT"

SQL> select * from tab;                 //can see all the table of scott

TNAME                          TABTYPE  CLUSTERID

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

BONUS                          TABLE

DEPT                           TABLE

EMP                            TABLE

SALGRADE 

                      TABLE

SQL> select count(1) from emp;  //can query to those table also.

  COUNT(1)

----------

        14

SQL>

Online rename and relocation of an online data file on DG environment.

 Now no need to perform number of steps to move or renaming of datafile. with 12c you can move the datafile online simply using ALTER DATABASE MOVE DATAFILE command ,while the data is being transferred, the user can perform DML and DDL tasks and you may can do migration from non-ASM to ASM and vice versa.  

Rename a data file:

SQL> select banner from v$version where rownum<=1;

BANNER

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

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

//Select the DB role

SQL> select database_role  from v$database;

DATABASE_ROLE

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

PRIMARY

//get the file location which you want to move as in my e.g i am going to perform on users datafile .

SQL> select name from  v$datafile where file#=6;

NAME

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

C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

//Check the max sequence number of primary DB

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

            22

// connect to standby 

QL> conn sys/xxxx@standby as sysdba

Connected.

//Check the applied sequence number .

SQL> select max(sequence#),applied from v$archived_log group by applied;

MAX(SEQUENCE#) APPLIED

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

            22 YES

//check the file location of db file 6 as in my case its on same path.

SQL> select name from  v$datafile where file#=6;

NAME

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

C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

// now login on primary and perform the movement operation.

SQL> conn sys/xxxx@primary as sysdba

Connected.

// performning he movement command .

SQL> alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF

' to 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf';

Database altered.

//as we can see file got change to its parent folder from oradata/orcl to oradata.

SQL> select name from  v$datafile where file#=6;

NAME

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

C:\APP\ADMINISTRATOR\ORADATA\USERS01.DBF

//Check the max sequence number of primary DB

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

           22

// not switch the logfile to check the synch status.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

            23

//login to standby and see the synch status and file path at standby server .

SQL> conn sys/xxxx@standby as sysdba

Connected.

SQL> select name from  v$datafile where file#=6;

NAME

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

C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

SQL> select max(sequence#),applied from v$archived_log group by applied;

MAX(SEQUENCE#) APPLIED

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

            23 NO

            22 YES

SQL> select max(sequence#),applied from v$archived_log group by applied;

MAX(SEQUENCE#) APPLIED

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

            23 YES

//As we can see DB got synced but file path is same this is new things in 12c, physical file movement wont go from redo you have to perform MOVE DATAFILE command at standby also. 

SQL> select name from  v$datafile where file#=6;

NAME

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

C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

SQL>

Now if you want to move the file at standby also need to perform move datafile command at standby db.

========================Moving the file at stanbdy====================

SQL> alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF

 ' to 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf';

alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF ' to

 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf'

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

//As MRP is running need to stop the same.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF

 ' to 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf';

Database altered.

// now users db file on both site are on same path.

//Below few more task(12c New feature ) you may can perform on-line if required  

Migrate a data file from non-ASM to ASM:

SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '+DG_DATA';

Migrate a data file from one ASM disk group to another:

SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_NEW';

Overwrite the data file with the same name, if it exists at the new location:

SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '/u01/data_new/users_01.dbf' REUSE;

Copy the file to a new location whilst retaining the old copy in the old location:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;

You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view. Additionally, you can also refer the alert.log of the database where Oracle writes the details about action being taken place.