Managing Oracle database instance - the basics

PROBLEM:
What is the basics of managing Oracle database instance?

SOLUTION:

-- Data Files
SELECT name FROM v$datafile;

-- Control Files
SELECT name FROM v$controlfile;

-- Redo Log Files
SELECT member FROM v$logfile;

--------------------------
-- View a parameter value
--------------------------

show parameter undo

show parameter retention

show parameter mttr

desc v$parameter

SELECT name, value
FROM v$parameter
WHERE name LIKE '%mttr%';

show parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS

---------------- 
-- Change values
----------------

ALTER SYSTEM SET fast_start_mttr_target=300;

SELECT name, value
FROM v$parameter
WHERE name LIKE '%mttr%';

show parameter mttr

ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS = 10 ;

ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS = 10 SCOPE = SPFILE;

SELECT  name, ISSYS_MODIFIABLE
FROM v$parameter
WHERE NAME IN ('fast_start_mttr_target', 'sec_max_failed_login_attempts')

--------------------------------------------------- 
-- Difference between V$parameter and v$spparameter
---------------------------------------------------

-- * V$PARAMETER
--   It displays the information about initialization parameters that
--   are currently in effect for the session.
-- * V$SPPARAMETER
--   It displays the information about contents of the server parameter file.
--   If a server parameter file was not used to start the instance,
--   then ISSPECIFIED column contains FALSE value.

SELECT name, value FROM v$spparameter
WHERE name LIKE '%mttr%';

SELECT name, value FROM v$parameter
WHERE name LIKE '%mttr%';

alter system set fast_start_mttr_target = 100 scope = spfile ;

SELECT name, value FROM v$spparameter
WHERE name LIKE '%mttr%';

SELECT name, value FROM v$parameter
WHERE name LIKE '%mttr%';

-- View only used parameters from the spfile :

select count(*)
from v$spparameter
where ISSPECIFIED <> 'FALSE'

--------------------------- 
-- CREATE PFILE FROM SPFILE
---------------------------

-- Default value : ARC%S_%R.%T

show parameter log_archive_format

ALTER SYSTEM SET log_archive_format = 'RAM%K' ;

ALTER SYSTEM SET log_archive_format = 'RAM%K' SCOPE = SPFILE ;

SHUTDOWN IMMEDIATE

STARTUP

CREATE PFILE FROM SPFILE ;

-- change parameter value in the pfile

CREATE SPFILE FROM PFILE ;

STARTUP

show parameter log_archive_format

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!