Oracle Database Startup Problems

SQL> startup

ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2403304 bytes
Variable Size             989856792 bytes
Database Buffers          654311424 bytes
Redo Buffers                6946816 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5712
Session ID: 242 Serial number: 3

What could this be? So I looked at the alert log.

Errors in file C:\APP\ORACLE\diag\rdbms\jhcdb\jhcdb\trace\jhcdb_ora_5768.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 12884901888 bytes is 100.00% used, and has 0 remaining bytes available.

Mon Jul 28 09:59:40 2014
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to 'C:\APP\ORACLE\FAST_RECOVERY_AREA\JHCDB\ARCHIVELOG\2014_07_28\O1_MF_1_408_%U_.ARC'
Mon Jul 28 09:59:40 2014
Errors in file C:\APP\ORACLE\diag\rdbms\jhcdb\jhcdb\trace\jhcdb_ora_5768.trc:
ORA-16038: log 3 sequence# 408 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'C:\APP\ORACLE\ORADATA\JHCDB\REDO03.LOG'

 
I think that says it all.

Of course I cannot get into rman without he database running, so I must increase the size of the db_recovery_file_dest_size so I can get the database running so I can tidy up.

sqlplus / as sysdba
              SQL> startup mount
              SQL> alter system set db_recovery_file_dest_size = 20G;
              System altered.
              SQL> shutdown immediate
              ORA-01109: database not open
              Database dismounted.
              ORACLE instance shut down.
              SQL> startup
              ORACLE instance started.

              Total System Global Area 1653518336 bytes
              Fixed Size                  2403304 bytes
              Variable Size             989856792 bytes
              Database Buffers          654311424 bytes
              Redo Buffers                6946816 bytes
              Database mounted.
              Database opened.
               SQL>

 

Now I can go into RMAN and sort things out.

 

Happyjohn.

Locked Package (ORA-04021)

When releasing a package to test, I got the following error. That is after a significant wait time.

Calling "pkg_loss _spec.sql"...

CREATE OR REPLACE PACKAGE pkg_loss IS

*

ERROR at line 1:

ORA-04021: timeout occurred while waiting to lock object

 

So I checked for blocking locks but that doesn’t apply to DDL. Instead you need to check the V$ACCESS view.

SELECT * FROM v$access WHERE object = 'PKG_LOSS';

 

This shows that there is certainly some sessions holding the object. So having discussed the matter with the developer, they authorised me to kill the sessions holding the object. So put this next statement together. It shows the relevant session information, plus the kill statement.

 

select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id,

      'alter system kill session '''||sid||','||serial#||''' immediate;' comm

from v$session sess where sid in (

SELECT sid FROM v$access WHERE lower(object) = 'pkg_rege_loss_objection');

 

I then copy & paste the kill statements I needed, and then was able to replace the package.

 

alter system kill session '230,2911' immediate;

 

 

HappyJohn.

 

ORA-28031: maximum enabled roles exceeded

So this is what I got…..

 

ORA-28031: maximum of 150 enabled roles exceeded

ORA-06512: at "SYS.UTL_RECOMP", line 865

 

I checked the parameter….

 

Show parameter max_enabled_roles

NAME                                 TYPE        VALUE

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

max_enabled_roles                    integer     150

 

But if you try to increase this, it won’t work.

 

SQL> alter system set max_enabled_roles = 200 scope = spfile;

alter system set max_enabled_roles = 200 scope = spfile

*

ERROR at line 1:

ORA-00068: invalid value 200 for parameter max_enabled_roles, must be between 1 and 148

 

Which is curious because its already set to 150 regardless of what the message says.

 

So I had to find out which users were to blame

 

select "Grantee", count(*) "Role Number" from
(select distinct connect_by_root grantee "Grantee", granted_role
from dba_role_privs
connect by prior granted_role=grantee)
group by "Grantee"
having count(*)>=148
order by count(*),"Grantee","Role Number"
/

and then revoke some roles to bring the number down.

 

 

 

