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.

Requirements for Enabling Flashback Database

The requirements for enabling Flashback Database are:

· Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.

· You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.

· For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.