OraFAQ Forum: Data Guard » Query-Only non-SYS user for Mounted instance

Home » Server Options » Data Guard » Query-Only non-SYS user for Mounted instance (Windows Server 2019, Oracle 19c EE)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query-Only non-SYS user for Mounted instance [message #686422]

Sun, 11 September 2022 04:14

Andrey_R
Messages: 441
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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Query-Only non-SYS user for Mounted instance [message #686423 is a reply to message #686422]

Sun, 11 September 2022 04:28

John Watson
Messages: 8976
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)

Report message to a moderator

Re: Query-Only non-SYS user for Mounted instance [message #686424 is a reply to message #686423]

Sun, 11 September 2022 04:37

Andrey_R
Messages: 441
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 ?

Report message to a moderator

Re: Query-Only non-SYS user for Mounted instance [message #686425 is a reply to message #686424]

Sun, 11 September 2022 09:57

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Query-Only non-SYS user for Mounted instance [message #686426 is a reply to message #686425]

Sun, 11 September 2022 10:21

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 18:56:02 CDT 2025