How to disable auto restart of CRS in oracle 10G and 11GR1

Check the present seeings in below folder

/etc/oracle/scls_scr/NODE_NAME/root/ in LINUX & AIX

/var/opt/oracle/scls_scr/NODE_NAME/root in SOLARIS

Step 1
=======
To know the current settings ( note the first word from the second line has the word "ENABLE")

node_rac1:DB_PROD1:/var/opt/oracle/scls_scr/node_rac1/root#cat crsstart
enablenode_rac1:DB_PROD1:/var/opt/oracle/scls_scr/node_rac1/root#

Step 2
========
Disable the autostart
( Note : if any other way to auto-start via shell script / Vertias Cluster is enabled need to disable that as well)

node_rac1(nus917pg) $ cd /opt/app/crs/bin
node_rac1(nus917pg) $ ./crsctl disable crs
node_rac1(nus917pg) $

Step 3
===========
 ( note the first word from the second line has the word "DISABLE")


node_rac1:DB_PROD1:/var/opt/oracle/scls_scr/node_rac1/root#cat crsstart
disablenode_rac1:DB_PROD1:/var/opt/oracle/scls_scr/node_rac1/root#

Step 4
===========
To reenable the crs auto restart

node_rac1(nus917pg) $ cd /opt/app/crs/bin
node_rac1(nus917pg) $ ./crsctl enable crs
node_rac1(nus917pg) $


Step 5
===========
To check the enabled status.
 ( note the first word from the second line has the word "DISABLE")

node_rac1:DB_PROD1:/var/opt/oracle/scls_scr/node_rac1/root#ls -lrt crsstart
-rw-r--r--   1 root     root           7 Sep 12 05:17 crsstart
node_rac1:DB_PROD1:/var/opt/oracle/scls_scr/node_rac1/root#cat crsstart
enablenode_rac1:DB_PROD1:/var/opt/oracle/scls_scr/node_rac1/root#


Before Implementing in Production this must be tested in your test enviroment

[SOLVED] Invalid objects after upgrade to Oracle 9.2.0.8 from 9.2.0.6 even after executing utlrp and manually compile (ORA-00942)

[SOLVED] Invalid objects after upgrade to Oracle 9.2.0.8 from 9.2.0.6 




Note :
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
Implementing or trying any of the advice from this blog is at your own risk.

Problem : Application VIEWs were invalid after upgrade to Oracle 9.2.0.8 from 9.2.0.6 
=======
SELECT COUNT(*),OWNER FROM DBA_OBJECTS WHERE STATUS LIKE 'INVALID' GROUP BY OWNER;
  COUNT(*) OWNER
---------- ------------------------------
      40   APPUSER

Tried Methods : Executed utlrp multiple time, Manually Recomplied, Requested the view owner to recompile
=================
SELECT COUNT(*),OWNER FROM DBA_OBJECTS WHERE STATUS LIKE 'INVALID' GROUP BY OWNER;

  COUNT(*) OWNER
---------- ------------------------------
      40   APPUSER

35 VIEWS and 5 PACKAGE BODY were invalid

Tried to make it valid by executing UTLRP but din't work

@?/rdbms/admin/utlrp
35 VIEWS and 5 PACKAGE BODY were invalid

Tried recompile from SYS User manually one by one  but din't work

SET SERVEROUTPUT ON;
ALTER VIEW APPUSER.TABLE_APP1 COMPLILE;
SHOW ERRORS
NO ERRORS
.
.
Did same for multiple view

But still its no objects were made VALID

Queried DBA_ERRORS

SELECT OWNER,NAME,TYPE,SEQUENCE,LINE,POSITION,TEXT FROM DBA_ERRORS;

OWNER        NAME        TYPE        SEQUENCE        LINE         POSITION    TEXT
-----------------------------------------------------------------------------------------------------------------------
APPUSER        TABLE_APP1    VIEW        1                0            0            ORA-00942 table or view does not exist
.
.
.

