Use password in scp command in shell script

Many times we may have to move the files to another server, to copy files to other server we need username & password of user. for scp it ask for password, where we need to provide it manually, to automate this, we can use password in scp command to copy the files to another server.

We can use sshpass to pass user password in scp command.

Here I’m moving the previous date files to another server.

#!/bin/bash
PREVDATE=$(date --date="1 day ago" +%Y%m%d)
echo $PREVDATE
cd /data/logfiles
sshpass -p '0racle123' scp -r logs_$PREVDATE*.gz oracle@10.155.212.225:/dataDir/archives

 

Date format in Linux

In day to day operations of Database Administrator, we may need to write shell script for various things, like backup file renaming, house keeping of trace files, may require to use date format in shell script.

Below are some examples for date formats, you can use for renaming, moving files according to date.

Display Date :

$ date
Wed Mar 1 22:10:12 IST 2017

If we want to format date in other way use simple formula

$ date +FORMAT

$ date +"%m-%d-%y"
03-01-17

$ date +"%m-%d-%Y"
03-01-2017

$ date +"%D"
03/01/17

Display only time

$ date +"%T"
22:13:33

If you want to save this to variable & use for other

$ dt=$(date +"%m-%d-%Y")
$ echo $dt
03-01-2017

If you want to use previous date in variable we can use 1 day ago

$ PREVDATE=$(date --date="1 day ago" +%Y%m%d)
$ echo $PREVDATE
20170228

consider 7 days old date

$ PREVDATE=$(date --date="7 day ago" +%Y%m%d)
$ echo $PREVDATE
20170222

List of formats can be used for date as follows:

%% a literal %
%a locale’s abbreviated weekday name (e.g., Sun)
%A locale’s full weekday name (e.g., Sunday)
%b locale’s abbreviated month name (e.g., Jan)
%B locale’s full month name (e.g., January)
%c locale’s date and time (e.g., Thu Mar 3 23:05:25 2005)
%C century; like %Y, except omit last two digits (e.g., 20)
%d day of month (e.g, 01)
%D date; same as %m/%d/%y
%e day of month, space padded; same as %_d
%F full date; same as %Y-%m-%d
%g last two digits of year of ISO week number (see %G)
%G year of ISO week number (see %V); normally useful only with %V
%h same as %b
%H hour (00..23)
%I hour (01..12)
%j day of year (001..366)
%k hour ( 0..23)
%l hour ( 1..12)
%m month (01..12)
%M minute (00..59)
%n a newline
%N nanoseconds (000000000..999999999)
%p locale’s equivalent of either AM or PM; blank if not known
%P like %p, but lower case
%r locale’s 12-hour clock time (e.g., 11:11:04 PM)
%R 24-hour hour and minute; same as %H:%M
%s seconds since 1970-01-01 00:00:00 UTC
%S second (00..60)
%t a tab
%T time; same as %H:%M:%S
%u day of week (1..7); 1 is Monday
%U week number of year, with Sunday as first day of week (00..53)
%V ISO week number, with Monday as first day of week (01..53)
%w day of week (0..6); 0 is Sunday
%W week number of year, with Monday as first day of week (00..53)
%x locale’s date representation (e.g., 12/31/99)
%X locale’s time representation (e.g., 23:13:48)
%y last two digits of year (00..99)
%Y year
%z +hhmm numeric timezone (e.g., -0400)
%:z +hh:mm numeric timezone (e.g., -04:00)
%::z +hh:mm:ss numeric time zone (e.g., -04:00:00)
%:::z numeric time zone with : to necessary precision (e.g., -04, +05:30)
%Z alphabetic time zone abbreviation (e.g., EDT)

ora-03206: maximum file size of (4194304) blocks in autoextend clause is out of range

While creating the tablespace i faced ORA-03206 error,

ora-03206: maximum file size of (4194304) blocks in autoextend clause is out of range

SQL> create tablespace proddb_tbs datafile '/u01/app/oracle/oradata/proddb/prodb_tbs01.dbf' size 1024m autoextend on maxsize 32768m;

ERROR at line 1: 
ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range

Issue : The maximum file size for an autoextendable file has exceeded the maximum number of blocks allowed.

Solution :

I have given size as 32*1024 = 32768 m (32 GB)

Change the maxsize to 32767m, it will create a tablespace / or add the datafile.

