Oracle DB Admin – Page 6

Rebuild Stand By database when PDB restored from restore point/Issue with the PDB

5 years ago by Venkata Sai PavanOracle DB Admin

Hello Readers, In this article, we will see Recovery models in SQL Server. 1. Take backup of the primary database

run {
allocate channel ch1 device type disk  maxpiecesize 2G;
allocate channel ch2 device type disk  maxpiecesize 2G;
backup incremental level 0 cumulative database format ='/rman/dbbackups/DB_BKP/full_%d_%T_df_%f_%s' tag 'INCREMENTAL LEVEL 0';
release channel ch1;
release channel ch2;
}
allocate channel for maintenance type disk;
delete noprompt obsolete device type disk;
release channel;

run {

allocate channel ch1 device type disk maxpiecesize 2G;

allocate channel ch2 device type disk maxpiecesize 2G;

backup incremental level 0 cumulative database format ='/rman/dbbackups/DB_BKP/full_%d_%T_df_%f_%s' tag 'INCREMENTAL LEVEL 0';

release channel ch1;

release channel ch2;

}

allocate channel for maintenance type disk;

delete noprompt obsolete device type disk;

release channel;

2. Create standby control file from primary database.

RMAN> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ForStandbyCTRL.bck';

1	RMAN> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ForStandbyCTRL.bck';

3. Transfer backup pieces along with control file from primary server to standby server.… Read More

How to Create an RMAN Recovery Catalog

5 years ago by Binnary Ajay KumarOracle DB Admin

Hello Readers, In this article, we will see Creation of RMAN Recovery Catalog. Target Database : Catalog Database: Name : AVPROD … Read More

Oracle : Important Commands for Dataguard (SecureCRT)

5 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Commands for Dataguard (SecureCRT) Menubar Name : Dataguard Tab Name : Instance_info

set linesize 300 
col machine for a60 
select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; 

NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY

set linesize 300

col machine for a60

select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance;

NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY

Tab Name : DG_GAP

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", 
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" 
FROM 
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)  
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)  
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
WHERE 
ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1; 

select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied; 

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  44791                 44791          0
         2                  42354                 42354          0
         3                  40223                 40223          0

         

SQL> 
   THREAD# APPLIED   MAX(SEQUENCE#)
---------- --------- --------------
         1 IN-MEMORY          44791
         1 NO                 39800
         1 YES                44790
         2 NO                 38782
         2 YES                42354
         3 NO                 38673
         3 YES                40223

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied;

Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

1 44791 44791 0

2 42354 42354 0

3 40223 40223 0

SQL>

THREAD# APPLIED MAX(SEQUENCE#)

---------- --------- --------------

1 IN-MEMORY 44791

1 NO 39800

1 YES 44790

2 NO 38782

2 YES 42354

3 NO 38673

3 YES 40223

Tab Name : ERR_DEST

set linesize 300 
col dest_name for a30 
col error for a70 
select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4); 
select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1; 

   DEST_ID DEST_NAME                      STATUS    ERROR
---------- ------------------------------ --------- --------------
         1 LOG_ARCHIVE_DEST_1             VALID
         2 LOG_ARCHIVE_DEST_2             INACTIVE
         3 LOG_ARCHIVE_DEST_3             INACTIVE
         4 LOG_ARCHIVE_DEST_4             DEFERRED

SQL> 
   INST_ID DEST_NAME                      STATUS    ERROR
---------- ------------------------------ --------- --------------
         1 LOG_ARCHIVE_DEST_1             VALID

set linesize 300

col dest_name for a30

col error for a70

select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4);

select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1;

DEST_ID DEST_NAME STATUS ERROR

---------- ------------------------------ --------- --------------

1 LOG_ARCHIVE_DEST_1 VALID

2 LOG_ARCHIVE_DEST_2 INACTIVE

3 LOG_ARCHIVE_DEST_3 INACTIVE

4 LOG_ARCHIVE_DEST_4 DEFERRED

SQL>

INST_ID DEST_NAME STATUS ERROR

---------- ------------------------------ --------- --------------

1 LOG_ARCHIVE_DEST_1 VALID

Tab Name : Date Format

alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

1	alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

Tab Name :… Read More

Autonomous Health Framework

5 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, In this article, we will see the Autonomous Health Framework. AHF includes number of diagnostic tools in a single bundle and helps us to gather various types og information like upgrading info. , oracheck….. AHF combines Trace file analyzer(TFA), orachk etc.., which oracle… Read More

Oracle : Important Queries for Parallel Feature (SecureCRT)

5 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Queries for Parallel Feature (SecureCRT). Menubar Name : Parallel Tab Name : Create table show parameter parallel_max_servers

create table owner.tablename_bkp as select /*+ parallel(tablename,64) */ * from owner.tablename ;

1	create table owner.tablename_bkp as select /+ parallel(tablename,64) / * from owner.tablename ;

create table vinod.tablename_bkp as select /*+ parallel(EMP,4) */ * from SCOTT.EMP;

