* ERROR at line 1: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

As a DBA, you must have come across the below error while working on pluggable databases

 

ERROR at line 1:

ORA-01219: database or pluggable database not open: queries allowed on fixed

tables or views only

 Generally, this error gets reported whenever you try to access a view or a table in the pluggable database which is not in the open state, Here I was trying to check users in the database and was hit with this error, then I checked the status of my pluggable database and I found it was in mount state and not in the open state.

Then immediately I opened the database using the below command and then I was able to query the table, 

SQL> alter pluggable database open;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 TECHNOPDB                      READ WRITE NO

SQL>  select count (*) from dba_users;

 

  COUNT(*)

----------

        40

SQL>

so wherever you see this type of error, try to check status of the database and if down, bring it up.

Read More

Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

You must have got mail from application team that they are facing below error while performing operations in database
Error:
Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Solution:
Check this parameter in database by login to database Click here to check how to login MySQL DB

mysql> show variables like '%log_bin_trust_function_creators%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+

we need to set this to 1
go to location of my.cnf , get backup of my.cnf file in case revert is required.

$ cp my.cnf my.cnf_$DATE

open my.cnf file using vi Insert mode and add the parameter log_bin_trust_function_creators in file
save the file and restart the mysql services

command to stop mysql services
$service mysql stop
command to start mysql services
$service mysql start
once restarted you will able to see the changes

mysql> show variables like '%log_bin_trust_function_creators%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+

Read More