40 rows selected.

Got error Table view doesn't exists
Saw from DBMS.METADATA.GETDDL that view were created or were showing without <SCHEMA_NAME>.<VIEW_NAME>

When tried to compile from SYS usere it was throwing ORA-00942 table or view does not exist in DBA_ERRORS

Solution
=========
Requested APPUSER to execute recompile the view from APPUSER it become VALID.

Example: On Resuming Replication on Skipped Table in logical standby DB

Example : On Resuming Replication on Skipped Table in logical standby DB
Check if table is listed in unsupported list.
SYS (ARS1T3) SQL>select owner,table_name from DBA_LOGSTDBY_UNSUPPORTED where table_name like 'FIELD_ENUM_VALUES';

no rows selected

-- Connect to Primary database and reset and unlock system user if required,If you already knew the password Ignore it as we are going to use DB Link to recreate and populate rows.


All the below steps are in In Logical standby DB
create public database link PROD_DB_SYSTEM connect to SYSTEM identified by <Password> using 'PROD_DB';


-- Make sure its applying
SELECT STATE  FROM  V$LOGSTDBY_STATE;


--Check the LAG time, if any first resolve the LAG.

col Primary format a30
col Standby format a30
SELECT TO_CHAR(latest_time,'yyyy/mm/dd hh24:mi:ss') "Primary",
TO_CHAR(applied_time,'yyyy/mm/dd hh24:mi:ss') "Standby",
round ((latest_time-applied_time)*24*60) "Lag in Minutes"
FROM V$LOGSTDBY_PROGRESS;


-- Check if this table is included in skip list., Here both , DML & DDL is skipped.

SYS (PROD_DB3) SQL>SET LINES 200
select * from DBA_LOGSTDBY_SKIP WHERE NAME LIKE 'FIELD_ENUM_VALUES';SYS (PROD_DB3) SQL>

ERROR                                                             STATEMENT_OPT                  OWNER                          NAME                           U E
----------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ - -
PROC
--------------------------------------------------------------------------------------------------
N                                                                 DML                            LOGICAL_USER                        FIELD_ENUM_VALUES              Y


N                                                                 SCHEMA_DDL                     LOGICAL_USER                        FIELD_ENUM_VALUES              Y





-- If their is no LAG then, Stop the logical standby Process

SYS (PROD_DB3) SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Database altered.

-- Unskip the DDL for this table
SYS (PROD_DB3) SQL>EXECUTE DBMS_LOGSTDBY.UNSKIP('SCHEMA_DDL','LOGICAL_USER','FIELD_ENUM_VALUES');

PL/SQL procedure successfully completed.

SYS (PROD_DB3) SQL>select * from DBA_LOGSTDBY_SKIP WHERE NAME LIKE 'FIELD_ENUM_VALUES';

ERROR                                                             STATEMENT_OPT                  OWNER                          NAME                           U E
----------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ - -
PROC
--------------------------------------------------------------------------------------------------
N                                                                 DML                            LOGICAL_USER                        FIELD_ENUM_VALUES              Y

-- Below procedure will , drop recreate the table and import data

SYS (PROD_DB3) SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('LOGICAL_USER','FIELD_ENUM_VALUES','PROD_DB_SYSTEM');

PL/SQL procedure successfully completed.

SYS (PROD_DB3) SQL>EXECUTE DBMS_LOGSTDBY.UNSKIP('DML','LOGICAL_USER','FIELD_ENUM_VALUES');

PL/SQL procedure successfully completed.

SYS (PROD_DB3) SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

SYS (PROD_DB3) SQL>select * from DBA_LOGSTDBY_SKIP WHERE NAME LIKE 'FIELD_ENUM_VALUES';

no rows selected

SELECT STATE  FROM  V$LOGSTDBY_STATE;

STATE
----------------------------------------------------------------
IDLE

press enter to continue