Blocking Locks

In order to identify who is blocking a session in Oracle, try the following example.

 

1.       Create a table and then lock it.

create table tab1 (acolumn date);
lock table tab1 in exclusive mode;

2.       Now from another session, try to insert into it.

insert into tab1 select sysdate from dual;

Of course because of the lock, this session will now hang waiting for the lock to be released.

3.       From a third session, take a look at the blocking lock.

set linesize 132
set heading off
col “Session 1” for a50
col “Session 2” for a50
select sess1.username || ' on ' || sess1.machine || ' (' || sess1.sid || '/' || sess1.serial# || ') ' ||
'Is blocking ' ||
sess2.username || ' on ' || sess2.machine || ' (' || sess2.sid || '/' || sess2.serial# || ') ' ||
' => ' || dobj1.owner || '.' || dobj1.object_name || ' (' || dobj1.object_type || ')' "object"
from v$lock    lock1,
v$session sess1,
v$lock    lock2,
v$session sess2,
v$locked_object lobj1,
dba_objects dobj1
where sess1.sid=lock1.sid and sess2.sid=lock2.sid
and lock1.BLOCK=1 and lock2.request > 0
and lock1.id1 = lock2.id1
and lock2.id2 = lock2.id2
and sess1.sid = lobj1.session_id
and lobj1.object_id = dobj1.object_id
/
set heading on


Which will return something like this. I included the sid/serial# so you can alter session kill ‘sid/serial#’ immediate; if you need to.

SYS on U-DOM1\HP20014881 (64/3207)    Is blocking  SYS on U-DOM1\HP20014881 (307/3513)

4.       Finally release the lock. On the original ‘lock’ session.

rollback;

You might want to rollback or commit the insert session and drop the table.

Happy Heth

 

 

Windows 7 - Overriding company settings on login.

Often when you’re working for a company, they preset things in order to prevent you spoiling the machine for others to use..

This often includes things like screen savers, desktop wallpapers and power setting.

This doesn’t include user privilege settings. As an Oracle Administrator I require administrator access, however, having to reset everything each time I log in.

 

This is a little script that you can try and put in your settings back to what you want.

 

Rem Firstly we wait for other things to complete., but remove it if you like

Rem The following lines manipulate the default mount points to what I want.

ping 1.1.1.1 -n 1 -w 10000

net use g: /delete      

ping 1.1.1.1 -n 1 -w 3000

net use g: \\dom1.net\Root0Drive /p:yes

net use s: \\dom1.net\GB539user$ \MyDocs /p:yes

 

Rem I took the time to create a power plan but kept losing it so I saved it and use the

Rem next line to reset it.

powercfg -setactive 4f871d8c-1b18-4933-a841-00dbf4d9c491

 

Rem The next line imports a file of settings into the registry

Rem however it sbetter just to list the entries in this file.

rem regedit /s "h:\my bits\jhSSreg1.reg"

Rem To prevent the screensaver coming on and unlock the visual options.

@echo off

REG ADD "HKCU\SOFTWARE\POLICIES\Microsoft\Windows\Control Panel\Desktop" /V "ScreenSaverIsSecure" /D "0" /F

@echo off

REG ADD "HKCU\SOFTWARE\POLICIES\Microsoft\Windows\Control Panel\Desktop" /V "ScreenSaveTimeOut" /D "10000" /F

 

Rem The following set the default home page on 3 different web browsers

@echo off

REG ADD "HKCU\SOFTWARE\MICROSOFT\INTERNET EXPLORER\MAIN" /V "START PAGE" /D "http://home.intranet.eon.com/ebs/en.html" /F

@echo off

REG ADD "HKCU\SOFTWARE\MICROSOFT\GOOGLE CHROME\MAIN" /V "START PAGE" /D "http://home.intranet.eon.com/ebs/en.html" /F

@echo off

REG ADD "HKCU\SOFTWARE\MICROSOFT\MOZILLA FIREFOX\MAIN" /V "START PAGE" /D "http://home.intranet.eon.com/ebs/en.html" /F

 

 

