Index information

–show index information. showDex.sql
set linesize 150
col owner for a10
col index_name for a25
col table_name for a20 hea TNAME
col table_owner for a10 hea TOWNER
col compression for a3 trunc hea CMP
col index_type for a10 trunc
col degree for a6
col ANLYZED for a7 trunc
col STATUS for a3 trunc hea STS
col ini_trans for 999 hea ITR
col max_trans for 999 hea MTR
col num_rows for 9999999 hea ROWS
col tablespace_name for a10 hea TBNAME
select owner,index_name,index_type,STATUS,INI_TRANS,MAX_TRANS,COMPRESSION,degree,TABLESPACE_NAME,num_rows,
to_char(LAST_ANALYZED, ‘DDMonYY’) ANLYZED,table_owner,table_name from DBA_INDEXES where owner in
(‘SYS’,’SYSTEM’,’OUTLN’,’DBSNMP’,’OUTLN’,’ORACLE’,’PERFSTAT’,’OPS$ORACLE’) order by 1,2;

Monitoring: database extents

1. Objects whose next extent is too big to fit in the tablespace (ORA-1653, ORA-1654, ORA-1655):

SELECT OWNER,SEGMENT_NAME, round(NEXT_EXTENT/(1024*1024),1) next_in_MB,
S.TABLESPACE_NAME,round(MAX_FREE_BYTES/(1024*1024),1) max_free_MB,
autoextensible_bytes/(1024*1024) autoextend_MB
FROM SYS.DBA_SEGMENTS S,
(SELECT TABLESPACE_NAME,MAX(BYTES) MAX_FREE_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(select tablespace_name, sum(maxbytes)-sum(bytes) autoextensible_bytes
from dba_data_files
group by tablespace_name) d
WHERE
S.NEXT_EXTENT > ( F.MAX_FREE_BYTES + d.autoextensible_bytes )
AND S.TABLESPACE_NAME=F.TABLESPACE_NAME and
f.tablespace_name=d.tablespace_name;

2. Objects that are near their MAXEXTENTS (ORA-1632):

select I.OWNER, INDEX_NAME, EXTENTS, I.MAX_EXTENTS
from dba_indexes I, DBA_SEGMENTS S
where I.OWNER=S.OWNER AND I.INDEX_NAME=S.SEGMENT_NAME AND
S.SEGMENT_TYPE=’INDEX’
AND I.max_extents < 100000
AND I.MAX_EXTENTS – S.EXTENTS < 10;

RMAN – When do you require DBID for database recovery

dbid integer  

specifies the db identifier, which is a unique 32-bit identification number computed when the database is created. The DBID column of the V$DATABASE data dictionary view displays the identifier. The DBID is also stored in the DB table of the recovery catalog.

The set dbid command is useful for restoring the control file when each of these conditions is met:

  • The control file has been lost and must be restored from a backup.

  • You are using a recovery catalog.

  • Multiple databases registered in the recovery catalog share a database name.

  • You receive the “RMAN-20005: target database name is ambiguous” message when you attempt to restore the control file.

If these conditions are not met, RMAN will correctly identify the control file to restore, so you do not need to use the set dbid command.

RMAN accepts set dbid only if you have not yet connected to the target database, i.e., set dbid must precede the connect target command. If the target database is mounted, then RMAN verifies that the user-specified DBID matches the DBID from the database; it not, RMAN signals an error. If the target database is not mounted, RMAN uses the user-specified DBID to restore the control file. Once you have restored the control file, you can mount the database to restore the rest of the database.

Do you need to put tablespaces hot backup mode when using RMAN

To understand why RMAN does not require extra logging or backup mode, you must first understand why those features are required for non-RMAN online backups.

A non-RMAN online backup consists of a non-Oracle tool, such as cp or dd, backing up a datafile at the same time that DBWR is updating the file. Oracle can’t prevent the tool from reading a particular block at the exact same time that DBWR is updating that block. When that happens, the non-Oracle tool might read a block in a half-updated state, so that the block which is copied to the backup media might only have been updated in its first half, while the second half contains older data. This is called a “fractured block”. If this backup needs to be restored later, and that block needs to be recovered, recovery will fail because that block is not usable.

The ‘alter tablespace begin backup’ command is oracle solution for the fractured block problem. When a tablespace is in backup mode, and a change is made to a data block, instead of logging just the changed bytes to the redo log, oracle also log a copy of the entire block image before the change, so that it can reconstruct this block if media recovery finds that this block was fractured. That block image logging is what causes extra redo to be generated while files are in backup mode.

The reason that RMAN does not require extra logging is that it guarantees that it will never back up a fractured block. it can make that guarantee because we know the format of Oracle data blocks, and it verify that each block that it read is complete before it copy it to the backup. If it read a fractured block, it will read the block again to obtain a complete block before backing it up. non-Oracle tools are not able to do the same thing because they do not know how to verify the contents of an Oracle data block.

Backup mode has another effect, which is to ‘freeze’ the checkpoint in the header of the file until the file is removed from backup mode. Oracle does this because it cannot guarantee that the third-party backup tool will copy the file header prior to copying the data blocks. RMAN does not need to freeze the file header checkpoint because it knows the order in which it will read the blocks, which enables it to capture a known good checkpoint for the file.

SCRIPT: what is the session doing

select
–‘alter system kill session ”’|| s.SID||’,’ || s.serial# ||”” ,
–‘kill -9 ‘ || p.spid,
p.SPID UnixProcess ,s.SID,s.serial#,
s.USERNAME,s.COMMAND,s.MACHINE,
s.SQL_ADDRESS,s.SQL_HASH_VALUE
,s.program, cpu_time,fetches,
disk_reads,buffer_gets,rows_processed,
executions,child_latch,event,sql_text,COMMAND_TYPE,
sbc.name,to_char(sbc.last_captured,’yyyy-mm-dd hh24:mi:ss’),
sbc.value_string
from v$session s
join v$process p on p.ADDR = s.PADDR — and p.SID in (27817)
left outer join v$sqlarea sa on sa.ADDRESS = s.SQL_ADDRESS
left outer join V$SQL_BIND_CAPTURE sbc on sbc.ADDRESS = s.SQL_ADDRESS
where 1=1
–and s.program like ‘imp%’
–and s.username =’SIEBEL’
–AND p.SPID in (15179,8304)
–and s.SID in (1019)

V$LICENSE

This view contains information about license limits.

Column Datatype Description
SESSIONS_MAX NUMBER Maximum number of concurrent user sessions allowed for the instance
SESSIONS_WARNING NUMBER Warning limit for concurrent user sessions for the instance
SESSIONS_CURRENT NUMBER Current number of concurrent user sessions
SESSIONS_HIGHWATER NUMBER Highest number of concurrent user sessions since the instance started
USERS_MAX NUMBER Maximum number of named users allowed for the database
CPU_COUNT_CURRENT NUMBER Current number of logical CPUs or processors on the system
CPU_CORE_COUNT_CURRENT NUMBER Current number of CPU cores on the system (includes subcores of multicore CPUs, as well as single-core CPUs)
CPU_SOCKET_COUNT_CURRENT NUMBER Current number of CPU sockets on the system (represents an absolute count of CPU chips on the system, regardless of multithreading or multicore architectures)
CPU_COUNT_HIGHWATER NUMBER Highest number of logical CPUs or processors on the system since the instance started
CPU_CORE_COUNT_HIGHWATER NUMBER Highest number of CPU cores on the system since the instance started (includes subcores of multicore CPUs, as well as single-core CPUs)
CPU_SOCKET_COUNT_HIGHWATER NUMBER Highest number of CPU sockets on the system since the instance started (represents an absolute count of CPU chips on the system, regardless of multithreading or multicore architectures)

A little additional info on hc_ files:

“Q1: What is the $ORACLE_HOME/dbs/hc_.dat file?
A1: The $ORACLE_HOME/dbs/hc_.dat is created for the instance health check monitoring. It contains information used to monitor the instance health and to determine why it went down if the instance isn’t up. The file will be recreated at every instance startup.

Q2: What happens if the $ORACLE_HOME/dbs/hc_.dat file is deleted?
A2: Per Bug 4645405 UNABLE TO START DB – ORA-7445 [KSIHSMRINI()+152] [SIGBUS],
if you replace the file with an empty “dummy” copy, you will get an ORA-7445 error. Therefore, if the file gets deleted on the fly while the database is up, or if the file is replaced with a 0 byte file, simply delete the file and restart the database. The file will be correctly recreated at the next database startup.”

A new method for a database rename instance – NID utility

Rename an Oracle database using the new dbnewid (also called nid, for new ID) utility:

* STEP 1: Backup the database.

* STEP 2: Mount the database after a clean shutdown:

SHUTDOWN IMMEDIATE
STARTUP MOUNT

* STEP 3: Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

nid TARGET=sys/password@TSH1 DBNAME=TSH2

Assuming the validation is successful the utility prompts for confirmation before performing the actions. Typical output may look something like:

C:\oracle\920\bin>nid TARGET=sys/password@TSH1 DBNAME=TSH2
DBNEWID: Release 9.2.0.3.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

Connected to database TSH1 (DBID=1024166118)

Control Files in database:
C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL
C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL
C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL

Change database ID and database name TSH1 to TSH2? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1024166118 to 1317278975
Changing database name from TSH1 to TSH2
Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL – modified
Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL – modified
Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL – modified
Datafile C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF – dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\TSH1\UNDOTBS01.DBF – dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\TSH1\CWMLITE01.DBF – dbid changed, wrote new name
Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL – dbid changed, wrote new name
Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL – dbid changed, wrote new name
Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL – dbid changed, wrote new name

Database name changed to TSH2.
Modify parameter file and generate a new password file before restarting.
Database ID for database TSH2 changed to 1317278975.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.

* STEP 4: Shutdown the database:

SHUTDOWN IMMEDIATE

* STEP 5: Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.

STARTUP MOUNT
ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE

* STEP 6: Create a new password file:

orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10

* STEP 7: Rename the SPFILE to match the new DBNAME.

* STEP 8: If you are using Windows you must recreate the service so the correct name and parameter file are used:

oradim -delete -sid TSH1
oradim -new -sid TSH2 -intpwd password -startmode a -pfile c:\oracle\920\database\spfileTSH2.ora

If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:

ORACLE_SID=TSH2; export ORACLE_SID

* STEP 9: Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:

lsnrctl reload

* STEP 10: Open the database with RESETLOGS:

STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

* STEP 11: Backup the database.

Renaming an Oracle database

The following log shows how a database name was renamed from ORA9 to CAT.

SQL> select name from v$database;

NAME
———
ORA9

SQL> alter system switch logfile;

SQL> alter database backup controlfile to trace;

The instance needs to be shut down. SYSDBA privileges will be needed.

SQL> shutdown

Modify (and optionally rename) the created trace file:

1. Find the line reading # Set #2. RESETLOGS case
2. Remove all lines above this line.
3. Change the line containing the database name from CREATE CONTROLFILE REUSE DATABASE “ORA9” RESETLOGS NOARCHIVELOG
to
CREATE CONTROLFILE SET DATABASE “CAT” RESETLOGS NOARCHIVELOG
Note, in my case, the database is running in noarchive log mode. The corresponding line reads ARCHIVELOG otherwise.
4. Remove the line reading RECOVER DATABASE USING BACKUP CONTROLFILE.
5. Remove lines starting with #.

It looks then something like:

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE “CAT” RESETLOGS NOARCHIVELOG
— SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ‘/home/oracle/databases/cat/redo1.ora’ SIZE 100M,
GROUP 2 ‘/home/oracle/databases/cat/redo2.ora’ SIZE 100M,
GROUP 3 ‘/home/oracle/databases/cat/redo3.ora’ SIZE 100M
— STANDBY LOGFILE
DATAFILE
‘/home/oracle/databases/cat/system.dbf’,
‘/home/oracle/databases/cat/undo.dbf’,
‘/home/oracle/databases/cat/data.dbf’
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/home/oracle/OraHome1/databases/ora9/temp.dbf’
SIZE 104857600 REUSE AUTOEXTEND OFF;

In my case, I renamed the file to /tmp/rename_db.sql.
Move the controlfiles away so that they can be re-created..

$ mv ctl_1.ora ctl_1.ora.moved
$ mv ctl_2.ora ctl_2.ora.moved
$ mv ctl_3.ora ctl_3.ora.moved

The database name must be entered (changed) in the initSID.ora:
initXXX.ora

db_name = CAT

sqlplus “/ as sysdba”

SQL> @/tmp/rename_db

SQL> select name from v$database;

NAME
———
CAT

RMAN recover options – UNTIL TIME/SCN/SEQUENCE

UNTIL TIME = ‘date_string’
Specifies a time as an upper limit. RMAN selects only files that can be used to recover up to but not including the specified time.
For example, LIST BACKUP UNTIL TIME ‘SYSDATE-7’ lists all backups that could be used to recover to a point one week ago.

UNTIL SCN = integer
Specifies an SCN as an upper limit. RMAN selects only files that can be used to recover up to but not including the specified SCN.
For example, RESTORE DATABASE UNTIL SCN 1000 chooses only backups that could be used to recover to SCN 1000.

UNTIL SEQUENCE = integer

Specifies a redo log sequence number and thread as an upper limit. RMAN selects only files that can be used to recover up to but not including the specified sequence number.
For example, REPORT OBSOLETE UNTIL SEQUENCE 8000 THREAD 1 reports only backups that could be used to recover through log sequence 7999.