OraMagic

parallel query queuing in 11gr2

2012-11-12T09:14:00.002+05:30

Oracle 11g release 2 introduced a number of enhancements related to parallel query processing.

One new parameter parallel_degree_policy has been introduced. Setting this parameter to MANUAL results in oracle 11g release1 behavior. Setting this parameter to Auto provides three facilities:
1. Each SQL statement will be considered for parallel execution according to a threshold.
2. in-memory parallel executions as well as
3. parallel statement queuing.

This section deals with parallel statement queuing feature.

With this feature , Oracle will queue parallel statement if required parallel server processes are not available. Only once the necessary resources becomes available, statement will dequeued and allowed to run. Prior to 11g release 2, if there were insufficient parallel servers available, then either the statement would be run with downgraded DOP or would serialize. But, in 11g release2 , statement will go into the queue if number of active parallel server process is greater or equal to the PARALLEL_SERVERS_TARGET parameter.
PARALLEL_SERVERS_TARGET is set lower to the PARALLEL_MAX_SERVER (which controls the maximum number of PX servers allowed on the system) , thus preventing the overloading of the system with PX servers and ensuring each parallel statement will get all of the required Px servers. Statement will be dequeued according to FIFO order. Here is the small demonstration for this feature.

Following are the parameters that needs to consider:

parallel_degree_limit string CPU
parallel_degree_policy string AUTO
parallel_max_servers integer 10
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_servers_target integer 6
parallel_threads_per_cpu integer 2

Here parallel_degree_policy is set to Auto. That means each statement will be considered for parallelism.

Now, Lets execute a query.

SQL> set autotrace on

