ORACLE: parameters

In order to check the values set for the parameters we can :

desc v$parameter

spool parameter.txt

col name for a25

col value for a45

select name,value from v$parameter order by 1;

spool off;

Now, parameters can be changed Dynamically or Statically. That having been said, there are still some that can be changed on the fly and there are others that can not.

In order to change a parameter on the fly, we need to have be running database using spfile. This can be done using

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\PRODUCT\10.2.0\DB_2\
                                                 DATABASE\SPFILEHARRY.ORA

if this outputs  name: spfile and value: as spfile location then we are running the database using spfile.

if spfile is not present and we try the following,

show parameter sess

alter system set sessions=200 scope=spfile;

Another instance, let say we startup using pfile, though an spfile exisits

startup pfile=’path\init.ora’;

show parameter spfile

will give null for the value.

or

show parameter sp

Now,

SQL> show parameter sess

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions integer 170
shared_server_sessions               integer
SQL> alter system set sessions=200;
alter system set sessions=200
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

--memory means only for the current session in memory
SQL> alter system set sessions=200 scope=memory;
alter system set sessions=200 scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

--both means in memory and spfile
SQL> alter system set sessions=200 scope=both;
alter system set sessions=200 scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sessions=200 scope=spfile;

System altered.
SQL> show parameter sess

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions integer 170
shared_server_sessions               integer
SQL> col name for a20
SQL> select name, ISSES_MODIFIABLE,  ISSYS_MODIFIABLE from v$parameter 
where name like '%sessions%';

NAME                 ISSES ISSYS_MOD
-------------------- ----- ---------
sessions             FALSE FALSE
license_max_sessions FALSE IMMEDIATE
license_sessions_war FALSE IMMEDIATE
ning

java_soft_sessionspa FALSE FALSE
ce_limit

java_max_sessionspac FALSE FALSE
e_size

NAME                 ISSES ISSYS_MOD
-------------------- ----- ---------
logmnr_max_persisten FALSE FALSE
t_sessions

shared_server_sessio FALSE IMMEDIATE
ns

7 rows selected.

ORACLE: DB CLONE

There are many ways to cloning, this is one.
We are going to take a hot backup of the Primary database by
1. putting db into 'alter database begin backup;' mode, 
2. copying the Primary's .DBF Files,
3. Backing up a Controlfile using the following.
Alter database backup controlfile to Trace as 'Path';
We will need to tweak this file in order to create a controlfile from it.
(delete unnecessary lines, Replace Reuse with SET and use RESETLOGS)
5. We then create an INITSID.ORA file in the database folder.....giving
db_name as the only mandatory parameter. ( In some cases we need to also provide
the Compatibility)
6. dimora -new -sid dbname
will create on windows account a new Service that you can then start up using
NET START ORACLESERVICEsid
8. Start up Clone database in nomount mode, all by using the controlfile trace.
9. Take database to mount mode.
10. provide archivelog source and apply all archive logs using
set logsource ='path'
recover database using backup controlfile until cancel;
11. Alter database open resetlogs;
and you have a clone. Congratulations!

c:\>echo %oracle_sid%
harry2

c:\>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jul 24 03:14:26 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

USER is "SYS"
SQL> @c:\trace.sql
ORACLE instance started.

Total System Global Area  117440512 bytes
Fixed Size                  1289196 bytes
Variable Size              58721300 bytes
Database Buffers           50331648 bytes
Redo Buffers                7098368 bytes

ORACLE: ORA-01130

During the process of cloning this error may cause a lot of agony.

Solution: In your Init.ora file add the compatibility level using the following.

compatible='10.2.0.3.0'
However, before setting the compatibility level of you clone, check
the source database compatibility using the following views.
SQL> col status for a6
SQL> col version for a10
SQL> col comp_name for a35
SQL> set lines 150
SQL> set pages 100
select  comp_name, version, status,modified from dba_registry;

