BNFD - Your DBA NoteBook On The Net!!!

Monday, October 27, 2008

Hot Backup in Oracle

When datafiles are put into hot backup mode, Oracle is still writing to them
Oracle slightly changes the way it performs updates to datafiles by copying whole datafile blocks, rather changed bytes , into the online redo logs.
Hot backups, by their very nature,take a corrupt copy of the datafiles.Since the instance is in ARCHIVELOG mode, all changes to the datafiles are available upon restore to correct any discrepancies created as the archiving process passes over any given datafile. This is why Oracle records redo information in whole blocks during hot backup: to help recover any inconsistent blocks in the datafiles upon restore.
And also during Hot backup mode oracle doesnt update the SCN in the header of the Datafile.
Online redo log files should never be backed up in hot backup mode.

STEPS:
1)Get listing of tablespaces and datafiles
(SELECT tablespace_name, file_name FROM sys.dba_data_files).

2. For each tablespacea) Put tablespace in hot backup mode
(ALTER TABLESPACE $TABLESPACENAME BEGIN BACKUP).
b) Copy, archive, or snapshot each datafile in this tablespace.
c) End hot backup mode for this tablespace
(ALTER TABLESPACE $TABLESPACENAME END BACKUP).

3. Backup the Oracle “controlfile”
(ALTER DATABASE BACKUP CONTROLFILE to trace)

4. Confirm all tablespaces returned to normal mode (SELECT FILE#,STATUS,CHANGE#,TIME FROM v$backup WHERE STATUS != 'NOT ACTIVE').

5.ALter System switch logfile ( it ll force the checkpoint which ll update SCN in the datafiles header)

6. Perform an archive log switch (ALTER SYSTEM ARCHIVE LOG CURRENT).
7. Backup archived redo logs.

Wednesday, October 22, 2008

Create Database in oracle

After you have install the binaries using Oracle Universal Installer you need to create the database. And you can do that in two ways:
Using the Database Configuration Assistant (DBCA)
With the SQL “create database” statement


Using “DBCA” :

The Database Configuration Assistant (DBCA) an Oracle supplied tool that enables you to create an Oracle database, configure database options for an existing Oracle database, delete an Oracle database, or manage database templates.
command prompt ( start->run->cmd->dbca).

Using SQL “create database” statement:


Step 1: Decide on Your Instance Identifier (SID)
There can be more than one Oracle instance on a single machine. In order to be able to distinguish these instances, Oracle uses a SID (System Identifier) which is a string.
The SID can be set through the ORACLE_SID environment
Example:

>set ORACLE_SID = OraProdEurope

Step 2: Establish the Database Administrator Authentication Method

Step 3: Create the initialization parameter file
When an Oracle instance starts up, it requires either an initialization paramter file (init.ora) or an SPFILE. SPFILES have binary content and must be created from init.ora files. Therefore, the init.ora file (which is an ordinary text file) is created first.
Create the init.ora file and put it in below path.
Unix Path is: $ORACLE_HOME/dbs
Windows path is: %ORACLE_HOME%\database

Step 4: Connect to the Instance
$ SQLPLUS /nolog
SQL>CONNECT SYS/password AS SYSDBA
Connected to an idle instance.

SQL*Plus tells us that we're connected to an idle instance. That means that it is not yet started. So, let's start the instance. We have to start the instance without mounting (nomount) as there is no database we could mount at the moment.

Step 5: Start the Instance.
SQL> STARTUP NOMOUNT

This created the SGA (System Global Area) and the background processes

Step 6: Issue the CREATE DATABASE Statement
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 ('/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
DATAFILE '/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

If something goes wrong with the creation, Oracle will write an error into the alert.log. The alert log is normaly found in the directory that is specified with the background_dump_dest. If this parameter was not specified (as is the case in our minimal init.ora), the alert.log will be written into %ORACLE_HOME%/RDMBS/trace.
If an ORA-01031: insufficient privileges is returned, that means most likely, that the current user is not in the dba group (on unix), or the ORA_DBA (windows).
If the init.ora file is not at its default location or has not been found with the pfile attribute, an ORA-01078: failure in processing system parameters and an LRM-00109: could not open parameter file '/appl/oracle/product/9.2.0.2/dbs/initadpdb.ora' error is issued.
The create database command also executes a file whose name is determined by the (hidden) init parameter _init_sql_file (which seems to default to sql.bsq)
After the creation of the database, it can be mounted and opened for use.

Step 7: Create Additional Tablespaces

CONNECT SYS/password AS SYSDBA
-- create a user tablespace to be assigned as the default tablespace for users
CREATE TABLESPACE users LOGGING
DATAFILE '/oracle/oradata/mynewdb/users01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING
DATAFILE '/oracle/oradata/mynewdb/indx01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
EXIT

Step 8: Run Scripts to Build Data Dictionary Views
CONNECT SYS/password AS SYSDBA
(In order to complete the db creation, the following scripts must be run as sys )

@/oracle/rdbms/admin/catalog.sql
@/oracle/rdbms/admin/catproc.sql
EXIT

CATALOG.SQL
Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL
Runs all scripts required for or used with PL/SQL.

The user system might also want to run /sqlplus/admin/pupbld.sql.
pupbld.sql creates a table that allows to block someone from using sql plus.

Monday, October 20, 2008

USER Profiles

Profiles in Oracle
Profiles are a means to limit resources a user can use.
Before profiles can be assigned, they must be created with create profile. Then, they can be assigned to users with alter user ... profile.
For Example:

CREATE PROFILE
SQL> CREATE PROFILE developer LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 0
PASSWORD_REUSE_MAX 0
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 2
CPU_PER_CALL 3000
PRIVATE_SGA 500K
LOGICAL_READS_PER_CALL 1000;

ALTER Profile:
SQL>ALTER PROFILE developer LIMIT FAILED_LOGIN_ATTEMPTS 3;

Drop Profile without user:
SQL> drp profile developer;

Drop Profile with user:
SQL> drp profile developer CASCADE;

Assigna profile to User:
SQL> alter user rama profile developer ;


Limitable resources

RESOURCE_LIMIT=TRUE is required for resource limiting portions of the profile. Password limiting functionality is not affected by this parameter.

SQL>SELECT name, valueFROM gv$parameterWHERE name = 'resource_limit';
SQL> ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;

Kernel limits
Maximum concurrent sessions for a user(sessions_per_user)
CPU time limit per session(cpu_per_session)
CPU time limit per call(cpu_per_call)Call being parse, execute and fetch
Maximum connect time(connect_time)The session will be dropped by oracle after specified time.
Maximum idle time(idle_time)The session will be dropped by oracle after specified time of doing nothing. Long running processes are not idle!
Maximum blocks read per session(logical_reads_per_session)
Maximum blocks read per call(logical_reads_per_call)
Maximum amount of SGA(private_sga)
....(composite_limit)
In order to enforce kernel limits, resource_limit must be set to true.

Password limits
-Maximum failed login attempts(failed_login_attempts)
-Maximum time a password is valid(password_life_time)
-Minimum of different passwords before password can be reused(password_reuse_max)
-Minimum of days before a password can be reused(password_reuse_time)
-Number of days an account is locked after failing to login(password_lock_time)
-Password grace time (password_grace_time)
-Verify function for passwords(password_verify_function)


If a session exceeds one of these limits, Oracle will terminate the session. If there is a logoff trigger, it won't be executed.

History of passwords
In order to track password related profile limits, Oracle stores the history of passwords for a user in user_history$.
Profiles already created and their settings can be queried through dba_profiles