Forum Stats

  • 3,759,507 Users
  • 2,251,556 Discussions
  • 7,870,689 Comments

Discussions

Categories

database or pluggable database not open: queries allowed on fixed tables or views only

Anand_Reddy
Anand_Reddy Member Posts: 10
edited Jun 1, 2016 8:37AM in Multitenant

C:\Users\anand>sqlplus sys/abcd as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue May 24 21:23:09 2016 Copyright (c) 1982, 2014, Oracle.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions SQL> select * from all_users   2  / select * from all_users               * ERROR at line 1: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1694498816 bytes Fixed Size                  3046416 bytes Variable Size            1040188400 bytes Database Buffers          637534208 bytes Redo Buffers              13729792 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> $rman target Argument    Value          Description ----------------------------------------------------------------------------- target      quoted-string  connect-string for target database catalog      quoted-string  connect-string for recovery catalog auxiliary    quoted-string  connect-string for auxiliary database nocatalog    none          if specified, then no recovery catalog cmdfile      quoted-string  name of input command file log          quoted-string  name of output message log file trace        quoted-string  name of output debugging message log file append      none          if specified, log is opened in append mode debug        optional-args  activate debugging msgno        none          show RMAN-nnnn prefix for all messages send        quoted-string  send a command to the media manager pipe        string        building block for pipe names script      string        name of catalog script to execute using        list of args  arguments for rman variables timeout      integer        number of seconds to wait for pipe input checksyntax  none          check the command file for syntax errors ----------------------------------------------------------------------------- Both single and double quotes (' or ") are accepted for a quoted-string. Quotes are not required unless the string contains embedded white-space. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00552: syntax error in command line arguments RMAN-01009: syntax error: found "end-of-file": expecting one of: "double-quoted- string, identifier, single-quoted-string, " RMAN-01007: at line 0 column 0 file: command line arguments SQL> alter database open resetlogs   2  / alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: 'C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF' SQL> select * from all_users   2  / select * from all_users               * ERROR at line 1: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

Lothar Flatz
· Share on TwitterShare on Facebook

Answers

  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    edited May 25, 2016 6:10AM

    Did you even check the post after posting ? It's just not readable at all. Not only that, you didn't even bother to tell what is the problem, what you have done to solve that problem?

    Anyways, this is your clue.

    ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only 
SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. 
SQL> startup
 ORACLE instance started. 
Total System Global Area 1694498816 bytes
 Fixed Size        3046416 bytes
 Variable Size            1040188400 bytes
 Database Buffers          637534208 bytes 
Redo Buffers              13729792 bytes 
Database mounted. 
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

    Aman....

    Lothar Flatz
    · Share on TwitterShare on Facebook
  • Pravin Takpire
    Pravin Takpire Member Posts: 1,762 Gold Trophy
    edited May 25, 2016 7:05AM

    Looks like you have copied the script to restore and recover database. Your database needs recovery.

    regards

    Pravin

    · Share on TwitterShare on Facebook
  • Anand_Reddy
    Anand_Reddy Member Posts: 10
    edited May 25, 2016 8:14AM

    Sorry Aman!! I completely agree with you. Before I adjusted it I had accidentally clicked on submit. My bad!!

    Coming to the problem, I had installed oracle 12 c on my laptop. I am getting the error while logging into scott schema. I have two questions here

    1) I haven't done anything extra, trust me, other than executing the executable provided by oracle. But still why is the database not mounted properly

    2) what are the readily available schemas once the database is installed. Do we have scott schema , else kindly help me with process to create a schema.image1.jpg

    image2.jpg

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited May 25, 2016 1:00PM

    I suggest that you uninstall and then reinstall your database.

    Before you do that you should read chapters 17 and 18 of the concepts doc and my replies in this thread.

    You are connecting to the root/cdb. Any sample schemas are in a PDB if you installed the sample database. That PDB will NOT be opened by default when you startup the database.

    · Share on TwitterShare on Facebook
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Jun 1, 2016 12:27AM

    Hi,

    Coming to the problem, I had installed oracle 12 c on my laptop. I am getting the error while logging into scott schema. I have two questions here
1) I haven't done anything extra, trust me, other than executing the executable provided by oracle. But still why is the database not mounted properly

    1. To be honest you are not revelaed the exact thing what you done at your end. Just shared the prior couple of steps of your issue

    2. Let's consider it as non-cdb database, when you can't query the database - then normally the database won't ask for you to opt for resetlogs, perhaps in your case its asking to do so. why ? Since their is untold story of what is your environment background.

    3. Since its clone of template db, mostly probably it won't happen. Please drop it and try to re-create it

    - Pavan Kumar N

    · Share on TwitterShare on Facebook
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    edited Jun 1, 2016 8:37AM

    Below is the same output  what you have shown to  us, 

    SQL> select created , cdb from V$database;

CREATED   CDB
--------- ---
31-MAY-16 NO

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
     0

PL/SQL Release 12.1.0.2.0 - Production
     0

CORE    12.1.0.2.0    Production
     0

TNS for Linux: Version 12.1.0.2.0 - Production
     0

NLSRTL Version 12.1.0.2.0 - Production
     0


SQL> shut immediate
ORA-01031: insufficient privileges
SQL> conn / as sysdba 
Connected.
SQL> startup force mount
ORACLE instance started.

Total System Global Area  553648128 bytes
Fixed Size            2926664 bytes
Variable Size          268437432 bytes
Database Buffers      276824064 bytes
Redo Buffers            5459968 bytes
Database mounted.
SQL> select count(*) from all_users;
select count(*) from all_users
                     *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only


SQL> select created , cdb from V$database;

CREATED   CDB
--------- ---
31-MAY-16 NO

    Now, you can see that I am in the Mount stage and the query on the data dictionary is not allowed-which is where you are too. BUT, after this, we are on different page. You are getting a completely different and quite clear error message, 

    SQL> !oerr ora 01589
01589, 00000, "must use RESETLOGS or NORESETLOGS option for database open"
// *Cause:  Either incomplete or backup control file recovery has been
//          performed. After these types of recovery you must specify either
//          the RESETLOGS option or the NORESETLOGS option to open your
//          database.
// *Action: Specify the appropriate option.

    And this means, you have done something that you are not telling us clearly OR somehow your db is now toast. So as Rp mentioned, it would be better to scrap this db and start all over again. You should check though in the Alert Log to see what it is going on . But then again, you should know about it as well.

    About the Scott schema and the rest of the schemas, if you have chosen to install the Sample Schema option along with the database creation, you would be getting them. But if you haven't , you have to install the Sample Schemas explicitly. See the doc link below for the same.

    http://docs.oracle.com/database/121/COMSC/installation.htm

    HTH

    Aman....

    · Share on TwitterShare on Facebook
This discussion has been closed.