COMP_NAME                           VERSION    STATUS MODIFIED
----------------------------------- ---------- ------ --------------------
Oracle Enterprise Manager           10.2.0.3.0 VALID  17-APR-2007 04:49:57
Spatial                             10.2.0.3.0 VALID  18-JUN-2011 16:02:56
Oracle interMedia                   10.2.0.3.0 VALID  18-JUN-2011 16:02:55
OLAP Catalog                        10.2.0.3.0 VALID  18-JUN-2011 16:02:56
Oracle XML Database                 10.2.0.3.0 VALID  18-JUN-2011 16:02:55
Oracle Text                         10.2.0.3.0 VALID  18-JUN-2011 16:02:51
Oracle Expression Filter            10.2.0.3.0 VALID  18-JUN-2011 16:02:51
Oracle Rules Manager                10.2.0.3.0 VALID  18-JUN-2011 16:02:55
Oracle Workspace Manager            10.2.0.1.0 VALID  18-JUN-2011 16:02:50
Oracle Data Mining                  10.2.0.3.0 VALID  18-JUN-2011 16:02:51
Oracle Database Catalog Views       10.2.0.3.0 VALID  18-JUN-2011 16:02:50
Oracle Database Packages and Types  10.2.0.3.0 VALID  18-JUN-2011 16:02:50
JServer JAVA Virtual Machine        10.2.0.3.0 VALID  18-JUN-2011 16:02:51
Oracle XDK                          10.2.0.3.0 VALID  18-JUN-2011 16:02:51
Oracle Database Java Packages       10.2.0.3.0 VALID  18-JUN-2011 16:02:51
OLAP Analytic Workspace             10.2.0.3.0 VALID  18-JUN-2011 16:02:55
Oracle OLAP API                     10.2.0.3.0 VALID  18-JUN-2011 16:02:55

17 rows selected.



SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

ORACLE: BACKUPS

C:\Users\test>@bk

C:\Users\test>SET ORACLE_HOME=C:\oracle\product\10.2.0\db_2

C:\Users\test>SET ORACLE_BASE=C:\oracle\product\10.2.0

C:\Users\test>set ORACLE_SID=BK

C:\Users\test>net start OracleServiceBK
The OracleServiceBK service is starting................
The OracleServiceBK service was started successfully.

C:\Users\test>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Jul 23 11:03:49 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

STATUS
------------
OPEN

NAME      OPEN_MODE
--------- ----------
BK        READ WRITE

USER is "SYS"

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Users\test>rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Sat Jul 23 11:04:36 2011

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

connected to target database: BK (DBID=696327744)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BK       696327744        PARENT  1          17-APR-07
2       2       BK       696327744        CURRENT 521803     11-JUL-11

RMAN> list backup;

RMAN> backup tag 'bkbackups1' database plus archivelog;

Starting backup at 23-JUL-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=1 stamp=756206611
input archive log thread=1 sequence=2 recid=2 stamp=756206653
input archive log thread=1 sequence=3 recid=3 stamp=756206803
input archive log thread=1 sequence=4 recid=4 stamp=756224561
input archive log thread=1 sequence=5 recid=5 stamp=756296392
input archive log thread=1 sequence=6 recid=6 stamp=756443629
input archive log thread=1 sequence=7 recid=7 stamp=756449553
input archive log thread=1 sequence=8 recid=8 stamp=757249578
channel ORA_DISK_1: starting piece 1 at 23-JUL-11
channel ORA_DISK_1: finished piece 1 at 23-JUL-11
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\BK\BACKUPSET\2011_07_23\O1_MF_ANNNN_BKBACKUPS1_72OWCZG0_.BKP t
ag=BKBACKUPS1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 23-JUL-11

Starting backup at 23-JUL-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\BK\SYSTEM01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\BK\SYSAUX01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\BK\UNDOTBS01.DBF
input datafile fno=00005 name=C:\ORACLE\PRODUCT\10.2.0\BK\RMAN.DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\BK\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 23-JUL-11
channel ORA_DISK_1: finished piece 1 at 23-JUL-11
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\BK\BACKUPSET\2011_07_23\O1_MF_NNNDF_BKBACKUPS1_72OWFB1L_.BKP t
ag=BKBACKUPS1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-JUL-11
channel ORA_DISK_1: finished piece 1 at 23-JUL-11
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\BK\BACKUPSET\2011_07_23\O1_MF_NCSNF_BKBACKUPS1_72OWHJ83_.BKP t
ag=BKBACKUPS1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 23-JUL-11

Starting backup at 23-JUL-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=9 recid=9 stamp=757249700
channel ORA_DISK_1: starting piece 1 at 23-JUL-11
channel ORA_DISK_1: finished piece 1 at 23-JUL-11
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\BK\BACKUPSET\2011_07_23\O1_MF_ANNNN_BKBACKUPS1_72OWHON4_.BKP t
ag=BKBACKUPS1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 23-JUL-11

SQL> select thread#,sequence#,recid,RESETLOGS_CHANGE#,STATUS,COMPLETION_TIME from v$archived_log;

   THREAD#  SEQUENCE#      RECID RESETLOGS_CHANGE# S COMPLETIO
---------- ---------- ---------- ----------------- - ---------
         1          1          1            521803 D 11-JUL-11
         1          2          2            521803 D 11-JUL-11
         1          3          3            521803 D 11-JUL-11
         1          4          4            521803 D 11-JUL-11
         1          5          5            521803 D 12-JUL-11
         1          6          6            521803 D 14-JUL-11
         1          7          7            521803 A 14-JUL-11
         1          8          8            521803 A 23-JUL-11
         1          9          9            521803 A 23-JUL-11