1	create table vinod.tablename_bkp as select /+ parallel(EMP,4) / * from SCOTT.EMP;

Tab Name : Finding_Parallel_Queries

set linesize 300  
col username for a19  
col terminal for a15  
col osuser for a15  
col program for a45 
col time for a25

set linesize 300

col username for a19

col terminal for a15

col osuser for a15

col program for a45

col time for a25

select inst_id,sid,sql_id,program,username,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') time,TERMINAL,OSUSER   
from gv$session where program like '%P0%' and sql_id is not null;   

   INST_ID        SID SQL_ID        PROGRAM                                       USERNAME            TIME                      TERMINAL        OSUSER
---------- ---------- ------------- --------------------------------------------- ------------------- ------------------------- --------------- ---------------
         1         58 29d991q9rq5kn oracle@awsserver003 (P000)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         1         59 29d991q9rq5kn oracle@awsserver003 (P001)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         2         37 29d991q9rq5kn oracle@awsserver004 (P001)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         2        207 29d991q9rq5kn oracle@awsserver004 (P000)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle

select inst_id,sid,sql_id,program,username,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') time,TERMINAL,OSUSER

from gv$session where program like '%P0%' and sql_id is not null;

INST_ID SID SQL_ID PROGRAM USERNAME TIME TERMINAL OSUSER

---------- ---------- ------------- --------------------------------------------- ------------------- ------------------------- --------------- ---------------

1 58 29d991q9rq5kn oracle@awsserver003 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle

1 59 29d991q9rq5kn oracle@awsserver003 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle

2 37 29d991q9rq5kn oracle@awsserver004 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle

2 207 29d991q9rq5kn oracle@awsserver004 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle

Tab Name : Select Query with Parallel Option… Read More

Oracle: Important commands for Lock/Unlock Stats and Backup/Restore Old Stats (SecureCRT)

5 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle: Important commands for Lock/Unlock Stats and Backup/Restore Old Stats (SecureCRT). MenuBar Name : Lock/Unlock Stats and Backup/Restore Old Stats Tab Name : Check Stats for Table

--example : 'DEPT','EMP','SALGRADE' 
set linesize 300 
set pagesize 100  
col owner for a15  
col LAST_ANALYZED for a30 
col table_name for a40 
select OWNER,TABLE_NAME ,  NUM_ROWS , BLOCKS , EMPTY_BLOCKS , 
CHAIN_CNT, AVG_ROW_LEN 
CACHE, to_char(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') LAST_ANALYZED 
from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'; 

Enter value for selected_tables_check_example: 'TEST'
Enter value for owner: VINOD
old   4: from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'
new   4: from dba_tables where table_name in ('TEST') and owner='VINOD'

OWNER           TABLE_NAME                                 NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT      CACHE LAST_ANALYZED
--------------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------- ------------------------------
VINOD           TEST                                              2          5            0          0          3 18-FEB-2021 22:00:21

--example : 'DEPT','EMP','SALGRADE'

set linesize 300

set pagesize 100

col owner for a15

col LAST_ANALYZED for a30

col table_name for a40

select OWNER,TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS ,

CHAIN_CNT, AVG_ROW_LEN

CACHE, to_char(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') LAST_ANALYZED

from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER';

Enter value for selected_tables_check_example: 'TEST'

Enter value for owner: VINOD

old 4: from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'

new 4: from dba_tables where table_name in ('TEST') and owner='VINOD'

OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT CACHE LAST_ANALYZED

--------------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------- ------------------------------

VINOD TEST 2 5 0 0 3 18-FEB-2021 22:00:21

Tab Name : Backup Existing Stats

exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');
exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');
select count(*) from vinod.TEST_STAT;

SQL> exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');

PL/SQL procedure successfully completed.

SQL> select count(*) from vinod.TEST_STAT;

  COUNT(*)
----------
         2

exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');

exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');

select count(*) from vinod.TEST_STAT;

SQL> exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016');

PL/SQL procedure successfully completed.

SQL> select count(*) from vinod.TEST_STAT;

COUNT(*)

----------

Tab… Read More

Oracle 19c Software Installation On Linux 7.6

5 years ago by Binnary Ajay KumarOracle 19c

In this article, we will see Oracle 19c (19.3) Software Installation On Linux 7.6 Click here to Download Oracle 19c Software from Oracle Support. Create a directory for ORACLE_HOME

[oracle@oracle19c ~]$ mkdir -p /oracle/app/product/19.3.0/dbhome_1

1	[oracle@oracle19c ~]$ mkdir -p /oracle/app/product/19.3.0/dbhome_1

Unzip the oracle 19c software

[oracle@oracle19c ~]$ unzip -d /oracle/app/product/19.3.0/dbhome_1/LINUX.X64_193000_db_home.zip

1	[oracle@oracle19c ~]$ unzip -d /oracle/app/product/19.3.0/dbhome_1/LINUX.X64_193000_db_home.zip

Goto Oracle home location and run the ./runinstaller

[oracle@oracle19c dbhome_1]$ ./runInstaller 
Launching Oracle Database Setup Wizard...

1 2	[oracle@oracle19c dbhome_1]$ ./runInstaller Launching Oracle Database Setup Wizard...

Start… Read More

Transport Plan from One Database to Another Database in Oracle

5 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, In this article, we will see the Transport Plan from One Database to Another Database in Oracle . Transport Good Execution Plan(Hash Plan) from one database to another database Requirement: ETL was taking longer period of time in Prod database where as in… Read More

STANDALONE to RAC Conversion

5 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, In this article, we will see the STANDALONE to RAC Conversion. 1)Take the existing database backup using the crontab job 2) Take the database information. (i.e datafile,tempfile,redolog & Controlfile) 3) Take the pfile backup to any location from spfile. 4) Export the SID… Read More

