#Toolbox - 
#
#





------PATCH------------------Patch-------------------PATCH------------------Patch-------------------PATCH----------

#Check version, and patch information

set linesize 999
COLUMN instance_name FORMAT A15
COLUMN host_name FORMAT A15
COLUMN VERSION_FULL FORMAT A15
COLUMN status FORMAT A10
COLUMN DATABASE_STATUS FORMAT A15
COLUMN STARTUP_TIME FORMAT A20
COLUMN ACTIVE_STATE FORMAT A15

select instance_name, host_name, VERSION_FULL, STARTUP_TIME, status, DATABASE_STATUS, ACTIVE_STATE from v$instance;



set linesize 999
COLUMN patched_on FORMAT A25
COLUMN VERSION FORMAT A25
COLUMN DESCRIPTION FORMAT A65
COLUMN ACTION FORMAT A15
COLUMN COMMENTS FORMAT A35
COLUMN NAMESPACE FORMAT A15
COLUMN ACTION_TIME FORMAT A30
COLUMN STATUS FORMAT A25

select to_char(action_time, 'DD-MON-YYYY  HH:MI:SS AM') patched_on, description, patch_id, action, status from dba_registry_sqlpatch order by patch_id, action;


SQL> select to_char(action_time, 'DD-MON-YYYY  HH:MI:SS AM') patched_on, description, patch_id, action, status, con_id from cdb_registry_sqlpatch order by patch_id, action;		//PDB or CDB

SQL> select to_char(action_time, 'DD-MON-YYYY  HH:MI:SS AM') patched_on, description, patch_id, action, status from dba_registry_sqlpatch order by patch_id, action;			//Non-CDB



------PATCH------------------Patch-------------------PATCH------------------Patch-------------------PATCH----------




--------------------Flashback-------------------------------------------Flashback-------------------------------------------Flashback-----------------------


Flashback DEMO

set linesize 999
select * from v$flash_recovery_area_usage;
show parameter db_re
show parameter retent

select * from V$RESTORE_POINT;
select flashback_on from v$database;

SQL> alter database flashback off;
SQL> alter database flashback on;
SQL> create restore point testexposureGRP guarantee flashback database;
SQL> drop restore point <name>;
SQL> drop restore point TESTEXPOSURE;
SQL> drop restore point TESTEXPOSUREGRP;
select * from V$RESTORE_POINT;
tail -111f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SET db_recovery_file_dest_size='1G' SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size='2G' SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size='4G' SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size='15G' SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size='500' SCOPE=BOTH;


#oracle: cd /u01/oracle/oradata/fragmt01/<SID>/flashback/
#oracle: cd /u01/oracle/oradata/fragmt01/<SID>/archivelog/
#oracle: df -h /u01/oracle/oradata/fragmt
#RMAN: CROSSCHECK


SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                           17                         0               1          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                        47.53                         0             146          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.


SQL> show parameter db_re

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/oracle/oradata/fragmt
db_recovery_file_dest_size           big integer 15G
db_recycle_cache_size                big integer 0



<hostname>  [/u01/oracle/oradata/fragmt/<SID>/flashback]
-rw-r----- 1 oracle oinstall 51M 2020-04-25 05:00 o1_mf_m1flashbackfilex_.flb
-rw-r----- 1 oracle oinstall 51M 2020-04-25 09:00 o1_mf_m2flashbackfilex_.flb
-rw-r----- 1 oracle oinstall 51M 2020-04-25 09:00 o1_mf_m2flashbackfilexx_.flb
-rw-r----- 1 oracle oinstall 51M 2020-04-25 09:46 o1_mf_m2flashbackfilex_.flb








--------------------Flashback-------------------------------------------Flashback-------------------------------------------Flashback-----------------------




------------start--stop-----------------start--stop-----------------start--stop-----


$ORACLE_HOME/bin/dbshut
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbstart $ORACLE_HOME
$ORACLE_HOME/bin/dbshut $ORACLE_HOME

#shutdown db
{ 
echo -e "shutdown immediate" | sqlplus / as sysdba
ps -ef | grep pmon | grep -v grep
}

#Start db 
{ 
echo -e "startup" | sqlplus / as sysdba
ps -ef | grep [p]mon 
}


------------start--stop-----------------start--stop-----------------start--stop-----