EasyReliableDBA

Software Training and dumps for Exam are available on www.easyreliable.com To download dumps. Please visit Links and Just pay $5/Rs300 http://easyreliable.com/easydumps/ This blog is Oracle DBA blog which cover Oracle RAC(10g,11g ,12c and 19c), Dataguard, RMAN,Backup&Recovery,Troubleshooting and Performance Tuning and other topics like Oracle Cloud,MongoDB,Golden Gate and Exadata.Please subscribe below to get update on my blog.We also provide Online SoftwareTraining and DBA Consulting

Thursday, 19 September 2024

Oracle Database Daily health check commands

1. Check Tablespace USAGES

alter session set "_hash_join_enabled"=true;

set pagesize 100

set lines 200

COLUMN free_space_mb format 99999990.00

COLUMN allocated_mb format 99999990.00

COLUMN used_mb format 99999990.00

COLUMN percent_used format 00.00

SELECT SUBSTR(df.tablespace_name,1,20) tablespace_name,

(df.bytes) / 1024 / 1024 allocated_mb,((df.bytes)-nvl(dfs.bytes,0))/1024/1024 used_mb,

NVL ((dfs.bytes) / 1024 / 1024, 0) free_space_mb,round( ((df.bytes-nvl(dfs.bytes,0))/df.bytes * 100),2) percent_used

FROM ( select sum(bytes) bytes,tablespace_name from dba_data_files group by tablespace_name) df,

( select sum(bytes) bytes , tablespace_name from dba_free_space group by tablespace_name) dfs

WHERE df.tablespace_name = dfs.tablespace_name

and df.tablespace_name='CUSTOM'

ORDER BY percent_used ;

If necessary, add datafile(s) to the tablespace:

For ASM

ALTER TABLESPACE CUSTOM

ADD DATAFILE '+EASYDATA_DG2/' size 30g;

For File System

ALTER TABLESPACE CUSTOM

ADD DATAFILE '/EASYFILE_bc/EASY/CUSTOM_e3_EASY.dbf' size 30g;

2. Check TEMPORARY Tablespace:

SET LINESIZE 200

COL TABLESPACE_NAME FORMAT A20

SELECT TABLESPACE_NAME,SUM(BYTES_USED/1024/1024) "USED_MB",SUM(BYTES_FREE/1024/1024) "FREE_MB",

SUM(BLOCKS_FREE) "FREE_BLOCKS"

FROM V$TEMP_SPACE_HEADER

WHERE TABLESPACE_NAME='SORT_TBLSPC' GROUP BY TABLESPACE_NAME;

select sysdate dtstamp,

s.tablespace_name,

d.tbspc_mb,

s.total_blocks*8192/1024/1024 temp_tot_mb,

s.used_blocks*8192/1024/1024 temp_used_mb,

s.free_blocks*8192/1024/1024 temp_free_mb

from v$sort_segment s,

(select tablespace_name,sum(bytes/1024/1024) tbspc_mb

from dba_data_files

group by tablespace_name

union

select tablespace_name,sum(bytes/1024/1024) tbspc_mb

from dba_temp_files

group by tablespace_name) d

where s.tablespace_name=d.tablespace_name;

Add TEMP data file if necessary:

column file_name format a50

select file_name,TABLESPACE_NAME,bytes/1024/1024 from dba_temp_files where TABLESPACE_NAME='TEMP' order by 1 ;

alter database tempfile '/ora/TESTDB/temp/oradata/TESTDB/tivoli_temp_01.dbf' resize 4000m

ALTER TABLESPACE SORT_TBLSPC

ADD TEMPFILE '/EASYFILE_bc/EASY/SORT_TBLSPC_t1.dbf' size 4g;

FOR ASM

ALTER TABLESPACE SORT_TBLSPC

ADD TEMPFILE 'EASYTEMP_DGNEW' size 4g;

3.Check Session and Table Lock on Database

To check Session Lock

SELECT DECODE(request,0,'Holder: ','Waiter: ') ||

sid sess, id1, id2, lmode, request, type

FROM V$LOCK

WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)

ORDER BY id1, request;

Object level lock

select a.sid, a.serial#,OBJECT_NAME

from v$session a, v$locked_object b, dba_objects c =

where b.object_id = c.object_id

and a.sid = b.session_id;

Select * from (

Select c.owner,c.object_name,

c.object_type,b.sid,

b.serial#,b.status,

b.osuser,b.machine

from v$locked_object a ,v$session b, dba_objects c

where b.sid = a.session_id

and a.object_id = c.object_id);

select p.spid,s.sid,s.serial#,s.username,to_char(s.logon_time,'dd-mon:hh24:mi'),machine,module from v$session s, v$process p

where p.addr=s.paddr and s.sid =’&sid’

ALTER SESSION SET "_HASH_JOIN_ENABLED"=TRUE;

select a.sid,a.serial#,b.lmode, ROUND(b.ctime/60) "L Time" from v$session a, v$lock b,v$locked_object c,dba_objects d

where b.sid=a.sid and b.SID=c.SESSION_ID and c.OBJECT_ID=d.object_id and d.object_name =(select table_name from

dba_synonyms where lower(synonym_name)='&SYN_NAME');

COL PROGRAM FORMAT A40

COL MACHINE FORMAT A20

COL USERNAME FORMAT A10

SELECT PROGRAM, MACHINE, USERNAME FROM V$SESSION WHERE SID=&SID;

