Home » Server Options » Data Guard » Query-Only non-SYS user for Mounted instance (Windows Server 2019, Oracle 19c EE)
Query-Only non-SYS user for Mounted instance [message #686422] Sun, 11 September 2022 04:14 Go to next message
Andrey_R
Messages: 439
Registered: January 2012
Location: Israel
Senior Member
Hi all,
I have a Oracle (Passive) Data Guard architecture.

I want to check that the DR instance is in sync by querying v$archived_log and other DMVs.

I am able to do it with user SYS, or any user I give SYSDBA privileges to.

If I try with a non-SYS user, as the instance is in MOUNT state - it results in

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 11 12:09:06 2022
Version 19.3.0.0.0

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

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

Is there a way I can monitor the DR and see that it's in sync without providing the monitoring person/application the SYSDBA privileges ?

Thanks
Andrey
Re: Query-Only non-SYS user for Mounted instance [message #686423 is a reply to message #686422] Sun, 11 September 2022 04:28 Go to previous messageGo to next message
John Watson
Messages: 8916
Registered: January 2010
Location: Global Village
Senior Member
That would be what the SYSDG privilege is for

(ps - moved the topic to a more appropriate forum)
Re: Query-Only non-SYS user for Mounted instance [message #686424 is a reply to message #686423] Sun, 11 September 2022 04:37 Go to previous messageGo to next message
Andrey_R
Messages: 439
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Sun, 11 September 2022 12:28
That would be what the SYSDG privilege is for

(ps - moved the topic to a more appropriate forum)
Thanks for the reply, and the valuable directions.

I've immediately looked up documentation in => https://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG786

But it says that a lot of pretty powerful permissions are included in this privilege ( alter database, startup, shutdown... etc )

Is there some option to check that the DR is in sync without giving such powerful permissions to a monitoring system's user ?

Re: Query-Only non-SYS user for Mounted instance [message #686425 is a reply to message #686424] Sun, 11 September 2022 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68600
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is there some option to check that the DR is in sync without giving such powerful permissions to a monitoring system's user ?
No.
Just a little think.
If you want to connect with a non-SYS user Oracle has to read the user$ table to verify the password, to read the table the database must be mounted and opened.
So the contrapositive: the database is not mounted then you can't connect with a non-SYS user.

Re: Query-Only non-SYS user for Mounted instance [message #686426 is a reply to message #686425] Sun, 11 September 2022 10:21 Go to previous message
Michel Cadot
Messages: 68600
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This was if you want to check connecting to the standby database but you can check from the primary database using fixed views like v$dataguard_status, v$archive_dest_status, v$archived_log.last_applied, v$archive_gap, v$dataguard_stats, v$managed_standby...

These views requires only SELECT ANY DICTIONARY privilege or SELECT_CATALOG_ROLE role.

Previous Topic: Random Slow archive Copy to Physical Standby
Next Topic: RMAN-06820 on rman backup from standby with OS authentication
Goto Forum:
  


Current Time: Mon Mar 04 01:51:42 CST 2024