Dec 17, 2019

Stop/Start all RAC databases at once from each individual node

Some times you may notice to stop all the databases running on oracle rac cluster as soon as possible, and assume there are 50+ databases are running on same host, then manually stoping all the databases will take lots of time. 
So, Stopping all RAC databases running on an ORACLE_HOME at once and saving the state before shutting them down is really helpful when you are doing patching on a server.
In my case I have databases running on some nodes and not the others, it’s getting complicated to keep track of which database is running where. With this method you will shutdown all databases at once and keep track of the databases running on that node before shutting them down.

Stopping all RAC databases of an ORACLE_HOME

This command will stop all databases running on the ORACLE_HOME specified and save the state before the shutdown in a state file.
$ srvctl stop home -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 -node node1 -statefile /tmp/node1_stop.dmp


Starting all RAC databases of an ORACLE_HOME

This command uses a state file to restart all databases previously running on a node.
srvctl start home -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 -node node1 -statefile /tmp/node1_stop.dmp

Dec 11, 2019

SQL Error: ORA-01720: grant option does not exist

After upgrading from 12.1.0.2 to 12.2.0.1, application team raised one issue. They not able to create view even all required grants exits. I reproduced the same issue. Let me explain.

Assume their is user scott. Now scott wants to create a view using tables of HR schema. SCOTT has select access on all tables of HR schema along with create view access.

e.g.,

create or replace view scott.vu_emp_details
(empid, ename, locality,street, city, state, pin, phone, email)
as
select * from
(select  empid, ename, address1,address2, city, state, pin, phone1, email1
from hr.emp_details);

Error received:

SQL Error: ORA-01720: grant option does not exist for 'hr.emp_details'
01720. 00000 -  "grant option does not exist for '%s.%s'"
*Cause:    A grant was being performed on a view and the grant option was
           not present for an underlying object.
*Action:   Obtain the grant option on all underlying objects of the view.


After long verification, I did not find any solution. But below workaround helped me.

 
-- drop the view first

drop view scott.vu_emp_details;

-- create the view then

create view scott.vu_emp_details ....

The reason behind:

"The code was changed in 12.2.0.1 so that create view behavior is similar to grant. If you try to make a GRANT on an existing view and the view owner does not have the grant option, then ORA-1720 is the expected result (even in 11.2.0.3). In 12.2.0.1, we have simply made CREATE VIEW consistent with the GRANT behavior, i.e. if an incompatible grant exists, then the new view definition must not be allowed (even with FORCE). In other words, we do not allow incompatible grants to coexist with the view definition and so an error must be thrown. The behavior in release 12.1.0.2 (and earlier) was incorrect; the new behavior in 12.2.0.1 is intentional and correct."

To correct:
1. Remove all grants on the view before REPLACING the view. This will ensure that no incompatible grants exist.
2. Drop and recreate the view. Dropping the view will automatically remove all grants.
In my case, the customer had just upgrade to 12.2.0.1. Hope this can help someone else!

Nov 22, 2019

Table Fragmentation and re-organisation in Oracle Database

How to Find and Remove Table Fragmentation?
Let us discuss.

What is Table Fragmentation?
In simple language you can say, table has less data but size is too high which is not expected at all.

Brief explanation:

When rows are inserted into the table, the high watermark(used blocks) of the table moved forward in order to accommodate new rows into the table. But during delete operation, oracle doesn’t allow high watermark to shift backward in order to decrease table size and release the free space.
Ideally once we delete the data from the table, the free space should be released or reused but additionally oracle acquire new blocks to accommodate the new rows for insertion which causes hole into the table.

How to remove table fragmentation in oracle?

There are different ways to remove fragmentation like table export/import, moving tables to same or different tablespace and table recreation. But here we will discuss most easy and common ways of removing fragmentation.

Steps To Check and Remove Fragmentation:

#1 :  Gather table statistics

In order to find the exact difference between the total size and actual size of the table from dba_segments and dba_tables views. You can also check the last analysed date of a table. If table has recently analysed and stale_stats is no then you can skip this step.

select table_name,last_analyzed,stale_stats from user_tab_statistics where table_name='&TABLE_NAME';

exec dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,granularity =>'AUTO', method_opt=> 'for all indexed columns size skewonly', degree => 8, cascade => TRUE );


#2 : Check table size

select sum(bytes)/1024/1024/1024 "size_GB" from dba_segments where segment_name='&TABLE_NAME' and owner='&OWNER';


#3 :  Check actual table size, fragmented size and percentage of fragmentation in a table
(Use 8 in place of 16 if your block size is 8k)

SELECT table_name,
  avg_row_len,
  ROUND(((blocks*16/1024)),2)  ||'MB' "TOTAL_SIZE",
  ROUND((num_rows*avg_row_len/1024/1024),2)  ||'MB' "ACTUAL_SIZE",
  ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)  ||'MB' "FRAGMENTED_SPACE",
  (ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/ROUND(((blocks*16/1024)),2))*100 "percentage"
FROM all_tables
WHERE table_name='&TABLE_NAME' and owner='&OWNER';

e.g.,

TABLE_NAME                    |AVG_ROW_LEN|TOTAL_SIZE|ACTUAL_SIZ|FRAGMENTED|percentage
------------------------------|-----------|----------|----------|----------|----------
SALES_ORDERS                  |       3754|444.64MB  |54.67MB   |389.97MB  |87.7046599


 If you find more than 20% fragmentation then you can proceed for de-fragmentation. You can also de-fragment based on size you are going to reclaim from the above mentioned  query.


