APPSDBA Data Lake

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

  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 revoke the access: SQL&g

 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 atnsid2 is running on node shsdbdr02 4th Step: Enable archive log and Flashback database [oratansid@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 – Production  Version 19.14.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.14.0.0.0 Before Enable flashback first we need to enable archivelog dest a

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

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

 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@shsebs121db01 ~]$ sqlplus "/as sysdba" SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 18 07:54:15 2023 Version 19.14.0.0.0 Copyright (c) 1982, 2021, Oracle.  All rights reserved. Connected t

  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