Oracle Database Scripts : June 2023

Thursday, June 22, 2023

Experience As a Fresher In Oracle Database

1. You'll get almost no help from colleagues to learn and understand the system.

2. You'll be fully rely on internet and various websites to help you resolve and learn the system.

3. You'll be ask to do difficult task even if you know nothing and if you ask for help you'll be taunt for it.

4. You'll get no respect neither from your colleagues or clients if they know you're fresher.

5. If anything happens bad you'll be the one who will blame for if you were there.

6. keep your seat belt pack when performing database operation after you grasp some hold on some experience.

7. crosscheck your steps what you're doing if you are careless then your job in danger.

8. you'll listen some hoax and stories about peoples moving from this team to another , this company to another don't give your attention and just focus on work.

9. don't make friends and enemy at work.

10. Keep upgrading your knowledge also dive into another technologies like development, testing and have keep good company if found any.

11. Always strive for betterment because everything needs some pay offs whether its relationship, jobs, needs, desires see you'll experience the burden of expectations of your family, relative and friends from you. Make sure not to be a step for some body.

12. Always keep your experience notes, gather them keep steps in notepad or notepad++.

13. Once you get sufficient material to support your actions and experience you'll see things getting simple and smoother.

14. Always tell less than necessary nobody in meeting wants to understand just they want the issue get resolved.

15. Your colleague will before you to put there blames on you keep alertness.

16. Keep checking your mailbox

17. Always make folders of your team members for their mails.

18. The daily task is like checking mails resolving them, answering meeting, give your work status for the day or week, requesting and engaging other teams to resolve complex issue.

19. patching, upgrading, change request (CR), trouble ticket (tt).

20. Finding contacts and mail ids of SDBA, PAS, CI/CD, SCM team and mail them.

21. Finding decommission database servers and telling info to other teams.

22. Oracle provides zero level of improvement if you wish to become expert since its extremely complex in the perspective of its older age and how it works.

23. Sometime i feel like i am learning 10 centuries at a time we can take an example of oracle versions 10g, 11g, 12c and 19c.

24. You'll need to frequently raise SR (support request/service request) to oracle support to get help for issue happens on production database and this is not a quickly resolve you need to wait and keep checking updates on oracle support website often needs to have meeting with oracle support guy. Again if the idea from oracle support guy not worked or your team lead/client rejected it you need to ask oracle support guy to review or give another idea/way this will keep going until the final best idea/way can meet.

Standby DB Parameters

# Changing Protection Mode:-

alter database set standby database to maximum {availability|performance|protection};

# Status of protection mode on standby database:-

select protection_mode from v$database;

Availability	AFFIRM/NOAFFIRM	SYNC	DB_UNIQUE_NAME
Performance (default)	NOAFFIRM	ASYNC	DB_UNIQUE_NAME
Protection	AFFIRM	SYNC	DB_UNIQUE_NAME

1. With LGWR, default is sync used.
2. With ARCH , only SYNC is valid.
3. SYNC attribute is used for no data loss. It ensure that redo is transmitted successfully at destination before continue.

compatible=release_number

control_file_record_keep_time=no of days (0-365)

control_files='/path/to/control_file.ctl','/path/to/control_file.ctl';

db_file_name_convert='location_primary_datafile','location_standby_datafile';

db_unique_name=unique_name_for_standby

fal_client=server_self_name ( no longer recommend can skip)

fal_server=server_source_name (no longer recommend can skip)

instance_name=standby_db_unique_name

# for standby

log_archive_config='DG_CONFIG=(db_unqiue_name_standby,db_unique_name_primary)' (highly recommended)

# for primary

log_archive_config='DG_CONFIG=(db_unqiue_name_primary,db_unique_name_standby)' (highly recommended)

log_archive_dest_n={LOCATION=path_name | SERVICE=service_name, attribute,attribute,...} ...req

log_archive_dest_state_n={ENABLE|DEFER|ALTERNATE} ....required

log_archive_format=log_%d_%t_%s_%r.arc ----req

log_archive_max_processes=integer .-----max arcn process (1-30) default 4

log_file_name_convert='location_redo_logs_primary','location_redo_log_standby'; --------------req

standby_file_management={auto|manual} ----req

To create standby db:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u02/app/oracle/dbs/stbycf.ctl';

Copy the standby control file.
Copy the backup datafiles.
Copy all available archived redo logs to the standby site.
Copy the online redo logs. This is recommended for switchover and failover operations.

Thursday, June 8, 2023

Resize Redo logs in Oracle Database

In RAC, each instance has a redo thread; when you start the instance, it mounts one and only one thread. Therefore if you want to add logs or increase the size, you must do it for each thread. The goal is normally to have the same number and size of logs for each thread.

First, determine what log members each thread has