Place all of the entries into a file with the BAT suffix and add it into the Startup directory on the Start menu.

 

 

Cloning and Deleting a Pluggable Database

The following example uses Windows 7 64bit, so the filenames will differ to those of a Linux environment.

And don't forget to amend the tnsnames.ora and register any new PDBs with the listener.

 

To clone a local/remote pluggable database

 

1.       Create a directory to hold the data files.

md C:\app\oracle\oradata\jhcdb\jhpdb3

2.       Make sure you’re in the root cdb. I’m using a common user with DBA granted to all containers.

alter session set container = cdb$root;

3.       Close the source PDB and re-open it read only.

alter pluggable database jhpdb1 close;
alter pluggable database jhpdb1 open read only;

4.       Create the clone with some filename remapping and sizing info.

create pluggable database jhpdb3 from jhpdb1
file_name_convert = ('C:\app\oracle\oradata\jhcdb\jhpdb1', 'C:\app\oracle\oradata\jhcdb\jhpdb3')
storage (maxsize 2G max_shared_temp_size 100M);

If you want to clone a remote pdb, you can use a database link. Same as above except firstly create a database link from the target DB, then open the pdb on the source read only, then create pluggable database on the target needs a @dblink next to the source name (eg jhpdb1@rem_db).

 

Don’t forget that a PDB must be opened at least once before you can do anything with it.

 

To remove a PDB

 

                drop pluggable database jhpdb3 including datafiles;     
-- this leaves the directory but none of the files.

 

If you miss off the including datafiles, it obviously leaves them and they can be reintroduced to the database later (see below).

 

To unplug a pluggable database

 

                Alter pluggable database jhpdb3 close;

                Alter pluggable database jhpdb3 unplug into 'C:\app\oracle\unplug\jhpdb3.xml';

 

Because the unplugged PDB is still part of the database, the files remain in the same location and they still form part of the rman backups.

 

To plug in an unplugged pluggable database

 

To replug an unplugged PDB into the same name, you must first drop the existing PDB but keep the datafiles

 

                drop pluggable database jhpdb3 keep datafiles;

                create pluggable database jhpdb3 using 'C:\app\oracle\unplug\jhpdb3.xml' nocopy tempfile reuse;

Alter pluggable database jhpdb3 open;

 

To replug an unplugged PDB into a new name (perhaps on another database) with new file location on another database

 

create pluggable database jhpdb5 using 'C:\app\oracle\unplug\jhpdb4.xml'

file_name_convert = ('C:\app\oracle\oradata\jhcdb\jhpdb4', 'C:\app\oracle\oradata\jhcdb\jhpdb5')

move;

 

Note you will get an ORa-65122 if you try to plug in a clone on the same CDB as its source. You must clone. The only way to do this is to plug in a dropped unplugged PDB as above.

 

 

Using DBMS_PDB to check plug compatibility

 

Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the unplugged PDB is compatible with the CDB.

 

SET SERVEROUTPUT ON

DECLARE

compatible CONSTANT VARCHAR2(3) :=

  CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

      pdb_descr_file => '/home/oracle/devpdb3.xml')

  WHEN TRUE THEN 'YES'

  ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

 

.

 

Happyjohn

 

Automatically Starting Pluggable Databases

At this time, Oracle has not provided a way of auto-starting pluggable databases in a multitenant environment.

 

This has the effect that should you need to restart your container database (CDB), then you must also connect to it, in order to manually restart the pluggables, otherwise they’ll sit in mounted state forever.

 

To avoid this ,try something like the following. You may add something a bit more clever to check the state of the database so that it only starts them under certain circumstances, and light include a table containing a single row per PDB with a preset indicator that you can control what will restart the next time the database opens.

 

CREATE or REPLACE trigger START_PLUGGABLES

   after startup on database

BEGIN

   execute immediate 'alter pluggable database all open';

END start_pluggables;

/

 

Happyjohn.