#4 : Check the indexes on the table

select index_name from dba_indexes where table_name='&TABLE_NAME' and owner='&OWNER';


#5 : Remove fragmentation by moving tables to same tablespace. You can also move tables to different tablespace.

alter table <table_name> move;

For moving tables into different tablespace

alter table <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace>;


#6 : Rebuild the indexes.

alter index index_name rebuild online;

#7 : Gather the statistics:

SQL> exec dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,granularity =>'AUTO', method_opt=> 'for all indexed columns size skewonly', degree => 8, cascade => TRUE );


#8: Finally check the table size and percentage of fragmentation:
(Use 8 in place of 16 if your block size is 8k)

SELECT table_name,
  avg_row_len,
  ROUND(((blocks*16/1024)),2) ||'MB' "TOTAL_SIZE",
  ROUND((num_rows*avg_row_len/1024/1024),2) ||'MB' "ACTUAL_SIZE",
  ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
  (ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/ROUND(((blocks*16/1024)),2))*100 "percentage"
FROM all_tables
WHERE table_name='&TABLE_NAME' and owner='&OWNER';

Note: If table move is not helping to avoid fragmentation, then proceed to drop and re-create table with downtime. Make sure you should take care of all FK constraints.  

Nov 17, 2019

Backup & Recovery plans for VLDBs

Backup and recovery is a crucial and important job for a DBA to protect business data.
Especially if database size is too big, then backup and recovery plan must be tested to define RPO and RTO.

Recovery Time Objective (RTO) is the time duration in which you want to be able to recover your data.

A Recovery Point Objective, or RPO, is the maximum amount of data that can be lost before causing detrimental harm to the organization. RPO indicates the data loss tolerance of a business process or an organization in general.

An efficient backup and recovery strategy for very large databases to reduce the overall resources necessary to support backup and recovery by using some special characteristics that differentiate data warehouses from OLTP systems.

A DBA should initially approach the task of data warehouse backup and recovery by applying the same techniques that are used in OLTP systems: the DBA must decide what information to protect and quickly recover when media recovery is required, prioritizing data according to its importance and the degree to which it changes. However, the issue that commonly arises for data warehouses is that an approach that is efficient and cost-effective for a 100 GB OLTP system may not be viable for a 10 TB data warehouse. The backup and recovery may take 100 times longer or require 100 times more storage.

Oracle Backup and Recovery:

In general, backup and recovery refers to the various strategies and procedures involved in protecting your database against data loss and reconstructing the database after any kind of data loss.

A backup is a representative copy of data. This copy can include important parts of a database such as the control file, archived redo logs, and data files. A backup protects data from application error and acts as a safeguard against unexpected data loss, by providing a way to restore original data.

1) Architecture backup environment:
Use VTL ( virtual Tape libary) rather than actual tape drive. VTL are fast and random accessible as compare to actual tape library.

2) Evaluate Backup tools:
- Evaluate Full, incremental and archive-log backups
- Evaluate file level backups - helpful for logical backups
- Verify support related to multiplex / Parallel features
- Verify PDB/ CDB backups from Oracle 12c on wards

3) Flashback technologies
- Evaluate to take whole database with snapshot basis
- Evaluate CDP ( continuous data protection) capabilities

4) Evaluate Compression features
- RMAN Backup level
- file level

6) Backup startergies
- Weekly level 0 (full) and daily level 1 at night
- Periodically archive log backups

Case-study: VLDB deployments

1) Enable block change tracking:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '<path_to_block_change_file_name>';
e.g.,
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/oradata/PROD/bct_prod.dbf';
-- incase of OMF/ RAC
SQL> alter database enable block change tracking;
2) Configure the preferred RMAN Recovery Window:
e.g.,
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
3) enable auto-backup of control-file in RMAN:
e.g.,
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
$) Enable/ configure parallelism:
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4  BACKUP TYPE TO BACKUPSET; # use parallelism as per core availability
   Note: In case of tape/ 3rd party console, enable parallelism at console level
4) Performing the Incremental / Full backups:

-- To Tape
run {
allocate channel c1 type 'SBT_TAPE';
allocate channel c2 type 'SBT_TAPE';
sql 'alter system archive log current';
BACKUP CURRENT CONTROLFILE format '%d_%T_%s_%p_full_ctl_%U.bkp' tag 'Inc_CF';
backup incremental level 0 database format '%d_%T_%s_%p_full_%U.bkp' tag 'Inc_BKP';
sql 'alter system archive log current';
backup archivelog all format '%d_%T_%s_%p_full_al_%U.bkp' tag 'Arc_BKP;
DELETE FORCE NOPROMPT OBSOLETE;
release channel c1;
release channel c2;

e.g.,

-- List backup

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
22269123 B  F  A SBT_TAPE    17-NOV-19       1       1       NO         INC_CF
22269184 B  1  A SBT_TAPE    17-NOV-19       1       1       NO         INC_BKP
22269185 B  1  A SBT_TAPE    17-NOV-19       1       1       NO         INC_BKP
22269255 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269256 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269257 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269258 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269511 B  F  A SBT_TAPE    17-NOV-19       1       1       NO         TAG20191117T103335

RMAN>

-- Disk backup
run
{
allocate channel c1 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
allocate channel c2 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
allocate channel c3 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
allocate channel c4 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
sql 'alter system archive log current';
BACKUP CURRENT CONTROLFILE format '%d_%T_%s_%p_full_ctl_%U.bkp' tag 'Inc_CF';
backup  as compressed backupset incremental level 0  check logical database plus archivelog;
release channel c1 ;
release channel c2 ;
release channel c3 ;
release channel c4 ;
}

5) Perform Restore and recover to evaluate RTO:

6) ASM Best Practices for VLDBs
- Large allocation units
- Between 1MB and 64MB (4,8,16,32 and 64)
- Use 64MB for a very large data warehousing system, which will reduce ASM
- memory requirements, and also improve ASM instance startup time
Disk groups with an External Redundancy

I hope this document may help to define backups in your environment.

Nov 13, 2019

VIP showing INTERMEDIATE and FAILED OVER


We received complain from application team, that we are not able to do connect database. But when we checked, database is up and running fine. One database configured in one of the 2-Node RAC (11gR2 RAC cluster). From verification found VIP of that node is in "INTERMEDIATE and FAILED OVER" state. Listeners are running fine in both nodes.

So, the issue is, VIP was showing as INTERMEDIATE and FAILED OVER in one of RAC node 

Checked crs the status and here is the sample output:

$ crsctl status resource -t

......
ora.example1a.vip
      1        ONLINE  ONLINE       example1a                              
ora.example1b.vip
      1        ONLINE  INTERMEDIATE example1a          FAILED OVER         


-- Now Check that particular VIP status

[oracle@example1b ~]$ crsctl status resource ora.example1b.vip
NAME=ora.example1b.vip
TYPE=ora.cluster_vip_net1.type
TARGET=ONLINE
STATE=INTERMEDIATE on example1a

[oracle@example1b ~]$ 


-- Now stop the VIP

crsctl stop resource ora.example1b.vip

ora.example1a.vip
      1        ONLINE  ONLINE       example1a                              
ora.example1b.vip
      1        OFFLINE OFFLINE 

-- Start the VIP
crsctl start resource ora.example1b.vip

[oracle@example1b ~]$ crsctl start resource ora.example1b.vip
CRS-2672: Attempting to start 'ora.example1b.vip' on 'example1b'
CRS-2676: Start of 'ora.example1b.vip' on 'example1b' succeeded
CRS-2672: Attempting to start 'ora.LISTENER.lsnr' on 'example1b'
CRS-2676: Start of 'ora.LISTENER.lsnr' on 'example1b' succeeded
[oracle@example1b ~]$ 

-- Check the status now

ora.exmaple1a.vip
      1        ONLINE  ONLINE       exmaple1a                              
ora.exmaple1b.vip
      1        ONLINE  ONLINE       exmaple1b 


Now it back to online.


Nov 11, 2019

ORA-19913: unable to decrypt backup - solution

While restoring database, I received below error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/11/2019 04:53:47
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-19870: error while restoring backup piece /u02/work/038_08-Nov-2019_Gouranga_arisg51d_refresh/rman/arisg51p_03-Nov-2019_13-39-15_full_cf.bkp
ORA-19913: unable to decrypt backup


From verification found, this backup was taken with encryption enabled database and there is wallet configure also.

So while you restore, then start the database in 'nomount' state and see the below status. If it is closed, then you can't restore. So follow the below steps to start restore database.

-- start in nmount 
[oracle@example01 ~]$ sqlplus / as sysdba


SQL> select status from v$encryption_wallet;

STATUS
------------------------------------------------------------------------
CLOSED

SQL> quit


status must be 'open'


-- fix/ Solution

copy the wallets files and configure in sqlnet.ora also, then verify the status

e.g.,

coped below wallet files to your location something like this:

/u01/app/oracle/admin/<your_db_name>/wallet

Then, point your wallet in 'sqlnet.ora' file.

$ cd $ORACLE_HOME/network/admin
$ cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
     (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet)
     )
  )


SQL> select status from v$encryption_wallet;

STATUS
-------------------------------------------------
OPEN


But ensure, to enable encryption while restore the database like what is enabled during backup.

e.g.,

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

-- Encryption enabled with key AES256
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256';

new RMAN configuration parameters:
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
new RMAN configuration parameters are successfully stored

Note that you can enable the encryption only after restoring the controlfile.

Aug 19, 2019

Create Password file for database in ASM Diskgroup - Oracle 12c RAC only

-- Verify Password file exists or not

$ crsctl stat res ora.HCMPRD.db -f | grep PWFILE
PWFILE=

-- Create password file in asm

orapwd file='+DATA/HCMPRD/orapwHCMPRD' dbuniquename='HCMPRD'

-- Now verify password file created or not

$ crsctl stat res ora.HCMPRD.db -f | grep PWFILE
PWFILE=+DATA/HCMPRD/orapwHCMPRD

-- you can see also in ASM_CLIENT

ASMCMD [+DATA/HCMPRD/PASSWORD] > ls
pwdHCMPRD.4594.1016696757
pwdHCMPRD.493.1016696613

-- you can find from asm diskgroup

$ asmcmd
ASMCMD> pwget --dbuniquename HCMPRD
+DATA/HCMPRD/orapwHCMPRD
ASMCMD>


-- Alternative method to create password file 12c asm

This method is valid only for oracle 12c onwards.

SYNTAX –
ASMCMD> pwcreate –dbuniquename {db_unique_name} {file_path}  {sys_password}

Example:

Here we will create a password file with dbuniquename HCMPRD.

ASMCMD> pwcreate --dbuniquename HCMPRD +DATA/PWDFILE/pwdHCMPRD oracle


-- You can check using srvctl also:

$ srvctl config database -d HCMPRD
Database unique name: HCMPRD
Database name:
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: +DATA/HCMPRD/PARAMETERFILE/spfile.2557.2026287697
Password file: +DATA/HCMPRD/orapwHCMPRD
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: HCMPRD1,HCMPRD2
Configured nodes: example1a,example1b
Database is administrator managed
$


Aug 9, 2019

Restore multi-million rows from existing table via pl/sql block

There is request to copy multi million rows from one archive table to original table with downtime.
I tried with export and import to copy fast but not succeeded even after many hours.

So I tried with below approach and it completed fast. This pl/sql block copy 10 thousand rows at a time and commit. So that we can avoid huge rollback segment and huge switch logs etc.

-- Here is the Pl/SQL block

declare 
cursor C1 is
select id,comment_text,comment_by,comment_date,cell_number,language
from scott.big_table_archive;
begin
for r1 in C1
loop
insert into scott.big_table values (
r1.id,
r1.comment_text,
r1.comment_by,
r1.comment_date,
r1.cell_number,
r1.language);
if (c1%rowcount mod 10000=0) then
commit;
end if;
end loop;
commit;
end;
/

-- if you want to add existing records again to original table, then you need to use merge command. If any PK constraints are there and updated records are there, then below pl/sql block will help you:

MERGE INTO scott.big_table a
USIING 
   (SELECT * FROM scott.big_table_archive)  b
ON (b.id=a._id and b.comment_text=a.comment_text)  /* PK */
WHEN MATCHED THEN
UPDATE
    SET a.comment_by=b.comment_by,
        a.comment_date=b.comment_date,
        a.Tcell_number=b.cell_number,
        a.language=b.language
WHEN NOT MATCHED THEN
INSERT  
    VALUES 
       (r1.ID,
        b.comment_text,
        b.comment_by,
        b.comment_date,
        b.cell_number,
        b.language
 );



Try this and post your feedback.



Jul 21, 2019

ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED

During cloning a database, I found below error:

ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED

--- duplicate terminated with below message

...
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure. 
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 07/20/2019 11:20:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED.
.....

Then, I tried to start, but not able to open the database and same error came.

e.g.,

SQL> startup;
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2932632 bytes
Variable Size            2634023016 bytes
Database Buffers         1644167168 bytes
Redo Buffers               13844480 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14693: The MAX_STRING_SIZE parameter must be EXTENDED.
Process ID: 85042
Session ID: 762 Serial number: 59546

When I verified Oracle document, then following thing I found.

This parameter was introduced in Oracle 12c. MAX_STRING_SIZE controls the maximum size of string size in Oracle database. Either we can set it to STANDARD or.EXTENDED The default value is STANDARD

MAX_STRING_SIZE = STANDARD means the maximum size of strings is 4000 bytes for VARCHAR2 and NVARCHAR2

MAX_STRING_SIZE = EXTENDED means maximum size can be upto 32767 byte .

We can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. But not from EXTENDED to STANDARD.

With MAX_STRING_SIZE set to STANDARD , if we try to set the length of column more than 4000, then it will throw ORA-00910 error.

So here, I did below fixes to open the database:

Example:

SQL> startup mount;

SQL> alter system set MAX_STRING_SIZE='EXTENDED' scope=spfile;

System altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2932632 bytes
Variable Size            2634023016 bytes
Database Buffers         1644167168 bytes
Redo Buffers               13844480 bytes
Database mounted.
Database opened.
SQL> 


SQL> @?/rdbms/admin/utl32k.sql

CONCLUSION: Once the max_string_size is changed to EXTENDED,We can revert to original STANDARD value.So before changing the value in critical or production database, Please do thorough testing , As you may hit uninvited bugs.

Jun 21, 2019

Restore, Recover and Rename RAC database using NID utility in Oracle 12c

In this article, we will see the steps to Restore, Recover and Rename RAC database using NID utility of Oracle.

Assume, Two node RAC,
Hosts are :
racexample1a
racexample1b

Source database name  :  hrdev
Target database name    : hrprd

HRDEV database disk backup taken and transfered to this target server. Assume the backup location of target server is :
/u03/backups/hrdev

Source and Target database version :  12.1.0.2.0 ( Oracle 12c)

Step-1: Create a temporary parameter file like below in one of node:


$cd $ORACLE_HOME/dbs
$ vi inithrdev.ora

*.audit_file_dest='/u01/app/oracle/admin/hrdev/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='12.1.0.2.0'
#*.control_files='+DATA/HRDEV/CONTROLFILE/Current.555.2008648327','+DATA/HRDEV/CONTROLFILE/Current.556.2008648327'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='hrdev'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=1024g
*.db_unique_name='hrdev'
*.diagnostic_dest='/u01/app/oracle'
hrdev1.instance_number=1
hrdev2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=18G
*.open_cursors=1000
*.processes=1000
*.remote_listener='racexample1:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=1000
hrdev1.thread=1
hrdev2.thread=2
hrdev1.undo_tablespace='UNDOTBS1'
hrdev2.undo_tablespace='UNDOTBS2'

Step:2 : You can create alias in your bash_profile to avoid multiple time exports:

export GRID_HOME=/u01/app/12.1.0.2/grid
export DB_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
alias grid='export ORACLE_SID=+ASM1;unset ORACLE_UNQNAME;export ORACLE_HOME=$GRID_HOME;PATH=$ORACLE_HOME/bin:/bin:/usr/bin'
alias hrdev='export ORACLE_SID=hrdev1;export ORACLE_UNQNAME=hrdev;export ORACLE_HOME=$DB_HOME;export PATH=$ORACLE_HOME/bin:/bin:/usr/bin'

$ . .bash_profile

Step-3:  Start the instance in nomount using temporary pfile:

$ hrdev
$ cd $ORACLE_HOME/dbs
$ sqlplus / as sysdba
SQL> startup nomount pfile='inithrdev.ora';

Step-4 : List the backup pieces

Go to the backup location and list out all the backup pices:

$ cd /u03/backups/hrdev
$ ls -lrt | awk '{print $9}'

hrdev_19062019_full_al_1vu4hct9_1_1.bkp
hrdev_19062019_full_cf.bkp
hrdev_19062019_full_db_1tu4hbto_1_1.bkp
hrdev_19062019_full_pf.bkp
hrdev_19062019_full_db_1uu4hcss_1_1.bkp
hrdev_19062019_full_tf.bkp
hrdev_19062019_full_sp.bkp
hrdev_19062019_full_sf.bkp

Step-5 : Restore the controlfile

racexample1a ~]$ hrdev
racexample1a ~]$ rman target /
RMAN> 
RMAN> set dbid 3665552429

executing command: SET DBID
RMAN> restore controlfile from "/u03/backups/hrdev/hrdev_19062019_full_cf.bkp";

Starting restore at 20-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1338 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA/HRDEV/CONTROLFILE/current.928.1011408689
output file name=+DATA/HRDEV/CONTROLFILE/current.925.1011408689
Finished restore at 20-JUN-19

RMAN>

Step-6 : Restore the database

SQL> alter database mount;

Database altered.

SQL>


RMAN> allocate channel c1 device type disk;
RMAN> catalog start with '/u03/backups/hrdev';
RMAN>
RUN
{
allocate channel ch1 type disk;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}

Output:

RMAN> RUN
{
allocate channel ch1 type disk;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}2> 3> 4> 5> 6>

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=1338 device type=DISK

Starting restore at 20-JUN-19

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to +DATA/HRDEV/DATAFILE/system.846.940328191
channel ch1: restoring datafile 00002 to +DATA/HRDEV/DATAFILE/sysaux.852.940328101
channel ch1: restoring datafile 00003 to +DATA/HRDEV/DATAFILE/undotbs1.884.940326787
channel ch1: restoring datafile 00004 to +DATA/HRDEV/DATAFILE/undotbs2.883.940326787
channel ch1: restoring datafile 00005 to +DATA/HRDEV/DATAFILE/users.848.940328187
....
channel ch1: restoring datafile 00080 to +DATA/HRDEV/DATAFILE/hr_data.839.940328213
channel ch1: restoring datafile 00081 to +DATA/HRDEV/DATAFILE/hr_index.850.940328147
channel ch1: reading from backup piece /u03/backups/hrdev/hrdev_19062019_full_db_1tu4hbto_1_1.bkp
channel ch1: piece handle=/u03/backups/hrdev/hrdev_19062019_full_db_1tu4hbto_1_1.bkp tag=19-JUN-2019_23-28-42_FULL_DB
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:30:55
Finished restore at 20-JUN-19

released channel: ch1

RMAN>


Step-7 : Recover the database

RMAN> RECOVER DATABASE;

Starting recover at 20-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1338 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=3398
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6102
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6103
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=3399
channel ORA_DISK_1: reading from backup piece /u03/backups/hrdev/hrdev_19062019_full_al_1vu4hct9_1_1.bkp
channel ORA_DISK_1: piece handle=/u03/backups/hrdev/hrdev_19062019_full_al_1vu4hct9_1_1.bkp tag=19-JUN-2019_23-28-42_FULL_AL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_1_seq_6102.789.1011411463 thread=1 sequence=6102
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3398.545.1011411463 thread=2 sequence=3398
channel default: deleting archived log(s)
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_1_seq_6102.789.1011411463 RECID=34952 STAMP=1011411462
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_1_seq_6103.1563.1011411463 thread=1 sequence=6103
channel default: deleting archived log(s)
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3398.545.1011411463 RECID=34953 STAMP=1011411462
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3399.1580.1011411463 thread=2 sequence=3399
channel default: deleting archived log(s)
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3399.1580.1011411463 RECID=34955 STAMP=1011411463
unable to find archived log
archived log thread=2 sequence=3400
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/20/2019 03:37:47
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 3400 and starting SCN of 11981117434031

RMAN>


Step-8 : Open the database


RMAN> ALTER DATABASE OPEN RESETLOGS;

Statement processed

RMAN>


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
HRPRD    READ WRITE

SQL> !hostname
racexample1a.oracle.com

SQL>
SQL> select name ,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
HRPRD    READ WRITE

SQL> alter user sys identified by Pa$$w0rd; -- create the passwordfile as well

User altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

SQL> exit


Step-9 : Proceed to rename the database

$nid TARGET=sys/Pa$$w0rd dbname=hrprd

output:
----------

[oracle@racexample1a dbs]$ nid TARGET=sys/Pa$$w0rd dbname=hrprd