col pid format 999999999

SELECT S.SID,S.SERIAL#,L.PID,L.CURRENT_OPTION_ID,L.HOST_NAME

FROM V$SESSION S,LGI L WHERE TO_NUMBER(L.PID)=TO_NUMBER(S.PROCESS)AND L.HOST_NAME=S.MACHINE

AND S.SID=&SID;

SELECT SERIAL#, SID FROM V$SESSION WHERE SID=&SID;

ALTER SYSTEM KILL SESSION '&SID,&SERIAL';

4.Check Long running Query/ Full Table Scan Checking:

alter session set "_hash_join_enabled"=true;

set linesize 200

col sid format 999999

col serial# format 9999

col opname format a25

col units format a8

col start format a8

col target format a30

col In_Minutes format 999

col % format 999

select sid,opname,target,to_char(start_time,'HH24:MI:SS') "START",ELAPSED_SECONDS/60

"In_Minutes",totalwork,sofar,units,round((sofar/totalwork)*100) "%",sql_id

from v$session_longops where (sofar/totalwork)*100<100 order by start_time ;

5.Check Problematic Events on Database

set pagesize 30

select event,count(event) from v$session group by event order by 2;

set linesize 200

col sid format 99999

col SL# format 99999

col PROC FORMAT a10

col MACHINE format a10

col UNAME format a10

col OSUSER format a8

col TERMINAL format a8

col PROGRAM format a32

col operation format a16

col options format a15

col CN format 99999

select a.SID,a.SERIAL# "SL#",a.PROCESS "PROC",a.SQL_ID,b.CHILD_NUMBER "CN" ,a.username "UNAME",a.OSUSER,a.PROGRAM,

b.OPERATION,b.OPTIONS,sum(b.CPU_COST) "CPU_COST",sum(b.IO_COST) "IO_COST"

from v$session a, v$sql_plan b

where a.SQL_ID=b.SQL_ID

and a.STATUS='ACTIVE'

and a.event='&event_name'

and b.options is not null

group by a.SID,a.SERIAL#,a.PROCESS,a.SQL_ID,a.username,a.OSUSER,a.PROGRAM,b.OPERATION,b.OPTIONS,b.CHILD_NUMBER

order by sum(b.CPU_COST);

6. Resource Utilization:

SET LINESIZE 200

set pagesize 200

select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE

,(INITIAL_ALLOCATION-CURRENT_UTILIZATION) "IN_POOL"

from v$resource_limit

where CURRENT_UTILIZATION>0

and resource_name in ('sessions','processes','parallel_max_servers')

group by RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE

order by CURRENT_UTILIZATION desc;

select * from v$resource_limit where CURRENT_UTILIZATION>0;

7) To check Archive log gap and applied status on standby Server:

alter session set "_hash_join_enabled"=true;

select STATUS,PROCESS,SEQUENCE#,BLOCK#,BLOCKS from v$managed_standby;

select max(sequence#)"Last Sequence" from v$loghist;

select max(SEQUENCE#)"Applied Sequence" from v$archived_log where APPLIED='YES' and dest_id=2;

set linesize 200

col INST_ID format 99

col process format 99

COL LOGSEQ FORMAT 9999999

col state format a8

col error format a10

col DEST_NAME format a20

col RECOVERY_MODE format a10

col process format 99

col ArcGap format 99

select ap.INST_ID,ad.DEST_NAME,ap.PROCESS,ap.STATUS,ap.LOG_SEQUENCE "LOGSEQ",ap.STATE,ad.STATUS,ad.ARCHIVED_SEQ#,

ad.APPLIED_SEQ#,(select max(sequence#) from V$loghist)-

(select APPLIED_SEQ# from GV$ARCHIVE_DEST_STATUS where APPLIED_SEQ#>0)"ArcGap",ad.ERROR

from GV$ARCHIVE_DEST_STATUS ad, GV$ARCHIVE_PROCESSES ap,gv$log a

where ad.ARCHIVED_SEQ#=a.SEQUENCE# and ap.LOG_SEQUENCE=ad.ARCHIVED_SEQ# and ap.LOG_SEQUENCE>0 order by ad.DEST_NAME;

8) Archival Generation History

set linesize 200

column LOGDATE format a12

col MidN format 9999

col 1AM format 99999

col 2AM format 99999

col 3AM format 99999

col 4AM format 99999

col 5AM format 99999

col 6AM format 99999

col 7AM format 99999

col 8AM format 99999

col 9AM format 99999

col 10AM format 9999

col 11AM format 9999

col Noon format 9999

col 1PM format 99999

col 2PM format 99999

col 3PM format 99999

col 4PM format 99999

col 5PM format 99999

col 6PM format 99999

col 7PM format 99999

col 8PM format 99999

col 9PM format 99999

col 10PM format 9999

col 11PM format 9999

select to_char(first_time,'mm-dd-yyyy') logdate,

sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",

sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",

sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",

sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",

sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",

sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",

sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",

sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",

sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",

sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",

sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",

sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",

sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",

sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",

sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",

sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",

sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",

sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",

sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",

sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",

sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",

sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",

sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",

sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"

from v$log_history

group by to_char(first_time,'mm-dd-yyyy')

order by 1;

9.Performance Troubleshooting Related Query

Execution Plan from cursor

select * from table ( dbms_xplan.display_cursor('&sql_id'))

Execution Plan from AWR

Check execution plan from the AWR using below query

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

select * from table ( dbms_xplan.display_awr('&sql_id'))

To check child number for sql_id

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

SQL_ID and child number of the SQL are known

A SQL_ID can have multiple children with different characteristics. You can identify the child number by selecting from V$SQL as follows:

select sql_id, child_number, sql_text from v$sql where sql_id='&sql';

SELECT sql_id, hash_value, child_number, SUBSTR(sql_text,1,40) Text

FROM v$sql

WHERE sql_text LIKE '%&Some_Identifiable_String%'

The first child for a cursor will have a CHILD_NUMBER of zero.

Once you have this information, you can use dbms_xplan.display_cursor as follows:

set linesize 150

set pagesize 2000

select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));