SQL> create tablespace proddb_tbs datafile '/u01/app/oracle/oradata/proddb/prodb_tbs01.dbf' size 1024m autoextend on maxsize 32767m;


Tablespace created.

You can create the tablespace with 32767M or create datafile with 31744M ( 31GB), or you may specify bigfile keyword while creating tablespace.

You may face this issue while creating tablespace or adding datafile.

Daily / Hourly archive log generation

If someone ask for daily log switch frequency & size of archives generated daily, we can use below queries to find the details, Same queries can be executed on RAC setup.

Daily Archive Log Generation :

select trunc(COMPLETION_TIME,'DD') Day, thread#, 
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from v$archived_log 
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Hourly Archive Log Generation :

set pages 1000
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , 
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from v$archived_log 
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

Daily Archive Log Generation :

SQL> select trunc(COMPLETION_TIME,'DD') Day, thread#, 
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from v$archived_log 
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

DAY                     THREAD#         GB ARCHIVES_GENERATED
-------------------- ---------- ---------- ------------------
10-OCT-2016 00:00:00          1         19                233
11-OCT-2016 00:00:00          1         34                417
12-OCT-2016 00:00:00          1         42                522
13-OCT-2016 00:00:00          1         40                487
14-OCT-2016 00:00:00          1         46                550
15-OCT-2016 00:00:00          1         34                418
16-OCT-2016 00:00:00          1         54                651
17-OCT-2016 00:00:00          1         29                356
18-OCT-2016 00:00:00          1         27                322
19-OCT-2016 00:00:00          1         42                514
20-OCT-2016 00:00:00          1         36                442
21-OCT-2016 00:00:00          1         31                364
22-OCT-2016 00:00:00          1         30                359
23-OCT-2016 00:00:00          1         47                554
24-OCT-2016 00:00:00          1         28                336
25-OCT-2016 00:00:00          1         31                384
26-OCT-2016 00:00:00          1         14                187

Hourly Archive Log Generation :

SQL> select trunc(COMPLETION_TIME,'HH') Hour,thread# , 
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from v$archived_log 
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

HOUR                    THREAD#         GB   ARCHIVES
-------------------- ---------- ---------- ----------
10-OCT-2016 07:00:00          1          0          4
10-OCT-2016 08:00:00          1          2         23
10-OCT-2016 09:00:00          1          2         20
10-OCT-2016 10:00:00          1          0          4
10-OCT-2016 11:00:00          1          1          9
10-OCT-2016 12:00:00          1          1         11
10-OCT-2016 13:00:00          1          0          4
10-OCT-2016 14:00:00          1          0          2
10-OCT-2016 15:00:00          1          0          3
10-OCT-2016 16:00:00          1          0          2
10-OCT-2016 17:00:00          1          0          1
10-OCT-2016 18:00:00          1          1         11
10-OCT-2016 19:00:00          1          1         16
10-OCT-2016 20:00:00          1          1         17
10-OCT-2016 21:00:00          1          4         46
10-OCT-2016 22:00:00          1          3         41
10-OCT-2016 23:00:00          1          2         19
11-OCT-2016 00:00:00          1          3         36
11-OCT-2016 01:00:00          1          2         24
11-OCT-2016 02:00:00          1          1          8
11-OCT-2016 03:00:00          1          1          9
11-OCT-2016 04:00:00          1          1         16
11-OCT-2016 05:00:00          1          1          6
11-OCT-2016 06:00:00          1          2         25
11-OCT-2016 07:00:00          1          2         24
11-OCT-2016 08:00:00          1          1         10
11-OCT-2016 09:00:00          1          2         23
11-OCT-2016 10:00:00          1          2         23
11-OCT-2016 11:00:00          1          2         24
11-OCT-2016 12:00:00          1          1         15
11-OCT-2016 13:00:00          1          1         16
11-OCT-2016 14:00:00          1          1          8
11-OCT-2016 15:00:00          1          0          3
11-OCT-2016 16:00:00          1          0          2
11-OCT-2016 17:00:00          1          0          3
11-OCT-2016 18:00:00          1          1         10
11-OCT-2016 19:00:00          1          1         16
11-OCT-2016 20:00:00          1          1         16
11-OCT-2016 21:00:00          1          4         45
11-OCT-2016 22:00:00          1          3         36
11-OCT-2016 23:00:00          1          2         19
12-OCT-2016 00:00:00          1          3         39
12-OCT-2016 01:00:00          1          3         44
12-OCT-2016 02:00:00          1          3         32
12-OCT-2016 03:00:00          1          3         33
12-OCT-2016 04:00:00          1          4         46
12-OCT-2016 05:00:00          1          2         23
12-OCT-2016 06:00:00          1          3         35
12-OCT-2016 07:00:00          1          3         36
12-OCT-2016 08:00:00          1          1         11
12-OCT-2016 09:00:00          1          2         22
12-OCT-2016 10:00:00          1          0          4
12-OCT-2016 11:00:00          1          0          5
12-OCT-2016 12:00:00          1          1          9
12-OCT-2016 13:00:00          1          0          3
12-OCT-2016 14:00:00          1          1         12
12-OCT-2016 15:00:00          1          0          4
12-OCT-2016 16:00:00          1          0          3
12-OCT-2016 17:00:00          1          0          2
12-OCT-2016 18:00:00          1          1         14
12-OCT-2016 19:00:00          1          1         18
12-OCT-2016 20:00:00          1          1         17
12-OCT-2016 21:00:00          1          4         53
12-OCT-2016 22:00:00          1          3         35
12-OCT-2016 23:00:00          1          2         22
13-OCT-2016 00:00:00          1          3         39
13-OCT-2016 01:00:00          1          2         23
13-OCT-2016 02:00:00          1          1          8
13-OCT-2016 03:00:00          1          1         12
13-OCT-2016 04:00:00          1          1         17
13-OCT-2016 05:00:00          1          1         10
13-OCT-2016 06:00:00          1          3         41
13-OCT-2016 07:00:00          1          2         21
13-OCT-2016 08:00:00          1          1          8
13-OCT-2016 09:00:00          1          2         26
13-OCT-2016 10:00:00          1          2         26
13-OCT-2016 11:00:00          1          2         24
13-OCT-2016 12:00:00          1          5         59
13-OCT-2016 13:00:00          1          1         12
13-OCT-2016 14:00:00          1          0          3
13-OCT-2016 15:00:00          1          0          5
13-OCT-2016 16:00:00          1          0          2
13-OCT-2016 17:00:00          1          0          2
13-OCT-2016 18:00:00          1          1         15
13-OCT-2016 19:00:00          1          1         17
13-OCT-2016 20:00:00          1          2         20
13-OCT-2016 21:00:00          1          3         41
13-OCT-2016 22:00:00          1          3         31
13-OCT-2016 23:00:00          1          2         25
14-OCT-2016 00:00:00          1          4         45
14-OCT-2016 01:00:00          1          2         26
14-OCT-2016 02:00:00          1          1         14
14-OCT-2016 03:00:00          1          1          9
14-OCT-2016 04:00:00          1          1         17
14-OCT-2016 05:00:00          1          1          6
14-OCT-2016 06:00:00          1          2         31
14-OCT-2016 07:00:00          1          5         60
14-OCT-2016 08:00:00          1          4         47
14-OCT-2016 09:00:00          1          3         31
14-OCT-2016 10:00:00          1          0          4
14-OCT-2016 11:00:00          1          0          4
14-OCT-2016 12:00:00          1          1          8
14-OCT-2016 13:00:00          1          1         12
14-OCT-2016 14:00:00          1          4         42
14-OCT-2016 15:00:00          1          2         28
14-OCT-2016 16:00:00          1          0          4
14-OCT-2016 17:00:00          1          0          3
14-OCT-2016 18:00:00          1          1         10
14-OCT-2016 19:00:00          1          1         16
14-OCT-2016 20:00:00          1          1         17
14-OCT-2016 21:00:00          1          4         45
14-OCT-2016 22:00:00          1          4         45
14-OCT-2016 23:00:00          1          2         26
15-OCT-2016 00:00:00          1          3         37
15-OCT-2016 01:00:00          1          2         19
15-OCT-2016 02:00:00          1          0          5
15-OCT-2016 03:00:00          1          1         12
15-OCT-2016 04:00:00          1          2         20
15-OCT-2016 05:00:00          1          0          2
15-OCT-2016 06:00:00          1          2         30
15-OCT-2016 07:00:00          1          2         27
15-OCT-2016 08:00:00          1          0          3
15-OCT-2016 09:00:00          1          3         30
15-OCT-2016 10:00:00          1          1          8
15-OCT-2016 11:00:00          1          1          7
15-OCT-2016 12:00:00          1          2         23
15-OCT-2016 13:00:00          1          1          9
15-OCT-2016 14:00:00          1          1          7
15-OCT-2016 15:00:00          1          1          7
15-OCT-2016 16:00:00          1          0          3
15-OCT-2016 17:00:00          1          0          3
15-OCT-2016 18:00:00          1          1         11
15-OCT-2016 19:00:00          1          1         16
15-OCT-2016 20:00:00          1          2         18
15-OCT-2016 21:00:00          1          4         48
15-OCT-2016 22:00:00          1          4         49
15-OCT-2016 23:00:00          1          2         24
16-OCT-2016 00:00:00          1          3         39
16-OCT-2016 01:00:00          1          2         25
16-OCT-2016 02:00:00          1          1          8
16-OCT-2016 03:00:00          1          1         12
16-OCT-2016 04:00:00          1          2         19
16-OCT-2016 05:00:00          1          0          5
16-OCT-2016 06:00:00          1          3         35
16-OCT-2016 07:00:00          1          2         19
16-OCT-2016 08:00:00          1          6         72
16-OCT-2016 09:00:00          1         11        125
16-OCT-2016 10:00:00          1          2         32
16-OCT-2016 11:00:00          1          4         54
16-OCT-2016 12:00:00          1          2         25
16-OCT-2016 13:00:00          1          1          7
16-OCT-2016 14:00:00          1          0          6
16-OCT-2016 15:00:00          1          0          6
16-OCT-2016 16:00:00          1          0          4
16-OCT-2016 17:00:00          1          0          3
16-OCT-2016 18:00:00          1          1         15
16-OCT-2016 19:00:00          1          2         18
16-OCT-2016 20:00:00          1          1         15
16-OCT-2016 21:00:00          1          4         49
16-OCT-2016 22:00:00          1          3         34
16-OCT-2016 23:00:00          1          2         24
17-OCT-2016 00:00:00          1          2         29
17-OCT-2016 01:00:00          1          2         19
17-OCT-2016 02:00:00          1          0          5
17-OCT-2016 03:00:00          1          1         13
17-OCT-2016 04:00:00          1          1         16
17-OCT-2016 05:00:00          1          2         19
17-OCT-2016 06:00:00          1          2         30
17-OCT-2016 07:00:00          1          2         23
17-OCT-2016 08:00:00          1          1         10
17-OCT-2016 09:00:00          1          1         15
17-OCT-2016 10:00:00          1          0          5
17-OCT-2016 11:00:00          1          0          3
17-OCT-2016 12:00:00          1          1          8
17-OCT-2016 13:00:00          1          0          5
17-OCT-2016 14:00:00          1          0          3
17-OCT-2016 15:00:00          1          0          3
17-OCT-2016 16:00:00          1          0          3
17-OCT-2016 17:00:00          1          0          2
17-OCT-2016 18:00:00          1          1          9
17-OCT-2016 19:00:00          1          1         17
17-OCT-2016 20:00:00          1          1         16
17-OCT-2016 21:00:00          1          4         46
17-OCT-2016 22:00:00          1          4         42
17-OCT-2016 23:00:00          1          1         15
18-OCT-2016 00:00:00          1          2         23
18-OCT-2016 01:00:00          1          2         20
18-OCT-2016 02:00:00          1          1          9
18-OCT-2016 03:00:00          1          0          4
18-OCT-2016 04:00:00          1          1          8
18-OCT-2016 05:00:00          1          0          5
18-OCT-2016 06:00:00          1          0          2
18-OCT-2016 07:00:00          1          2         18
18-OCT-2016 08:00:00          1          1          8
18-OCT-2016 09:00:00          1          2         27
18-OCT-2016 10:00:00          1          1          8
18-OCT-2016 11:00:00          1          1         11
18-OCT-2016 12:00:00          1          1          9
18-OCT-2016 13:00:00          1          0          6
18-OCT-2016 14:00:00          1          0          3
18-OCT-2016 15:00:00          1          0          4
18-OCT-2016 16:00:00          1          0          2
18-OCT-2016 17:00:00          1          0          2
18-OCT-2016 18:00:00          1          1         10
18-OCT-2016 19:00:00          1          2         24
18-OCT-2016 20:00:00          1          1         17
18-OCT-2016 21:00:00          1          4         43
18-OCT-2016 22:00:00          1          4         44
18-OCT-2016 23:00:00          1          1         15
19-OCT-2016 00:00:00          1          2         26
19-OCT-2016 01:00:00          1          4         47
19-OCT-2016 02:00:00          1          2         29
19-OCT-2016 03:00:00          1          2         28
19-OCT-2016 04:00:00          1          3         38
19-OCT-2016 05:00:00          1          2         22
19-OCT-2016 06:00:00          1          3         32
19-OCT-2016 07:00:00          1          2         30
19-OCT-2016 08:00:00          1          1         10
19-OCT-2016 09:00:00          1          2         23
19-OCT-2016 10:00:00          1          0          4
19-OCT-2016 11:00:00          1          2         29
19-OCT-2016 12:00:00          1          1         17
19-OCT-2016 13:00:00          1          0          5
19-OCT-2016 14:00:00          1          0          5
19-OCT-2016 15:00:00          1          0          5
19-OCT-2016 16:00:00          1          0          3
19-OCT-2016 17:00:00          1          0          4
19-OCT-2016 18:00:00          1          1         15
19-OCT-2016 19:00:00          1          2         19
19-OCT-2016 20:00:00          1          1         17
19-OCT-2016 21:00:00          1          5         54
19-OCT-2016 22:00:00          1          3         37
19-OCT-2016 23:00:00          1          1         15
20-OCT-2016 00:00:00          1          2         30
20-OCT-2016 01:00:00          1          3         37
20-OCT-2016 02:00:00          1          2         27
20-OCT-2016 03:00:00          1          2         25
20-OCT-2016 04:00:00          1          2         26
20-OCT-2016 05:00:00          1          1         17
20-OCT-2016 06:00:00          1          2         22
20-OCT-2016 07:00:00          1          1         14
20-OCT-2016 08:00:00          1          0          5
20-OCT-2016 09:00:00          1          2         26
20-OCT-2016 10:00:00          1          1         12
20-OCT-2016 11:00:00          1          1         11
20-OCT-2016 12:00:00          1          1          7
20-OCT-2016 13:00:00          1          1         16
20-OCT-2016 14:00:00          1          1         14
20-OCT-2016 15:00:00          1          1         10
20-OCT-2016 16:00:00          1          0          4
20-OCT-2016 17:00:00          1          0          5
20-OCT-2016 18:00:00          1          1         11
20-OCT-2016 19:00:00          1          1         17
20-OCT-2016 20:00:00          1          2         18
20-OCT-2016 21:00:00          1          3         33
20-OCT-2016 22:00:00          1          4         41
20-OCT-2016 23:00:00          1          1         14
21-OCT-2016 00:00:00          1          1         12
21-OCT-2016 01:00:00          1          3         37
21-OCT-2016 02:00:00          1          1         10
21-OCT-2016 03:00:00          1          1          7
21-OCT-2016 04:00:00          1          1          8
21-OCT-2016 05:00:00          1          1          6
21-OCT-2016 06:00:00          1          0          2
21-OCT-2016 07:00:00          1          1         17
21-OCT-2016 08:00:00          1          2         22
21-OCT-2016 09:00:00          1          4         49
21-OCT-2016 10:00:00          1          0          4
21-OCT-2016 11:00:00          1          1         11
21-OCT-2016 12:00:00          1          1          7
21-OCT-2016 13:00:00          1          0          3
21-OCT-2016 14:00:00          1          0          5
21-OCT-2016 15:00:00          1          1          8
21-OCT-2016 16:00:00          1          0          5
21-OCT-2016 17:00:00          1          0          2
21-OCT-2016 18:00:00          1          1         11
21-OCT-2016 19:00:00          1          1         16
21-OCT-2016 20:00:00          1          2         18
21-OCT-2016 21:00:00          1          4         43
21-OCT-2016 22:00:00          1          4         42
21-OCT-2016 23:00:00          1          2         19
22-OCT-2016 00:00:00          1          1         16
22-OCT-2016 01:00:00          1          2         22
22-OCT-2016 02:00:00          1          1          8
22-OCT-2016 03:00:00          1          0          6
22-OCT-2016 04:00:00          1          1         12
22-OCT-2016 05:00:00          1          0          3
22-OCT-2016 06:00:00          1          1          7
22-OCT-2016 07:00:00          1          2         21
22-OCT-2016 08:00:00          1          1          8
22-OCT-2016 09:00:00          1          3         35
22-OCT-2016 10:00:00          1          1         16
22-OCT-2016 11:00:00          1          0          5
22-OCT-2016 12:00:00          1          1          7
22-OCT-2016 13:00:00          1          1         11
22-OCT-2016 14:00:00          1          0          4
22-OCT-2016 15:00:00          1          0          4
22-OCT-2016 16:00:00          1          0          4
22-OCT-2016 17:00:00          1          0          2
22-OCT-2016 18:00:00          1          1         11
22-OCT-2016 19:00:00          1          1         17
22-OCT-2016 20:00:00          1          1         17
22-OCT-2016 21:00:00          1          5         53
22-OCT-2016 22:00:00          1          5         53
22-OCT-2016 23:00:00          1          1         17
23-OCT-2016 00:00:00          1          1         15
23-OCT-2016 01:00:00          1          2         29
23-OCT-2016 02:00:00          1          1          9
23-OCT-2016 03:00:00          1          1          7
23-OCT-2016 04:00:00          1          1         14
23-OCT-2016 05:00:00          1          0          3
23-OCT-2016 06:00:00          1          1         17
23-OCT-2016 07:00:00          1          1         12
23-OCT-2016 08:00:00          1          6         69
23-OCT-2016 09:00:00          1         11        128
23-OCT-2016 10:00:00          1          2         22
23-OCT-2016 11:00:00          1          4         42
23-OCT-2016 12:00:00          1          1         19
23-OCT-2016 13:00:00          1          1          8
23-OCT-2016 14:00:00          1          1          7
23-OCT-2016 15:00:00          1          1          7
23-OCT-2016 16:00:00          1          0          2
23-OCT-2016 17:00:00          1          0          2
23-OCT-2016 18:00:00          1          1         15
23-OCT-2016 19:00:00          1          2         18
23-OCT-2016 20:00:00          1          1         15
23-OCT-2016 21:00:00          1          5         54
23-OCT-2016 22:00:00          1          1         17
23-OCT-2016 23:00:00          1          2         23
24-OCT-2016 00:00:00          1          1         15
24-OCT-2016 01:00:00          1          2         21
24-OCT-2016 02:00:00          1          1          7
24-OCT-2016 03:00:00          1          1          7
24-OCT-2016 04:00:00          1          1         10
24-OCT-2016 05:00:00          1          1          6
24-OCT-2016 06:00:00          1          1         17
24-OCT-2016 07:00:00          1          2         29
24-OCT-2016 08:00:00          1          0          6
24-OCT-2016 09:00:00          1          2         20
24-OCT-2016 10:00:00          1          0          3
24-OCT-2016 11:00:00          1          0          3
24-OCT-2016 12:00:00          1          0          4
24-OCT-2016 13:00:00          1          0          2
24-OCT-2016 14:00:00          1          0          2
24-OCT-2016 15:00:00          1          0          4
24-OCT-2016 16:00:00          1          0          3
24-OCT-2016 17:00:00          1          0          3
24-OCT-2016 18:00:00          1          1         10
24-OCT-2016 19:00:00          1          2         24
24-OCT-2016 20:00:00          1          1         17
24-OCT-2016 21:00:00          1          5         53
24-OCT-2016 22:00:00          1          4         46
24-OCT-2016 23:00:00          1          2         24
25-OCT-2016 00:00:00          1          1         11
25-OCT-2016 01:00:00          1          2         24
25-OCT-2016 02:00:00          1          1          9
25-OCT-2016 03:00:00          1          1          9
25-OCT-2016 04:00:00          1          0          3
25-OCT-2016 05:00:00          1          0          2
25-OCT-2016 06:00:00          1          1          8
25-OCT-2016 07:00:00          1          0          5
25-OCT-2016 08:00:00          1          0          3
25-OCT-2016 09:00:00          1          2         26
25-OCT-2016 10:00:00          1          1         16
25-OCT-2016 11:00:00          1          2         24
25-OCT-2016 12:00:00          1          3         41
25-OCT-2016 13:00:00          1          1          7
25-OCT-2016 14:00:00          1          0          3
25-OCT-2016 15:00:00          1          0          6
25-OCT-2016 16:00:00          1          0          2
25-OCT-2016 17:00:00          1          0          2
25-OCT-2016 18:00:00          1          1         10
25-OCT-2016 19:00:00          1          2         29
25-OCT-2016 20:00:00          1          2         25
25-OCT-2016 21:00:00          1          4         46
25-OCT-2016 22:00:00          1          5         54
25-OCT-2016 23:00:00          1          2         19
26-OCT-2016 00:00:00          1          1         16
26-OCT-2016 01:00:00          1          3         40
26-OCT-2016 02:00:00          1          2         30
26-OCT-2016 03:00:00          1          2         28
26-OCT-2016 04:00:00          1          2         29
26-OCT-2016 05:00:00          1          2         21
26-OCT-2016 06:00:00          1          2         21
26-OCT-2016 07:00:00          1          0          2

385 rows selected.

You (oracle) are not allowed to use this program (crontab)

Today, we have installed oracle on server & were trying to schedule cron job for daily database backup.

When we tried to access the cron, we got an error.

Issue :

$ crontab -l
You (oracle) are not allowed to use this program (crontab)
See crontab(1) for more information

Solution :

This error is due to oracle don’t have access to crontab.

There are two files on server, to allow & deny access to crontab, Files are named as

/etc/cron.allow & /etc/cron.deny

check that if oracle user is present in cron.deny file. if it is present remove oracle user from cron.deny file & add oracle user to cron.allow file.

In my case, oracle user was not present in any of the file.

# cat /etc/cron.allow
root

so, i haved added oracle user to cron.allow file, file will look as follows:

# cat /etc/cron.allow
root
oracle

Now Oracle user able to access the crontab.

$ crontab -l
no crontab for oracle

 

NOTE – If the files“/etc/cron.allow” and “/etc/cron.deny” files are not present on server / system, we can create both files manually.

# touch /etc/cron.allow /etc/cron.deny

Unable to connect with sys, ORA-00020: maximum number of processes (n) exceeded

Today, application team was complaining about connection issue with database.

They were getting ORA-00020: maximum number of processes (150) exceeded error, while trying to connect to database. Issue was due to maximum number of processes exceeded.

When i checked, i was also not able to login to database.

I was also getting the same error.

-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 21 16:58:44 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
 ORA-00020: maximum number of processes (150) exceeded

If you cannot login to the database, because of maximum number of processes exceeded,  there is -prelim option with sqlplus to login with sys user.

Login with sqlplus -prelim “/as sysdba” shut down the database with abort option & increase the process parameter.

-bash-4.1$ sqlplus -prelim "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 21 16:59:48 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL>

 

Multiplex controlfile in RAC / ASM

Sometimes you may need to multiplex the controlfile in RAC or ASM database.

Connect to database from any node in cluster, & check the controlfile location.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +REDO/mcudbprd/controlfile/
                                                  current.271.924459341 
control_management_pack_access       string      DIAGNOSTIC+TUNING

Or you can check,

SQL> select name from v$controlfile;
 
 NAME
 --------------------------------------------------------------------------------
 +REDO/mcudbprd/controlfile/current.271.924459341

I need to multiplex the controlfile to + DATA.

Add + DATA to control_files parameter & keep scope=spfile.

alter system set control_files='+REDO/mcudbprd/controlfile/current.271.924459341','+DATA' scope=spfile;

Shutdown RAC database & start database in nomount state.

$ srvctl stop database -d mcudbprd

$ srvctl status database -d mcudbprd
Instance mcudbprd1 is not running on node 
Instance mcudbprd2 is not running on node 


 srvctl start database -d mcudbprd -o nomount
 srvctl status database -d mcudbprd
Instance mcudbprd1 is running on node 
Instance mcudbprd2 is running on node

Connect to any node & check database status & control_files parameter, +DATA will be added to parameter.

SQL> select name,open_mode from v$database;
select name,open_mode from v$database
                           *
ERROR at line 1:
ORA-01507: database not mounted

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +REDO/mcudbprd/controlfile/cur
                                                 rent.271.924459341, +DATA
control_management_pack_access       string      DIAGNOSTIC+TUNING

Now connect to RMAN & issue below command, it will create a copy of controlfile to + DATA

restore controlfile from '+REDO/mcudbprd/controlfile/current.271.924459341';

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 5 18:34:57 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: mcudbprd (not mounted)

RMAN>

RMAN> restore controlfile from '+REDO/mcudbprd/controlfile/current.271.924459341';

Starting restore at 05-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=247 instance=mcudbprd1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+REDO/mcudbprd/controlfile/current.271.924459341
output file name=+DATA/mcudbprd/controlfile/current.266.924460567
Finished restore at 05-OCT-16

RMAN> exit

When RMAN restores control file to new location, it updates the new path in spfile.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +REDO/mcudbprd/controlfile/cur
                                                 rent.271.924459341, +DATA/ucmd
                                                 bprd/controlfile/current.266.9
                                                 24460567
control_management_pack_access       string      DIAGNOSTIC+TUNING

Stop the database & Start database.

srvctl stop database -d mcudbprd

srvctl start database -d mcudbprd

Once the database is open, check control_files parameter.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +REDO/mcudbprd/controlfile/cur
                                                 rent.271.924459341, +DATA/ucmd
                                                 bprd/controlfile/current.266.9
                                                 24460567
control_management_pack_access       string      DIAGNOSTIC+TUNING

 

Drop database in Oracle 11gR2 RAC

Steps to drop RAC database.

1. Before dropping database, check cluster resources & database status using              crsctl stat res -t command.
2. Connect to server ( can connect to any node ).
3. Check DISPLAY parameters for GUI mode.
4. Execute DBCA from command line, It will open the Welcome screen for dbca.
5. Select 1st option. Oracle Real Application Cluster Database.
6. Select Delete Database.
7. Select the database which needs to be dropped, if multiple databases are running.
8. Click on Finish.
9. It will drop the database.
10. Check again cluster resources & database status using crsctl stat res -t command. dropped database resources wouldn’t be available.

 

SCAN Listener in INTERMEDIATE status with “Not All Endpoints Registered”

While checking cluster status, it was showing Not All Endpoints Registered for scan listener with INTERMEDIATE status.

ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       prodracdb1        
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  INTERMEDIATE prodracdb2         Not All Endpoints Registered  
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  INTERMEDIATE prodracdb2         Not All Endpoints Registered