DBNEWID: Release 12.1.0.2.0 - Production on Thu Jun 20 04:17:32 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to database HRDEV (DBID=3665552429)

Connected to server version 12.1.0

Control Files in database:
    +DATA/HRDEV/CONTROLFILE/current.928.1011408689
    +DATA/HRDEV/CONTROLFILE/current.925.1011408689

Change database ID and database name HRDEV to HRPRD? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3665552429 to 4195710300
Changing database name from HRDEV to HRPRD
    Control File +DATA/HRDEV/CONTROLFILE/current.928.1011408689 - modified
    Control File +DATA/HRDEV/CONTROLFILE/current.925.1011408689 - modified
    Datafile +DATA/HRDEV/DATAFILE/system.1418.101141110 - dbid changed, wrote new name
    Datafile +DATA/HRDEV/DATAFILE/sysaux.1339.101141092 - dbid changed, wrote new name
    Datafile +DATA/HRDEV/DATAFILE/undotbs1.933.101140936 - dbid changed, wrote new name
    Datafile +DATA/HRDEV/DATAFILE/undotbs2.1006.101140936 - dbid changed, wrote new name
    Datafile +DATA/HRDEV/DATAFILE/users.1420.101141113 - dbid changed, wrote new name
    ...
    Datafile +DATA/HRDEV/DATAFILE/hr_data.959.101141116 - dbid changed, wrote new name
    Datafile +DATA/HRDEV/DATAFILE/hr_index.1411.101141108 - dbid changed, wrote new name
    Datafile +DATA/HRDEV/TEMPFILE/temp.778.101141163 - dbid changed, wrote new name
    Control File +DATA/HRDEV/CONTROLFILE/current.928.1011408689 - dbid changed, wrote new name
    Control File +DATA/HRDEV/CONTROLFILE/current.925.1011408689 - dbid changed, wrote new name
    Instance shut down

Database name changed to HRPRD.
Modify parameter file and generate a new password file before restarting.
Database ID for database HRPRD changed to 4195710300.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

[oracle@racexample1a dbs]$


Step-10 : Create the new directories in ASM

[oracle@racexample1a dbs]$ grid
[oracle@racexample1a dbs]$ env | grep ORA
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/12.1.0.2/grid

[oracle@racexample1a ]$ sqlplus / as sysasm
SQL>
SQL> alter diskgroup DATA add directory '+DATA/HRPRD';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/DATAFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/TEMPFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/PARAMETERFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/ONLINELOG';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/CONTROLFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/ARCHIVELOG';


-- List all files what are restored recently

$ asmcmd -p
ASMCMD [+DATA/HRDEV/CONTROLFILE] > ls
current.925.1011408689
current.928.1011408689

ASMCMD [+DATA/HRDEV/ONLINELOG] > ls
group_11.1562.1011411631
group_11.542.1011411631
...
group_5.1788.1011411629
group_5.789.1011411629
group_6.1433.1011411631
group_6.773.1011411629


ASMCMD [+DATA/HRDEV/TEMPFILE] > ls
TEMP.778.1011411637

ASMCMD [+DATA/HRDEV/DATAFILE] > ls
HR_DATA.959.101141116
HR_INDEX.1411.101141108
..
SYSAUX.1339.1011410927
SYSTEM.1418.1011411107
UNDOTBS1.933.1011409367
UNDOTBS2.1006.1011409367
USERS.1420.1011411135
ASMCMD [+DATA/HRDEV/DATAFILE] >

Step-11 : Edit the parameter file and change old database name with new one. Create new alias in bash_profile like above

$ vi inithrprd1.ora
– Save and Exit

$ vi .bash_profile

alias hrprd='export ORACLE_SID=hrprd1;export ORACLE_UNQNAME=hrprd;export ORACLE_HOME=$DB_HOME;export PATH=$ORACLE_HOME/bin:/bin:/usr/bin'

$ . .bash_profile
$ hrprd
[oracle@racexample1a dbs]$ env | grep ORA
ORACLE_UNQNAME=hrprd
ORACLE_SID=hrprd1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

[oracle@racexample1a dbs]$ sqlplus / as sysdba

SQL> startup nomount pfile='inithrprd';
ORACLE instance started.

Step-12 : In ASM copy the control file from hrdev directory to /tmp

ASMCMD [+DATA/HRDEV/CONTROLFILE] > cp +DATA/HRDEV/CONTROLFILE/current.925.1011408689 /tmp


Step-13 : Copy the restored control file to target location

[oracle@racexample1a dbs]$ rman target /

RMAN> restore controlfile from '/tmp/current.925.1011408689';   

Starting restore at 20-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1339 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/HRPRD/CONTROLFILE/current.1035.1011433041
output file name=+DATA/HRPRD/CONTROLFILE/current.1038.1011433041
Finished restore at 20-JUN-19

RMAN> quit
Recovery Manager complete.
$


Step-14 : Update the control file location in target parameter file ( inithrprd.ora) and start the database in mount state

already your instance in nomout state

$ sqlplus / as sysdba
SQL> alter database mount;
SQL> alter database open RESETLOGS;

Database altered.

SQL>

step-15 : Oracle 12c onwards, you can move datafiles to new ASM location wven datafiles status 'ONLINE'.

So move all datafiles, redo files to new locations.

SQL>

