Linux for Oracle DBA

 786

Logging On Remotely via the Command Line

$ ssh -l oracle rmoug1

$ ssh -p 71 -l oracle rmoug1

$ telnet -l oracle dbsrver

Logging Off the Server

Pressing Ctrl+D
Typing exit
Typing logout

/etc/bashrc change the parameter TMOUT

Running a Command

df (disk free)

options, flags and switches

ls -a

also arguments

df -h /dev/sda2

The man pages are usually divided into ten sections. The man command will display the first
man page match it finds for a specified command. The following list shows the section number
and type of commands documented in each section:

1. User commands
2. System calls
3. Subroutines (library functions)
4. Devices
5. File formats
6. Games
7. Miscellaneous
8. System administration
9. Local
10. New

Sometimes a Linux utility will be documented in more than one man section. To view all man
documentation available for a tool, use the -f option (this is equivalent to running the whatis
command).

$ man -f cd

Sometimes DBAs are confused when they type man cd and are presented with the Bash
shell’s built-in documentation.  To view the man documentation specific to the cd utility, then
specify the 1p page:

$ man 1p cd

To scroll through all man sections associated with a command, use the -a option. When in
this mode, press the Q key to advance to the next man section of information.

CAPTURING MAN PAGES IN A TEXT FILE

$ man find >find.txt

$ man find | col -b >find.txt  (to avoid unreadable characters)

whatis pwd

The number (enclosed by parentheses) specifies the section of the man page where you can
find the command. When you see multiple lines listed by whatis, this indicates the command
is documented in more than one location in the man pages.

Another interesting use of the whatis command is to view a one-line description of
commands in the /bin directory. This example uses whatis with ls, xargs, and less to view,
one page at a time, the descriptions of all commands in the /bin directory:

$ cd /bin

$ ls | xargs whatis | less

Locating a Command

Use which or whereis to locate the executable binary file of a command.

$ which man
/usr/bin/man

while using man command options:

Keystroke                                                     Action

J, E, or down arrow                                      Move down one line.

K, Y, or up arrow                                         Move up one line.

Up arrow                                                     Move up one line.

Down arrow                                                 Move down one line.

/<string>                                                     Search for <string>.

N                                                                 Repeat the previous search forward.

Shift+N                                                         Repeat the previous search backward.

H                                                                 Display help page.

F, spacebar, or Page Down                         Move down one page.

B or Page Up                                             Move up one page.

Q                                                                 Exit man page.

$ man -f cd

$ man 1p cd

$ man -a cd

$ whatis pwd

whatis command is to view a one-line description of commands in the /bin directory:

$ cd /bin

$ ls | xargs whatis | less

The previous line of code will first list the files in the /bin directory; then, the output of that
is piped to the xargs command. The xargs command will take the output of ls and send it to
the whatis utility. The less command will display the output one page at a time. To exit from
the documentation (displayed by less), press the Q key (to quit).

The whatis command is identical to the man -f command

Locating a Command

Use which or whereis to locate the executable binary file of a command

[oracle@prod ~]$ which man
/usr/bin/man

following command displays the location of the echo command and its corresponding man documentation files:

[oracle@prod ~]$ whereis echo

echo: /bin/echo /usr/share/man/man1p/echo.1p.gz /usr/share/man/man3/echo.3x.gz /usr/share/man/man1/echo.1.gz

Getting the Version

[oracle@prod ~]$ who --version

who (GNU coreutils) 5.97

Copyright (C) 2006 Free Software Foundation, Inc.

This is free software.  You may redistribute copies of it under the terms of

the GNU General Public License <http://www.gnu.org/licenses/gpl.html>.

There is NO WARRANTY, to the extent permitted by law.

Written by Joseph Arceneaux, David MacKenzie, and Michael Stone.

Showing Help

$ df --help

Finding Manual Page Documentation

if u remember the partial name of the utility you seek:

$ apropos find

The apropos command is equivalent to man -k.

Listing Extensive Documentation

$ info 

N to go to the next section

P to go to the previous section

Q to exit

where there is * go there and press enter key.

? list all commands

D return to introduction page

H go to the tutorial

$ info cpio

to view a tutorial on info :

$ info info 

Showing Available Commands

$ ls<Tab><Tab>

[oracle@prod ~]$ ls

ls           lsb_release  lslk         lsnrctl      lsof         lss16toppm

lsattr       lshal        lsnodes      lsnrctl0     lspgpot      lsscsi

You should hear a bell sound (sometimes called a beep) after you press the first Tab. After

pressing the second Tab, the Bash shell will attempt to find all commands that start with ls that

are located in any directories contained in the PATH variable.

Correcting Command-Line Mistakes

RMAN Basics

export ORACLE_SID=prod

rman target /

rman target / nocatalog

backup database;

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/fra
db_recovery_file_dest_size           big integer 10G

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7

rman target / nocatalog

show all;

configure controlfile autobackup on;

show all;

configure retention policy to redundancy 2;

configure backup optimization off;

FRA = Flash Recovery Area or Fast Recovery Area

show parameter recover;

alter system set db_recovery_file_dest_size=10G scope=both;

mkdir -p /u01/fra

alter system set db_recovery_file_dest='/u01/fra' scope=both;