To check number of Executions

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

select sql_id,plan_hash_value,executions from gv$sql where sql_id='&SQL_ID'

To find out change in SQL plan for SQL_ID

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

The below script will help you find the SQL plan ID for previous executions and check if there is any change in SQL plan ID.

planchange.sql

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

col begin_interval_time for a30

break on plan_hash_value on startup_time skip 1

select ss.snap_id, ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,

nvl(executions_delta,0) execs,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, version_count

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where s.sql_id = nvl('&sql_id','4dqs2k5tynk61')

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0 --and plan_hash_value=3099046451

order by 1, 2, 3

select s.sql_text, s.sql_id, s.plan_hash_value from v$sql s where s.plan_hash_value = '&plan_hash_value';

select sql_id,LAST_ACTIVE_TIME,PLAN_HASH_VALUE,ROWS_PROCESSED,EXECUTIONS,END_OF_FETCH_COUNT,LOADS,ELAPSED_TIME/power(10,6)/60,SORTS, PX_SERVERS_EXECUTIONS

from V$SQLSTATS where sql_id='&SQLID';

--Look at the V$SQLSTATS view, which gives information about currently or recently run SQL statements. If you wanted

to get the five recent SQL statements that performed the most disk I/O, you could issue the following query:

The V$SQLSTATS view is meant to help more quickly find information on resource-consuming SQL statements. V$SQLSTATS

has the same information as the V$SQL and V$SQLAREA views, but V$SQLSTATS has only a subset of columns of the other

views. However, data is held within the V$SQLSTATS longer than either V$SQL or V$SQLAREA

SELECT sql_text, disk_reads FROM

(SELECT sql_text, buffer_gets, disk_reads, sorts,

cpu_time/1000000 cpu, rows_processed, elapsed_time

FROM v$sqlstats

ORDER BY disk_reads DESC)

WHERE rownum <= 5;

You can use the V$SQL_MONITOR view to see real-time statistics of currently running SQL and see the resource consumption

used for a given query based on such statistics as CPU usage, buffer gets, disk reads, and elapsed time of the query. Let’s

first find a current executing query within our database:

SELECT sid, sql_text FROM v$sql_monitor

WHERE status = 'EXECUTING'

SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds FROM v$sql_monitor WHERE SID=100

AND status = 'EXECUTING';

SELECT * FROM (

SELECT sql_id, to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start,

sql_exec_id, sum(buffer_gets) buffer_gets,

sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_secs

FROM v$sql_monitor

WHERE sql_id = '21z86kt10h3rp'

GROUP BY sql_id, sql_exec_start, sql_exec_id

ORDER BY 6 desc)

WHERE rownum <= 5

Keep in mind that if a statement is running in parallel, one row will appear for each parallel thread for the query,

including one for the query coordinator. However, they will share the same SQL_ID, SQL_EXEC_START, and

SQL_EXEC_ID values. In this case, you could perform an aggregation on a particular statistic, if desired. See the

following example for a parallelized query, along with parallel slave information denoted by the PX_SERVER# column:

SELECT sql_id, sql_exec_start, sql_exec_id, px_server# px#, disk_reads,

cpu_time/1000000 cpu_secs, buffer_gets

FROM v$sql_monitor

WHERE status = 'EXECUTING'

ORDER BY px_server#;

Then, to perform a simple aggregation for a given query, in this case, our parallelized query, the aggregation is

done on the three key columns that make up a single execution of a given SQL statement:

SELECT sql_id,sql_exec_start, sql_exec_id, sum(buffer_gets) buffer_gets,

sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_seconds

FROM v$sql_monitor

WHERE sql_id = '&SQL_ID'

GROUP BY sql_id, sql_exec_start, sql_exec_id;

If you wanted to perform an aggregation for one SQL statement, regardless of the number of times is has been

executed, simply run the aggregate query only on the SQL_ID column, as shown here:

SELECT sql_id, sum(buffer_gets) buffer_gets,

sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_seconds

FROM v$sql_monitor

WHERE sql_id = '&SQL_ID'

GROUP BY sql_id;

=========

if SQL is no longer available in v$sql, you can find it in the AWR history views: DBA_HIST_SQLTEXT and DBA_HIST_SQLSTAT

SELECT s.sql_id, s.plan_hash_value,t.sql_text,s.snap_id FROM dba_hist_sqlstat s,

dba_hist_sqltext t

WHERE s.dbid = t.dbid

AND s.sql_id = t.sql_id

AND sql_text LIKE 'SELECT /* TARGET SQL */%'

ORDER BY s.sql_id

Analyze a particular SQL ID and see the trends for the past day.

select

s.snap_id,

to_char(s.begin_interval_time,'HH24:MI') c1,

sql.executions_delta c2,