alter database move datafile '+DATA/HRDEV/DATAFILE/system.1418.1011411107';
alter database move datafile '+DATA/HRDEV/DATAFILE/sysaux.1339.1011410927';
alter database move datafile '+DATA/HRDEV/DATAFILE/undotbs1.933.1011409367';
alter database move datafile '+DATA/HRDEV/DATAFILE/undotbs2.1006.1011409367';
alter database move datafile '+DATA/HRDEV/DATAFILE/users.1420.1011411135';
...
alter database move datafile '+DATA/HRDEV/DATAFILE/hr_data.959.1011411169';
alter database move datafile '+DATA/HRDEV/DATAFILE/hr_index.1411.1011411081';


Same way you can move all redo logs also:
e.g.,
alter database move logfile 'group_11.1562.1011411631';
alter database move logfile 'group_11.542.1011411631';
....


Note: As db_create_file_dest='+DATA' set in parameter file, here target location is not necessary. Automatically it will move to respective locations as per the RAC feature.

Ensure all files moved to new locations.

Now shut down the database and startup.

SQL> shut immediate;
SQL> startup;

Step-16 : Check location of all files once the database is opened

SQL> select name from v$datafile;
SQL> select name from v$tempfile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;

SQL> select * from v$recover_file;

no rows selected

SQL> select distinct status from v$datafile;

STATUS
-------
ONLINE

Step-17 : create new parameter file in ASM location and make enable cluster_database as 'TRUE'

SQL> create spfile='+DATA' from pfile;
SQL> shut immediate;
SQL> startup;
SQL> alter system set cluster_database=TRUE scope=spfile;
SQL> shut immediate;
SQL> startup;

Step-18 : Configure database and instances to cluster

$ srvctl add database -d hrprd -o /u01/app/oracle/product/12.1.0.2/dbhome_1
$ srvctl add instance -d hrprd -i hrprd1 -n racexample1a
$ srvctl add instance -d hrprd -i hrprd2 -n racexample1b

Step-19 : Stop the instance and create init parameter file on both nodes as shown below

$ sqlplus / as sysdba
SQL> shutdown immediate

[Rename exisitng init parameter file and spfile]
$ mv inithrprd1.ora inithrprd1.ora_orig

$ vi inithrprd1.ora
SPFILE='+DATA/HRPRD/PARAMETERFILE/spfile.1411.1011437099'
[Save and Exit]

[SCP the file on node2]
$ scp inithrprd1.ora racexample1b:$ORACLE_HOME/dbs/inithrprd2.ora

Step-20 :  Start the database using srvctl utility

$ srvctl start database -d hrprd

$ srvctl status database -d hrprd
Instance hrprd1 is running on node racexample1a
Instance hrprd2 is running on node racexample1b

$ srvctl start database -d hrprd

$ srvctl status database -d hrprd
Instance hrprd1 is running on node racexample1a
Instance hrprd2 is running on node racexample1b



Jun 6, 2019

Restrict connectivity from other tools to your database

This trigger can help you to restrict connectivity from other tools to your database:

rem -----------------------------------------------------------------------
rem Filename:   RestricTools.sql
rem Purpose:    Restrict your critical production database from using TOAD or PLSQL Developer and other tools.
rem Date:       06-Jun-2019
rem Author:     Gouranga Mohapatra
Rem Remark:     To be created from sys user
rem -----------------------------------------------------------------------

CONNECT / AS SYSDBA;

CREATE OR REPLACE TRIGGER trg_block_tools
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog 
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  -- Don't Check SYS Connections
    AND  rownum = 1;  -- Parallel processes will have the same AUDSID's

  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Third party tools are not allowed on production database');
  END IF;
END;
/

May 1, 2019

ORA-20005: object statistics are locked (stattype = ALL) - Solution

During tuning a query, I found one table has stale statistics. While running gather stats for that table, got below error. Let me demonstrate with a demo table:

SQL> execute dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'EMPLOYEE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

BEGIN dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'EMPLOYEE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1


SQL> 

Analysis:

Verify whether statistics is locked for that table or not.


SQL> col OWNER for a12
SQL> col table_name for a30
SQL> col STATTYPE_LOCKED for a15
SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='EMPLOYEE' and owner='HR';

OWNER        TABLE_NAME                     STATTYPE_LOCKED
------------ ------------------------------ ---------------
HR           EMPLOYEE                           ALL

SQL> 

So, the column "stattype_locked" value is showing "ALL", that means statistics is locked for that table.

Solution Description:

The table statistics can be locked from the further update by using dbms_stats package. This will help to restrict from automatic statistics updation. When you get this error you can check whether your table is locked on further statistics updation using following query. The stattype_locked should be null to update the statistics.

STATTYPE_LOCKED – ALL -> stats are locked for the table
STATTYPE_LOCKED – NULL – > stats are not locked

To Unlock stats:

SQL> EXEC DBMS_STATS.unlock_table_stats('HR','EMPLOYEE');

PL/SQL procedure successfully completed.

SQL> 


Now try to run stats again:


SQL> execute dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'EMPLOYEE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> 

Similarly we can unlock stats for a schema also.

SQL> EXEC DBMS_STATS.unlock_schema_stats('HR');

PL/SQL procedure successfully completed.

Mar 31, 2019

Run sql script with nohup or background

SqlPlus run in background with 'nohup'


Sometimes you have to run a query that takes very long time to complete, and you want to go home with your laptop. If you have access to a server with SQL*Plus, you can run the query there in the background.

We can see some example:

1) Create a .sql file containing your query.

