Clouds blog

Category: Oracle

  • Oracle 12c RESTRICTED mode, disabled functionality

    Oracle 12c RESTRICTED mode, disabled functionality

    oracle databaseToday I learned a lot when our production database went in restricted mode. We had huge issues with our Oracle 12c database that was patched unsuccessfully. After the patch did not finish the database always restarted in RESTRICTED MODE, which means only admin / sys can connect to the database. All of our application could not access the database, which resulted in an all around panic situation. The cause was the half finished patch. The database detected that a patch did not finish correctly and because of that it always started restricted.

    The solution was to clear all data from the unsuccessful patch (I don’t know exactly where) and apply the patch again.

    Info about restricted database

    About it on Oracle page – https://docs.oracle.com/database/121/OSTMG/GUID-74FA4067-04D2-477B-A09C-40150BBCD702.html

    Below are some warnings from Oracle page about this special mode. Active sessions are not disconnected when enabling restricted mode. It is a good idea to start the database directly in restricted mode.

    To place an instance in restricted mode, where only users with administrative privileges can access it, use the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause. After placing an instance in restricted mode, you should consider killing all current user sessions before performing any administrative tasks.

    To lift an instance from restricted mode, use ALTER SYSTEM with the DISABLE RESTRICTED SESSION clause.

    Useful commands

    You can check the status of the database with:

    SELECT logins from v$instance; — If “Allowed” , than the database is normally accessible.

    After that you can  enable or disable restricted session with:

    alter system enable restricted session;
    alter system disable restricted session;

    Granting users permission to access the restricted database with:

    grant restricted session to joshua;

    Disabled functionality

    Interesting thing is I could not find any info of what functionality is  disabled when database is in RESTRICTED MODE. I found on my own skin that the following thing do not work:

    • Scheduler (only if explicitly enabled)
    • APEX ( always asked for XDB username and password when accessing apex web page)
    • DB Links (failing with an awesome error as shown below)  After normal start the DB link was still not functioning. I am not sure if restricted mode disables it.
    DB link error message
    Error message when testing DB link from SQL Developer when the database is in restricted mode

     

    Hope it helps someone.

    Bye

  • Oracle: Enable/Disable all constraints in schema at once

    SQL developer 4.1Here are the snippets for enabling and disabling all constraints in an oracle schema. This is useful when you create a new schema and then fill it up with the data from other databases.

    For whatever reason you will need this be careful to only enable primary keys at first, otherwise all foreign key validation will fail.

    Also check my post about updating sequences – 

    --
-- disable all
--

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   ORDER BY c.constraint_type DESC)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);
  END LOOP;
END;
/

--
--enable all
--
BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'DISABLED'
   --AND CONSTRAINT_NAME LIKE '%_PK%'   --first you have to enable only primary keys, otherwise foreign keys will not validate
   ORDER BY c.constraint_type)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name);
  END LOOP;
END;
/

  • Oracle: Update all sequences for tables for current user

    SQL developer 4.1Below is procedure that updates all table sequences to match the max ID currently in a table. Useful when there is a mismatch of values.

    -- Update all sequences to match actual table values
DECLARE
v_max VARCHAR2 (1000);
BEGIN

FOR rec IN (
select tabs.table_name table_name,
seqs.sequence_name seq_name,
seqs.last_number
from user_tables tabs
join user_triggers trigs
on trigs.table_name = tabs.table_name
join user_dependencies deps
on deps.name = trigs.trigger_name
join user_sequences seqs
on seqs.sequence_name = deps.referenced_name
where last_number = 1 --only sequnces that have last number 1
)
LOOP
--Info print
--dbms_output.put_line(rec.table_name);
--dbms_output.put_line(rec.seq_name);
--dbms_output.put_line(rec.last_number);

--reset sequence last number to 0
EXECUTE IMMEDIATE 'ALTER sequence ' || rec.seq_name || ' INCREMENT BY -' || rec.last_number ;
EXECUTE IMMEDIATE 'SELECT ' || rec.seq_name || '.NEXTVAL FROM DUAL' ;

--Find highest id in table
EXECUTE IMMEDIATE 'select max(id)+1 from ' || rec.table_name INTO v_max;

--dbms_output.put_line(v_max);

EXECUTE IMMEDIATE 'ALTER sequence ' || rec.seq_name || ' INCREMENT BY ' || v_max ;

EXECUTE IMMEDIATE 'SELECT ' || rec.seq_name || '.NEXTVAL FROM DUAL' ;

EXECUTE IMMEDIATE 'ALTER sequence ' || rec.seq_name || ' INCREMENT BY 1' ;

END LOOP;
END;
/