Database Startup time

Startup information is stored in v$instance.startup_time,we can get the last start up time for an instance using:

 SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance;

During a normal shutdown, Oracle writes message to the alert log, but under an emergency abort (i.e. un-plug the server), nothing appears in the logs, nor within Oracle. Oracle stops abruptly, and there is no way to see it, except periodic daemon polling for a PMON or SMON process.

Startup and shutdown are written to the alert log, externally, but you could define the alert log as an external table and then make a SQL query to parse out the shutdown and startup messages.

If you assume that PMON startup time is the same as the database startup time, you can get the uptime here:

select to_char(logon_time,'DD/MM/YYYY HH24:MI:SS') from v$session where sid=1; 

To get the history of Database Startup, we can check using below query:

SELECT startup_time FROM dba_hist_database_instance ORDER BY startup_time DESC;

From the below query we can calculate the uptime, from v$instance and compute the total uptime for the database.

select 
   'Hostname : ' || host_name
   ,'Instance Name : ' || instance_name
   ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
   ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
   trunc( 24*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))) || ' hour(s) ' ||
   mod(trunc(1440*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
   mod(trunc(86400*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from 
   sys.v_$instance; 

Alter System vs Alter Database

ALTER SYSTEM is an Instance Level commands generally it applies for running processes, parameters etc where as ALTER DATABASE is a Database Level commands generally it applies to the physical structure of the database. Consider the RAC environment most of our ALTER SYSTEM command local to the instance (ALTER SYSTEM DUMP is an exceptional) and ALTER DATABASE command for the whole database.

Mostly we can use ALTER SYSTEM command when the database status is OPEN while Alter database we can use in MOUNT state.In the sense of Auditing ALTER DATABSE command cannot be audited where as ALTER SYSTEM can.

Finally Use the ALTER DATABASE statement to modify, maintain, or recover an existing and Use the ALTER SYSTEM statement to dynamically alter your Oracle Database instance. The settings stay in effect as long as the database is mounted.

Changing Database Default Tablespace for Users

At the time of user[Schema] creation, we define the tablespace for that user. But for some case, we may need to change the tablespace for a user. We can easily perform that following the below steps:

1. Create new Tablespace for schema
create tablespace datafile '/u01/app/oracle/oradata/orclpaul/_DBF.DBF' size 5120m autoextend on;
2. Take Export of schema on Seperate drive.
EXP SYSTEM/SYSMAN@pauldb-uat OWNER= FILE=/home/paul/dbdump/_2501016.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= /home/paul/dbdump/_2501016.log
3. Now create new schema or if you have already existing schema then drop and recreate it.
drop OLD_SCHEMA_NAME CASCADE;

CREATE USER
  IDENTIFIED BY VALUES
  DEFAULT TABLESPACE
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

  --  Role for
  GRANT DBA TO WITH ADMIN OPTION;
  ALTER USER DEFAULT ROLE ALL;
  --  System Privilege for
  GRANT UNLIMITED TABLESPACE TO WITH ADMIN OPTION;
  -- Tablespace Quota for
  ALTER USER QUOTA UNLIMITED ON ;

alter user quota 0 on quota unlimited on ;


In the case while changing the default tablespace for the user's in the same database, don't forget to assign quota unlimited on new tablespace and revoke unlimited quota on old tablespace. This might not required while changing the tablespace of user's of different database.

revoke unlimited tablespace from ;

4. Now try to import the dump you have taken. Make sure dump is properly copied on the drive.

IMP SYSTEM/sysman@pauldb-uat FILE=/home/paul/dbdump/_2501016.DMP FROMUSER= TOUSER= LOG= /home/paul/dbdump/_2501016.log

Don't forget to assign back quota unlimited on tablespace.
alter user DEFAULT tablespace QUOTA UNLIMITED ON ;