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.

Apply the patch on HA(AlwaysON DB 2012)

Apply the patch on HA(AlwaysON DB 2012)

My test environment consists of two SQL Server 2012 SP1 servers named Primary (principal) and  standby (mirror) that contain one mirrored databases and I'm upgrading to SP2.

Here is a look at the two mirrored instances.

Dashboard report from Primary instance:

Step 1
Always backup all system and user databases before applying patches.

Step 2
Remote Desktop into the "Mirror" server (standby in our example) and download/copy the patch to the server.

Run the query select @@version to get the service pack details as we can see current version is 2012 with sp1 of standby instance.

Step 3

Stop all SQL Services on the "Mirror" server.

Step 4
Run the patch on the "Mirror" server as run as administrator option.

Step 5 : all the component verification passed  click on next

Step 6 : accept the license terms and click on next.

Step 7: Select the feature which you want to get upgrade in my case i want all and click on next

Step 8: selected service will be checked at this stage will fail if any service is in use in my case all service are stop so all passed click on next

Step 9: Here are you ready to update click on upgrade

 Step 10: this process will take time as all component are being upgrade .

Step 11: All the component got upgrade can close the window.

Once the patch is complete, reboot the "Mirror" server.

Step 12: Check the Always on dashboard report and can see DB got again synched if not much change happen on primary during upgrade activity else db will be in recovery mode until recovery not get complete after recovery db mode will be synchronized. 

Step 13:Can see now DB got upgraded to SP2 using

select @@version             

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Now STANBDY got upgraded to sp2.

Step 14: Login to the primary instance  check the dashboard report can see both site are in synched.

Step 15: do the failover for Primary to standby ...right click on Availability group and select the failover option.

Step 16: perform the failover click on next

Step 17: Select the replica to which you want to make as active (new primary) click on next.

step 18: click on connect option

Step 19: will get the login prompt select the correct instance and click on connect option.

Step 20: Will get the window as below and click on next option.

Step 21:Verify the details like which server is current primary and standby click on finish for failover.

Step 22:will take few min to get complete then close the window now standby instance become active instance and primary(old) instance will become passive instance which we can proceed for upgrade.

Step 23: can see in always on Availability group showing as secondary

Step 24: Stop all SQL Services on the primary(old) instance.

Step 25: Run the installer as run as administrator option.

Step 26: accept the license terms and click on next.

 Step 27: Select the feature which you want to get upgrade in my case i want all and click on next

Step 28: selected service will be checked at this stage will fail if any service is in use in my case all service are stop so all passed click on next

Step 29: Here are you ready to update click on upgrade

Step 30:this process will take time as all component are being upgrade .

Step 31: All the component got upgrade can close the window.

Step 32: Once the patch is complete, reboot the server.

Step 33: Check the Always on dashboard report and can see DB got again synched if not much change happen on primary during upgrade activity else db will be in recovery mode until recovery not get complete after recovery db mode will be synchronized.

and we can see DB got upgraded to SP2 and primary AlwaysON HA availability group showing as secondary you do failover again to make ..Primary instance as primary and standby as secondary .  

Do test in you test environment before apply on production , please let us know if facing any issue.