9 rows selected.

ORACLE: RESTORE

RMAN> alter database mount;

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

RMAN> SQL “ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ”C:\TRACE12.TXT” “;

ORACLE: RMAN with Shared Server

Configuring RMAN for Use with a Shared Server

RMAN cannot connect to a target database through a shared server dispatcher. RMAN requires a dedicated server process. If your target database is configured for shared server, then you must modify your Oracle Net configuration to provide dedicated server processes for RMAN connections.

To ensure that RMAN does not connect to a dispatcher when a target database is configured for a shared server, the net service name used by RMAN must include (SERVER=DEDICATED) in the CONNECT_DATA attribute of the connect string.

Oracle Net configuration varies greatly from system to system. The following procedure illustrates only one method. This scenario assumes that the following service name in the tnsnames.ora connects to a target database using the shared server architecture, where inst1 is a value of the SERVICE_NAMES initialization parameter:

inst1_shs =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=inst1_host)(port=1521))
    (CONNECT_DATA=(SERVICE_NAME=inst1)(SERVER=shared))
  )

To use RMAN with a shared server:

Create a net service name in the tnsnames.ora file that connects to the nonshared SID. For example, enter:

inst1_ded =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=inst1_host)(port=1521))
    (CONNECT_DATA=(SERVICE_NAME=inst1)(SERVER=dedicated))
  )

Start SQL*Plus and then connect using both the shared server and dedicated server service names to confirm the mode of each session.

For example, connect with SYSDBA privileges to inst1_ded and then execute the following SELECT statement (sample output included):

SQL> SELECT SERVER 
  2  FROM   V$SESSION 
  3  WHERE  SID = (SELECT DISTINCT SID FROM V$MYSTAT);

SERVER   
---------
DEDICATED
1 row selected.

To connect to a shared server session, you could connect with SYSDBA privileges to inst1_shs and then execute the following SELECT statement (sample output included):

SQL> SELECT SERVER 
  2  FROM   V$SESSION 
  3  WHERE  SID = (SELECT DISTINCT SID FROM V$MYSTAT);

SERVER   
---------
SHARED 
1 row selected.

Start RMAN and connect to the target database using the dedicated service name. Optionally, connect to a recovery catalog. For example, enter:

% rman
RMAN> CONNECT TARGET SYS@inst1_ded 

target database Password: password
connected to target database: INST1 (DBID=39525561)

RMAN> CONNECT CATALOG rman@catdb

Reference:http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmconfa.htm

ORACLE: TABLE PARTITIONING

Partitioning tables is simple and easy. Here is a demo to get started.
During range scans, with of course, proper indexes and stats, partitioned tables can provide
good response times.

SQL> CREATE TABLE NEW ( ID INT, NAME CHAR(5))
     PARTITION BY RANGE (ID)
     (PARTITION LESSTHAN1000 VALUES LESS THAN (1000),
     PARTITION LESSTHANMAXVALUE VALUES LESS THAN (MAXVALUE)
     );

SQL> DECLARE
     BEGIN
     FOR i in 1..2000
     loop
     THEN if mod(i,2)=0
     insert into new values (i,'RAMA')
     ELSE
     INSERT INTO NEW VALUES (i,'SITA')
     END IF; 
     END LOOP;
     END;

SQL> SELECT OWNER,TABLE_NAME,TABLESPACE_NAME,PARTITIONED FROM DBA_TABLES WHERE TABLE_NAME= 'NEW'

ORACLE: BAT FILE

Trick to speed up things.

Create a file in the location where your cmd defaults to as

My databases name is HARRY, so I will put the following lines in a notepad:

SET ORACLE_HOME=C:\oracle\product\10.2.0\db_2

SET ORACLE_BASE=C:\oracle\product\10.2.0

set ORACLE_SID=HARRY

NET START OracleServiceHARRY

sqlplus "/ as sysdba"
save the file as databasename.bat
Next time to you can simply specify the @HARRY
BAT file name and it will take you into the sql prompt.
You have probably saved a few seconds of your productive time....
getting to the things your want to faster!

Additionally, you can go to
C:\oracle\product\10.2.0\db_2\sqlplus\admin\glogin.sql
and add the following lines at the bottom so that they are executed after the login into sqlplus
is successful. This way you get a quick look into your system.
SELECT STATUS FROM V$INSTANCE;
SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;
SHOW USER;
Enjoy!

ORACLE: Architecture 2 (Thanks Sowfeer)

ORACLE: ORA-12560: TNS:protocol adapter error

This error is usually due to the OracleServiceSID being down.

The reason this could happend are due to the oracle instance being shutdown and

oracle database services are not set to start up to startup automatically.

Solution: cmd> net start OracleservicesSID

NB. Replace SID with your oracle database name.