e.g. script.sql (for immediate test)

SET PAGESIZE 50000
SET MARKUP HTML ON TABLE "class=detail cellspacing=0" ENTMAP OFF
SPOOL output.log
# query
select sysdate from dual;

spool off;

2) Now call the query with Username and Password or 'as sysdba' from the OS command prompt.

$ nohup sqlplus username/password @script.sql > file.out 2>&1 & 

$ nohup sqlplus "/ as sysdba" @script.sql > file.out 2>&1 &




Mar 15, 2019

Automation on Grant to Any user/ schema in Oracle database

Some times you may have request from application owners that some uses should have access as soon as any table created on specified schema. To meet this requirement, you can create once database level trigger. It is depend on what kind of access to different user. based on the requirement you need to create some roles and grant those roles to specified to users before creating database level trigger.

Here, I am creating one role with all DML access and one role with read-only ( select) access.
Example: test_rw_role and test_ro_role

SQL> create role test_ro_role;
SQL> create role test_rw_role;

-- Create Trigger

create or replace trigger T_GIVE_GRANTS
        after CREATE on database 
declare 
        l_str1 varchar2(255); 
l_str2 varchar2(255);
        l_job1 number;
l_job2 number;  
obj_own varchar2(30);
obj_name varchar2(30);
str varchar2(255);
begin 
obj_own:=ora_dict_obj_owner;
obj_name:=ora_dict_obj_name;
str:=obj_own||'.'||obj_name;
        if ( ora_dict_obj_type = 'TABLE' )
and ora_dict_obj_owner not in('SYS','SYSTEM','DBSNMP') then 
                l_str1 := 'execute immediate "grant select,insert,update,delete on ' ||str || ' to test_rw_role";'; 
l_str2 := 'execute immediate "grant select on ' ||str || ' to test_ro_role";'; 
                dbms_job.submit( l_job1, replace(l_str1,'"','''') ); 
dbms_job.submit( l_job2, replace(l_str2,'"','''') ); 
        end if; 
end; 


Trigger Created.

========================
--- Testing ---
========================

--- connecting to SYS

SQL> connect / as sysdba
SQL> grant test_ro_role to test;
SQL> grant test_rw_role to scott;

Note: There are two users scott and test are exiting users

---- Connecting to SCOTT schema

SQL> conn scott
Enter password: 
Connected.

SQL> create table scott_tab(id NUMBER GENERATED ALWAYS AS IDENTITY, remark varchar2(10));
SQL> insert into scott_tab(remark) values ('test1');
SQL> commit;
SQL> select * from scott.scott_tab;

        ID REMARK
---------- ----------
         1 test1

-- Connecting to TEST schema

SQL> disc
SQL> conn test/test
Connected.

SQL> select * from scott.scott_tab;

        ID REMARK
---------- ----------
         1 test1

SQL> insert into scott.scott_tab(remark) values ('test2');
insert into scott.scott_tab(remark) values ('test2')
                  *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> create table test_tab(n number);
Table created.

SQL> insert into test_tab values (9999);
1 row created.

SQL> commit;
Commit complete.

SQL> 


---- Connecting to SCOTT schema

SQL> disc
SQL> connect scott
Enter password: 
Connected.
SQL> 
SQL> select * from test.test_tab;

         N
----------
      9999

SQL> insert into test.test_tab values(8888);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test.test_tab;

         N
----------
      9999
      8888

SQL> 


-- Some Debugging:

That, when run in a given user account will schedule a job to grant SELECT on any table created to SCOTT. We use dbms_job to run the grant in the background because this is executing as part of a larger data dictionary transaction and we cannot commit (nor can we use an autonomous transaction - the table is not yet "fully" created). 


-- Before I change the value

SQL> show parameter job_queue_processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
job_queue_processes                  integer     1000
SQL> 

-- Modifying the value 

SQL> alter system set job_queue_processes=10;
System altered.

SQL> show parameter job_queue_processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
job_queue_processes                  integer     60
SQL> 

-- If any pending jobs are there, you can see using below query

SQL> col WHAT for a120;
SQL> select job, what from user_jobs;

       JOB WHAT
---------- ---------------------------------------------------------
         7 execute immediate 'grant select,insert,update on T1 to test_rw_role';
         6 execute immediate 'grant select,insert,update on SAMPLE1 to test_rw_role';
         4 execute immediate 'grant select,insert,update on T1 to test_rw_role';
         2 execute immediate 'grant select,insert,update on T3 to test_rw_role';
         1 execute immediate 'grant select,insert,update on T2 to test_rw_role';

6 rows selected.

SQL> 

-- remove job from queue

SQL> exec DBMS_JOB.REMOVE(1);

PL/SQL procedure successfully completed.

Ref:
https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm
https://docs.oracle.com/database/121/ADMIN/appendix_a.htm#ADMIN11002

Configuring DBMS_JOB

The JOB_QUEUE_PROCESSES initialization parameter specifies the maximum number of processes that can be created for the execution of jobs.

Beginning with Oracle Database 11g, JOB_QUEUE_PROCESSES defaults to 1000. The job coordinator process starts only as many job queue processes as are required, 

based on the number of jobs to run and available resources. You can set JOB_QUEUE_PROCESSES to a lower number to limit the number of job queue processes.


Setting JOB_QUEUE_PROCESSES to 0 disables DBMS_JOB jobs and DBMS_SCHEDULER jobs.

Translate >>