Primary                        Standby                        Lag in Minutes
------------------------------ ------------------------------ --------------
2014/12/10 18:52:10            2014/12/10 18:5:23                         2


After 2 mins

SYS (PROD_DB3) SQL>
SELECT STATE  FROM  V$LOGSTDBY_STATE;SYS (PROD_DB3) SQL>

STATE
----------------------------------------------------------------
APPLYING

SYS (PROD_DB3) SQL>col Primary format a30
col Standby format a30
SELECT TO_CHAR(latest_time,'yyyy/mm/dd hh24:mi:ss') "Primary",
TO_CHAR(applied_time,'yyyy/mm/dd hh24:mi:ss') "Standby",
round ((latest_time-applied_time)*24*60) "Lag in Minutes"
FROM V$LOGSTDBY_PROGRESS;
SYS (PROD_DB3) SQL>SYS (PROD_DB3) SQL>  2    3    4
Primary                        Standby
------------------------------ ------------------------------
                                    Lag in Minutes
--------------------------------------------------
2014/12/10 18:52:10            2014/12/10 18:52:10
                                                 0


SYS (PROD_DB3) SQL>
SYS (PROD_DB3) SQL>set lines 200
SYS (PROD_DB3) SQL>/

Primary                        Standby                                                            Lag in Minutes
------------------------------ ------------------------------ --------------------------------------------------
2014/12/10 18:52:10            2014/12/10 18:52:10                                                             0



Check the number of rows on the source and dest table, It must be matching,..

Note :
====
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
====
Implementing or trying any of the advice from this blog is at your own risk.

