Home » RDBMS Server » Server Administration » Cannot see DBA_USERS with SYS in a new database (Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production)
Cannot see DBA_USERS with SYS in a new database [message #679752] Mon, 23 March 2020 04:32 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,
I have a strange situation here, I got a Linux OEL 7.7 machine where Oracle software was installed, but not the database.

I have no info on how it was installed. I have user oracle ( and root if needed ).

My ORACLE_BASE, ORACLE_HOME are:


[oracle@MY_ORACLE_MACHINE oracle]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@MY_ORACLE_MACHINE oracle]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0.2/dbhome

I do NOT have any instance running:


[oracle@MY_ORACLE_MACHINE oracle]$ ps -ef | grep pmon
oracle   14183 10193  0 18:46 pts/0    00:00:00 grep --color=auto pmon

I have file /u01/app/oracle/product/12.1.0.2/dbhome/dbs/initMYDB.ora that contains:




db_name='MYDB'
memory_target=5G
processes = 300
audit_file_dest='/u01/app/oracle/admin/MYDB/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle/diag'
dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

So I go ahead and make sure the paths "/u01/app/oracle/admin/MYDB/" , "/u01/app/oracle/admin/MYDB/adump" and "/u01/app/oracle/fast_recovery_area" exist:

[oracle@MY_ORACLE_MACHINE dbs]$ mkdir -p /u01/app/oracle/admin/MYDB/
[oracle@MY_ORACLE_MACHINE dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/MYDB
[oracle@MY_ORACLE_MACHINE dbs]$ mkdir -p /u01/app/oracle/admin/MYDB/adump
I also created a folder for my data files:

mkdir -p $ORACLE_BASE/ORADATA/MYDB


Now I want to create a database.

I decide that

- My log files will be stored in /u01/app/oracle/fast_recovery_area/MYDB
- My datafiles will be stored in /u01/app/oracle/ORADATA/MYDB

So I am starting the database in nomount with the new pfile:


[oracle@MY_ORACLE_MACHINE dbs]$ export ORACLE_SID=MYDB
[oracle@MY_ORACLE_MACHINE dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 22 19:09:25 2020

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

Connected to an idle instance.

SQL> startup nomount pfile=/u01/app/oracle/product/12.1.0.2/dbhome/dbs/initMYDB.ora
ORACLE instance started.

Total System Global Area 5368709120 bytes
Fixed Size                  2935712 bytes
Variable Size            2919236704 bytes
Database Buffers         2432696320 bytes
Redo Buffers               13840384 bytes
SQL>

So now I want to create my controlfiles and database, so I go:


SQL> CREATE DATABASE MYDB
  2     USER SYS IDENTIFIED BY Newproj2020
  3     USER SYSTEM IDENTIFIED BY Newproj2020
  4     LOGFILE GROUP 1 ('/u01/app/oracle/fast_recovery_area/MYDB/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/fast_recovery_area/MYDB/redo02.log') SIZE 100M,
  5    6             GROUP 3 ('/u01/app/oracle/fast_recovery_area/MYDB/redo03.log') SIZE 100M
  7     MAXLOGFILES 5
  8     MAXLOGMEMBERS 5
  9     MAXLOGHISTORY 1
 10     MAXDATAFILES 100
 11     MAXINSTANCES 1
 12     CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
 13   14     DATAFILE '/u01/app/oracle/ORADATA/MYDB/system01.dbf' SIZE 325M REUSE
 15     EXTENT MANAGEMENT LOCAL
 16     SYSAUX DATAFILE '/u01/app/oracle/ORADATA/MYDB/SYSAUX01.DBF' SIZE 100M REUSE
 17  AUTOEXTEND ON
 18  NEXT 10M MAXSIZE 200M
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/ORADATA/MYDB/temp01.dbf'
      SIZE 20M REUSE
 19   20   21   22     UNDO TABLESPACE UNDOTBS
 23        DATAFILE '/u01/app/oracle/ORADATA/MYDB/undotbs01.dbf'
 24        SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

Database created.

SQL>



I check that it is open:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL>

But when I try to query DBA tables - I get a strange error... and I have checked that I am user SYS:


SQL> select table_name from dba_tables;
select table_name from dba_tables
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> show user
USER is "SYS"
SQL>

What am I missing, why do I have a problem accessing DBA views ?

Will appreciate some advice. Thanks very much in advance
Andrey
Re: Cannot see DBA_USERS with SYS in a new database [message #679753 is a reply to message #679752] Mon, 23 March 2020 05:00 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You have to run scripts to create them. Have you read the docs? You seem to be here,

https://docs.oracle.com/database/121/ADMIN/create.htm#ADMIN11082

[Updated on: Mon, 23 March 2020 05:01]

Report message to a moderator

Re: Cannot see DBA_USERS with SYS in a new database [message #679754 is a reply to message #679753] Mon, 23 March 2020 05:19 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Mon, 23 March 2020 12:00
You have to run scripts to create them. Have you read the docs? You seem to be here,

https://docs.oracle.com/database/121/ADMIN/create.htm#ADMIN11082
That helped to solve the issue.

And yes, I've read some parts of it but I admit I got a little hasty and impatient about reading it all,
partially because I wasn't sure I can find a step by step explanation to the style of installation I did.

I promise to improve Smile

Great thanks for the help.
Have a nice day.

Andrey

[Updated on: Mon, 23 March 2020 05:20]

Report message to a moderator

Previous Topic: Actualizar vista v$database_block_corruption; (merged)
Next Topic: Relink Oracle after OS upgrade
Goto Forum:
  


Current Time: Thu Mar 28 11:22:54 CDT 2024