BACKUP DATABASE;

BACKUP DATABASE PLUS ARCHIVELOG;

BACKUP ARCHIVELOG ALL;

BACKUP ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';

To delete archive log using RMAN

RMAN> DELETE ARCHIVELOG ALL;

RMAN> DELETE ARCHIVELOG <archivelog name>;

RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2';

To delete archive log using SQL

SQL> ALTER SYSTEM ARCHIVE LOG ALL DELETE;

SQL> ALTER SYSTEM ARCHIVE LOG <archivelog name> DELETE;

SQL> DELETE FROM v$archived_log WHERE completion_time < SYSDATE-2;

set linesize 300
col name for a40
select recid, name, thread#, sequence#, dest_id, creator, registrar,
standby_dest, status, completion_time 
from v$archived_log
order by completion_time;

SQL> DELETE FROM v$archived_log WHERE completion_time < SYSDATE-2;

select sid,recid,status,start_time,end_time from v$rman_status
order by start_time;

How to delete archive log files using PL/SQL

declare

  f_count number ;

begin

  select count(*) into f_count from v$rman_status where STATUS like 'RUNNING%';

  if f_count>0 then
    dbms_output.put_line('RMAN backup job is running, archive log files cannot be deleted');
  return;
end if;

  f_count:=0;

  for c1 in (

 select RECID,STAMP,NAME,THREAD#,SEQUENCE#,
RESETLOGS_CHANGE#,FIRST_CHANGE#,BLOCK_SIZE
from v$archived_log
where STANDBY_DEST='NO' and deleted='NO)

  loop

     begin

       dbms_backup_restore.deleteArchivedLog(c1.RECID,c1.STAMP,c1.NAME,c1.THREAD#,c1.SEQUENCE#,c1.RESETLOGS_CHANGE#,c1.FIRST_CHANGE#,c1.BLOCK_SIZE);

       dbms_output.put_line(c1.name||' is deleted.');

       f_count:=f_count+1;

     exception

       when others then

          null;

     end;

  end loop;

  if f_count=0 then

     dbms_output.put_line('No archive log files deleted.');

  end if;

end;

/

The PL/SQL block can be enclosed in database scheduler job definition or Linux/Unix shell script running as crontab job. Here is a simple bash shell example,

#!/bin/bash

# Redirect all STDOUT & STDERR messages to log file

exec &>>/u01/log/backup.log   

# Set Oracle environment

export ORAENV_ASK=NO

export ORACLE_SID=<Database SID>

. oraenv 1>/dev/null

# Run PL/SQL block in SQLPLUS

sqlplus -S /nolog <<EOF | grep -v 'PL/SQL procedure successfully completed.'

connect / as sysdba

set serveroutput on

declare

  f_count number ;

begin

  /* If there is RMAN job running, f_count=0 -> no job running*/

  select count(*) into f_count from v\$rman_status where STATUS like 'RUNNING%';

  if f_count>0 then

     dbms_output.put_line('RMAN job is running, archivelog log files cannot be deleted while RMAN job is running');

     return;

  end if;

  f_count:=0;

  for c1 in (

     select RECID,STAMP,NAME,THREAD#,SEQUENCE#,RESETLOGS_CHANGE#,FIRST_CHANGE#,BLOCK_SIZE

       from v\$archived_log

      where STANDBY_DEST='NO' and deleted='NO'

  )

  loop

     begin

       dbms_backup_restore.deleteArchivedLog(c1.RECID,c1.STAMP,c1.NAME,c1.THREAD#,c1.SEQUENCE#,c1.RESETLOGS_CHANGE#,c1.FIRST_CHANGE#,c1.BLOCK_SIZE);

       dbms_output.put_line(c1.name||' is deleted.');

       f_count:=f_count+1;

     exception

       when others then

          null;

     end;

  end loop;

  if f_count=0 then

     dbms_output.put_line('No archivelog files deleted.');

  end if;

end;

/

EOF

BACKUP DEVICE TYPE sbt 
  ARCHIVELOG ALL 
  DELETE ALL INPUT;

RMAN Creation Steps

RMAN Snapshot Control File

The snapshot control file is a temporary file that is created at a certain location specified over the operating system. This file is used by RMAN to synchronize the information with the current control file. This file, except for synchronizing the information with the current control file, does not do anything else and hence, is not included in the recovery catalog.  It does not contain any information about the current backup that you have taken, though it does know about the last backups that were done and are not marked yet as obsolete. So this file can be used to recover a scenario where you have lost everything and also were not using the catalog. You can use the last backup information recorded in this file and can recover the database with that backup.

The location of this file depends on the platform and also uses a default naming convention as well.  Interestingly, irrespective of the flash recovery area being configured, the snapshot control file does not use it and is set for the location, normally to the ORACLE_HOME/database or ORACLE_HOME/dbs location with the name sncf<sid>.ora.

You can see the current name and location of the file by the show command.

RMAN> show snapshot controlfile name;

RMAN configuration parameters are:

configure snapshot controlfile name to 'E:\ORACLE\product\10.2.0\db_1\database\sncforcl.ora'; # default

In this case, it is set to the default location. You can change it using the configure command.

Oracle Scripts in Github

 https://github.com/fatdba/Oracle-Database-Scripts