SQL> select /*+ q1 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c group by cust_last_name;

//output not shown//

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2696793116

--------------------------------------------------------------------------------
------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | | | |

| 1 | PX COORDINATOR | | | | |
| | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10002 | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | P->S | QC (RAND) |

| 3 | HASH GROUP BY | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | PCWP | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 4 | PX RECEIVE | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | PCWP | |

| 5 | PX SEND HASH | :TQ10001 | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,01 | P->P | HASH |

| 6 | HASH GROUP BY | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,01 | PCWP | |

| 7 | MERGE JOIN CARTESIAN | | 50G| 617G| 13M (1)| 4

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
5:42:18 | | | Q1,01 | PCWP | |

| 8 | PX RECEIVE | | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,01 | PCWP | |

| 9 | PX SEND BROADCAST | :TQ10000 | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | P->P | BROADCAST |

| 10 | PX BLOCK ITERATOR | | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | PCWC | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 11 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | PCWP | |

| 12 | BUFFER SORT | | 918K| 4486K| 16M (17)| 5
4:07:28 | | | Q1,01 | PCWP | |

| 13 | PX BLOCK ITERATOR | | 918K| 4486K| 271 (2)| 0
0:00:04 | 1 | 28 | Q1,01 | PCWC | |

| 14 | TABLE ACCESS FULL | SALES | 918K| 4486K| 271 (2)| 0
0:00:04 | 1 | 28 | Q1,01 | PCWP | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
------------------------------------------------------

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2

While the query is running, from another terminal, view the number of active px processes. These can be identified by ora_pxxx.

[oracle@host1 dbs]$ ps -ef|grep ora_p
oracle 4730 1 0 10:09 ? 00:00:00 ora_pmon_orc11
oracle 4742 1 0 10:09 ? 00:00:00 ora_psp0_orc11
oracle 12314 1 0 12:10 ? 00:00:00 ora_pmon_db11
oracle 12326 1 0 12:10 ? 00:00:00 ora_psp0_db11
oracle 12765 1 0 12:18 ? 00:00:00 ora_p000_db11
oracle 12767 1 1 12:18 ? 00:00:00 ora_p001_db11
oracle 12769 1 0 12:18 ? 00:00:00 ora_p002_db11
oracle 12771 1 0 12:18 ? 00:00:00 ora_p003_db11
oracle 12788 11529 0 12:18 pts/2 00:00:00 grep ora_p

Currently, two sets of parallel server processes are running because DOP is 2, meaning 4 parallel server process are running at a time. Default DOP is 2 because of parallel_degree_limit parameter.

On the same time while the first query is executing, run another query from another terminal.

SQL> set autotrace on

SQL> select /*+ q2 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c group by cust_last_name;

[oracle@host1 ~]$ ps -ef|grep ora_p

oracle 4730 1 0 10:09 ? 00:00:00 ora_pmon_orc11

oracle 4742 1 0 10:09 ? 00:00:00 ora_psp0_orc11

oracle 14001 1 0 12:45 ? 00:00:00 ora_pmon_db11

oracle 14013 1 0 12:45 ? 00:00:00 ora_psp0_db11

oracle 14810 1 0 13:00 ? 00:00:00 ora_p000_db11

oracle 14812 1 0 13:00 ? 00:00:00 ora_p001_db11

oracle 14814 1 6 13:00 ? 00:00:27 ora_p002_db11

oracle 14816 1 6 13:00 ? 00:00:26 ora_p003_db11

oracle 15156 1 0 13:06 ? 00:00:00 ora_p004_db11

oracle 15158 1 0 13:06 ? 00:00:00 ora_p005_db11

oracle 15160 1 33 13:06 ? 00:00:20 ora_p006_db11

oracle 15162 1 0 13:06 ? 00:00:00 ora_p007_db11

oracle 15203 14835 0 13:07 pts/4 00:00:00 grep ora_p

See, number of active processes running has increased .

From the third terminal, run the parallel query again at the same time while the other two queries are active.
Now, you will see that your query is queued. You can monitor the status of SQL statement using V$SQL_MONITOR.

SQL>select status,sql_text from v$sql_monitor where sql_text like '%sh%';

STATUS
-------------------
SQL_TEXT
--------------------------------------------------------------------------------
QUEUED
select /*+ q3 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name

EXECUTING
select /*+ q1 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name

EXECUTING

select /*+ q2 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name

Additional Note: Statement is waiting on the event "PX QUEUING: statement queue". It is the first statement in the statement queue. All other statements in the queue will be waiting on "ENQ JX SQL statement queue". Only when a statement gets to the head of the queue will the wait event switch to "PX QUEUING: statement queue".
interesting !

change of IP-address in linux box running Oracle server

2012-10-19T12:01:00.004+05:30

Most people face problem while working on the database whenever they changed the ip-address and/or hostname of the machine. Enterprise manager may not working properly.

I am mentioning this issue in this post with the problem that I face. My machine is configured with DHCP. Oracle recommends to use static IP address. So I also want to change this. To configure static address in rhel5, you need to follow these two steps:

1. Assigning static IP address

issue neat command

It will open network configuration GUI window.

Select eth0 here, go to edit. Select statically set IP address. Enter ip address and mention subnet mask. Click on OK button. After, click on File >> save to save your changes. After ,you will get the dialogue saying

Changes are saved. You may want to restart the network and network services or restart the computer.

click on OK. Exit the network configuration window. Then, logged in as root user, issue

service network restart.

Now, to change the host name(if you want), make modification in /etc/sysconfig/network. My /etc/sysconfig/network looks like:

----------------------------------------------------------------------------------------------------------------------

NETWORKING=yes

NETWORKING_IPV6=yes

HOSTNAME=host2.oracle.com

--------------------------------------------------------------------------------------------------------------------

2. Now, as a second step, make appropriate entry in /etc/hosts. My /etc/hosts after modification looks like:

-----------------------------------------------------------------------------------------------------------------------------------

# Do not remove the following line, or various programs that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

#::1 localhost6.localdomain6 localhost6

192.168.2.102 host2.oracle.com host2

------------------------------------------------------------------------------------------------------------------------------------

Now, reboot the system.

Now, after changing ip address and /or hostname ,you may face certain problems while working on the database. Your listener and/or em console may not working properly.

Use netmgr to configure your listener again.

And to enable proper functioning of em console, start your listener after proper configuration, then start database. .

Firstly, I issued

emca -config dbcontrol db

Command ran successfully. But after getting enterprise manager console , homepage shows

Error : java.lang.Exception: UnknownHostException sending request :: host1.oracle.com.

The solution is to issue below command , not above.

[Below command won't work if you do not start the database]

[oracle@host2 ~]$ emca -config dbcontrol db -repos recreate

And, answer the questions that it asks. Here is the output:

-------------------------------------------------------------------------------------------------------------

STARTED EMCA at Oct 19, 2012 11:27:22 AM

EM Configuration Assistant, Version 10.2.0.1.0 Production

Enter the following information:

Database SID: orcl

Database Control is already configured for the database orcl

You have chosen to configure Database Control for managing the database orcl

This will remove the existing configuration and the default settings and perform a fresh configuration

Do you wish to continue? [yes(Y)/no(N)]: y

Listener port number: 1521

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional): sys

Invalid email.

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/10.2.0/db_1

Database hostname ................ host2.oracle.com

Listener port number ................ 1521

Database SID ................ orcl

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: y

Oct 19, 2012 11:27:45 AM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/orcl/emca_2012-10-19_11-27-22-AM.log.

Oct 19, 2012 11:27:46 AM oracle.sysman.emcp.util.DBControlUtil stopOMS

INFO: Stopping Database Control (this may take a while) ...

Oct 19, 2012 11:27:48 AM oracle.sysman.emcp.EMReposConfig dropRepository

INFO: Dropping the EM repository (this may take a while) ...

Oct 19, 2012 11:28:56 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Oct 19, 2012 11:28:56 AM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) ...

Oct 19, 2012 11:32:41 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully created

Oct 19, 2012 11:32:44 AM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

Oct 19, 2012 11:34:22 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: Database Control started successfully

Oct 19, 2012 11:34:22 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: >>>>>>>>>>> The Database Control URL is http://host2.oracle.com:5501/em <<<<<<<<<<<

Enterprise Manager configuration completed successfully

FINISHED EMCA at Oct 19, 2012 11:34:31 AM

[oracle@host2 ~]$ emctl start dbconsole

TZ set to Asia/Calcutta

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

http://host2.oracle.com:5501/em/console/aboutApplication

- An instance of Oracle Enterprise Manager 10g Database Control is already running.

And Enjoy..............

Resolving Autotrace error

2012-10-11T13:08:00.001+05:30

I was working in 10.2.0 version. When I tried to set autotrace to on. I got SP2-0618 error. And here is a small illustration and solution.

SQL> conn hr/hr
Connected.

SQL> set autotrace on;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> conn / as sysdba
Connected.

SQL> set autotrace on;

SQL>select * from hr.demo1;

ID
----------
1
2
3
4
5
6
7
8
9
10
11

ID
----------
12
13
----------output truncated----------------------------

Execution Plan
----------------------------------------------------------
Plan hash value: 970289426

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 195K| 21 (5)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEMO1 | 50000 | 195K| 21 (5)| 00:00:01 |
---------------------------------------------------------------------------

Statistics not shown.

SQL> conn / as sysdba
Connected.
SQL> alter user scott identified by tiger account unlock;

User altered.

SQL> conn scott/tiger;
Connected.

SQL> set autotrace on;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> conn / as sysdba
Connected.

SQL> grant plustrace to public;
grant plustrace to public
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

Execute plustrce.sql from $ORACLE_HOME/sqlplus/admin folder.

SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

After the execution of the script,

SQL> grant plustrace to public;

Grant succeeded.

SQL> conn hr/hr
Connected.

SQL> set autotrace on;

No error shown.
From hereon, autotrace facility will output properly.

SQL>conn / as sysdba
Connected.
SQL> set autotrace on;

select * from hr.demo1;

ID
----------
1
2
3
4
5
6
7
8
9
10
11

ID
----------
12
13
----------output truncated----------------------------

Execution Plan
----------------------------------------------------------
Plan hash value: 970289426

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 195K| 21 (5)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEMO1 | 50000 | 195K| 21 (5)| 00:00:01 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3415 consistent gets
0 physical reads
0 redo size
923122 bytes sent via SQL*Net to client
37048 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed

Statistics also shown.
I also tested the same thing in 11g release 2 and found that that is also true for 11g.

Thats all.

login as sysoper

2012-03-09T15:54:00.001+05:30

I want get to know about the role of oper group that has always been created during installation of oracle10g on linux box. 3 groups are always created as a prequisite for oracle 10g installation on linux box. They are oper,dba and oinstall.
I was knowing the purpose of all these groups. For ex, dba group is used so that an os user can logged in as / as sysdba. oinstall is the owner for oracle inventory. And in my case, there is an os user named oracle which is the member of both these groups, oinstall as well as dba. A user which is the member of dba group can logged in as / as sysdba as well as / as sysoper, and that is what oracle user can does.
Now I wanted to understand the purpose of oper group then. It was clear to me that oper group is created so that an os user can logged in as / as sysoper to work as system operator and to perform day-to -day basic administrative tasks such as startup, shutdown, backup ,recovery,etc. And the user who can logged in as sysdba has more powerful privilege that sysoper. That means there must be a second os user who can perform the role of system operator by logging as / as sysoper and not as / as sysdba.

Then I started with creating a second user, besides oracle, who can acess oracle database to perform basic administrative operations. After various tries and errors , I succeded. It is how I did.

$useradd geet
$ chmod -R 777 /u01
$usermod -G oper geet
$ls -l /u01
total 20
drwxrwxrwx 8 oracle oinstall 4096 Mar 1 15:40 app
--------rest truncated-------------

ls -ld /u01
drwxrwxrwx 8 oracle oinstall 4096 Feb 10 10:16 /u01

ls -l /u01/app
total 24
drwxrwxrwx 8 oracle dba 4096 Mar 1 10:32 admin
drwxrwxrwx 6 oracle dba 4096 Mar 1 10:36 flash_recovery_area
drwxrwxrwx 2 oracle dba 4096 Mar 1 16:20 logminer_dir
drwxrwxrwx 6 oracle dba 4096 Mar 1 10:35 oradata
drwxrwxrwx 7 oracle dba 4096 Mar 1 10:21 oraInventory
drwxrwxrwx 3 oracle oinstall 4096 Feb 8 12:37 product

Check the permission for $ORACLE_HOME/bin/oracle. It should be

$ls -l oracle
-rwsrwsrwx 1 oracle dba 93362259 Mar 7 11:26 oracle
Here is two s.

$id geet
uid=503(geet) gid=506(geet) groups=506(geet),501(oper),503(oinstall)

$su - geet
[geet@new-host-15 ~]$ export ORACLE_HOME=/u01/app/product/10.2.0/db_1
[geet@new-host-15 ~]$ export ORACLE_SID=orcl
[geet@new-host-15 ~]$ cd $ORACLE_HOME
[geet@new-host-15 db_1]$ cd bin
[geet@new-host-15 bin]$ ./sqlplus / as sysoper

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 7 14:12:19 2012

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

Connected.

If this fails:

$ ./sqlplus / as sysoper

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 14:17:26 2012

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

ERROR:
ORA-01031: insufficient privileges

Then check $ORACLE_HOME/rdbms/lib config.c which looks something like this:

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */
#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};

