How to enable or disable archive log mode in Oracle 11g

Check current archive log mode

[oracle@oel68 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 12 22:27:44 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Current log sequence           6
SQL>

Enable archive log mode

Steps to enable archive log mode are to shutdown the database,  startup in mount mode, enable archive log mode and open the database.

Optional steps are to take backup after shutting down the database, and to shutdown again and take backup after enabling archive log mode.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1553305600 bytes
Fixed Size            2213656 bytes
Variable Size          973080808 bytes
Database Buffers      570425344 bytes
Redo Buffers            7585792 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6

Where are my archive log files

By default archive log files are on use_db_recovery_file_dest

SQL> show parameter recovery_file_dest

NAME                     TYPE     VALUE
———————————— ———– ——————————
db_recovery_file_dest             string     /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size         big integer 3882M

Disable archive log mode

Steps to disable archive log mode are to shutdown the database,  startup in mount mode, disable archive log mode and open the database.

Again, taking backup is optionally but recommended.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1553305600 bytes
Fixed Size            2213656 bytes
Variable Size          973080808 bytes
Database Buffers      570425344 bytes
Redo Buffers            7585792 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Current log sequence           6
SQL>

Ifconfig command not found error

If you type in the console of a CentOS or Oracle Linux ifconfig command, you may get command not found error.

Of course, this doesn’t mean that this command really doesn’t exists, it just means that the shell can not find it on your PATH to execute it.

To check your path, type

echo $PATH

Folder /sbin/ won’t be there, and that is the reason for command not found error.

So, you can try to locate ifconfig with

[user@host ~]$ whereis ifconfig

ifconfig: /sbin/ifconfig /usr/share/man/man8/ifconfig.8.gz

Why whereis command for this?

Because it searches binary, source and man pages for a input.

 
And now, for getting your IP settings just type

/sbin/ifconfig

Other way is to add /sbin/ to your PATH ( PATH=$PATH:/sbin:/usr/sbin/ ) and just type ifconfig. 😉

Enterprise Manager is not able to connect to the database instance

Here is the situation:

– database is Oracle 11g

– OS is CentOS Linux

– database listener is up and running

– connections to the database are normal( SYS, SYSTEM, local database users can login)

– Enterprise Manager gives this error – Enterprise Manager is not able to connect to the database instance

– agent connection is down

– database seems to be down in the EM browser

So, this narrows it down to OEM problem, not the database or listener.

First, check status of the Enterprise Manager with

emctl status dbconsole

You will get something like this

Oracle Enterprise Manager 11g is running.

——————————————————————

Logs are generated in directory $ORACLE_HOME/sysman/log

You will probably find in your emoms.log

ORA-28001: the password has expired

In Oracle 11g default password for database users is 180 days. In this post I wrote about changing password for various users, but here I will show you how to get Enterprise Manager working after password has expired.

So, we have to change SYSMAN user password.

Note, this is for Oracle 11g only!

For 10g this procedure is a little bit different.

1) Stop enterprise manager if it is running

emctl stop dbconsole

2) Connect to the database

sqlplus / as sysdba

3) Give SYSMAN new or the same password as it was before expiring

alter user sysman identified by password;

alter user sysman account unlock;

4) Try to connect with new password to verify is everything ok

sqlplus sysman/password

5) Now get OEM to register the change

emctl setpasswd dbconsole

6) Type password from step 3

7) Now start OEM

emctl start dbconsole

8) Check the status of OEM and agent

emctl status dbconsole

emctl status agent

9) Now login to OEM from your browser

 

Fedora 17 add user to administrators group

Fedora 17 offers in a process of a creating first user on a system check box to add user to administrators ( wheel ) group.

With that privilege you can install software, edit configuration files and do other administrative work on your system, with your user and password instead of doing this as a root with root password.

If you forgot to do this when creating user, it’s very simple add it later.

Start console and become root.

usermod -a -G whell yourUserName

Usermod is a command that modifies users account.

-a means add user to some groups. But this should only be used with -G option like in this example, to avoid removing user from groups that are not listed in a command.

Wheel is a name of administrators group.

And if you don’t like console work, you can do this with GUI too.

Start Users and Groups, enter the root password, click properties for your user, and then check wheel group from groups tab.

Getting help from the Linux shell(man, apropos, whatis)

You are sitting in front of a Linux terminal, no access to internet, no friends around and you need some help for commands you are about to execute. So what now, from where you can get help?

From that same terminal of course 🙂

In terminal, there are a lot of tools designed to help user, explain how commands work, how to find exactly the command that you need, and what are available options for that command.

For this purpose we can use these commands – man, apropos, whatis.

Man command
===========

Man pages, short for manual pages are built-in system documentation. This documentation is divided into sections. Execute man man command to read more about man pages. Sections are:

1   Executable programs or shell commands
2   System calls
3   Library calls
4   Special files
5   File formats and conventions

6   Games
7   Miscellaneous
8   System administration commands
9   Kernel routines
Basic usage of this command ( for ls command as example ) is

man ls

Apropos
=======
Apropos search the manual pages and descriptions, and it searches strings, not complete words and commands. Very handy if you need the command, you know a part of it or its description, but you can’t remember exact command name.

Whatis
======
Whatis searches lists of commands, but for a complete word match.

See this example to better understand difference between apropos and whatis.

apropos delete

and

whatis delete

First command lists all commands displays all commands and descriptions with delete word, and second returns nothing apropriate. 🙂

First question now could be – can I get a list of all available commands in my Linux box?
We can use

apropos .

or

man -K .

Dot (.) here means ‘any character’.
Since there are many command you could redirect these commands to a file, using >

apropos . > commands.txt

for example.

Second question here could be – how can i get a list of all available commands from section 8 ( system administration commands ) ?
Use man or apropos, but limit command to show you only those commands like this

apropos -s 8 .

Third question here could be – how can I find all the commands that have something to do with delete word? For example delete user, delete file, folder, group etc.
Use

apropos . -w ‘*delete*’

or

apropos delete

And result is something like this

argz_delete (3)      – functions to handle an argz list
at (1)               – queue, examine or delete jobs for later execution
batch (1)            – queue, examine or delete jobs for later execution
groupdel (8)         – delete a group
lppasswd (1)         – add, change, or delete digest passwords.
mdel (1)             – delete an MSDOS file
mdeltree (1)         – recursively delete an MSDOS directory and its rmdir (2)            – delete a directory
……….

Or you could use

man -K delete

for similar results, but this query is a little slower.

After this, search man page for command that you need to see all available options and usage.
And what is the number meaning in this output? Numbers represents sections earlier mentioned.

Ok, so now that you have your command, how to see exactly how this command work?
Man pages of course, but you can use info command al well.

Sometimes info command can provide more detailed explanation than man command.

Meaning of dot slash (./) in Linux

A colleague of mine was confused about dot slash command (./) when he was executing some shell scripts.

He was not in the directory where script was and he tried to execute it with ./ command. And of course, terminal couldn’t find his script. He thought ./scriptName was the only way to execute script in Linux. He was wrong of course, but then we started a discussion about people execute commands, get the job done, but in some cases they don’t really know what they are doing. 🙂

So, you can execute shell script in a few ways, for example

./test.sh

if you are in the directory where script is, or you can use absolute path( if you are or are not in the directory where script is )

/home/user/test.sh

or you can specify the interpreter in the command line like

sh test.sh

if your script is in directory from which you execute command, or

sh /home/user/test.sh

if you are not in that directory.

That’s all nice, but let me briefly explain dot slash meaning here.

Dot means current directory, and slash is directory path separator.

And ./ means execute script from my current directory.

Dot (.), or current directory is never on the PATH ( echo PATH to check this ) for security reasons and it never should be.

ORA-03136: Inbound Connection Time Out

Oracle documentation about this error:

ORA-03136: inbound connection timed out

Cause: Inbound connection was timed out by the server because user authentication was not completed within the given time specified by SQLNET.INBOUND_CONNECT_TIMEOUT or its default value

Action:

1) Check SQL*NET and RDBMS log for trace of suspicious connections.

2) Configure SQL*NET with a proper inbound connect timeout value if
necessary.

Default timeout is 60 seconds in Oracle 10g.

 
There are a few reasons why this error occurs.

 
– The server gets connection from a client that is not allowed to connect to the database. In this case ORA-03136 is correct behaviuor and you may want  to check log files to see address of that client to take further actions.

 
– The server receives a connection from a client that is allowed to connect, but client takes a long period of time to authenticate his request (usually firewall or other network problems).

 
– Database server is heavily loaded, large RAM or CPU usage, and can’t finish job about logging client within timeout specified. Check state of database and server in a period of time when this error ocured in a log files.

 
Workaround to avoid these ORA messages is to increase timeout parameter SQLNET.INBOUND_CONNECT_TIMEOUT in your $ORACLE_HOME/network/admin/sqlnet.ora file, or add this variable if it isn’t there already.

 

SQLNET.INBOUND_CONNECT_TIMEOUT = 120

 
For example if you set this value to 0, timeout is unlimited. Or you can set some other value, 120 ( for 120 seconds period for timeout ) or else.
And you can change this value in listener too.
From the console

$ lsnrctl> set inbound_connection_timeout 120

Linux history command(change HISTSIZE variable)

History is a very handy command to see what was typed and executed in a shell for a period of time.

For example, you are troubleshooting Linux server and you want to see the last 20 commands executed.

$ history 20

Or you want to find all commands with sudo word in them.

$ history | grep sudo

You can combine with less, tail and so on, it all depends of your needs.

After search you’ve found a command with number 234 and you want to execute it again.

$ !234

For more details about history command -> man history.

So how this works, how long is history and how to change its size?

Shell command history is stored in a .bash_history file in your /home folder ( cat .bash_history to see all the entries there ).

Number of commands is defined by histsize variable.

Type

$ echo $HISTSIZE

or

$ env | grep HISTSIZE

to see the number of commands that can be stored in this file.

My default number on Oracle Linux is 1000.

But what if you want to store 3000 commands for example, how can you change this variable?

Well quite simply, just use gedit or vi editor.

Change .bash_profile, add HISTSIZE=3000, export this variable to override one in /etc/profile, save file and load the changes with

$ source .bash_profile

Your .bash_profile should look like this

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

………

HISTSIZE=3000
export PATH HISTSIZE

So now you can store up to 3000 command in your history.

How to kill Oracle sessions

There are few ways to kill blocking or hanging Oracle sessions.

But beware when you are doing this, because a small mistake in SID or PID number can bring down entire database( if you kill some Oracle background process for example).

So first we have to identify session that we want to kill.

This can be done from the Enterprise Manager or from the terminal.

select s.sid, s.serial#, p.spid, s.osuser, s.program
from v$process p,
v$session s
where p.addr = s.paddr;

If you know problematic SID, you can add AND clause to this query and get all the data you need.

Alter system kill session

Basic syntax is

alter system kill session ‘sid, serial#’;

When you mark session to be killed, any active transaction is rolled back, so if you had a lot of data changes that are not commited, undo work must be done before session is really killed. And this can take a while.

alter system kill session ‘sid, serial#’ immediate;

From Oracle documentation

Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.

Alter system disconnect session

Basic syntax is

alter system disconnect session ‘sid, serial#’ post_transaction;

alter system disconnect session ‘sid, serial#’ immediate;

This command kills server process.

From Oracle documentation.

The POST_TRANSACTION setting allows ongoing transactions to complete before the session is disconnected. If the session has no ongoing transactions, then this clause has the same effect described for as KILLSESSION.

The IMMEDIATE setting disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.

Linux way

From the terminal with the kill command.

kill spid;

kill -9 spid;

%:kill 235

Windows way

From the terminal with the orakill command.

orakill instanceName spid;

C:\orakill myoradb 235;

ORA-28002: the password will expire within 7 days

On one of my development 11g databases I got this error when I connected with SqlPlus. Yeah, I completely forgot about this new feature of 11g version.

Default profile has more strict password rules, and now, default lifetime of password is 180 days. You can check your settings with

select * from dba_profiles order by 1;

And the result will be something like this

“PROFILE”    “RESOURCE_NAME”                 “RESOURCE_TYPE”    “LIMIT”
DEFAULT       FAILED_LOGIN_ATTEMPTS           PASSWORD                    10
DEFAULT       PASSWORD_GRACE_TIME            PASSWORD                      7
DEFAULT       PASSWORD_LIFE_TIME                 PASSWORD                   180

My test user has a default profile, so before making any changes, check the profile of your user.

select username, profile from dba_users;

Notice that SYS and SYSTEM users have default profile too.

So, the simple solution is to change passwords for all users that are members of the default profile.

alter user hr identified by <password>;

alter user sys identified by <password>;

And so on for all users.

But their new password’s lifetime is again 180 days.

Other solution is to set password_life_time to unlimited to the default profile.

alter profile default limit password_life_time unlimited;

And now the password will never expire.

Of course, this policy depends if you are using test or development database, or your production database.

For production databases go with your company security policies.