Here is a sample of a script to show what log members you currently have and their sizes:
-- Script begins here -
spool log.lst
set echo off
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
spool off
-- End of script --
Sample output:
GROUP# THREAD# MEMBER ARCHIVED STATUS MB
------ ------- ---------------------------------- -------- --------- ---
3 2 /u02/oradata/redologs/redo03a.log NO INACTIVE 10
3 2 /u02/oradata/redologs/redo03b.log NO INACTIVE 10
4 2 /u02/oradata/redologs/redo04a.log NO CURRENT 10
4 2 /u02/oradata/redologs/redo04b.log NO CURRENT 10
5 1 /u02/oradata/redologs/redo05a.log NO CURRENT 50
5 1 /u02/oradata/redologs/redo05b.log NO CURRENT 50
6 1 /u02/oradata/redologs/redo06a.log NO INACTIVE 50
6 1 /u02/oradata/redologs/redo06b.log NO INACTIVE 50
7 1 /u02/oradata/redologs/redo07a.log NO INACTIVE 50
7 1 /u02/oradata/redologs/redo07b.log NO INACTIVE 50
8 1 /u02/oradata/redologs/redo08a.log NO INACTIVE 50
8 1 /u02/oradata/redologs/redo08b.log NO INACTIVE 50

EXAMPLE:

Consider the above sample output. For Thread 2, you have 2 redo log groups with 2 10MB member each. You want to create 4 50MB logs for thread 2.

1. Add 4 new redo log groups to Thread 2, with two member each, the member 50MB in size :

alter database add logfile
thread 2 group 9
('/u02/oradata/redologs/redo09a.log','/u02/oradata/redologs/redo09b.log') size 50M;

alter database add logfile
thread 2 group 10
('/u02/oradata/redologs/redo10a.log','/u02/oradata/redologs/redo10b.log') size 50M;

alter database add logfile
thread 2 group 11
('/u02/oradata/redologs/redo11a.log','/u02/oradata/redologs/redo11b.log') size 50M;

alter database add logfile
thread 2 group 12
('/u02/oradata/redologs/redo12a.log','/u02/oradata/redologs/redo12b.log') size 50M;

2. Once you have added them, rotate the logs ("alter system switch logfile") on instance 2 so that
the active log is 50MB and both of the 10MB logs are inactive.

Rotate the logs:

alter system switch logfile;

select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize
from v$log l, v$logfile f where f.group# = l.group#
and l.thread#=2 ;

repeat until the active log is 50MB and both of the 10MB logs are inactive.

3. Once both of the 10MB logs are inactive, you can drop the redo log groups with the 10mb members.

alter database drop logfile group 3;
alter database drop logfile group 4;

Result:

GROUP# THREAD# MEMBER ARCHIVED STATUS MB
------ ------- --------------------------------- -------- ---------- ---
5 1 /u02/oradata/redologs/redo05a.log NO CURRENT 50
5 1 /u02/oradata/redologs/redo05b.log NO CURRENT 50
6 1 /u02/oradata/redologs/redo06a.log NO INACTIVE 50
6 1 /u02/oradata/redologs/redo06b.log NO INACTIVE 50
7 1 /u02/oradata/redologs/redo07a.log NO INACTIVE 50
7 1 /u02/oradata/redologs/redo07b.log NO INACTIVE 50
8 1 /u02/oradata/redologs/redo08a.log NO INACTIVE 50
8 1 /u02/oradata/redologs/redo08b.log NO INACTIVE 50
9 2 /u02/oradata/redologs/redo09a.log NO CURRENT 50
9 2 /u02/oradata/redologs/redo09b.log NO CURRENT 50
10 2 /u02/oradata/redologs/redo10a.log NO INACTIVE 50
10 2 /u02/oradata/redologs/redo10b.log NO INACTIVE 50
11 2 /u02/oradata/redologs/redo11a.log NO INACTIVE 50
11 2 /u02/oradata/redologs/redo11b.log NO INACTIVE 50
12 2 /u02/oradata/redologs/redo12a.log NO INACTIVE 50
12 2 /u02/oradata/redologs/redo12b.log NO INACTIVE 50

Friday, June 2, 2023

Trace files and alert log view Quickly

tail -10f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log

tail -10f $ADR_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log

~/.bashrc Importance in shell script

You can make changes permanent on current shell using bashrc script like :-

~/.bashrc:-

1. make alias command='command'

2. functions and packages

3. shopt -s cdspell (set), shopt -u cdspell (unset), shopt -s autocd (set), shopt -u autocd (unset)

shopt command magic

To auto correct directory names on cd :- shopt -s cdspell

To use "directory name" as "cd directory_name" :- shopt -s autocd

To unset shopt commands :- shopt -u cdspell and shopt -u autocd

To make shopt changes permanent :- add shopt -s cdspell and shopt -s autocd in ~/.bashrc script

To show all set parameters of shopt command:-

Important options:-

shopt -s cdspell (-s set)

shopt -u cdspell (-u unset)

and shopt -p (print all option)

Oracle Database Scripts