DBMS_SCHEDULER.STOP_JOB ORA-27486 insufficient privilege

Trying to stop scheduled job as an non-dba user: myUser

exec DBMS_SCHEDULER.STOP_JOB('myJob',force => true);


ORA-27486 insufficient privilege 
ORA-06512 at "SYS.DBMS_ISCHED", line 199
ORA-06512 at "SYS.DBMS_SCHEDULER", line 557


The user is lacking the system privilege: MANAGE SCHEDULER

solution:

grant MANAGE SCHEDULER to myUser;

I was trying to apply path 11.2.0.2.11 on Grid and database homes:

# opatch auto /home/oracle/software/p16742320_112020_Linux-x86-64 -oh /oracle/11.2.0.2 -ocmrf /tmp/ocm.rsp


The opatch minimum version  check for patch /home/oracle/software/p16742320_112020_Linux-x86-64/16459322/custom/server/16459322 failed  for /oracle/11.2.0.2
The opatch minimum version  check for patch /home/oracle/software/p16742320_112020_Linux-x86-64/16619893 failed  for /oracle/11.2.0.2

In the log /opt/oracle/grid/OPatch/crs/../../cfgtoollogs/opatchauto2013-09-10_09-09-27.log

2013-09-10 09:09:39: The opatch minimum version  check for patch /home/oracle/software/p16742320_112020_Linux-x86-64/16619893 failed  for /oracle/11.2.0.2
2013-09-10 09:09:39: The opatch version check failed with following error
2013-09-10 09:09:39: bash: /oracle/11.2.0.2/OPatch/opatch: Permission denied

I discovered I had unzipped the opatch-software as root and that caused the problem. Change to oracle:oinstall :

# chown -R oracle:oinstall /oracle/11.2.0.2/OPatch

Now all is working just fine:

# opatch auto /home/oracle/software/p16742320_112020_Linux-x86-64 -oh /oracle/11.2.0.2 -ocmrf /tmp/ocm.rsp

Detected Oracle Clusterware install

Using configuration parameter file: /opt/oracle/grid/crs/install/crsconfig_params

Successfully unlock /oracle/11.2.0.2

patch /home/oracle/software/p16742320_112020_Linux-x86-64/16459322  apply successful for home  /oracle/11.2.0.2

patch /home/oracle/software/p16742320_112020_Linux-x86-64/16619893  apply successful for home  /oracle/11.2.0.2

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

Sql server 2008 R2

Trying to shrink the logfile for out Team Foundation 2010 database:



BACKUP LOG  Tfs_DefaultCollection TO DISK='NUL:'


DBCC SHRINKFILE(Tfs_DefaultCollection_log, 1)

I get the followin error message:

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

Trying the solution with sp_repldone:

USE Tfs_DefaultCollection

GO

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1

Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

exec sp_replicationdboption @dbname = N'Tfs_DefaultCollection', @optname = N'merge publish', @value = N'true'

GO

Msg 20028, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 60

The Distributor has not been installed correctly. Could not enable database for publishing.

The replication option 'merge publish' of database 'Tfs_DefaultCollection' has been set to false.

Running opentran show we have some unreplicated data, that's really strange because there is no replication setup for this database:

dbcc opentran

Transaction information for database 'Tfs_DefaultCollection'.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (193362:85316:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

sp_removedbreplication Tfs_DefaultCollection

Msg 1205, Level 13, State 57, Procedure sp_MSrepl_clean_replication_bit, Line 25

Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

alter database Tfs_DefaultCollection set offline with rollback immediate

Msg 1205, Level 13, State 68, Line 1

Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

I founbd and killed one of my own sessions...

alter database Tfs_DefaultCollection set offline with rollback immediate

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Failed to restart the current database. The current database is switched to master.

sp_removedbreplication Tfs_DefaultCollection

alter database Tfs_DefaultCollection set online with rollback immediate

Now running opentran again:

dbcc opentran

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

BACKUP LOG  Tfs_DefaultCollection TO DISK='NUL:'

DBCC SHRINKFILE(Tfs_DefaultCollection_log, 1)

Success!

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

On a newly installed Oracle 11.2.0.2 instance I tried to enable archive logging:


SQL> startup mount force


SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

WTF!!!

SQL> alter database recover;

Database altered.

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

!?!?!?

What happens if we do a new shutdown and startup mount (without force this time)?

SQL> alter database open;

Database altered.

SQL> shutdown

SQL> startup mount

SQL> alter database archivelog;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/11.2.0.2/dbs/arch

Oldest online log sequence     45

Next log sequence to archive   46

Current log sequence           46

SQL> alter database open;

Database altered.

All well again!  :)

oracle tablespace usage SQL script

This SQL could be usefull if you would lika a list of your tablespaces and be able to see how full they are:


SELECT tablespace_name,
       ROUND (used_bytes / (1024 * 1024)) used_mb,
       ROUND (max_bytes / (1024 * 1024)) max_mb,
       ROUND ( (used_bytes) / max_bytes * 100) percent_full
  FROM (SELECT ddf.tablespace_name,
               NVL (dfs.used_bytes, 0) used_bytes,
               ddf.max_bytes
          FROM (  SELECT tablespace_name, SUM (bytes) used_bytes
                    FROM dba_segments
                GROUP BY tablespace_name) dfs,
               (  SELECT tablespace_name,
                         SUM (GREATEST (bytes, maxbytes)) max_bytes
                    FROM dba_data_files
                GROUP BY tablespace_name
                union
                  SELECT tablespace_name,
                         SUM (GREATEST (bytes, maxbytes)) max_bytes
                    FROM dba_temp_files
                GROUP BY tablespace_name) ddf
         WHERE ddf.tablespace_name = dfs.tablespace_name(+));





TABLESPACE_NAME                   USED_MB     MAX_MB PERCENT_FULL
------------------------------ ---------- ---------- ------------
SYSAUX                               1478      32768            5
UNDOTBS1                               70       4096            2
USERS                                  44       4096            1
SYSTEM                                669      32768            2
UNDOTBS2                               18       4096            0
TEMP                                    0       4096            0

The script also includes the temporary tablespaces like TEMP and calculates the MAX_MB from the maxsize of the datafiles and not tha actual size.

Could not start database ORA-01092: Oracle instance terminated. Disconnection forced

Oracle11g CRS with an Oracle10g database.

When testing our new RAC installation we disconnected our NetApp storage brutally from the cluster by disconnecting the network cables.
Connected it back again and restarted the both nodes of the cluster. The crs came upp fine but the database failed to start.
The log shiws the following errors:

ORA-00604: error occured at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oradata/myDb/dbfile/undotbs01.dbf'



$ . oraenv
myDb1

$ sqlplus / as sysdba

SQL> startup mount

SQL> select name, status, file# from gv$datafile;

NAME                                          STATUS                FILE#
----------------------------------   ------------------  -----
/oradata/myDb/dbfile/undotbs01.dbf   OFFLINE             2
/oradata/myDb/dbfile/undotbs02.dbf   ONLINE               3
/oradata/myDb/dbfile/system01.dbf     ONLINE                1

The undo-tablespace has suddenly gone offline!

I tried the following:

SQL> alter database recover automatic datafile 2;

SQL> alter database datafile 2 online;

SQL> alter database open;

database opened


This could also be done from RMAN:


$ rman target / nocatalog

RMAN> startup mount

RMAN> recover datafile 2;

RMAN> sql 'alter database datafile 2 online';

RMAN> alter database open;

database opened

All well again!

PRKR-1005 : adding of cluster database testdb configuration failed, PROC-5: User does not have permission to perform a cluster registry operation on this key

PRKR-1005 : adding of cluster database myDb configuration failed, PROC-5: User does not have permission to perform a cluster registry operation on this key

I was struggling trying to get a failed databas installation, Oracle 10.2.0.5 2 nodes RAC, working. The database instances could be started manually with:

Logged in as oracle

$ sqlplus / as sysdba

SQL> startup


But the registration with the crs, in this case an Oracle 11.2.0.3, did not work and I tryed everything to unregister the instances and the database from the cluster:

$ srvctl remove instance -d myDb -i myDb1

$ srvctl remove database -d myDb

I restarted the cluster and crs_stat showed no database registered with crs! But...

$ srvctl add database -d myDb -o /oracle/10.2.0.5

Just wouldn't work! The databas was already registered!

Run the following as root

This is strange but can bve fixed. First check that you have a backup of the crs config:

$ ocrconfig -showbackup

Then stop all cluster services:

$ crsctl stop cluster -all

Export the settings to a temporary file:

$ ocrconfig -export /tmp/config

Edit the file /tmp/config and remove the lines containing myDb:

DATABASE.LOG.myDb

DATABASE.LOG.myDb1.INSTANCE

DATABASE.LOG.myDb2.INSTANCE

Import the setting back again:

$ ocrconfig -import /tmp/config

Start the cluster services:

$ crsctl start cluster -all

Now you can try to register again as oracle

$ srvctl add database -d myDb /oracle/10.2.0.5