if second #define do not contain oper, but dba, then u have to modify this file as above. After modification you have to relink this executable. To relink I used:
$relink all

But after relinking, I am able to issue

conn / as sysoper

successfully.

but when issuing
SQL> conn scott/tiger
ERROR:
ORA-00600: internal error code, arguments: [17069], [0x30AD70A4], [], [], [],
[], [], []
ERROR:
ORA-00600: internal error code, arguments: [17069], [0x30AD70A4], [], [], [],
[], [], []

Error accessing package DBMS_APPLICATION_INFO
Connected.

This error is solved by running utlirp.sql script by logging as sysdba.

loss of all controlfiles using user managed backup and recovery

2011-12-06T13:37:00.000+05:30

SETUP : All scenarios are in archivelog mode.

Database version is 10.2.0.1.0

1. Loss of all controlfiles when all redos are available.

SQL> startup

SQL> alter database begin backup;

SQL> select * from v$backup;

$cp /u01/app/oradata/orcl1/*.* /u01/app/backup/orcl1/.

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 ACTIVE 482215 05-DEC-11

2 ACTIVE 482215 05-DEC-11

3 ACTIVE 482215 05-DEC-11

4 ACTIVE 482215 05-DEC-11

5 ACTIVE 482215 05-DEC-11

SQL> alter database end backup;

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 NOT ACTIVE 482215 05-DEC-11

2 NOT ACTIVE 482215 05-DEC-11

3 NOT ACTIVE 482215 05-DEC-11

4 NOT ACTIVE 482215 05-DEC-11

5 NOT ACTIVE 482215 05-DEC-11

SQL> select NAME,SEQUENCE#,RESETLOGS_CHANGE#,FIRST_CHANGE#,NEXT_CHANGE# from v$archived_log;

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7frrnnyw_.arc 1 482026 482026 482412

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7frrnp2f_.arc 2 482026 482412 482414

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_7frrnvjn_.arc 3 482026 482414 482416

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 4 52428800 1 NO CURRENT 482416 05-DEC-11

2 1 2 52428800 1 YES INACTIVE 482412 05-DEC-11

3 1 3 52428800 1 YES INACTIVE 482414 05-DEC-11

Now, delete all the control files.

SQL> shutdown abort

SQL> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218292 bytes

Variable Size 71305484 bytes

Database Buffers 88080384 bytes

Redo Buffers 7168000 bytes

ORA-00205: error in identifying control file, check alert log for more info

-> Restore controlfile from backup to all the locations mentioned in initialization parameter file.

$cp /u01/app/backup/contro1.ctl /u01/app/oradata/orcl1/control01.ctl

$cp /u01/app/backup/contro1.ctl /u01/app/oradata/orcl1/control02.ctl

$cp /u01/app/backup/contro1.ctl /u01/app/oradata/orcl1/control03.ctl

SQL> alter database mount;

Database altered.

SQL> recover database using backup controlfile [until cancel];

ORA-00279: change 482216 generated at 12/05/2011 11:42:39 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_%u_.arc

ORA-00280: change 482216 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

-> It requires archived log related to seq1. Suggestion is pointing to current location, and press enter to accept the suggestion

ORA-00279: change 482412 generated at 12/05/2011 11:50:04 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_%u_.arc

ORA-00280: change 482412 for thread 1 is in sequence #2

ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7frrnnyw_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

-> It requires archived log related to seq2. Suggestion is pointing to current location, and press enter to accept the suggestion

ORA-00279: change 482414 generated at 12/05/2011 11:50:05 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_%u_.arc

ORA-00280: change 482414 for thread 1 is in sequence #3

ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7frrnp2f_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

-> It requires archived log related to seq3. Suggestion is pointing to current location, and press enter to accept the suggestion

ORA-00279: change 482416 generated at 12/05/2011 11:50:11 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_4_%u_.arc

ORA-00280: change 482416 for thread 1 is in sequence #4

ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_7frrnvjn_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

-> It requires archived log related to seq4. Suggestion is pointing to an archived location, but required redo is available in redo01.log(it is clear from the output of redo log) so,specify the file name and location and press enter.

/u01/app/oradata/orcl1/redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

-> And, you will get all the data back.

**********************************************************************************

2. Loss of all controlfiles and one of the redo is missing

SQL> startup

SQL> alter database begin backup;

$cp /u01/app/oradata/orcl1/*.* /u01/app/backup/orcl1/

SQL> alter database end backup;

*****Perform some changes*************

SQL> Alter system switch logfile;

SQL> select name,sequence# from v$archived_log;

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7fs1hyz7_.arc 1

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7fs1hzyk_.arc 2

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_7fs1j3o5_.arc 3

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_4_7fs1j40o_.arc 4

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 5 52428800 1 NO CURRENT 485153 05-DEC-11

2 1 3 52428800 1 YES ACTIVE 485148 05-DEC-11

3 1 4 52428800 1 YES ACTIVE 485151 05-DEC-11

->Remove archived log sequence#2

$ rm /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7fs1hzyk_.arc

-> remove all controlfiles

$ rm /u01/app/oradata/orcl1/control*.ctl

SQL> shutdown abort

SQL> startup

Error-> error in identifying controlfile

Database is in nomount state.

-> copy all the controlfile and datafiles from backup location to target location

$ cp /u01/app/backup/orcl1/*.* /u01/app/oradata/orcl1/*.*

SQL> alter database mount

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 484998 generated at 12/05/2011 14:15:41 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_%u_.arc

ORA-00280: change 484998 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 485146 generated at 12/05/2011 14:21:10 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_%u_.arc

ORA-00280: change 485146 for thread 1 is in sequence #2

ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7fs1hyz7_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

--> Since redo sequenc#2 does not exists.Issue cancel.

CANCEL

Media recovery cancelled.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

**********************************************************************************

3. Recovery from loss of all controlfiles by creating controlfile.

-> Same procedure will work whether all the archivelogs is available or not.

SQL> SQL> startup

SQL> alter database begin backup;

$cp /u01/app/oradata/orcl1/*.* /u01/app/backup/orcl1/

SQL> alter database end backup;

SQL> alter database backup controlfile to trace as '/u01/app/control.bak' noresetlogs;

*****Perform some changes*************

SQL> Alter system switch logfile;

SQL> select name,sequence# from v$archived_log;

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_1_7fv6mo17_.arc

SEQUENCE#

----------

NAME

--------------------------------------------------------------------------------

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_2_7fv6zzz6_.arc

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_3_7fv700xt_.arc

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_4_7fv70466_.arc

SEQUENCE#

----------

NAME

--------------------------------------------------------------------------------

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_5_7fv70532_.arc

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_6_7fv70b3f_.arc

SQL> select * from v$log;

delete archive log related to seq 2 and delete all the controlfiles

SQL> shutdown abort

SQL> startup

error in identifying contorlfile.

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL1" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oradata/orcl1/redo01.log' SIZE 50M,

GROUP 2 '/u01/app/oradata/orcl1/redo02.log' SIZE 50M,

GROUP 3 '/u01/app/oradata/orcl1/redo03.log' SIZE 50M

DATAFILE

'/u01/app/oradata/orcl1/system01.dbf',

'/u01/app/oradata/orcl1/undotbs01.dbf',

'/u01/app/oradata/orcl1/sysaux01.dbf',

'/u01/app/oradata/orcl1/users01.dbf',

'/u01/app/oradata/orcl1/example01.dbf'

CHARACTER SET WE8ISO8859P1

;

Controlfuile created

SQL> recover database;

SQL> alter database open;

**********************************************************************************

create database manually with minimum configuration

2011-04-28T22:52:00.009+05:30

This blog discusses how to create oracle database manually using CREATE DATABASE command.

Following are some of the ways alongwith minimum requirement that must be satisfied in order to create database successfully.

These steps have been tested in oracle 10g Enterprise Edition 10.2.0.1.0. on Linux platform.

1. First of all, set ORACLE_SID and ORACLE_HOME to apprpriate values.

Suppose you want to create a new database named TEST. Then , issue

$ORACLE_SID=TEST

$ORACLE_HOME=/oracle/product/10.2.0/db_1

2. Create initialization parameter file $ORACLE_HOME/dbs/initTEST.ora

/*****initTEST.ora******/