Oracle : Important Queries to check Table Fragmentation (SecureCRT)

5 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Queries to check Table Fragmentation (SecureCRT). Menubar Name : Fragmentation Tab name : Fragmented_tablesList in HTML

col spoolname new_value spoolname 
select 'Fragmented_tables'||to_char(sysdate, 'YYYYMONDD')||'.html' spoolname from dual; 
spool '&spoolname' 
set pagesize 200 
set markup html on 
WITH STAT_SPACE AS( 
  SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE 
  FROM   DBA_TABLES  
  WHERE  OWNER='SCOTT'), 
SEG_SPACE AS ( 
  SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB  
  FROM DBA_SEGMENTS  
  WHERE 1=1 
  AND OWNER='SCOTT' 
  AND SEGMENT_TYPE='TABLE') 
SELECT sesp.tablespace_name, StSp.TABLE_NAME,  sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) claim_gb 
FROM   STAT_SPACE STSP, SEG_SPACE SESP 
WHERE  abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) > 1 
AND    STSP.TABLE_NAME = SESP.SEGMENT_NAME 
AND    STSP.OWNER = SESP.OWNER 
ORDER BY CLAIM_GB desc; 
spool off 
exit

col spoolname new_value spoolname

select 'Fragmented_tables'||to_char(sysdate, 'YYYYMONDD')||'.html' spoolname from dual;

spool '&spoolname'

set pagesize 200

set markup html on

WITH STAT_SPACE AS(

SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE

FROM DBA_TABLES

WHERE OWNER='SCOTT'),

SEG_SPACE AS (

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB

FROM DBA_SEGMENTS

WHERE 1=1

AND OWNER='SCOTT'

AND SEGMENT_TYPE='TABLE')

SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) claim_gb

FROM STAT_SPACE STSP, SEG_SPACE SESP

WHERE abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) > 1

AND STSP.TABLE_NAME = SESP.SEGMENT_NAME

AND STSP.OWNER = SESP.OWNER

ORDER BY CLAIM_GB desc;

spool off

exit

Output : Tab name : BLOCKS Size

set linesize 300 
col table_name for a30 
col "Blocks Size" for a30 
select table_name,round((blocks*8),2)||' KB' "Blocks Size",(round((blocks*8),2))/1024/1024 ||' GB' "Blocks Size" 
from dba_tables 
where owner='&OWNER' and table_name = upper('&table_name'); 


TABLE_NAME                     Blocks Size                    Blocks Size
------------------------------ ------------------------------ ------------------------------
EMPTEST123                     1231264 KB                     1.174224853515625 GB

set linesize 300

col table_name for a30

col "Blocks Size" for a30

select table_name,round((blocks*8),2)||' KB' "Blocks Size",(round((blocks*8),2))/1024/1024 ||' GB' "Blocks Size"

from dba_tables

where owner='&OWNER' and table_name = upper('&table_name');

TABLE_NAME Blocks Size Blocks Size

------------------------------ ------------------------------ ------------------------------

EMPTEST123 1231264 KB 1.174224853515625 GB

Tab name : Rows Size

set linesize 300 
select table_name,round((num_rows*avg_row_len/1024),2)||' KB' "Rows Size",(round((num_rows*avg_row_len/1024),2))/1024/1024||' GB' "Rows Size" 
from dba_tables 
where owner='&OWNER' and table_name = upper('&table_name'); 

TABLE_NAME                     Rows Size                                                                Rows Size
------------------------------ ------------------------------------------------------------------------ ----------------------------
EMPTEST123                     10597.63 KB                                                              .0101066875457763671875 GB

set linesize 300

select table_name,round((num_rows*avg_row_len/1024),2)||' KB' "Rows Size",(round((num_rows*avg_row_len/1024),2))/1024/1024||' GB' "Rows Size"

from dba_tables

where owner='&OWNER' and table_name = upper('&table_name');

TABLE_NAME Rows Size Rows Size

------------------------------ ------------------------------------------------------------------------ ----------------------------

EMPTEST123 10597.63 KB .0101066875457763671875 GB

Tab name… Read More