different ways to connect to sqlplus console from remote server

 sqlplus /  as sysdba  ---------------------> from the host server

sqlplus user/passwd@service_or_sid ---------------------> from remote server

sqlplus user/passwd@host:1521/service_or_sid ---------------------> remote server 

to check database status:- 

sudo -iu oracle----> tnsping servicename ----> thats it as per tnsnames.ora entries 

to check server status :-

ping server as per /etc/hosts entries

from shell script 

export OH

export OS

export PATH

export LD_LIBRARY_PATH

sqlplus \/nolog <<< !! > output.txt

conn $constring;

$query;

exit;

!!

Automatically export ORACLE environment variables in Linux

 

cat /etc/oratab | grep -e "^[\a-Az-Z0-9\]\{0,8\}\:*/[[:graph:]]*" | awk -F: '{print $1,$2}' | read a b

$ echo $a

target

 $ echo $b

/usr/local/opt/oracle/app/oracle/product/11.2.0.4

export ORACLE_HOME=$b

export ORACLE_SID=$a

export PATH=$ORACLE_HOME/bin:$PATH

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.

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 

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 RESETLOGS AND NORESETLOGS Options

RESETLOGS:-

alter database open resetlogs;

this option will reset redo logs and start generating redo logs newly so all old redo logs will be discarded. 

NORESETLOGS:-

alter database open noresetlogs;

this option will used pre-existing redo logs and don't generate redo logs newly. 

if test options and it usage In Linux

if test (expression)

if test !(expression)

if test expression1 -a expression2 

if test expression1 -o expression2

if test -z STRING 

if test STRING1 = STRING2 

if test STRING1 != STRING2

if test INTEGER1 -eq INTEGER2 

if test INTEGER1 -ge INTEGER2 

if test INTEGER1 -gt INTEGER2 

if test INTEGER1 -lt INTEGER2

if test INTEGER1 -le INTEGER2 

if test INTEGER1 -ne INTEGER2 

if test file1 -nt file2                    # -nt :- newer than 

if test file1 -ot file2                    # -ot :- older than

if test -f file                                # if file exists 

if test -d directory_name           # if directory exists 

if test -x file                               # if file is executable 

if test -r  file                               # if file is readable 

if test -w file                               # if file is writable 

if test -s "filename.txt"; then

    # Code to execute if the file is not empty

else

    # Code to execute if the file is empty or does not exist

fi

if ! test -s "filename.txt"; then

    # Code to execute if the file is empty or does not exist

else

    # Code to execute if the file is not empty

fi

if test -n "$myString"; then

    # Code to execute if the string is not empty

else

    # Code to execute if the string is empty

fi

if ! test -n "$myString"; then

    # Code to execute if the string is empty

else

    # Code to execute if the string is not empty

fi

Enable Flashback Mode In Oracle Database

 To Enable Flashback Database 

shutdown immediate;

startup nomount;

alter database mount;

archive log list;

alter database archivelog;

change FRA size and then dest :-

alter system set db_flashback_retention_target=2880; ----> 2880 minutes 

alter system set db_recovery_file_dest_size=10G;

alter system set db_recovery_file_dest='/path/to/FRA';

alter system set undo_retention=86400; -----------> 86400 seconds

alter database flashback on;

alter database open;

if not open then do "alter database open resetlogs" or "alter database open noresetlogs" 

SQL >

select name, value from gv$parameter where name in ('db_flashback_retention_target','db_recovery_file_dest_size','db_recovery_file_dest','undo_retention');

To Check Flashback on/off :-

select flashback_on from gv$database;