control_files=(/oracle/product/10.2.0/db_1/oradata/TEST/control01.ctl,/oracle/product/10.2.0/db_1/oradata/TEST/control02.crl)

undo_management=AUTO

undo_tablespace=UNDOTBS

db_name=TEST

db_block_size=8192

sga_max_size=268000000

sga_target=268000000

user_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/udump'

background_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/bdump'

core_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/cdump

/********************/

Specified location must exist. Otherwise, error returns.

3. Now, connect to sqlplus:

$sqlplus /nolog

SQL> conn / as sysdba

4. Start the database in nomount state.

SQL> startup nomount

5.Issue following create database statement:

SQL> create database TEST

logfile group 1('/oracle/product/10.2.0/db_1/oradata/TEST/redo01.log') size 10M,

group 2('/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log') size 10M,

group 3('/oracle/product/10.2.0/db_1/oradata/TEST/redo03.log') size 10M

character set WE8ISO8859P1

national character set AL16UTF16

datafile '/oracle/product/10.2.0/db_1/oradata/TEST/system01.dbf' size 100M autoextend on next 10M maxsize unlimited extent management local

sysaux datafile '/oracle/product/10.2.0/db_1/oradata/TEST/sysaux01.dbf' size 10M autoextend on next 10M maxsize unlimited

undo tablespace undotbs datafile '/oracle/product/10.2.0/db_1/oradata/TEST/undotbs01.dbf' size 10M

default temporary tablespace temp tempfile '/oracle/product/10.2.0/db_1/oradata/TEST/temp01.dbf' size 10M;

Above procedure would create datafiles in the specified location. It create four tablespaces: system,sysaux,undotbs and temp.

6. run $ORACLE_HOME/rdbms/admin/catalog.sql script.

7. run $ORACLE_HOME/rdbms/admin/catproc.sql script.

Creating database using OMF feature

You can also create database using OMF feature. Change initTEST.ora file as follows:

undo_management=AUTO

sga_max_size=268000000

sga_target=268000000

undo_tablespace=UNDOTBS1

db_name=TEST

db_block_size=8192

db_create_file_dest='/oracle/product/10.2.0/db_1/oradata'

db_create_online_log_dest_1='/oracle/product/10.2.0/db_1/oradata'

user_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/udump'

background_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/bdump'

core_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/cdump'

Here, two OMF related parameters have been set,that is, db_create_file_dest and db_create_online_log_dest.

And issue create database statement as:

SQL> create database TEST

character set WE8ISO8859P1

national character set AL16UTF16

undo tablespace undotbs1

default temporary tablespace temp;

No need to specify the name of any of the datafiles and redolog files here. It will create all the datafiles according to the specified OMF related parameters' values.

All the other steps are same except step no.2 and 5.

If creating database using OMF features, controlfile parameter needs to be set in the initialization parameter file after successful creation of the database as Oracle would not identify correct controlfile during next startup of the database. Oracle will locate the controlfile in $ORACLE_HOME/dbs but can not find one. That's why always set controfile parameter that points to the correct location before shutting down the instance.

You can issue even much simple create database statement when using OMF feature. Issue just:

SQL> create database TEST;

But all other steps are same.

restoring controlfile in archivelog mode using RMAN

2011-04-27T22:23:00.004+05:30

If controlfile has been dropped and we are running in archivelog mode, what options do we have to restore controlfile.

1. RMAN> startup nomount
2. RMAN> restore controlfile from autobackup;
3. RMAN> alter database mount;
4. RMAN> recover database;
5. RMAN> alter database open resetlogs;

Procedure is same if any archivelog is missing

complete recovery of datafile using RMAN

2011-04-27T22:09:00.003+05:30

You can perform following experiment to perform complete recovery of datafile in archivelog mode.
1. Assume database is up and running.
2. Delete datafile 4.
3. Valid backup must exist.
4. All the redo logs after the last valid backup exist.

To restore it,
1. RMAN> SQL'alter database datafile 4 offline';
2. RMAN> restore datafile 4;
3. RMAN> recover datafile 4;
4. RMAN>SQL'alter database datafile 4 online';

If an active redo log file is lost, then incomplete recovery of a datafile is not possible . Instead we have to perform Tablespace point in time recovery.

Resolving Exception in thread "main" error

2011-04-02T17:12:00.002+05:30

When trying to execute dbca from my unix(ubuntu 9) prompt, I get following error:

Exception in thread "main"
and prompt returns.
Solution: If working on the local machine, set DISPLAY variable as
$ DISPLAY=:0.0
$ export DISPLAY

$ dbca

and everything works fine.

Incremental backup and restore in oracle 10g

2011-03-08T12:11:00.014+05:30

Here I am presenting the simple steps to demonstrate how to take incremental level backup,perform restore and recovery using RMAN.One can learn Rman incremental level backup easily by going through this post. I used oracle 10g express edition.

Difference between differential and cumulative incremental backup will be clear by going through this simple demo.

Simple scenarios has been taken.

Demonstrating DIFFERENTIAL INCREMENTAL BACKUP

1. Database is in NOARCHIVELOG mode.
2. Not using recovery catalog.
3. RMAN configuration setting that I used is following. I am posting the output of
RMAN> SHOW ALL;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 400 M FORMAT '/usr/lib/oracle/xe/backup/df_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/snapcf_XE.f'; # default

4. Since database is in NOARCHIVELOG mode, it is necessary to shut down the database cleanly using immediate,normal or transactional options.

RMAN> shutdown

5. To take incremental 0 level backup,mount the database. This step is required even in non-incremental backups.

RMAN> startup mount

6. Issue
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
This takes the full backup of the database and also includes controlfile as well as spfile.

7. Issue
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0

2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1084366

8. RMAN> ALTER DATABASE OPEN;

9. Now, perform some DML operations. For example,

SQL> CREATE TABLE T1(C1 NUMBER);
SQL> INSERT INTO T1 VALUES(10);
SQL> /
SQL> /
SQL> COMMIT;

10. Again, shutdown the database to take incremental level backup.
RMAN>SHUTDOWN
RMAN>STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';
It backups only the changes made since the last incremental level n or lower backup.

11. Open the database again to perform some insertions.
RMAN> ALTER DATABASE OPEN;

12. SQL> INSERT INTO T1 VALUES(1);
SQL> /
SQL> /
SQL> /
SQL> COMMIT;

13. Shutdown the database again to take incremental level 2 backup.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 2 DATABASE TAG 'INC_1';

14. Open database again to made some insertions.
RMAN> ALTER DATABASE OPEN;
SQL> INSERT INTO T1 VALUES(9);
SQL> /
SQL> /
SQL> COMMI;

15. select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0

2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1084663

Here all the changes since the last backup has been stored in the redo logs.

16. Now, delete controlfile.
$ mv oradata/XE/controlfile cf.bak

17. RMAN> SHUTDOWN ABORT;

18. RMAN> STARTUP
Database will not open. Database will only go upto the NOMOUNT state since controlfile has been lost.

19. RMAN> Now following steps are needed to recover the database.

20. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
If controlfile autobackup is located in default location, then we can skip this step.

21. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP

22. RMAN> ALTER DATABASE MOUNT;

23. RMAN> RESTORE DATABASE;
It will restore the database using the 0 level backup.

24. RMAN> RECOVER DATABASE
Watch the output carefully. You can recognise various backups that are being applied. Look for the tags that you have given to backupsets. It will applies all the incrementals one by one. First it will apply level 1 incremental, and then level 2. Then it will search for appropriate log sequence and applies the same if found. If log switching has not been taken place after the last incremental backup, then we get all the data without any data loss. And database will restore upto the current point in time. In our case no log switching has taken place and all the data since the last backup exist in the redologs. NOREDO option is needed when log switching has taken place. NOREDO option is not needed if log switch has not taken place.

25.RMAN> ALTER DATABASE OPEN RESETLOGS;

26. Now, view the table t1 and you will find the table restored upto the latest point in time if all the redos has been applied.

27. Always take full backup of the database after opening the database in resetlogs mode.

Demonstrating CUMULATIVE INCREMENTAL BACKUP

Here we will utilize same scenario as above,i.e. no recovery catalog mode,no archivelog mode.
We will start here by taking incremental level 0 backup.

1. RMAN> SHUTDOWN
2. RMAN> STARTUP MOUNT
3. RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
4. RMAN> ALTER DATABASE OPEN;

5. SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;
6. Make some insertion.
SQL> INSERT INTO T1 VALUES(2);
SQL> /
SQL> /
SQL> COMMIT;

7. Again shutdown the database to take incremental level 1 database which will copies only the changed blocks since the last incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';

8.ALTER DATABASE OPEN;

9. SQL> INSERT INTO T1 VALUES(3);
SQL> /
SQL> /
SQL> COMMIT;

10. Again shutdown the database to take cumulative incremental level 1 backup, this time. This backups all the changes made after the last n-1 or lower backup,here it will backup all the changes since the incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'CUM_1';

11. RMAN> ALTER DATABASE OPEN;

12. Perform some DML again.
SQL> INSERT INTO T1 VALUES(9);
SQL>/
SQL> /
SQL> COMMIT;

13. Now, we will shutdown the database to take incremental level 1 backup this time.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1_1';

14. RMAN> ALTER DATABASE OPEN;

15. SQL> INSERT INTO T1 VALUES(0);
SQL> /
SQL> COMMIT;

16. Delete controlfile.
$ mv oradata/XE/controlfile cf.bak

17. RMAN> SHUTDOWN ABORT

18. RMAN> STARTUP
Database will not open.It will go upto only NOMOUNT state.

19. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/XE/backup/cf_%F';

20. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

21. RMAN> ALTER DATABASE MOUNT;

22. RMAN> RESTORE DATABASE;,
It will restore the database from the last incremental level 0 database.

23. RMAN> RECOVER DATABASE;
Here NOREDO option is not necessary as it is assumed that redo logs has not been switched and complete recovery is possible. Check the same by looking at the contents of v$log and comparing with the checkpoint_change# that have noted in step 5. If all the changes since that value exist in the redo logs then NOREDO option is not needed. Else it is required.

Here first of all, cumulative incremental level 1 backup would restore as there is no need for the backup having tag 'INC_1' as cumulative incremental level 1 backup take backup of all the changes since the last level 0 backup. Thus our first incremental level 1 backup is not applied. Then it applies backup having tag 'INC_1_1' which we take after cumulative incremental level backup. After that it will apply all the changes recorded in the online redologs.
Thus , only two incremental level backups needed to apply here. But in the first scenario all the incremental backups had been applied. Thus we can say that using cumulative incremental backup in your incremental strategy provides faster recovery as number of incremental backups to be applied is less.

24. RMAN> ALTER DATBASE OPEN RESETLOGS;

25. Take whole database backup after opening database in resetlogs mode. It is a good practice to perform th same.

26. Now check your table. It must have all the changes that we made, keeping all the scenario same.

Configuring non-default listener to listen to non-default port in Oracle xe 10g

2011-03-02T12:39:00.006+05:30

Following are the steps that I use to configure non-default listener to listen to non-default port,1522.

1. Edit initXE.ora file which resides in $ORACLE_HOME/dbs
Add local_listener=listener1 line to this file. Listener1 will be the name of the non-default listener.

2. Edit listener.ora file to configure listener1. listener.ora file resides in $ORACLE_HOME/network/admin.

My listener.ora file looks like:

LISTENER1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vishesh-netbook)(PORT = 1522)))
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
(GLOBAL_DBNAME = XE)
(SID_NAME = XE)))

3. Edit tnsnames.ora file to resolve non-default listener name,listener1.

My tnsnames.ora file looks like:

# tnsnames.ora Network Configuration File:
LISTENER1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vishesh-netbook)(PORT = 1522)))
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vishesh-netbook)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)

4. Now connect to the database.
5. After connecting, use
SQL> CREATE SPFILE FROM PFILE; #can be run after or before instance startup
SQL> SHUTDOWN

At OS prompt, use
#lsnrctl start listener1
Then use
# sqlplus /nolog

Recover from loss of controlfile in NOARCHIVELOG mode using RMAN

2011-02-25T22:17:00.010+05:30

Here I am taking simple scenario to recover the controlfile. Following describes a scenario when my database is in NOARCHIVELOG mode. Database in oracle 10.2.0.1.0

SCENARIO: WHEN USED WITH DEFAULT RMAN CONFIGURATION SETTINGS.

Here only controlfile is lost, all the datafile and redo log files remain intact.
Following are the steps that can be taken:

1.RMAN> STARTUP MOUNT
2.RMAN> BACKUP DATABASE
3. RMAN> ALTER DATABASE OPEN;
4. delete controlfile.
5. RMAN> SHUTDOWN ABORT;
6. RMAN> STARTUP;
Database will not start. It starts in nomount state.
7. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
8. RMAN> ALTER DATABASE MOUNT;
9. RMAN> RESTORE DATABASE;
10.RMAN> RECOVER DATABASE ;
11.RMAN> ALTER DATABASE OPEN RESETLOGS;

And database will be recovered. Here SET DBID command is not needed because backup is located in flash recovery area. Controlfile autobackup default format is used.

SCENARIO: WHEN USED WITH non-DEFAULT RMAN CONFIGURATION SETTINGS.

I changed some of the default RMAN configuration settings. Now backup is not using flash recovery area. Also, controlfile autobackup is not using flash recovery area.

RMAN> SHOW ALL;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 400 M FORMAT '/usr/lib/oracle/xe/backup/df_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/snapcf_XE.f'; # default

Now follow these steps:

1.RMAN> STARTUP MOUNT
2.RMAN> BACKUP DATABASE
3. RMAN> ALTER DATABASE OPEN;
4. delete controlfile.
5. RMAN> SHUTDOWN ABORT;
6. RMAN> STARTUP;
Database will not start. It starts in nomount state.
7. RMAN> SET DBID=
8. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
9. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
10. RMAN> ALTER DATABASE MOUNT;
11. RMAN> RESTORE DATABASE;
12.RMAN> RECOVER DATABASE ;
13.RMAN> ALTER DATABASE OPEN RESETLOGS;

Note: If all the redo logs since the last database exist, then you can skip RESTORE DATABASE statement in both the above cases.

RMAN restore and recovery in NOARCHIVELOG mode

2011-02-13T22:17:00.016+05:30

Here I am writing about simple scenarios related to backup and recovery so that RMAN can be learned easily.
Lets start with a simple scenario for performing backup,restore and recovery using RMAN when our database is running in NOARCHIVELOG mode.
Oracle doc says that recovery cannot be performed in NOARCHIVELOG mode. But following scenarios explains how can we perform complete as well as incomplete recovery in NOARCHIVELOG mode. Different scenarios has been given to show how to recover database to a point in time as well as upto the recent time when the disaster occur. Each scenario has been tested in Oracle 10g xe hoping that each will also work with oracle 10g enterprise edition. Each scenario has been tested in NOARCHIVELOG mode.

Complete recovery can be performed in NOARCHIVEOG mode if all the changes since the last backup exists in the redo logs(including the redo logs that exists during the backup.)

Incomplete recovery can be performed if all the changes since the last backup up to a point in time exists in the redo log.

We can perform incomplete recovery until scn,until time and until sequence using RMAN.

SCENARIO I:Complete recovery: a datafile is lost

1. I am using Oracle 10g express edition.
2. Database is running in NOARCHIVELOG mode.
3. Cold backup has been performed after clean shutdown of the database.
4. One of the datafiles ,say users.dbf, has been lost.
5. All the redologs including one with the same log seq# as during the backup is available.
6. Thus here we have to restore only the effected datafiles,not the whole database.
Use the following sequence of steps:
1. RMAN> shutdown
2. RMAN> startup mount
3. RMAN> restore datafile 4; or restore tablespace users;
4. RMAN> recover datafile 4; or recover tablespace users;
5. RMAN> alter database open;

Instead of step 3 and 4, we can also use
RMAN> restore database; and
RMAN> recover database, respectively.

But we do not need to restore and recover the whole database in this scenario. Important point is that No data loss is guaranteed in NOARCHIVELOG mode if all the redo logs exist since the last backup. Thus complete recovery has been performed in NOARCHIVELOG mode.

SCENARIO II : Incomplete recovery
Keeping everything same as scenario I, some redo log seq no# since the last backup is not available say, redo log has been switched. Users.dbf has been lost. Here, to restore the lost datafile following steps should be taken:

1. RMAN>startup force nomount;
If database is already shutdown, then don't use force option.
2. RMAN>restore controlfile from autobackup; or restore controlfile from 'autobackup_location';
3. RMAN>alter database mount;
4. RMAN>restore database;
5. RMAN>recover database noredo;
6. RMAN> alter database open resetlogs;

Here restore of whole database is must.

You can also follow following link:

SCENARIO III: Incomplete recovery - until scn-when a table is dropped

SQL> CREATE TABLE T1(C1 NUMBER);
SQL> INSERT INTO T1 VALUES(10);
SQL>/
SQL>/
SQL> COMMIT;

SQL> select current_scn from v$database;
It generated 810390;
SQL> drop table t1;

Check v$log. If all the scns since the last backup upto the point you want to recover to (in our case it is, 810390, because table is dropped after that time) exists , then we can recover table t1 even in noarchivelog mode.
Use following operations:

RMAN> run
{
shutdown;
startup mount;
restore database;
recover database until scn 810390;
alter database open resetlogs;
}

Now you will find recovered table t1.

SCENARIO IV- incomplete recovery -until time: when a table is dropped.

1. Yesterday night, you have taken cold,consistent whole database backup.
2. In the next morning, some user error caused employees table t1 to drop.
3. If you know the approximate time of dropping the table, you can recover the table even to the point of recent changes made in the table. There is no need to panic even if your database is running in NOARCHIVElog mode.
4. Check v$log. If all the changes since the last backup is present in the redo log groups, we can recover the dropped table. Use the following procedure. Suppose the table is dropped at 10:30 in the morning.

RMAN> RUN {
2> SET UNTIL TIME = '2011-02-22:10:28:00';
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> ALTER DATABASE OPEN RESETLOGS;
}

For until time to function you must set $NLS_LANG and $NLS_DATE_FORMAT appropriately. In my case $NLS_LANG has been set to american and NLS_DATE_FORMAT has been set to 'YYYY-MM-DD:HH24:MI:SS';

SCENARIO V-INCOMPLETE RECOVERY : SQLPLUS UNTIL CANCEL:DATAFILES AND ACTIVE REDO LOG FILE IS LOST

Suppose two datafiles as well as active redo log group is lost. Here to recover database, use following procedure:

RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
SQL> RECOVER DATABASE UNTIL CANCEL;

ORA-00279: change 828771 generated at 02/23/2011 11:38:39 needed for thread 1
ORA-00289: suggestion :
/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/archivelog/2011_02_23/o1_mf
_1_1_%u_.arc
ORA-00280: change 828771 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

RMAN> ALTER DATABASE OPEN RESETLOGS;

Since one of the redolog is lost ,we do not have all the redo logs available after the last backup. That is why we have to use sql command UNTIL CANCEL. Type cancel when prompted. We will get changes only upto the last backup that is restored.

SCENARIO VI-INCOMPLETE RECOVERY : UNTIL SCN:DATAFILES AND ACTIVE REDO LOG FILE IS LOST

Here we will suppose that along with one datafile, active redo log file has also been lost. Yes, of course, it is the same scenario as scenario VI. But read out, difference will be clear.

1. SQL> truncate table t1;

Table truncated.

SQL> inert into t1 values(0);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

C1
----------
0
0

SQL> alter system switch logfile;

System altered.
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 1 52428800 1 NO ACTIVE
1183659 14-MAR-11

2 1 2 52428800 1 NO CURRENT
1184383 14-MAR-11

Group# 2 is current. We will delete log file that belongs to group# 2. Changes since the last backup are stored in log seq#1 which belongs to group# 1. Only the changes that are stored in log seq#2 will be lost. We will get back our changes that we made in table t1. Note the low scn (1184383,here)that is listed in Log seq#2. We will need it

Get the name and location of the redo log group# 2 by listing the contents of v$logfile.
SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
2 ONLINE
/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6qwfdq7v_
.log
YES

1 ONLINE
/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_1_6qwm5xz0_

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
.log
YES

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

oracle@geet:~$ mv /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6qwfdq7v_.log m.log
oracle@geet:~$ mv oradata/XE/users.dbf uu.bak

RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database UNTIL SCN 1184383;
RMAN> recover databse UNTIL SCN 1184383;
RMAN> alter database open resetlogs;

SQL> select * from t1;

C1
----------
0
0
See the output above, we got the modification of our table back.

When is listener.ora needed

2011-01-18T22:55:00.003+05:30

Today I got stucked with ora:12514 : TNS: listener does not know cuurrently of service requested in connect descriptor.

When I tried to connect with sqlplus using
sql> conn sys/sys@xe as sysdba

I got ora:12514 error

Initially, I thought that error occured due to non registration of xe database service with the listener. And I tried to set the parameters of static service registration in listener.ora file.
But later I got the knowledge that error occured because my database server has not yet been started. Without starting the database I was trying to connect to the database which is a wrong, even impossible action.
After much struggle, I got the idea of the actual problem.

A listener.ora file is NOT needed to start a default listener. I renamed my file to something else, so that I have NO file named "listener.ora". Then I restarted the listener, and it came up just fine with all default values .

If the listener uses default values, there is nothing special needed to get the instance to self-register with the listener. If the listener is already running when the instance starts, it should register on startup. It will also re-register every few minutes there after, so if the listener comes up after the db instance, it may take a couple of minutes for the registration to occur and the listener come to know of the instance.

using ed command

2010-08-08T12:33:00.002+05:30

When trying to edit a sql query or a pl/sql program using ed command on linux platform, I end up with a probably primitive editor.
What I got is as:

SQL> ed
Wrote file afiedt.buf
29

?

The solution is to add follwing line to glogin.sql file of oracle user:

define_editor=vi

Ora-12154 when connecting to Oracle forms

2010-03-18T21:12:00.002+05:30

When I started oracle Forms to create a new data blocks, I was unable to connect to the database. I got error:
Ora 12154: TNS name could not resolve the connect identifier specified

I have installed Oracle Developer suite 10g after Oracle 10g server. Platform is Windows Xp.

Solution: copy tnsnames.ora file from oracle home location to oracle developer home location.
that is,
from
d:\ora_10g\ora92\network\admin\tnsname.ora to
d:\oracle_tool\network\admin\tnsname.ora

Shutdown and restart the oc4J instance again. Try to connect again. everything work fine.

FRM-18107: Failed to create a DE context for the module

2010-03-15T19:33:00.002+05:30

When starting Forms 10g Developer after starting OCJ4 Instance, I got the above error.
I installed Oracle developer suite 10g after Oracle 10g server. (Windows platform)
Solution is to delete Oracle Home environment Variable. I just delete Oracle Home environment variable which points to, on my system, oracle 10g server which I installed at first. And everything works fine.

inserting string containing '&' in table.

2010-02-07T20:19:00.003+05:30

How can a string like ':&nbnc' be inserted in a table.
This string is using as is bind variable but not actually it is so.

To insert this or similar string into a table, use

SET DEFINE OFF

Then use insert statement. For example,

SQL> desc t2;
Name Null? Type
----------------------------------------- -------- -------------

C1 VARCHAR2(10)

SQL> insert into t2 values(':&nbnc');
Enter value for nbnc: rr
old 1: insert into t2 values(':&nbnc')
new 1: insert into t2 values(':rr')

1 row created.

SQL> SET DEFINE OFF
SQL> insert into t2 values(':&nbnc');

1 row created.

SQL>

isqlplus port number

2010-02-01T14:27:00.002+05:30

How can I know Which port to use to start isqlplus from browser.
Solution. Use following file path to get port number of isqlplus(in oracle 10g).

$ORACLE_HOME/install/portlist.ini
Then use following url to start isqlplus

http://host:port/isqlplus

Difference between spfile and pfile

2010-01-29T20:53:00.001+05:30

1. spfile resides at server side. it is a binary file. pfile is static client-side file.
2. Pfile must be updated using text editor. Updating spfile manually will corrupt it. To update spfile, Alter System set Command is used.
3. Spfile can be backed up using RMAN.

database is using pfile or spfile

2010-01-29T20:40:00.003+05:30

How can I know whether my database is started using spfile or pfile?

Execute the following query

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';

If result displays pfile, it means that database has been started with pfile and vice-versa.

rename of dbname and dbid

2009-11-12T18:19:00.000+05:30

To rename database_name and database sid we can use dbnewid utility.

Following steps belong to oracle 10g.2 on windows platform

1. Backup the database
2. SQL> Shutdown immediate
3. SQL> startup mount
4. Run the following command from the command line:

nid target=sys/password@olddbname DBNAME=ORCL

After the DBNEWID completed successfully, database has been shut down

5. SQL> STARTUP MOUNT
Instance start with error.

6. Modify initialization parameter DB_NAME to reflect new name.
sql> ALTER SYSTEM SET DB_NAME=ORCL SCOPE=SPFILE;

7. sql>SHUTDOWN IMMEDIATE

8. create new password file.
orapwd file=c:\oracle\ora92\database\pwdorcl.ora password=password entries=5

9.Rename the SPFILE and modify SPFILE to reflect new dbname.

10. Delete the old service and create the new one.

oradim -delete -sid olddbname
oradim -new -sid orcl -intpwd password -startmode a -pfile
 c:\oracle\ora92\database\spfileorcl.ora

11. Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:

lsnrctl reload

12. Open the database with RESETLOGS:
SQL >STARTUP MOUNT
SQL >ALTER DATABASE OPEN RESETLOGS;

13.Backup the database.

ORA-12162 TNS:service name is incorrectly specified

2009-08-20T11:31:00.000+05:30

I got this error in oracle 10g on fedora 11 whenever I tried
sqlplus command.
Solution: Just set Oracle_Sid in .bash_profile of oracle user. Export it.