sql.buffer_gets_delta c3,

sql.disk_reads_delta c4,

sql.iowait_delta c5,

sql.cpu_time_delta c6,

sql.elapsed_time_delta c7

from

dba_hist_sqlstat sql,

dba_hist_snapshot s

where

s.snap_id = sql.snap_id

and s.begin_interval_time > sysdate -5

and

sql.sql_id='&sqlid'

order by c7

DB REFRESH USING RMAN DB RESTORATION & RECOVERY STEPS-REAL TIME

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

Database Refresh from production to UAT Database using RMAN backup and restoration method

Current Environment : Single node Production and UAT Database

Backup Method : RMAN Backup on TAPE

Prerequisite:

Step 1) Check with the requestor if the UAT database backup is required

Step 2) During the refresh activity the UAT database should not be in use by the application team, take a confirmation e-mail from the requestor

Step 3) Check the version for both source and target database.

Step 4) Check the size of the mount point on both the target and source database

if the mount point size is not same and request platform team to add the space to the mount point of the target database.

Step 5) Take backup of DB Link using metadata and DB link

mkdir -p /backup/restoration

CREATE DIRECTORY dumpdblinkref AS '/backup/restoration/dbrefresh';

SQL> grant READ on directory IMPORT_DIR to dumpdblinkref;

SQL> grant WRITE on directory EXPORT_DIR to dumpdblinkref;

set pagesize 1000

set linesize 120

column directory_name format a25

column directory_path format a70

select directory_name directory_name, directory_path directory_path

from dba_directories order by directory_name;

DATABASE LINKS EXPORT BACKUP

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

vi dblink_1MAY2023.par

directory=dumpdblinkref

dumpfile=dblink_1MAY2023.dmp

full=y

include=DB_LINK

logfile=expdp_dblink_1MAY2023.log

expdp \"/ as sysdba\" parfile=dblink_1MAY2023.par

set lines 2000

set pages 2000

set owner for a13

set username for a12

col host for a50

col username for a15

col DB_LINK for a30

col CREATED for a30

select * from dba_db_links ;

set long 9999999

set pagesize 0

set linesize 120

select dbms_metadata.get_ddl('DB_LINK','&db_name','&owner') from dual;

Step 6) Take backup of DB users

set pagesize 500

