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.