Current sid and serial# of a session from SQL*PLUS

1. From within Oracle SQL*Plus session.

2. Using the userenv(‘SESSIONID’) to retrieve current sid and serial#.

select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);

SQL> select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);

SID SERIAL#
———- ———-
408 16328

SQL>

3. Note: That logging out of SQL*Plus session or reconnecting will change your SID and SERIAL#.

Larry Catt
OCP

Creating new undo tablespace 11g

Error:

ORA-01578: ORACLE data block corrupted (file # 3, block # 136)
ORA_01110: data file 3: ‘/u01/oradata/ORCL/UNDOTBS01.DBF’

This procedure replaces the current UNDO tablespace which contains a corrupted block with a new clean tablespace.

1. Startup database in nomount mode.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.2761E+10 bytes
Fixed Size 2240256 bytes
Variable Size 8388608256 bytes
Database Buffers 4362076160 bytes
Redo Buffers 7634944 bytes
SQL>

2. Verify that you are using an spfile and not a pfile for your system paramaters.

SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string
SQL>

2a. If parameter spfile has a value, continue to step 3, if it does not perform 2b through 2e.

2b. Execute ‘create spfile’ command:

SQL> create spfile from pfile;
File created.
SQL>

2c. Shutdown the oracle instance.

SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>

2d. Startup the oracle instance in nomount mode.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.2761E+10 bytes
Fixed Size 2240256 bytes
Variable Size 8388608256 bytes
Database Buffers 4362076160 bytes
Redo Buffers 7634944 bytes
SQL>

2e. Verify the use of spfile with the show command.

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /opt/app/oracle/ORCL_db/dbs/sp
fileORCL.ora
SQL>

3. Get the values for parameters undo_management and undo_tablespaces.

SQL> show parameter undo_management

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
SQL> show parameter undo_tablespace

NAME TYPE VALUE
———————————— ———– ——————————
undo_tablespace string UNDOTBS1
SQL>

4. Change the undo_management setting to manual.

SQL> alter system set undo_management=manual scope=spfile;
System altered.
SQL>

5. Mount and Open the database instance.

SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL>

6. Create new undo tablespace.

SQL> create undo tablespace undotbs02 datafile ‘/u02/oradata/ORCL/UNDOTBS02.DBF’ size 5G;

Tablespace created.
SQL>

7. alter parameter undo_tablespace to undotbs02.

SQL> alter system set undo_tablespace=undotbs02 scope=spfile;
System altered.
SQL>

8. alter parameter undo_management to auto.

SQL> alter system set undo_management=auto scope=spfile;
System altered.
SQL>

9. Shutdown and startup the oracle instance.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.2761E+10 bytes
Fixed Size 2240256 bytes
Variable Size 8388608256 bytes
Database Buffers 4362076160 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SQL>

10. Check parameters undo_management and undo_tablespaces.

SQL> show parameter undo_management

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
SQL> show parameter undo_tablespace

NAME TYPE VALUE
———————————— ———– ——————————
undo_tablespace string UNDOTBS02
SQL>

That completes changing undotablespaces in 11g

Larry Catt
OCP

Finding SCN for Oracle Database by Date and time

Oracle provides the function TIMESTAMP_TO_SCN to determine the database SCN number for a given time. This is very useful in performing multiple Oracle Administrative tasks. This procedure explains the use of this function to extract SCN.

1. Logon to Oracle Database server as the Oracle software owner and start SQLPLUS.

[root@linux2 etc]# su – oracle
Last login: Thu Jun 1 09:25:23 EST 2017 on pts/1
[oracle@linux2 ~]$
[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 1 13:47:01 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

2. Determine Oracle SCN one hour ago by specifying sysdate-1/24

SQL> select timestamp_to_scn(sysdate-1/24) from dual;

TIMESTAMP_TO_SCN(SYSDATE-1/24)
——————————
5062307

SQL>

3. Determine Oracle SCN 1 day ago by specifying sysdate-1

SQL> select timestamp_to_scn(sysdate-1) from dual;

TIMESTAMP_TO_SCN(SYSDATE-1)
—————————
4975332

SQL>

4. Determine Oracle SCN 1 week ago by specifying sysdate-7

SQL> select timestamp_to_scn(sysdate-7) from dual;

TIMESTAMP_TO_SCN(SYSDATE-7)
—————————
4696226

SQL>

5. Determine Oracle SCN by specific date time by using the to_date() function with appropriate date mask.

SQL> select timestamp_to_scn(to_date(’25-MAY-2017 06:30:00′,’DD-MON-YYYY HH24:MI_SS’)) from dual;

TIMESTAMP_TO_SCN(TO_DATE(’25-MAY-201706:30:00′,’DD-MON-YYYYHH24:MI_SS’))
————————————————————————
4696226

SQL>

6. This completes the use of the function TIMESTAMP_TO_SCN.

Larry Catt
OCP

Executing SQL script from command line or executable shell script

You can call into SQLPLUS to execute SQL script from any shell script. This procedure shows how to structure your statements to LINUX environment for call into SQLPLUS from shell script.

1. Logon to your LINUX server as a user with access to SQLPLUS.
2. Use VI to create a executable file. Replace the connection string with appropriate username/password@SID values. Additionally, change the script you which to execute in this example we are executing “utlrp.sql”.

sqlplus sys/password@orcl as sysdba < < EOF @?/rdbms/admin/utlrp.sql exit; EOF

Larry Catt
OCP