select 'alter user '||name||' identified by values '''||password||''';' from user$ ;

select * from user$

select * from dba_users

Take detail from UAT Database before drop/refresh:

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

SQL> set pages 1999

select host_name from v$instance;

select name,open_mode from v$database;

col value for a50

sho parameter spfile

select NAME " DATA FILE "from V$datafile order by 1;

select name " Controlfile " from V$controlfile order by 1;

select MEMBER " LOGFILE " from v$logfile order by 1;

select name " TEMPFILE " from v$tempfile order by 1;

archive log list

Implementation

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

Step1 : Login on UAT database and take necessary backup

A) Login on UAT Server and set DB enviroment accordingly

{orauat@easyserver:/db1/oraredo/easyDB}pwd

/db1/oraredo/easyDB

{orauat@easyserver:/db1/oraredo/easyDB}cd

-rwxr-xr-x 1 orauat oinstall 163 Nov 16 2020 easyDB_uat.env

-rwxr-xr-x 1 orauat oinstall 523 Dec 17 2020 easyDB_12c.env

-rwxr-xr-x 1 orauat oinstall 195 Sep 15 2021 easyDB_restore.env

{orauat@easyserver:/home/orauat}cat easyDB_12c.env

export ORACLE_BASE=/oracle02

export ORACLE_HOME=/oracle02/app/orauat/product/12.2.0.1/easyDB_home

export ORACLE_SID=easyDB_uat

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/local/lib:/lib64:/lib:/usr/lib64:/usr/lib

export LD_LIBRARY_PATH

PATH=$PATH:$HOME/bin:/usr/local/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch; export PATH

TEMP=/tmp

TMPDIR=/tmp

export TEMP TMPDIR

export PS1='\u@\h:\w[$ORACLE_SID] >'

stty erase ^?

#export TZ="MUT-4:00"

#export TNS_ADMIN=

{orauat@easyserver:/home/orauat}. easyDB_12c.env

{orauat@easyserver:/home/orauat}s

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 14 01:47:47 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

USER is "SYS"

NAME UQ_NAME INSTANCE VERSION OPEN_MODE HOST_NAME ROLE DATABASE_STATUS STATUS LOGINS UPTIME

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

EASYDB_UAT easyDB_uat easyDB_uat 12.2.0.1.0 READ WRITE easyserver PRIMARY ACTIVE OPEN ALLOWED 14-NOV-2022 01:37:23

INSTANCE_NAME HOST_NAME STATUS

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

easyDB_uat easyserver OPEN

SQL> select name, open_mode from v$database;

NAME OPEN_MODE

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

easyDB_UAT READ WRITE

B.Create Pfile on UAT Database for backup:

SQL> create pfile='/tmp/initEASYDB_UAT05NOV22.ora' from spfile;

C. Create trace control file:

alter database backup controlfile to trace as '/tmp/EASYDB_UAT_control_1Jun2021.trc';

STEP: 2: Drop UAT Database

drop UAT – easyserver database(easyDBdb_uat) or clean file system related to datafiles ,redo files and control files for restoration

SQL> Shut Immediate;

SQL > STARTUP MOUNT RESTRICT

SQL>DROP DATABASE.

STEP:3:

=======

2) set environment using easyDB_restore.env . it will set environment for easyDB (not easyDBdb_uat)

{orauat@easyserver:/home/orauat}cat easyDB_restore.env

export ORACLE_BASE=/oracle02

export ORACLE_HOME=/oracle02/app/orauat/product/12.2.0.1/easyDB_home

export ORACLE_SID=easyDB

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

{orauat@easyserver:/home/orauat}. easyDB_restore.env

pfile location

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

/oracle02/app/orauat/product/12.2.0.1/easyDB_home/dbs

2) start database in unmount stage and check database name. it should be easyDB

SQL>startup nomount

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string easyDB

SQL>

STEP:4:

=======

Restore Controlfile on UAT Database:

===================

exit sqlprompt and restore control file from RMAN full backup

{orauat@easyserver:/home/orauat/DBBACKUP}cat restore_control_easyDB.sh

#!/bin/ksh

rman log=/home/orauat/DBBACKUP/easyDB_restore_$(date +"%Y%m%d-%H%M%S").log<< EOF

connect target /

run

{

allocate channel 'dev_0' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=easyDB,OB2BARLIST=SWIDB001_ORA_EASYDB_POST_EOD_DAILY)';

restore controlfile from 'EASYSERVER_ORA_EASYDB_POST_EOD_DAILY<easyDB_216629:1116224562:1>.dbf';

release channel 'dev_0';

}

EOF

{orauat@easyserver:/home/orauat/DBBACKUP}

Note: Once controlfile restoration is done then we need to start database restoration process.

STEP:5:

======

put the database in mount state. and run below restore command

{orauat@easyserver:/home/orauat}cd DBBACKUP

{orauat@easyserver:/home/orauat/DBBACKUP}ls -ltr

total 96

-rwxrwxrwx 1 orauat oinstall 1159 Oct 16 15:54 Restore_database_EASYDB_16oct22.sh

-rw------- 1 orauat oinstall 149 Oct 16 15:55 nohup.out

-rw-r--r-- 1 orauat oinstall 26142 Oct 16 15:56 easyDB_datafile_restore_20221016-155556.log

{orauat@easyserver:/home/orauat/DBBACKUP}cat Restore_database_EASYDB_16oct22.sh

#!/bin/ksh

rman log=/home/orauat/chan/easyDB_datafile_restore_$(date +"%Y%m%d-%H%M%S").log<< EOF

connect target /

run

{

allocate channel 'dev_0' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_1' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_2' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_3' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_4' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_5' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_6' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_7' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

restore database ;

switch datafile all;

release channel 'dev_0';

release channel 'dev_1';

release channel 'dev_2';

release channel 'dev_3';

release channel 'dev_4';

release channel 'dev_5';

release channel 'dev_6';

release channel 'dev_7';

}

EOF

NOTE: Once restoration process is done then we need to check restoration logfile and verify the restoration process status

select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

STEP:6:

======

{orauat@easyserver:/home/orauat/DBBACKUP}cat Recover_database_151122.sh

#!/bin/ksh

rman log=/home/orauat/DBBACKUP/easyDB_datafile_recover_$(date +"%Y%m%d-%H%M%S").log<< EOF

connect target /

run

{

allocate channel 'dev_0' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=easyDB,OB2BARLIST=EASYSERVER_ORA_EASYDB_POST_EOD_DAILY)';

allocate channel 'dev_1' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=easyDB,OB2BARLIST=EASYSERVER_ORA_EASYDB_POST_EOD_DAILY)';

allocate channel 'dev_2' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=easyDB,OB2BARLIST=EASYSERVER_ORA_EASYDB_POST_EOD_DAILY)';

allocate channel 'dev_3' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=easyDB,OB2BARLIST=EASYSERVER_ORA_EASYDB_POST_EOD_DAILY)';

allocate channel 'dev_4' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=easyDB,OB2BARLIST=EASYSERVER_ORA_EASYDB_POST_EOD_DAILY)';

allocate channel 'dev_5' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=easyDB,OB2BARLIST=EASYSERVER_ORA_EASYDB_POST_EOD_DAILY)';

allocate channel 'dev_6' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=easyDB,OB2BARLIST=EASYSERVER_ORA_EASYDB_POST_EOD_DAILY)';

allocate channel 'dev_7' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=easyDB,OB2BARLIST=EASYSERVER_ORA_EASYDB_POST_EOD_DAILY)';

recover database;

release channel 'dev_0';

release channel 'dev_1';

release channel 'dev_2';

release channel 'dev_3';

release channel 'dev_4';

release channel 'dev_5';

release channel 'dev_6';

release channel 'dev_7';

}

EOF

{orauat@easyserver:/home/orauat}. easyDB_restore.env

{orauat@easyserver:/home/orauat} nohup sh Recover_database_151122.sh &

STEP: 7 Post restoration steps:

======================

1. Check the Standby logfiles:

SQL> SELECT MEMBER FROM V$LOGFILE WHERE TYPE = 'STANDBY';

2. DROP ALL STANDBY LOGIFLES:

3. SQL> alter database drop standby logfile group ;

4.CHECK THE NORMAL REDOLOGFILES AND DROP FEW LOGFILES:

SQL> select group#,status,member from v$logfile;

5. AFTER DROPPING STANDBY & NORMAL LOGFILES AND WE NEED TO RENAME LOGFILES FROM PRODUCTION LOCATION TO UAT LOCATION:

SQL> alter database rename file '/db1/oraredo2/easyDB/easyDB_redo_01b.log' to '/db1/oraredo/easyDB/easyDB_redo_01b.log';

6.AFTER RENAMING LOGFILE CHECK THE DATABASE STATUS

NOTE: NEED TO CONNECT EASYDB_RESTORE.ENV and check database status

SQL> @up

USER is "SYS"

NAME UQ_NAME INSTANCE VERSION OPEN_MODE HOST_NAME ROLE DATABASE_STATUS STATUS LOGINS UPTIME

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

EASYDB easyDB easyDB 12.2.0.1.0 MOUNTED easyserver PRIMARY ACTIVE MOUNTED ALLOWED 14-NOV-2022 01:17:18

INSTANCE_NAME HOST_NAME STATUS

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

easyDB easyserver MOUNTED

7. CHANGE THE DATABASE STATE FROM MOUNT TO OPEN RESETLOGS:

SQL>alter database open resetlogs;

SQL> @up

USER is "SYS"

NAME UQ_NAME INSTANCE VERSION OPEN_MODE HOST_NAME ROLE DATABASE_STATUS STATUS LOGINS UPTIME

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

EASYDB easyDB easyDB 12.2.0.1.0 READ WRITE easyserver PRIMARY ACTIVE OPEN ALLOWED 14-NOV-2022 01:37:23

INSTANCE_NAME HOST_NAME STATUS

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

easyDB easyserver OPEN

no rows selected

STEP : 8 : SHUT THE DATABASE AND START THE DATABASE IN MOUNT STATE:

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 8589934592 bytes

Fixed Size 19289376 bytes

Variable Size 5502929632 bytes

Database Buffers 3053453312 bytes

Redo Buffers 14262272 bytes

Database mounted.

SQL> @up

USER is "SYS"

NAME UQ_NAME INSTANCE VERSION OPEN_MODE HOST_NAME ROLE DATABASE_STATUS STATUS LOGINS UPTIME

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

EASYDB easyDB easyDB 12.2.0.1.0 MOUNTED easyserver PRIMARY ACTIVE MOUNTED ALLOWED 14-NOV-2022 02:06:37

INSTANCE_NAME HOST_NAME STATUS

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

easyDB easyserver MOUNTED

from v$PDBs v inner join dba_pdbs d

ERROR at line 2:

ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

u@h:w[easyDB] >ls -lrt *.env

-rwxr-xr-x 1 orauat oinstall 147 Apr 25 2019 asm.env

-rwxr-xr-x 1 orauat oinstall 163 Nov 16 2020 easyDB_uat.env

-rwxr-xr-x 1 orauat oinstall 523 Dec 17 2020 easyDB_12c.env

-rwxr-xr-x 1 orauat oinstall 195 Sep 15 2021 easyDB_restore.env

STEP : 9 : CHANGING DATABASE NAME USING NID METHOD

u@h:w[easyDB] >. easyDB_restore.env

u@h:w[easyDB] >nid target=/ DBNAME=EASYDB_UAT

DBNEWID: Release 12.2.0.1.0 - Production on Mon Nov 14 02:07:22 2022

Connected to database EASYDB (DBID=3405867136)

Connected to server version 12.2.0

Control Files in database:

/db1/oraredo/easyDB/control/control01.ctl

/db1/orafra/easyDB/control/control02.ctl

Change database ID and database name EASYDB to EASYDB_UAT? (Y/[N]) => y

Proceeding with operation

Changing database ID from 3405867136 to 3121593306

Changing database name from EASYDB to EASYDB_UAT

Control File /db1/oraredo/easyDB/control/control01.ctl - modified

Control File /db1/orafra/easyDB/control/control02.ctl - modified

Datafile /db1/oradata/easyDB/system01.db - dbid changed, wrote new name

Datafile /db1/oradata/easyDB/sysaux01.db - dbid changed, wrote new name

Datafile /db1/oradata/easyDB/EASYDB_UNDOTBS1_01.db - dbid changed, wrote new name

Datafile /db1/oradata/easyDB/acquirer01.db - dbid changed, wrote new name

Datafile /db1/oradata/easyDB/bo_index01.db - dbid changed, wrote new name

Datafile /db1/oradata/easyDB/issuer01.db - dbid changed, wrote new name

Datafile /db1/oradata/easyDB/log01.db - dbid changed, wrote new name

Datafile /db1/oradata/easyDB/main01.db - dbid changed, wrote new name

Datafile /db1/oradata/easyDB/others01.db - dbid changed, wrote new name

Datafile /db1/oradata/easyDB/sqs01.db - dbid changed, wrote new name

Control File /db1/oraredo/easyDB/control/control01.ctl - dbid changed, wrote new name

Control File /db1/orafra/easyDB/control/control02.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to EASYDB_UAT.

Modify parameter file and generate a new password file before restarting.

Database ID for database EASYDB_UAT changed to 3121593306.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

u@h:w[easyDB] >ls -lrt *.env

-rwxr-xr-x 1 orauat oinstall 147 Apr 25 2019 asm.env

-rwxr-xr-x 1 orauat oinstall 163 Nov 16 2020 easyDB_uat.env

-rwxr-xr-x 1 orauat oinstall 523 Dec 17 2020 easyDB_12c.env

-rwxr-xr-x 1 orauat oinstall 195 Sep 15 2021 easyDB_restore.env

u@h:w[easyDB] >. easyDB_12c.env

u@h:w[easyDB_uat] >s

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 14 02:08:14 2022

Connected to an idle instance.

SQL> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 1.5032E+10 bytes

Fixed Size 15746840 bytes

Variable Size 3690989800 bytes

Database Buffers 1.1308E+10 bytes

Redo Buffers 17805312 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL>

USER is "SYS"

NAME UQ_NAME INSTANCE VERSION OPEN_MODE HOST_NAME ROLE DATABASE_STATUS STATUS LOGINS UPTIME

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

EASYDB_UAT EASYDB_UAT easyDB_uat 12.2.0.1.0 READ WRITE easyserver PRIMARY ACTIVE OPEN ALLOWED 14-NOV-2022 02:08:20

INSTANCE_NAME HOST_NAME STATUS

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

easyDB_uat easyserver OPEN

NID is an utility which is used to change the database internal name and dbname of a database. The utility is placed in the $ORACLE_HOME/bin directory. Prior to the nid utility w

We need to manually create a database and give it a new database name by recreating a control file.

more nid_easyDB_uat.log_OLD

a) Create a pfile from spfile.

b) Shutdown the database if already running.

c) Startup mount the database.

d) Check Database status.

e) Start NID utility using the below syntax.

syntax:

nid target=sys/password@your_current_db_name dbname=new_db_name

Changing database ID from 3405867136 to 2905412457

Changing database name from EASYDB to EASYDB_UAT

nid target=/ dbname= EASYDB_UAT

nid TARGET=SYS/password@EASYDB DBNAME=EASYDB_UAT

set enviroment and run

nid TARGET=SYS DBNAME=EASYDB_UAT

Note -change the db_name parameter in the parameter file. but her we can start database using easyDB_12c.env

EASYDB_UAT restoration has been completed

Post refresh activity in summary

========================================

1) disable database archive log mode

2) Now check the target database size is equal production database.

3) select file_name,sum(bytes) from dba_temp_files where tablespace_name='TEMP' group by file_name;

4) check dblink and user details and import the dblinks which we taken backup of dblinks.

col OWNER for a15

col USERNAME for a25

col DB_LINK for a25

col HOST for a20

set lines 300

select * from dba_db_links;

SQL> SELECT DBMS_METADATA.get_ddl ('DB_LINK', db_link, owner)

FROM dba_db_links; 2

SQL> CREATE DIRECTORY dumpdblinkref AS '/backup/restoration/dbrefresh';

Directory created.

SQL>

impdp \'/ as sysdba\' directory=dumpdblinkref DUMPFILE=dblink_1MAY2023.dmp logfile=import_dblink03may.log

With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle@CBTDA201:{/backup/restoration/dbrefresh/}impdp \'/ as sysdba\' directory=dumpdblinkref DUMPFILE=dblink_1MAY2023.dmp logfile=import_dblink03may.log

5) check users details

select username ,password,profile from dba_users;

set long 99999

select DBMS_METADATA.GET_DDL ('USER',username) from dba_users;

SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username'

Inform to start the middleware services and check the application functionality.

Wednesday, 18 September 2024

redo resize on RAC enviroment to improve Database performance realtime

in this , We ware going to resize online redo log file/group from 2gb to 4 GB and adding

in standby redo log (n+1) to improve performance of database on both primary and standby database

current setup = 4 node RAC primary database and 4 node RAC Standby database

Note First add online redo log group with recommended size and add standby redo group (n+1) on standby then proceed on Primary Database

Pre-Requisite

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

1 Sufficient free space is availble on ASM(Both DC And DR)

2 change parameter -standby_file_management to manual (Both DC And DR)

3 disable DC-DR replication(Defer)

Implementatio on DR(Standby Database)

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

1 cancel recovery on standby database

2 add redo log group with recommended size on standby database

3 Verify newly created online redo log file

4 add new standby redo log group on standby database (N+1)

5 verify standby redo log file

6 drop old redo log group from standby database

Verify online redo log file after drop

Implementation on Primary database (DC)

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

1 add online redo log group with recommended size on primary database

2 Verify newly created online redo log file

3 add standby redo log with recommended size primary database (N+1)

4 Verify newly created standby redo log file

5 drop old redo log group from primary database

6 verify online redo log file after drop

Post Implementation

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

1 enable DC-DR replication(enable)

2 change parameter -standby_file_management to auto (Both DC And DR)

3 Monitor Primary and Standby sync

in Detail with example

Pre requisite (Primary and Standby Database)

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

Check if the parameter standby_file_management is set to AUTO or MANUAL on standby database. If it is not set to MANUAL, then set it.

it will run on both Primary and Standby database

SQL> show parameter standby_file_management

NAME TYPE VALUE

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

standby_file_management string AUTO

SQL> alter system set standby_file_management=manual scope=both sid='*';

SQL> show parameter standby_file_management

NAME TYPE VALUE

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

standby_file_management string MANUAL

The command will run on primary database only

2) disable DC-DR replication(Defer)

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH sid='*'; -DC

Now we will run below command on standby(DR)

Addition of online redo file with recommended size

select THREAD#,MAX(SEQUENCE#) from v$loghist group by THREAD#;

select name,open_mode,log_mode from v$database;

column member format a50 wrap heading 'Member'

column group# format 99990 heading 'Group#'

column status format a10 wrap heading 'Status'

SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;

select group#,members,status,bytes/1024/1024 as mb from v$log;

select group#,member from v$logfile;

alter database recover managed standby database cancel;

alter database add logfile thread 1 group 101 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 102 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 103 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 104 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 105 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 106 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 107 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 108 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 109 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 110 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 111 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 1 group 112 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

column member format a50 wrap heading 'Member'

column group# format 99990 heading 'Group#'

column status format a10 wrap heading 'Status'

SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;

select group#,members,status,bytes/1024/1024 as mb from v$log;

select group#,member from v$logfile;

alter database add logfile thread 2 group 113 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 114 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 115 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 116 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 117 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 118 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 119 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 120 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 121 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 122 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 123 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 2 group 124 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

column member format a50 wrap heading 'Member'

column group# format 99990 heading 'Group#'

column status format a10 wrap heading 'Status'

SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;

select group#,members,status,bytes/1024/1024 as mb from v$log;

select group#,member from v$logfile;

alter database add logfile thread 3 group 125 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 126 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 127 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 128 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 129 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 130 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 131 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 132 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 133 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 134 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 135 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 3 group 136 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

column member format a50 wrap heading 'Member'

column group# format 99990 heading 'Group#'

column status format a10 wrap heading 'Status'

SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;

select group#,members,status,bytes/1024/1024 as mb from v$log;

select group#,member from v$logfile;

alter database add logfile thread 4 group 137 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 138 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 139 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 140 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 141 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 142 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 143 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 144 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 145 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 146 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 147 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add logfile thread 4 group 148 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

column member format a50 wrap heading 'Member'

column group# format 99990 heading 'Group#'

column status format a10 wrap heading 'Status'

SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;

select group#,members,status,bytes/1024/1024 as mb from v$log;

select group#,member from v$logfile;

Drop the log groups on standby database :

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

column member format a50 wrap heading 'Member'

column group# format 99990 heading 'Group#'

column status format a10 wrap heading 'Status'

SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;

select group#,members,status,bytes/1024/1024 as mb from v$log;

select group#,member from v$logfile;

Note : Since the status of Group is still clearing, lets clear it manually before dropping the group.

alter database clear logfile group 1;

select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

alter database drop logfile group 4;

alter database drop logfile group 5;

alter database drop logfile group 6;

alter database drop logfile group 7;

alter database drop logfile group 8;

alter database drop logfile group 9;

alter database drop logfile group 10;

select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;

alter database drop logfile group 11;

alter database drop logfile group 12;

alter database drop logfile group 13;

alter database drop logfile group 14;

alter database drop logfile group 15;

alter database drop logfile group 16;

alter database drop logfile group 17;

alter database drop logfile group 18;

alter database drop logfile group 19;

alter database drop logfile group 20;

select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;

alter database drop logfile group 21;

alter database drop logfile group 22;

alter database drop logfile group 23;

alter database drop logfile group 24;

alter database drop logfile group 25;

alter database drop logfile group 26;

alter database drop logfile group 27;

alter database drop logfile group 28;

alter database drop logfile group 29;

alter database drop logfile group 30;

alter database drop logfile group 31;

select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;

alter database drop logfile group 32;

alter database drop logfile group 33;

alter database drop logfile group 34;

alter database drop logfile group 35;

alter database drop logfile group 36;

alter database drop logfile group 37;

alter database drop logfile group 38;

alter database drop logfile group 39;

alter database drop logfile group 40;

alter database drop logfile group 41;

alter database drop logfile group 42;

alter database drop logfile group 43;

select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;

alter database drop logfile group 44;

alter database drop logfile group 45;

alter database drop logfile group 46;

alter database drop logfile group 47;

alter database drop logfile group 48;

alter database drop logfile group 49;

alter database drop logfile group 50;

alter database drop logfile group 51;

alter database drop logfile group 52;

alter database drop logfile group 53;

alter database drop logfile group 54;

alter database drop logfile group 55;

alter database drop logfile group 56;

alter database drop logfile group 57;

select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;

alter database drop logfile group 58;

alter database drop logfile group 59;

alter database drop logfile group 60;

alter database drop logfile group 61;

alter database drop logfile group 62;

alter database drop logfile group 63;

alter database drop logfile group 64;

Post Implementation on primary

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

To verify size for newly created log file

column member format a50 wrap heading 'Member'

column group# format 99990 heading 'Group#'

column status format a10 wrap heading 'Status'

SELECT a.group#,b.thread#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;

add standby redo log file on (n+1)13 Standby redo Database(DR) in each thread

select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

alter database add standby logfile thread 1 group 201 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 202 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 203 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 204 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 205 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 206 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 207 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 208 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 209 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 210 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 211 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 212 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 1 group 213 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

alter database add standby logfile thread 2 group 214 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 215 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 216 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 217 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 218 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 219 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 220 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 221 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 222 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 223 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 224 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 225 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 2 group 226 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

alter database add standby logfile thread 3 group 227 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 228 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 229 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 230 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 231 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 232 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 233 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 234 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 235 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 236 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 237 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 238 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 3 group 239 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

alter database add standby logfile thread 4 group 240 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 241 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 242 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 243 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 244 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 245 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 246 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 247 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 248 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 249 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 250 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 251 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

alter database add standby logfile thread 4 group 252 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;

select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

---------end of online and standby redo addition Standby Database -----------------

Now Start on Primary Database

Add on 12 online redo with recommend size and 13 (n+1)standby redo on Primary Database (DC)

Change Implementation Steps: