Database auto-start and Stop

Hi folks, I am going to demonstrate in this post the process of writing and launching a script to automatically start and stop a database and listener on Oracle. I am working in this environment:

Oracle 11.2.0.1.0
Linux 5.4
instance name PATIENTS

Purpose: creating an auto-start script on PATIENTS database

Log in as Oracle

vi start_PATIENTS

add this line:

#!/bin/sh

ORACLE_SID=PATIENTS

export ORACLE_SID

PATH=$ORACLE_HOME/bin:$PATH

export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH

echo "Starting Database $ORACLE_SID"

sqlplus /nolog >/dev/null <

     connect / as sysdba

     startup;

     exit

EOF

if [ $? != 0 ] ; then

 echo "Database $ORACLE_SID did not start!"

else

 echo "Database $ORACLE_SID started!"

fi

vi stop_PATIENTS

add this line:

#!/bin/sh

ORACLE_SID=PATIENTS

export ORACLE_SID

PATH=$ORACLE_HOME/bin:$PATH

export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH

echo "Stopping Database $ORACLE_SID"

sqlplus /nolog >/dev/null <

     connect / as sysdba

     shutdown immediate;

     exit

EOF

if [ $? != 0 ] ; then

 echo "Database $ORACLE_SID did not stop!"

else

 echo "Database $ORACLE_SID stopped!"

fi

vi start_listener

add this line:

#!/bin/sh

echo "Starting listener"

lsnrctl start > /dev/null

if [ $? != 0 ] ; then

  echo "listener did not start"

else

  echo "listener started!"

fi

vi stop_listener

add this line:

#!/bin/sh

echo "Stopping listener"

lsnrctl stop > /dev/null

if [ $? != 0 ] ; then

  echo "listener did not stop"

else

  echo "listener stopped!"

fi

change file to executable

[oracle@localhost ~]$ chmod +x start_PATIENTS
[oracle@localhost ~]$ chmod +x stop_PATIENTS
[oracle@localhost ~]$ chmod +x start_listener
[oracle@localhost ~]$ chmod +x stop_listener

launching the script:

[oracle@localhost ~]$ ./start_listener
Starting listener
listener started!

[oracle@localhost ~]$ ./start_PATIENTS
Starting Database PATIENTS
Database PATIENTS started!

[oracle@localhost ~]$ ./stop_PATIENTS
Stopping Database PATIENTS
Database PATIENTS stopped!

[oracle@localhost ~]$ ./stop_listener
Stopping listener
listener stopped!


launching the script:

Log in as Oracle (SSH or Putty)

From /home/oracle, launch the files.

Start database in this order….

Launch start_listener
Launch start_PATIENTS

Stop database this way….

Launch stop_PATIENTS
Launch stop_listener


You should get this message after launching the script…

[oracle@localhost ~]$ ./start_listener
Starting listener
listener started!

[oracle@localhost ~]$ ./start_PATIENTS
Starting Database PATIENTS
Database PATIENTS started!

[oracle@localhost ~]$ ./stop_PATIENTS
Stopping Database PATIENTS
Database PATIENTS stopped!

[oracle@localhost ~]$ ./stop_listener
Stopping listener
listener stopped!

Thanks for reading.

RMAN-06169: could not read file header for datafile 7 error reason 9

Error
While attempting to run a backup of the database, we got an error identifying a problem with a datafile. In this post, I will discuss the cause of the problem and how the error was resolved.

rman target sys/****@PUBLIC

Starting backup at 01-APR-13
using channel ORA_DISK_1
RMAN-06169: could not read file header for datafile 7 error reason 9
RMAN-06169: could not read file header for datafile 7 error reason 9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 04/01/2013 15:54:32
RMAN-06056: could not access datafile 7

cause
There is a problem with the datafile 7. It could be due to corruption, or any other problem with the datafile. Oracle will not be able to back up the database except this datafile is restored. Alternative, as a temporary solution, you can skip the datafile in your backup. If the datafile is skipped, RMAN will be able to back up the database but it is not advisable.


solution:

Check the status of the datafile
sqlplus sys/***@PUBLIC

SQL> select file#, status from v$datafile where file# = 7;
FILE# STATUS
---------- -------
7 RECOVER

SQL> select file#, status, enabled from v$datafile where file# = 7;
FILE# STATUS ENABLED
---------- ------- ----------
7 RECOVER READ WRITE
exit;

Restore the datafile from backup
A copy of the datafile in backup can be used to restore the datafile. Simply log back into rman and issue this command:
RMAN> restore datafile 7;
datafile restored

NOTE: You do not need to put your database in mount mode to do this.

Recover the datafile
Recovering a datafile after restore brings it up to date. At this stage, archivelogs and incremental backups will be applied to bring the datafile to the point it was before the problem.

RMAN> recover datafile 7;
starting media recovery
database recovered


For more details on RMAN recovery and restore process, check out Oracle documentations at RMAN

TEMPFILE ERROR ON ORACLE 10r2/LINUX

Hi folks. Here is one of the errors you can encounter on your job as a Database Java1istrator.
I was running a full export of a database (software) and I ran into this error. I hope sharing this error and the solution will help someone out there.

Environment
Linux 4.9
Oracle 10.2
Database name: software

Error
Linux Error: 2: No such file or directory
Additional information: 3

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6234
----- PL/SQL Call Stack -----
object line object
handle number name
0x34ec54e8 14916 package body SYS.KUPW$WORKER
0x34ec54e8 6293 package body SYS.KUPW$WORKER
0x34ec54e8 2339 package body SYS.KUPW$WORKER
0x34ec54e8 6854 package body SYS.KUPW$WORKER
0x34ec54e8 1259 package body SYS.KUPW$WORKER
0x5803b23c 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_06" stopped due to fatal error at 18:54:47


[oracle@software ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 26 18:58:12 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

SQL> desc v$tempfile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)

SQL> select name, status from v$tempfile;

NAME STATUS
-------------------------------------
/u10/oradata/software/temp01.dbf ONLINE

/u10/oradata/software/java1temp01.dbf ONLINE

/u10/oradata/software/java2temp01.dbf ONLINE

/u10/oradata/software/java3temp01.dbf ONLINE

4 rows selected.

Here comes the errors . From here you have a clue where the problem is and what you need to do to resolve this issue

SQL> desc dba_tempfile;
ERROR:
ORA-04043: object dba_tempfile does not exist


SQL> desc dba_tempfiles;
ERROR:
ORA-04043: object dba_tempfiles does not exist


SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;
ERROR:
ORA-01116: error in opening database file 204
ORA-01110: data file 204: '/u10/oradata/software/java301.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
no rows selected

Solution

From this error, you can tell that there is a problem with a tempfle named '/u10/oradata/software/java301.dbf'. What we need to do is figure out how to get that tempfile online and alter the user presently using this tempfile to use the default tempfile from TEMP tablespace.

Crosscheck the default and temporary tablespaces in the database. This will give you an idea of where to go to get the issue resolved

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
JAVA1
JAVA1TEMP
JAVA2
JAVA2TEMP
JAVA3
JAVA3TEMP


Check for the user(s) presently using the JAVA3TEMP that has issues

SQL> select username from dba_users
2 where temporary_tablespace='JAVA3TEMP';

USERNAME
------------------------------
JAVA3

Change the temporary tablespace of user JAVA3 from JAVA3TEMP to TEMP. This will ensure that the user can use the default temporary tablespace

SQL> alter user JAVA3
2 temporary tablespace TEMP;

User altered.

Check to ensure that the user is properly altered to use TEMP instead of JAVA3TEMP

SQL> select username from dba_users
2 where temporary_tablespace='JAVA3TEMP';

no rows selected

Take the problematic tempfile offline

SQL> alter database tempfile '/u10/oradata/software/java3temp01.dbf' offline;

Database altered.

Please drop a comment if you have any questions. Thanks and watch this space for more posts!

Oracle user creation and privileges in 10g/11g

Oracle user creation and privileges in 10g/11g

Creating a user and setting privileges for them are common everyday tasks of Oracle DBAs and Developers. You can either do them through the commandline or use client tools, eg. SQL Developer.

creating a default tablespace for the user
Default tablespace is the main tablespace where all the objects of a user reside. A quota has to be set for the user on this tablespace, else oracle will assume the quota is unlimited. Here is the syntax to create a default tablespace for our user:

CREATE TABLESPACE ORACLE_USER
DATAFILE '/home/oracle/app/oracle/oradata/ORACLE_USER/datafile/ORACLE_USER01.dbf' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

creating a temporary tablespace for the user
Temporary tablespace is useful for sorting. Here is the syntax to create a temporary tablespace for our user:

CREATE SMALLFILE TEMPORARY TABLESPACE ORACLE_USERTEMP TEMPFILE '/home/oracle/app/oracle/oradata/ORACLE_USER/datafile/ORACLE_USERtemp01.dbf' SIZE 5M AUTOEXTEND ON NEXT 2048K MAXSIZE 1024M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

creating the user
When creating the user, these are important options:

"identified by" is used to specify the password of the user
"profile" the resource limits specified for the user. We will look at profile and roles in subsequent postings
"default tablespace" is used to specify where the objects of the user will be kept
"quota" specifies the percentage of the space in the default tablespace that is assigned to the user
"temporary tablespace" is already explained above
""account unlock" specifies the status of the account. If you specify "account lock", the account will be locked and the user will not be able to connect after creation.

CREATE USER ORACLE_USER PROFILE "DEFAULT" IDENTIFIED BY "XXXXX"
DEFAULT TABLESPACE ORACLE_USER
TEMPORARY TABLESPACE ORACLE_USERTEMP
QUOTA UNLIMITED ON ORACLE_USER ACCOUNT UNLOCK;

granting privileges for the user
This gives the user the privilege to access the database. "Create session" must be granted for the user to be able to connect to the database. Other privileges are self-explanatory. Oracle did a good job by using simple English meaning for the database terms.

GRANT CREATE SESSION TO ORACLE_USER;
GRANT CONNECT TO ORACLE_USER;
GRANT CREATE TRIGGER TO ORACLE_USER;
GRANT CREATE VIEW TO ORACLE_USER;
GRANT CREATE PROCEDURE TO ORACLE_USER;
GRANT CREATE SEQUENCE TO ORACLE_USER;
GRANT CREATE TABLE TO ORACLE_USER;
GRANT CREATE SYNONYM TO ORACLE_USER;

checking privileges granted to user
You can query the view "dba_sys_privs" to check the privileges granted to a user at any point in time. Here is an example:

SQL> select privilege from dba_sys_privs
2 where grantee='ORACLE_USER';

PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE TABLE
CREATE SEQUENCE
CREATE SYNONYM
CREATE PROCEDURE
CREATE SESSION
CREATE VIEW
7 rows selected.

checking the user's info
You can query the view "dba_users" to check the user's info. For example:

SQL> select username, default_tablespace from dba_users where username='ORACLE_USER';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_USER ORACLE_USER

SQL>

dropping a user
You can delete a user by using the syntax:

drop user ORACLE_USER cascade;

This will permanently delete the user and all its objects from the database. It will also purge all the users objects in the recycle bin in Oracle 11g.