Cause : 

Listener was started from $ORACLE_HOME instead of $GRID_HOME

Solution :

1. Check the listener status.

[oracle@prodracdb2bin]$ ps -ef|grep tns
root       245     2  0  2015 ?        00:00:00 [netns]
oracle    9943     1  0 Sep16 ?        00:00:39 /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle    9976     1  0 Sep16 ?        00:01:48 /oracle/app/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
oracle    9986     1  0 Sep16 ?        00:01:45 /oracle/app/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
oracle   45140 36935  0 10:47 pts/0    00:00:00 grep tns

2. Stop the listener running from ORACLE_HOME.

[oracle@prodracdb2bin]$ /oracle/app/oracle/product/11.2.0/db_1/bin/lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-OCT-2016 10:47:58

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

Check the listener status again to check listener running from database home.

[oracle@prodracdb2bin]$ ps -ef|grep tns
root       245     2  0  2015 ?        00:00:00 [netns]
oracle    9976     1  0 Sep16 ?        00:01:48 /oracle/app/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
oracle    9986     1  0 Sep16 ?        00:01:45 /oracle/app/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
oracle   46019 36935  0 10:47 pts/0    00:00:00 grep tns

3. Stop the listener from GRID_HOME

$<GRID_HOME>/bin/srvctl stop listener -n <node name>
$<GRID_HOME>/bin/srvctl stop scan_listener -i <scan#>

srvctl stop listener -n prodracdb2
srvctl stop scan_listener -i 2
srvctl stop scan_listener -i 3

4. Start the LISTENER and LISTENER_SCAN2,LISTENER_SCAN3  from GRID_HOME

srvctl start listener -n prodracdb2
srvctl start scan_listener -i 2
srvctl start scan_listener -i 3

5. Check the cluster status again.

ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       prodracdb1
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       prodracdb2
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       prodracdb2

Now SCAN listener is showing online.

 

Authentication token is no longer valid; new one required – crontab issue

Yesterday, i was checking backup, while checking i came to know that backup was no executed. when i tried to access crontab i got an error:

-bash-4.1$ crontab -l

Authentication token is no longer valid; new one required
You (oracle) are not allowed to access to (crontab) because of pam configuration.

Error is related to password expiry, to check chage -l username

-bash-4.1$ chage -l oracle
Last password change                                    : Aug 16, 2016
Password expires                                        : Sep 30, 2016
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : 0
Maximum number of days between password change          : 45
Number of days of warning before password expires       : 7

It shows password expired on sept 30, 2016.

# passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

login with oracle  & check crontab.

# su - oracle
-bash-4.1$
-bash-4.1$ crontab -l
00 23 * * * /u01/scripts/db_expdp.sh