Posts

Showing posts from February, 2023

How to extract the DB LINKS DDL

S cript to extract the DB Links DDL with the encrypted password. SET LONG 20000 SELECT DBMS_METADATA.GET_DDL('DB_LINK',link.db_link,link.owner) FROM dba_db_links link; Another way to get the DDL is as below: SQL> set pages 500 SQL> set long 20000 SQL> set lin 1000 SQL>SELECT 'SELECT dbms_metadata.get_ddl(''DB_LINK'',''' || db_link || ''',''' || owner || ''') as "DB Link DDL" FROM dual;' AS "Query for DDL" FROM dba_db_links WHERE db_link IN ('& DB1LINK '); SQL> set long 1000000 linesize 1000 pagesize 0 feedback off trimspool on verify off serveroutput on SQL> select dbms_metadata.get_ddl('DB_LINK','&link_name') from dual; SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10) ||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10) ||'connect to ' || A.U...
Post a Comment
Read more

How to Grant Quota Unlimited on Tablespace to User

  To grant quota on tablespace to users there are two options. Specific Tablespace SQL> create user webuser identified by 'backup hash key' default  tablespace USERS temp tablespace TEMP; User created. SQL> alter user webuser  quota  unlimited on users; User altered. SQL> column tablespace_name format a30; SQL> column "QUOTA(GB)" format a10; SQL> select tablespace_name, case max_bytes when -1 then 'UNLIMITED' else to_char(max_bytes/1024/1024/1024) end "QUOTA(GB)" from dba_ts_quotas where username = 'WEBUSER'; TABLESPACE_NAME      QUOTA(GB) -------------------- ---------- USERS                UNLIMITED Revoke Tablespace Quota To quota off, just use 0 to revoke its usage on the tablespace. SQL> alter user webuser quota 0 on USERS; User altered. To check as below: SQL> select * from dba_ts_quotas where username = 'WEBUSER' and tablespace_name = 'USERS'; no rows selected Another way to revok...
Post a Comment
Read more

How to enable Flashback Database In Oracle 19c RAC Database

 Flashback Database In Oracle 19c RAC Database 1st Step: Check the status.       SQL> select log_mode,flashback_on from gv$database;                LOG_MODE FLASHBACK_ON                ———— ——————                NOARCHIVELOG NO                NOARCHIVELOG NO 2nd Step: Stop the database service.           [oratansid@shsdbdr01 ~]$ srvctl stop database -d tansid 3rd Step: Start the database in mount state.           [oratansid@shsdbdr01 ~]$ srvctl status database -d tansid                          Instance tansid1 is running on node shsdbdr01                          Instance atn...
Post a Comment
Read more

How to Flush Bad SQL Plan from Shared Pool

At times required to flush the BAD SQL_PLAN from shared_pool so that the new better execution plan can be picked by SQL_ID STEP 1: FIND ADDRESS AND HASH_VALUE OF SQL_ID SQL> select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '97s9z1zzpsp99%'; ADDRESS          HASH_VALUE ---------------- ---------- 000000085FD77AF7  108321884   STEP 2 : PURGE THE PLAN FROM SHARED POOL SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77AF7, 108321884', 'C'); PL/SQL procedure successfully completed.   STEP 3: VERIFY SQL> select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '97s9z1zzpsp99%'; no rows selected ‘C’ (for cursor) or ‘S’ (for SQL) BELOW SCRIPT CAN BE USED FOR THE SAME: SELECT inst_id, loaded_versions, invalidations, address, hash_value FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1; SELECT inst_id, child_number, plan_hash_value, executions, is_shareable FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1...
Post a Comment
Read more

Steps to run SQL Tuning Advisor For a sql_id

To get the tuning recommendations we run SQL tuing advisor for a sql statement or a query. It might give suggestion to create few indexes or accepting a SQL profile also. Diagnostic and Tuning license would be required for the same. Here are the steps how to run sql tuning advisor against sql_Id. Suppose the sql id is – 97s9z1zzpsp99 Step 1. Create Tuning Task DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (sql_id => '97s9z1zzpsp99', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 500, task_name => '97s9z1zzpsp99_tuning_sid1', description => 'Tuning task1 for statement 97s9z1zzpsp99'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / Step 2. Execute Tuning task: EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '97s9z1zzpsp99_tuning_sid1'); Step 3. Get the Tuning advisor report. set long 6553600 set longchunksize 10000 set linesize 1000 se...
Post a Comment
Read more

Step By Step How to Recreate Data guard Broker Configuration

 Parameters used dg_broker_start dg_broker_config_file1 dg_broker_config_file2 ALTER SYSTEM SET DG_BROKER_START=FALSE scope=both; [ortnbtyi@shsebs121db01 dbs]$ rm -rf dr2TANSID_P1.dat dr1TANSID_P1.dat [root@shsebs121db01 bin]# su - ororgrid Last login: Sat Feb 18 07:44:16 IST 2023 [ororgrid@shsebs121db01 ~]$ asmcmd ASMCMD> ls ARCH/ CRS/ FRA/ REDO/ TNBSID_DATA1/ Type  Redund  Striped  Time  Sys  Name                              Y    TANSID/                              Y    TANSID_P1/ ASMCMD> cd TANSID_P1/ ASMCMD> pwd +ARCH/TANSID_P1 ASMCMD> mkdir DGCONFIG ASMCMD> cd DGCONFIG ASMCMD> pwd +ARCH/TANSID_P1/DGCONFIG ASMCMD> exit [ororgrid@shsebs121db01 ~]$ exit logout [root@shsebs121db01 bin]# su - ortnbtyi Last login: Fri Feb 17 19:21:14 IST 2023 on pts/0 [ortnbtyi@shsebs1...
Post a Comment
Read more

Enable and Disable Fast-Start-Failover Data Guard Broker

  Enable Fast-Start-Failover Data Guard Broker Can be enabled automatic failover using Fast-Start-Failover Observer with Data Guard broker. Configure FSFO The StaticConnectIdentifier parameter must be set both in primary and standby On primary(tansid_p1): dgmgrl sys/sys@tansid_p1 DGMGRL> show database tansid_p1 StaticConnectIdentifier; DGMGRL> show database tansid_p2 StaticConnectIdentifier; SQL> ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.71)(PORT=1521))'; FastStartFailoverTarget: we need to pair physical standby with primary to let Fast Start Failover  On primary (tansid_p1): ==================== dgmgrl sys/****@tansid_p1 DGMGRL> SHOW FAST_START FAILOVER DGMGRL> EDIT DATABASE tansid_p1 SET PROPERTY FastStartFailoverTarget = 'tansid_p1'; DGMGRL> EDIT DATABASE proddb_p2 SET PROPERTY FastStartFailoverTarget = 'tansid_p2'; DGMGRL> show database verbose tansid_p1; DGMGRL> show database verbose tansid_p2; FastStar...
Post a Comment
Read more

Step by Step recover after a failed switchover

 Issue  Description We might find in a situation where your switchover fails and left environment with two physical standby databases.   Solution   1.       Logon (as sysdba) to the instance that was your Primary database instance before the switchover. 2.       Confirm the database role. SQL> select database_role from v$database;   DATABASE_ROLE ------------------------------- PHYSICAL STANDBY [ortnbtyi@shsebs121db01 ~]$ srvctl stop database -d tansid_p1 [ortnbtyi@shsebs121db01 ~]$ srvctl start database -d tansid_p1 -o mount [ortnbtyi@shsebs121db01 ~]$ sqlplus "/as sysdba" SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 18 11:59:45 2023 Version 19.14.0.0.0 Copyright (c) 1982, 2021, Oracle.  All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.14.0.0.0 SQL>  alter database recover managed standby database canc...
Post a Comment
Read more