Solved : ORA-01927: cannot REVOKE privileges you did not grant ORA-01749: you may not GRANT/REVOKE privileges to/from yourself DB Vault Enabled. An execute privilege on DBMS_RLS package has been granted by DVSYS user (that's DB vault sys user I assume)

Solved : ORA-01927: cannot REVOKE privileges you did not grant ORA-01749: you may not GRANT/REVOKE privileges to/from yourself DB Vault Enabled.
An execute privilege on DBMS_RLS package has been granted by DVSYS user (that's DB vault sys user I assume)
I have been trying to revoke as SYS user and getting ORA-01927: cannot REVOKE privileges you did not grant
Also tried with DVADMIN user to revoke Got ORA-01749: you may not GRANT/REVOKE privileges to/from yourself DB Vault Enabled.


Objective is to Remove GRANT option to DVADMIN user

SYS (DEV_DB) SQL>revoke execute on DBMS_RLS from DVADMIN;
revoke execute on DBMS_RLS from DVADMIN
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

So tried to Grant and Revoke once again.,,,. Entries got added twice Revoke didn't succeed with option

SYS (DEV_DB) SQL>grant execute on dbms_rls to DVADMIN;

Grant succeeded.


GRANTEE  OWNER  TABLE_NAME   GRANTOR  PRIVILEGE  GRA HIE
--------------- ---------- --------- ----------- - ---
DVADMIN  SYS    DBMS_RLS     SYS      EXECUTE    NO  NO
DVADMIN  SYS    DBMS_RLS     DVSYS    EXECUTE    YES NO


SYS (DEV_DB) SQL>revoke execute on dbms_rls from DVADMIN;

Revoke succeeded.

SYS (DEV_DB) SQL>SELECT * FROM DBA_TAB_PRIVS WHERE  GRANTEE LIKE 'DVADMIN';

GRANTEE  OWNER   TABLE_NAME   GRANTOR  PRIVILEGE   GRA HIE
-------- ----- - -----------  -------  ----------  --- ---
DVADMIN  SYS     DBMS_RLS     DVSYS    EXECUTE     YES NO
                           
SYS (DEV_DB) SQL>revoke execute on dbms_rls from DVADMIN;
revoke execute on dbms_rls from DVADMIN
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant


Solutions is :Revoked EXECUTE on DBMS_RLS from DVSYS (SYS is the grantor for DVSYS), Which eventually revoked
from DVADMIN as well DVSYS with grant option, Later I granted only Executed permission without grant
Revoked


-- Note I removed some columns for reading ease.

SYS (DEV_DB) SQL>SELECT * FROM DBA_TAB_PRIVS WHERE table_name like 'DBMS_RLS';

GRANTEE                 TABLE_NAME   GRANTOR        GRA HIE
----------------------- ------------ -------------- --- ---
EXECUTE_CATALOG_ROLE    DBMS_RLS     SYS            NO  NO
USER1                     DBMS_RLS     SYS            NO  NO
DVSYS                   DBMS_RLS     SYS            YES NO
DV_OWNER                DBMS_RLS     SYS            NO  NO
DVADMIN                 DBMS_RLS    DVSYS          YES NO

SYS (DEV_DB) SQL>REVOKE EXECUTE ON DBMS_RLS FROM DVSYS;

Revoke succeeded.

SYS (DEV_DB) SQL>SELECT * FROM DBA_TAB_PRIVS WHERE table_name like 'DBMS_RLS';

GRANTEE               TABLE_NAME  GRANTOR   GRA HIE
--------------------- ----------- ------------- ---
EXECUTE_CATALOG_ROLE  DBMS_RLS    SYS       NO  NO
USER1                 DBMS_RLS    SYS       NO  NO
DV_OWNER              DBMS_RLS    SYS       NO  NO

SYS (DEV_DB) SQL>
SYS (DEV_DB) SQL>GRANT  EXECUTE ON DBMS_RLS TO DVSYS;

Grant succeeded.

SYS (DEV_DB) SQL>SELECT * FROM DBA_TAB_PRIVS WHERE table_name like 'DBMS_RLS';
                                                                        
GRANTEE               OWNER   TABLE_NAME  GRANTOR    GRA HIE
--------------------- ------- ----------- ---------  --- ---
EXECUTE_CATALOG_ROLE  SYS     DBMS_RLS    SYS        NO  NO
USER1                 SYS     DBMS_RLS    SYS        NO  NO
DV_OWNER              SYS     DBMS_RLS    SYS        NO  NO
DVSYS                 SYS     DBMS_RLS    SYS        NO  NO

SYS (DEV_DB) SQL>
SYS (DEV_DB) SQL>GRANT  EXECUTE ON DBMS_RLS TO DVADMIN;

Grant succeeded.

Successfully Revoked and granted only without GRANT OPTION to user.

Note :
====
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
====
Implementing or trying any of the advice from this blog is at your own risk.

Solved: ORA-01918,ORA-00911 Error User Does not exist I was trying to drop a user , which was created with junk character

Solved: ORA-01918,ORA-00911 Error User Does not exist
I was trying to drop a user , which was created with junk character

**********Recommended solution is to drop the user via OEM,*******

Below is NOT recommended by oracle, hence it may be risk
============================

Junk character would have been added by additional space/backspace keystroke by user while creating,.
example
========
username "king" while creating a space or backspace key stroke was hit by user in creation user script as junk character




SQL> select username from dba_users where username like 'king';

no rows selected


SQL> -- Notice the junk character below
SQL> select username from dba_users where username like '%king%';

USERNAME
------------------------------
king?

SQL>drop user king
ERROR at line 1:
ORA-01918: user 'king' does not exist

SQL> drop user king?;
drop user king?
              *
ERROR at line 1:
ORA-00911: invalid character

SQL> drop user "king?";
drop user "king?"
          *
ERROR at line 1:
ORA-01918: user 'king?' does not exist


SQL> -- Note : Make sure their are no segments owned by user
SQL> -- any way user would have not logged in so for,... but just saying
SQL>      
SQL> select user#,name from sys.user$ where name like 'king%';

     USER# NAME
---------- ------------------------------
        96 king?
       
SQL>-- Will update the new name in sys.user$ table, and drop the user and recreate correctly. --- This is unreommeded as we are not suppose to touch the base,.. it may create logical corruption.
SQL>-- Please make sure you DB is backed up if you dont have any other option...

SQL> update sys.user$ set name='king_new' where user#=96 and name like 'king%';

1 row updated.

SQL> commit;

Commit complete.

SQL> select username from dba_users where username like 'king_new';

USERNAME
------------------------------
king_new

SQL> select username from dba_users where username like 'king%'
  2  ;

USERNAME
------------------------------
king_new
SQL> -- Note the double quote
SQL> drop user "king_new";

User dropped.

New create the user again without junk character.


Note :
====
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
====
Implementing or trying any of the advice from this blog is at your own risk.

SQL Query to check how much RMAN backup completed so for .

1st Query
==========
--Query to check the size how much MB RMAN May Take

select sum(Bytes/1024/1024) AS SIZE_MB from dba_segments;


2nd Query
=============

-- Query to check the how much RMAN has taken backup so far

select to_char(completion_time,'dd/mm/yyyy') "DATE",sum(blocks)*8/1024 "Backup IN MB" 
from v$backup_datafile
group by to_char(completion_time,'dd/mm/yyyy')
having to_char(completion_time,'dd/mm/yyyy')= to_char(sysdate,'dd/mm/yyyy');



Note : 1. Will only show completed size after first channel completion.... else shows 0
       2. Will only show for the current sysdate ,. 
            which means how much ever completed at 23:59. 
            again if you trigger the query after 00:00 it will calculate for current date and shows 0 until it completes the next channel
           
Note :
====
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
====
Implementing or trying any of the advice from this blog is at your own risk.

[SOLVED] RMAN Restore failed with error ORA-19870,ORA-19504, ORA-27086 Permission denied Restore to different host

[SOLVED] RMAN Restore failed with error ORA-19870,ORA-19504,  

Note :
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
Implementing or trying any of the advice from this blog is at your own risk.  



ORA-27086 Permission denied 

Backup Taken in Lab Server 1

and Restoration tested in different Server.

Below is the error noticed and restoration completed for other peice but not completed for one peice

ORA-19870: error reading backup piece bk_ug4p912_s98820_p1_t855090210
ORA-19504: failed to create file "/u01/ORCL_APPT_2.dbf"
ORA-27086: unable to lock file - already in use
Linux : 13: Permission denied

ORA-19870: error reading backup piece bk_uikpekrcs_s97876_p1_t854224284
ORA-19507: failed to retrieve sequential file, handle="bk_uikpeks_s97876_p1_t854224284", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
   Backup file <bk_uikpekrcs_s97876_p1_t854224284> not found in DISK
failover to previous backup

But backup peice is available in backed up location


====

After analysing  below error
ORA-19870: error reading backup piece bk_ug4p912_s98820_p1_t855090210
ORA-19504: failed to create file "/u01/ORCL_APPT_2.dbf"
ORA-27086: unable to lock file - already in use --------------------- <<<<<<< Note this error, can lead you to solution.
Linux : 13: Permission denied

Understood the error

One source Server backup was taken the datafile ORCL_APPTSB_2 name was repeated but if different mount point. like below

File # 5 belongs to /u01/ORCL_APPT_2.dbf  ---- >>>> belongs to /u01
File # 6 belongs to /u02/ORCL_APPT_2.dbf  ---- >>>> belongs to /u02 ----<<<<<

When restoring the file it restores to same destination in /u01 and throws below error
ORA-19870: error reading backup piece bk_ug4p912_s98820_p1_t855090210
ORA-19504: failed to create file "/u01/ORCL_APPT_2.dbf"
ORA-27086: unable to lock file - already in use
Linux : 13: Permission denied



Solution 
=====

Set newname to datafile 6 to '/u01/ORCL_APPTSB_2_2.dbf'; solved the issue.

For datafile 5 let it be same