Category Archives: Oracle

11g, Oracle, Oracle 10g, oracle 9i

Oracle – Adding DBA user to password file:

Leave a comment

Oracle provides for a normal DBA user account to remotely connect to a database with SYSDBA privileges through the use of a password file. This article will cover granting a normal DBA user the SYSDBA privilege which in turn adds the user to the password file for that database.
This allows the user to connect remotely to the database for the purpose of shutting down the database and starting the database.
This procedure will work on any OS.

1. From a remote machine, not your Oracle database server, attempt to connect as SYSDBA to your database with an account which has DBA privileges.

C:\> sqlplus ljcatt/password5@oracle as SYSDBA

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Oct 18 13:32:27 2009

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

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

Now attempt to connect without specifying SYSDBA privilege.

C:\> sqlplus ljcatt/password5@oracle

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Oct 18 13:34:27 2009

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

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

SQL>

2. The reason this fails with insufficient privileges at first, is because the user LJCATT is not recorded in the database password file for remote access with SYSDBA privilege. The following steps outline how to add the user LJCATT to the remote access password file.

3. Logon to your Oracle database server as the Oracle software owner.

4. Logon to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Oct 18 13:42:31 2009

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

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

SQL>

5. Verify that you are using a password file by ensuring that the init parameter REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared.

SQL> show parameter password

NAME TYPE VALUE
———————————— ———– ——————
REMOTE_LOGIN_PASSWORDFILE string EXCLUSIVE
SQL>

6. If the REMOTE_LOGIN_PASSWORDFILE init parameter is set to NONE, adjust it to EXCLUSIVE or SHARED.

7. In SQLPLUS, grant the privilege SYSDBA to LJCATT.

SQL> grant SYSDBA to ljcatt;

Grant succeeded.

SQL>

8. Now, the user LJCATT has been added to the password file. From a remote machine, not your Oracle database server, attempt to connect to your database with the account LJCATT with SYSDBA.

C:\> sqlplus ljcatt/password5@oracle as SYSDBA

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Oct 18 13:59:08 2009

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

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

SQL>

This completes adding a user to the remote password file for access as SYSDBA from a remote connection.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

linux, Oracle, Oracle 10g, oracle 9i

SQLPLUS does not allow connect as sysdba:

Leave a comment

In Oracle configuration files and system parameters there exist certain setting which will prevent the Oracle software owner from connecting to the database with sysdba privileges. In this article we will discuss a SQLNET.ORA setting which will result in this error. This article was written using a linux environment; however the result will be very similar on any OS.

1. Logon to your Oracle server as the Oracle software owner and attempt to logon to sqlplus with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Oct 19 19:00:46 2009

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

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

2. Exit out of sqlplus by hitting return twice.

3. Change directory to $ORACLE_HOME/network/admin.

mylinux:>cd $ORACLE_HOME/network/admin
mylinux:>

4. Open the file sqlnet.ora and comment out the line sqlnet.authentication.

mylinux:>vi sqlnet.ora
“sqlnet.ora” 2 lines, 59 characters
#sqlnet.authentication_services=none

5. Save your changes and exit your editor.

6. Now try to connect to sqlplus with sysdba privileges as the oracle software owner.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Oct 19 19:05:01 2009

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

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

SQL>

You have successfully connected with SYSDBA privilege.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Changing init parameters with an SPFILE:

Leave a comment

Since Oracle 9i, Oracle has introduced the use of SPFILE over INIT files for control of the RDBMS initialization parameters. The introduction of SPFILE was to allow for the dynamic changing of initialization parameters setting while the database is up and eliminating the need to open an OS layer file to make changes to the ORACLE initialization parameters. This article will cover: how to change initialization parameter from within an Oracle database. This procedure is the same on any OS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Connect to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Sep 27 09:49:04 2009

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

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

SQL>

3. We use the alter system command to change initialization parameters from within the Oracle database while an SPFILE is in use. The following is an example of the alter system command.

alter system set {initialization_parameter} = {new_value} scope = {scope}

4. The key here is the scope clause which can have three possible values:

MEMORY – No changes are made to the SPFILE at all and the change is only mode in the current instance. To use this option, the initialization parameter must be dynamic and modifiable.

BOTH – Changes are made both in the current instance and in the SPFILE for future restart of the database. To use this option, the initialization parameter must be dynamic and modifiable.

SPFILE – Changes are not made in the current instance and the SPFILE is updated with new initialization parameter setting; however it will only take affect on restart of the database.

5. Examples of changing initialization parameter with alter system command:

SQL> alter system set user_dump_dest = ‘/opt/oracle/udump’ scope=both;

System altered.

SQL> alter system set user_dump_dest = ‘/opt/oracle/udump ‘ scope=memory;

System altered.

SQL> alter system set audit_file_dest=’/opt/oracle/udump ‘ scope=spfile;

System altered.

This completes adjusting initialization parameters in an SPFILE.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i

Determining if an initializing parameter is dynamic or not:

Leave a comment

Since Oracle 9i, Oracle has introduced the use of SPFILE over INIT files for control of the RDBMS initialization parameters. The introduction of SPFILE was to allow for the dynamic changing of initialization parameters setting while the database is up and eliminating the need to open an OS layer file to make changes to the ORACLE initialization parameters. This article will cover: how to determine if an initialization parameter is dynamic. This procedure is the same on any OS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Connect to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Sep 18 20:49:04 2009

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

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

SQL>

3. Execute the following SQL statement: select NAME, ISSYS_MODIFIABLE from V$PARAMETER;

SQL> set pagesize 500
SQL> column name format a30
SQL> select NAME, ISSYS_MODIFIABLE from V$PARAMETER;
NAME ISSYS_MODIFIABLE
——————— ——————-
..
..
..
user_dump_dest IMMEDIATE
max_dump_file_size IMMEDIATE
audit_file_dest DEFERRED
LINUX_sched_noage FALSE
object_cache_optimal_size DEFERRED
object_cache_max_size_percent DEFERRED
session_max_open_files FALSE
..
..
..
SQL>

4. Note: In the example above we have the NAME of the initialization parameter and three values for ISSYS_MODIFIABLE column, we can use this to determine if the initialization parameter is modifiable or not.

IMMEDIATE – The parameter is dynamic and can take affect immediately no matter if you are using a SPFILE or INIT file. When using an SPFILE you can use the clause SCOPE in the following ways: BOTH = change will take affect immediately and on restart of database; MEMORY = change will only take affect in current instance; and SPFILE = change will only take affect on restart of database. When using an INIT file you can use the clause SCOPE in the following ways: MEMEORY = change will only take affect in current instance.

DEFERRED – The parameter is not dynamic and will not take affect immediately. When using an SPFILE you can use the clause SCOPE in the following ways: SPFILE = change will only take affect on restart of database. When using an INIT file you have to manually update INIT file and restart database.

FALSE – The parameter is not dynamic and cannot be changed immediately. When using an SPFILE you can use the clause SCOPE in the following ways: SPFILE = change will only take affect on restart of database. When using an INIT file you have to manually update INIT file and restart database.

This completes determining if an initialization parameter is dynamic.

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, RAC

Oracle RAC and difference between V$ and GV$ views

Leave a comment

Since the introduction of Oracle RAC, we have seen the additional system dictionary views of GV$ which reflect information across the various nodes of a RAC system. Unlike the V$ views of the data dictionary, which relate to the current status of the single node you are connected to, the GV$ allows you to see status throughout the system. In this article we will show how to view sessions through the entire system instead of just a single node.

1. Connect to NODE1 of your Oracle RAC system as a DBA users, in this example we connect as the user ljcatt..

mylinux>$ sqlplus ljcatt/ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Sep 1 18:05:43 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

2. Connect to NODE2 of your Oracle RAC as sysdba.

mylinux>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Sep 1 18:07:28 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

3. Use the SQL statement “select distinct(username) from v$session;” to view the current connections within NODE2 of your RAC. NOTE: You will not be able to see the connection from LJCATT in this dictionary view, because it only reflects connections to NODE2 and not the entire RAC.

SQL> select distinct(username) from v$session;

USERNAME
——————————

SYS

4. Wile still connected to NODE2 as sysdba, use the SQL statement “select distinct(username) from gv$session;” to view all sessions within the RAC. NOTE: Now you can see the connection to LJCATT.

SQL> select distinct(username) from gv$session;

USERNAME
——————————

LJCATT
SYS

SQL>

5. The GV$ dictionary views have the additional INST_ID column which give the node that the action is occurring in. Use the SQL statement “select inst_id from gv$session where username=’LJCATT’” to identify the NODE which LJCATT is currently connected to.

SQL> Select inst_id from gv$session where username=’LJCATT’;

INST_ID
———-
1

SQL>

The lesson learned, when you are administrating a RAC system use the dictionary views of GV$ and not V$.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, SQL

How Oracle handles the distinct clause between 10gR1 and 10g R2

Leave a comment

Oracle has changed the algorithm used to perform “select distinct” operations from 10gR1 and earlier version to 10gR2 and new versions. This has caught a lot of custom application and script writers by surprise, when they depended on the ordering of returned values from 10gR1 and earlier versions. The problem is caused by the way in which the Oracle parser handles a “select distinct” operation: From Oracle 8 to 10gR1, the parser used a sort algorithm to determine distinct values and from 10gR2 and later, the parser uses a hash algorithm to determine distinct values. The end result is that 8 to 10gR1 will return “select distinct” operations in sorted order and new version will not. So to resolve this issue you must use the “order by” clause to guarantee values are returned in sorted order. This article shows the plan differences between the two parsed statements in Oracle 10gR1 and 10gR2.

1. Logon to Oracle 10gR1 and execute “select version from v$instance to show the exact version of this release.

SQL> select version from v$instance;

VERSION
—————–
10.1.0.3.0

SQL>

2. Create the table test1 with the following DDL and insert ten numerical values.

create table test1(v_number number(10));

declare

v_ct number:=1;

begin

while v_ct<10
loop
insert into test1(v_number) values(v_ct);

v_ct := v_ct+1;

end loop;
end;
/

3. Select from this table with “select distinct” clause, as you can see the values are returned in sorted order even though we did not specify the order by clause.

select distinct v_number from test1;

SQL> select distinct v_number from test1;

V_NUMBER
———-
1
2
3
4
5
6
7
8
9

9 rows selected.

SQL>

4. Execute explain plan on the previous statement and you can see that the parser is performing a sort to find all distinct values in the table, thus returning the values in sorted order.

SQL> explain plan for select distinct v_number from test1;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
——————————————————————

Plan hash value: 1260548514

——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | 9 | 117 | 4 (25)| 00:00:01 |
| 1 | SORT UNIQUE | | 9 | 117 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 9 | 117 | 3 (0)| 00:00:01 |
——————————————————————–

Note

PLAN_TABLE_OUTPUT
———————————————————————
– dynamic sampling used for this statement

13 rows selected.

SQL>

5. Logon to 10gR2 server and select version from v$instance to show the exact RDBMS version.

SQL> select version from v$instance;

VERSION
—————–
10.2.0.4.0

SQL>

6. Create the table test1 and insert ten numerical values.

SQL> create table test1(v_number number(10));

Table created.

SQL>
SQL> declare
2
3 v_ct number:=1;
4
5 begin
6
7 while v_ct<10
8 loop
9 insert into test1(v_number) values(v_ct);
10
11 v_ct := v_ct+1;
12
13 end loop;
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>

7. Perform select distinct operation on test1 table and note that the values are returned in an unsorted order.

SQL> select distinct v_number from test1;

V_NUMBER
———-
1
6
2
4
5
8
3
7
9

9 rows selected.

SQL>

8. Perform a explain plain on your “select distinct” statement and note that the sort operation has been replaced by a hash operation, thus values will not be returned in a sorted format.

SQL> explain plan for select distinct v_number from test1;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
——————————————————————–

Plan hash value: 255531131

——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————
| 0 | SELECT STATEMENT | | 9 | 117 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 9 | 117 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 9 | 117 | 3 (0)| 00:00:01 |
———————————————————————-

Note

PLAN_TABLE_OUTPUT
——————————————————————-
– dynamic sampling used for this statement

13 rows selected.

SQL>

9. The reason for the change in the parser’s behavior is speed of execution; the hash algorithm is much more efficient then the sort operation and thus execution time decreases. In order to have a guaranteed sorting of your value, you must us an “order by” clause.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

ORACLE RMAN Full Database Backup

Leave a comment

Since the release of Oracle 8, Oracle has provided the Recovery Manger utility (RMAN) to perform database backup and recovery operations for Oracle databases. The utility has provided vast improvements over previous backup procedures. While there have been third party products to backup Oracle and other database systems, RMAN is a proprietary utility provided by Oracle and is freely available with your standard Oracle license. In this article we will review the procedures to perform a full database backup with the RMAN utility.

NOTE: This article assumes you have setup a recovery catalog. See article ‘Oracle 10g RMAN database Setup’ at http:/www.relidb.com/?p=80

1. Logon to your Oracle database server as the Oracle software owner.
2. Set your oracle SID to the database you wish to backup in this example the database is orcl10g.

mylinux: >set ORACLE_SID=orcl10g
mylinux: >

3. Execute the utility command RMAN.

Mylinux:> rman

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Aug 24 19:16:23 2009

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

RMAN>

4. Connect to your target database and recovery catalog.

RMAN> connect target system/oracle
connected to target database: ORCL10G (DBID=879290681)
RMAN> connect catalog rman/rman@rman
connected to recovery catalog database
RMAN>

5. Create your catalog with ‘CREATE CATALOG’ command.

RMAN> create catalog
recovery catalog created
RMAN>

6. Register the database with the ‘REGISTER DATABASE’ command.

RMAN> register database
2> ;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

7. Begin the backup of your Oracle database with the ‘BACKUP FULL’ command.

RMAN> backup full tag BACKUP_DB_ORCL format ‘/U01/ORACLE/ORADATA/ORCL10G/%d_%t_%p_%s_%c_%u.dbf’ d
atabase;

Starting backup at 24-AUG-09
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/U01/ORACLE/ORADATA/ORCL10G/SYSTEM01.
DBF
input datafile fno=00003 name=/U01/ORACLE/ORADATA/ORCL10G/SYSAUX01.
DBF
input datafile fno=00005 name=/U01/ORACLE/ORADATA/ORCL10G/EXAMPLE01
.DBF
input datafile fno=00002 name=/U01/ORACLE/ORADATA/ORCL10G/UNDOTBS01
.DBF
input datafile fno=00006 name=/U01/ORACLE/ORADATA/ORCL10G/USERDATA1
.DBF
input datafile fno=00004 name=/U01/ORACLE/ORADATA/ORCL10G/USERS01.D
BF
input datafile fno=00007 name=/U01/ORACLE/ORADATA/ORCL10G/TEST_DATA
01.DBF
input datafile fno=00008 name=/U01/ORACLE/ORADATA/ORCL10G/DATA02.DB
F
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_695765204_1_3_1_03KNH26K.DBF tag=BACKUP_DB_ORCL c
omment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
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 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_695765270_1_4_1_04KNH28M.DBF tag=BACKUP_DB_ORCL c
omment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-AUG-09

RMAN>

8. Begin the backup of your controlfiles with the ‘BACKUP CURRENT CONTROLFILE’ command.

RMAN> backup current controlfile format ‘/U01/ORACLE/ORADATA/ORCL10G/%d_%t_%p_%s_%c_%u.ctl’;

Starting backup at 24-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_695765322_1_5_1_05KNH2AA.CTL tag=TAG20090824T2008
42 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 24-AUG-09

RMAN>

9. Crosscheck the archivelogs, alter the archivelogs to current, and backup archivelogs

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
specification does not match any archive log in the recovery catalog

RMAN> sql ‘alter system archive log current’;

sql statement: alter system archive log current

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
validation succeeded for archived log
archive log filename=/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL10G/ARCHIV
ELOG/2009_08_24/O1_MF_1_15_596BXB7T_.ARC recid=1 stamp=695765354
Crosschecked 1 objects

RMAN> backup format ‘/U01/ORACLE/ORADATA/ORCL10G/%d_S%s_P%p_t%t_arch’ ( archivelog all delete inpu
t );

Starting backup at 24-AUG-09
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=15 recid=1 stamp=695765354
input archive log thread=1 sequence=16 recid=2 stamp=695765359
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_S6_P1_T695765362_ARCH tag=TAG20090824T200921 comm
ent=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL10G/ARCHIV
ELOG/2009_08_24/O1_MF_1_15_596BXB7T_.ARC recid=1 stamp=695765354
archive log filename=/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL10G/ARCHIV
ELOG/2009_08_24/O1_MF_1_16_596BXGWB_.ARC recid=2 stamp=695765359
Finished backup at 24-AUG-09

RMAN>

10. That completes taking a complete database backup with the RMAN utility.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com