Reading List Element Under the Condition in Python

# Reading List Element Under the Condition in Two Ways

mylist=["ABC","DEF","DEFG","ABBC"]

for i in mylist:

    if i[0:2]=="AB":

        print(i)

    if i.startswith("DE"):

        print(i)

    

Oracle Database Concepts

#Oracle Database Knowledge and Concepts.  

 To check the password which is used during installation check the below file
[root@ol7-19 ~]# grep password  -A1 $ORACLE_HOME/install/response/db_install.rsp

SQL> show con_name
     or Run alter session set container=PDB;
      session altered
SQL>set markup csv on;
    
CON_NAME
------------------------------
CDB$ROOT
SQL> show PDBs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        MOUNTED

SQL> alter pluggable database ORCLPDB  open;

Pluggable database altered

SQL> show PDBs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO

------------------------------------------------------------------
To print the database services register in oracle listener.

[root@ol7-19 ~]#  lsnrctl status

[root@ol7-19 ~]#sqlplus sys/password@LOCALHOST:1521/pdb1 as sysdba

SQL>show con_name

To unlock a user:
-------------------
Alter User ${} Account unlock
Alter user hr  account unlock;

To set a password for a user
------------------------------
Alter user hr Identified by  password


Query HR Schema
-----------------------
SQL>set markup csv on;
SQL>select * from hr.employees;


#count employee and give raise
sqlplus hr/hrpass@192.168.1.15/ddb1<<EOF
select count(*) from employees;
update employee set slary=salary*2
commit
quit
EOF

To check fairewall status
# systemctl status firewalld

To stop fairewall status
# systemctl stop firewalld

To disable fairewall status
# systemctl disable firewalld


To configure a instance
----------------------
1) initialization parameter file is used to configure an instance.
2) location of the file is $ORACLE_HOME/dbs directory
3) We can change initalization parameter file with ALTER SYSTEM command
4) A SPFILE is created automatically when you create a CDB.
5) PFILE is a text parameter file contain parameter values in name/value pair
6) parameter are a) sessions b) transactions c) processes
7) SGA_TARGET  parameter (shared memory structure) data and control information
8) Alter System and Alter session command to change the parameter values in SQL plus
9) SQL>create pfile='/home/oracle/pfile_2021.conf' from spfile;
10) Query the view V$PARAMETER
11)select name,value,ises_modifiable,issys_modifiable,ispdb_modifiable from V$PARAMETER
    where name in (nls_date_format', sec_max_failed_login_attempts');
12) ALTER Session set NLS_DATE_FORMAT='mon dd yyyy';
13 ) ISSYS_MODIFIABLE column tell you when the system level parameter change is made by using
     ALTER System command
14) ISSYS_MODIFIABLE values are
     1) immediate
     2) Deffered
     3) False ( parameter with false are called static parameter)
15) Scope caluse
Alter system set SEC_MAX_Failed_login_Attempts=5  Scope=SPFILE;
16) We only change the parameter values with Alter System command wher the
    ISSYS_Modifiable is true in V$Parameter View.
17 SQL> show parameter pool; or select name,value from v$parameter where name like %pool%
18) View V$SPParameter contain information about server parameter file
19) View V$Parameter2 contain information about  parameter which has currently effect of the session
20) View V$System_Parameter2: contain information about the initialzation parameter which has currently in effect of instance

Starting up Oracle Instance
1) startup nomount
2) V$Database view  is not availabe in nomount mode of instance
SQL>select dbid,name,open_mode fro v$database; (give us database not mounted)
SQL>alter database open;
3) startup Mount
    a) associating old started instance with the new started instance
    b) Locatingf and opening all the control files specified in parameter file
    c) Reading the control files to obtain the name of the datafiles and redo log files
     
    Datafiles
      a) system01.dbf
      b) user01.dbf
      c) sysaux01.dbf
4) startup Opend
    a) Open the data file and redo log file as register in  control files
5) Alert Log file
   location can be determine using select * from V$diag_info; view
   location $Oracle_base/diag/rdbms/db_name/SID/trace/alert_<sid>.log
   create, alter, drop datbase and tablespaces  
------------------------------------------------
Start up the instance and compare the logs
------------------------------------------------
T1
oracle@oracle$ . oraenv
oracle@oracle$ echo $Oracle_base
/u01/app/oracle
oracle@oracle$ sqlplus / as sysdba
SQL>startup

T2
oracle@oracle$ tail $ORACLE_BASE/diag/rdbms/orcldb/orcl/trace/alter_orcl.log
 | tee $ORACLE_HOME/example_of_successful_start_up_operation.log
Working with PDBs.
----------------
SQL>show pdbs;
Opening and Closing PDB:
------------------------
Alter Pluggable database  PDB_Name Open;
Alter Pluggable database  PDB_Name Closed;
PDB have 3 Modes
1) R/W
2) ReadOnly
3) MIGRATE
4) MOUNTED (The PDB is shut down/closed)
When instance is started
1) inialization parameter file is read by process
2) path $ORACLE_HOME/dbs directory
3) need to set 5 or 30 or upto 300
Two type of parameter files
1) Server parameter file(SPFILE)
    An SPFILE is automatically created when you create a CDB.
    binary file
    can not be edited  manually
    need ALTER SYSTEM Command for changes in the file
2) Text parameter file (PFILE)
     a) db can not write to this file
     b) manual changes in file required
     c) db instance  restart is required for any change
     d) not downtime free
---------------------------
Diagnostic Repository
---------------------------
Trace file:- When a process detects an error it dumps or write the error information to trace file
and the data is store in ADR automatic diagnostic Reporsitory for traces,alter log, health reports of DBs
---------------------

ADRCI utility to

---------------------

oracle@oracle$ adrci
ADR base= /u01/app/oracle
adrci> show homes
adrci> set home diag/rdbms/orcldb/orcl
adrci> show alert  
ADR home= diag/rdbms/orcldb/orcl:
output the results to files: /tmp/alert_orcl_1.ado
adrci>select shortp_policy,longp_policy from ADR_CONTROL;
adrci>help to see the list of avaiable commands
adrci>show incident
adrci>show incident -mode detail -p " incident_id=123"  then report to oracle support

Note ADR has purging mechanism for  incidents and alert log / trace files
1) Time based retention (ShortP_Policy 720h=30day/Long_Policy 8760h=365days)
2) The Size-base retention for an ADR

Dictionary cache
--------------------------
Data Dictionary
---------------------------
 Is a collection of database tables,view,reference info about the DB and
its structures and its users
Note dynamic view started with V$view_name
1) SQL>Select * from Dictionary order by table_name;
2) Select con_id,dbid,name,open_mode,restricted,total_size from v$pdbs
3) Select owner,table_name from all_tables where owner='HR';
--List currently Login able to login
select username,account_status from dba_users where account_status='OPEN'
--List of table created in a Schema
select table_name,tablespace_name from user_tables;
-- List the name of sequences
select sequence_name,min_value,max_value,incremented by from all_sequences
where sequence_owner in ('MDSYS','XDB')
select * from Dictionary;
DESCRIBE dba_indexes

Note all data dictionary view is updated automatically for consistancy

CDBs_ and DBA_  view are seen by DBA
All_  and User_ view can be Query by everyone.
Quering Dynamic Performance view in Oracle
------------------------------------------
To check session from a specific machine on yesterday
--------------------------------------
select * from V$session where machine='ABC2' and logon_time>sysdate-1;

To  check file states of datafile or dba_data_files
---------------------------------------------------
select * from v$DataFiles

To Find out session and user who locked the database objects
----------------------------------------------------------
select sid,ctime from v$lock where block>0;
To find the SQL statment ( and their associate numberof executions) is the CPU time
is> than 100,00 microseconds
select sql_text,executions from v$sql where cup_time>100,000;
To see the list of backgroud processes
select * from V$BGPROCESS

Shutdown the Database Instance
-----------------------------
*uncommited changes rolled back
*Database buffer cache written to datafiles
*Resources released
*No instance recover on startup

shutdown
shutdown transactional
shutdown immediate

--------------------
Creating PDBs
Ways to find the location of PDBs
-----------------------------
$ grep dataLocation $ORACLE_HOME/install/response/db_install.rsp
oracale.install.db.config.starterdb.fileSystemStorage.dataLocation=/u01/app/oracle/product/19.0.0/db_1/mydbfiles

The parameter name DB_CREATE_FILE_DEST tell the location of oracleManaged datafile (OMF)
2) SQL>show parameter db_create_file_dest;
3rway to find the location of files is to query dba_data_files view
SQL>show con_name
Con_Name
CDB$ROOT
3) select  FILE_NAME from dba_data_files
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/undotbs01.dbf
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/users01.dbf

SQL>alter session set container=PDB1;
session altered
SQL> select FILE_NAME from dba_data_files
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1/undotbs01.dbf
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1/users01.dbf
[root@oracle] su oracle
[root@oracle]$ cd
[root@oracle root] . oraenv
ORACLE_SID= [oracle] ? orcl
[root@oracle root]$ export PS1='printf"\033[30m$ \033[47m"'

To create PDB from SEED we can use the following statement

CREATE PLUGGABLE DATABASE {} ADMIN USER {} IDENTIFIED BY {PWD} ROLES={dba}

SQL>CREATE PLUGGABLE DATABASE pdb2 ADMIN USER admin2 IDENTIFIED BY Oracle_2021 ROLES=(CONNECT) FILE_NAME_CONVERT=
('/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdbseed', '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb2')
[oracle@oracle]$sqlplus / as sysdba
[oracle@oracle]$ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdbseed/
[oracle@oracle]$ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb2/
contains same files

SQL> select PDB_ID,PDB_NAME,STATUS,CON_ID,CREATION_TIME from CDB_PDBS;
SQL>show pdbs;
con_id, con_name, Open_Mode, Restricted
2       PDB_SEED   READ ONLY  NO
3       PDB1       READ WRITE NO
4       PDB2       MOUNTED
SQL>alter pluggable database PDB2 open;
pluggable database altered

Select Tablespace_name,status,con_id from CDB_tablespaces
4 new tablespaces have been created from our PDB
1) SYSTEM,
2) SYSAUX
3)  UNDOTBS
4   TEMP
Clonning the Database
1) Hot Clonning
The Source PDB can be open in READ WRITE mode and remain operational during the cloning process
Requires ARCHIVELOG and local undo mode
2) Cold Clonning
The source PDB should be opened in Read ONLY mode
exercise creating PDB3 from PDB1
1) Open PDB1 in READ ONLY mode
we need to  
2) Alter pluggable database pdb1 open Read only; --> open database in Readonly mod
3) CREATE PLUGGABLE DATABASE pdb3 from pdb1 create_file_Dest ='/u01/app/oracle/product/19.0.0/db_1/mydbfiles';
pluggable database created
select name from V$datafiles; give the file location for new PDB datab
select name,con_id, guid from v$pdbs
name con_id, guid
PDB1  3     BB8906
PDB1  5     C704
Cloning Remote PDB
----------------------
SQL create public database link  LINK_PDB1 conect ot  hr identified by "hr" using
'(Description=(Address=(Protocal=TCP)(HOST=localhost)(PORT=1521)(CONNECT_DATA=
 (SERVICE_NAME=pdb1)))';

CREATE PLUGGABLE DATABASE pdb4 from pdb1@link_pdb1
Listener is running on the remote side
$lsnr status
SQL>alter session set db_create_file_dest='/u01/app/oracle/product/19.0.0/db_1/mydbfiles';
session altered.
On the remote side Grant the permission to create pluggable database
SQL> Grant create pluggable database to hr;
SQL> alter session set container=CDB$ROOT;
SQL>create public database link  LINK_PDB1 conect ot  hr identified by "hr" using
'(Description=(Address=(Protocal=TCP)(HOST=localhost)(PORT=1521)(CONNECT_DATA=
 (SERVICE_NAME=pdb1)))';
SQL>alter pluggable database PDB4 open;
SQL>show pdbs;
SQL> drop public database link LINK_PDB1;
SQL>CREATE pluggable database pdb1_ref_cln1 from pdb1@link_pdb1 refresh mode every 60 Minutes
SQL>CREATE pluggable database pdb1_ref_cln1 from pdb1@link_pdb1 parallel 2 standbys=NONE Refresh Mode Manual;
SQL>Alter pluggable database pdb1_ref_cln2 referesh Mode NONE;
SQL> select * from pdb_plug_in_violations where name='pdb1_ref_cln2';
To unplug a PDB
1)Close PDB
2) Generate an XML metadata file (.xml ext)
alter pluggable database pdb4 unplug into '/home/oracle/pdb4.xml';
3) Drop PDBj
4) Copy files to Remote System if it is needed
To plugging a PDB using 3 Methods
1) NOCOPY Method
Create pluggable database pdbx using '/home/oracle/pdb4.xml' NOCOPY TEMPFILE REUSE;
2) COPY Method
Create pluggable database pdbx using '/home/oracle/pdb4.xml' COPY FILE_NAME_CONVERT('pdb4_dir','pdb5_dir');
3) AS CLONE or MOVE/COPY Method
Create pluggable database pdbx using '/home/oracle/pdb4.xml' MOVE;
select * from gV$session s  join gv$pdbs p
on  p.con_id=s.con_id  and p.inst_id=s.inst_id
Oracle Net
Listener
 listener is a process that runs onthe the server and listens for client connection requests
Database Service:
is logical representation of a database, which is the way that the base is presented to clients.
Oracle Net configuration files are
1) $ORACLE_HOME/network/admin/listener.ora
1) $ORACLE_HOME/network/admin/sqlnet.ora
[root@oracle] su oracle
[root@oracle]$ cd
[root@oracle root] . oraenv
ORACLE_SID= [oracle] ? orcl
[oracle@oracle]$sqlplus / as sysdba
[oracle@oracle]$ cat $ ORACLE_HOME/network/admin/listener.ora
LSNRCTL command is used to administer the listener.
[oracle@oracle]$lsnrctl start
[oracle@oracle] ps auxww | grep tnslsnr
Net Service Covers Firewall,Network Traffic Encryption and Listener Security.
Listener Registration Process
LREG
1)Name of the database services
2) Name of the database instance
3) Service handlers available for the instance
SQL> show parameter listener
Name, Type, Value,
forward_listener,string
listener_network,string
local_listener, string,   Listener_orcl
remote_listener string
SQL>cat $ORACLE_HOME/network/admin/tnsnames.ora
To check the list of all registered service
[oracle@oracle]$lsnrctl status
[oracle@oracle]$sqlplus hr/hr@pdb1_conn ( alias name defined in Listener)
------------------------------------------------------------------------
Data Access Control Covers
Privilege/Roles
Virtual private Database
User Creation
Creat User statement
Create user c##abc identified by pass123 Container=ALL default tablespace users Temporary Tablespace temp Account Unlock
User created in specified PDBs
alter session set container=pdb1;
show con_name
Create user c##abc identified by pass123
alter session set container=cdbs$Root;
show con_name
Select  p.pdb_id,

 p.pdb_name,
 u.username,
 u.common
from DBA_PDBs p , CDB_Users u
where  p.pdb_id=u.con_id
order by p.pdb_id;

GRANT create session to abcuser container=ALL
Creat ROLE  abcrole Container=ALL
------------------- create a role then assign grant priviliage to those Role as shown below---
Create Role HR_Clerk;
Grant create session to HR_Clerk
Grant select,update on Hr.employees To hr_clerk
Grant ALL on hr.jobs to Hr_clerk;
Grant hr_clerk to hr_mngr
Now Grant Roles to the users in the environment
tmp table space
Quota size on tablespace
select * from CDB_TEMP_FILES
Revoke select on hr.employee from hr_clerk
Drop Role VIP_SECURE;
Drop User TOM
Authetication of user by Operating System
--------------------------------------------
[oracle@oracle]$grep 'oracle' /etc/group
groupname:password:GroupID:user_list
oinstall :   x:      54321:           oracle

orapwORACLE_SID
[oracle@oracle]$ ls $ORACLE_HOME/dbs/orapworcl

SQL>show parameter REMOTE_LOGIN_PASSWORDFILE;
SQL>select username,sysdba from v$PWFILE_USERS;

profile of users
-------------------
vim $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
utlpwdmg.sql script changes the Default profile with Alter Profile command
To create profile
----------------------
Function of profile:-
------------------------------
Using Profile we can control following
---------------------------------------

1)CPU Resources( limit on per session )
2)Network and Memory Resources(idle time of concurrent Sessions)
3)Disc IO Resources(limit on total number of reads)

A)Create user joey identified by oracle
B)Grant create session to joey;
C)Select username,user_id,profile from cdb_user where username='JOEY';
D)Alter profile abc limit idle_time 30;
SQL>create profile abc_profile limit
SQL>select * from CDB_Profiles
SQL>create profile abc  limit idle_time 60;
SQL>select * from dba_TS_QuoTas

DATA Confidentiality Covers following
Data Maksing
DBMS_CRYPTO
DB Storage Security
---------------------------
Transparent Data Encription
Data Encryption/ Oracle Secure Backup Encription in following ways
  1)Data files and OS files goes to OSB---Encripted-- TapeDevices
                   2)Redo log files
                   3)Archived Redo log files
                   4)Backup files
is  manages by key automatically.
Expdp Data is encrypted to dump file
Data is import/Exported using Parameter
                  1) ENCRYPTION
                  2) ENCRYPTION_MODE
                  3) ENCRYPTION_PASSWORD
                  4)  ENCRYPTION_ALGORITHM
STEPS
Create Software Keysotre
Create a wallet Directory
mkdir -p $ORACLE_BASE/admin/orcldb/wallet/tde
Set the key store Location & Type
WALLET_ROOT="/u01/app/oracle/admin/orcldb/wallet"

Create a password-base Software KeyStore
create keystore '/u01/app/oracle/admin/orcldb/wallet/tde'
--------------------------------------------------------
[oracle@oracle]$mkdir -p $ORACLE_BASE/admin/orcldb/wallet/tde
[oracle@oracle]$sqlplus / as sysdba
To Create a pfile from SPFILE
SQL> create pfile='home/oracle/backup/init786.ora' from spfile;
SQL>alter system set WALLET_ROOT="/u01/app/oracle/admin/orcldb/wallet" scope=spfile;
SQL>shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL>startup
Total System Global Area
Fixed Size
Variable Size
Database Buffer
Reo Buffer
Database mounted.
Database Opened.
SQL>alter system set TDE_CONFIGURATION="KEYSTONE_CONFIGURATION=FILE" scope=spfile;
System altered.
SQL>shutdown immediate
Database closed.
Database dismounted.
SQL>startup
Total System Global Area
Fixed Size
Variable Size
Database Buffer
Reo Buffer
Database mounted.
Database Opened.
SQL>administer key management create keystore '/u01/app/oracle/admin/orcldb/wallet/tde'
keystore altered.
SQL> select status from v$encryption_wallet;
SQL>set markup csv on;
SQL>select WRL_TYPE,SRL_Parameter,status,con_id from v$encryption_wallet;
SQL>!ls -ltr /u01/app/oracle/admin/orcldb/wallet/tde
SQL>select Tablespace_name,encrypted from  dba_tablespaces;
Do regular copies of Keystore to some 2nd storages
-------------------------------------------------------------------------------------
TableSpace:-
A tablespace is an allocation of space in DB that can contail shema objects
When a table is created a segment is create to hold it data.
A samll file tablespace contains 1022 datafiles
each of which contains 4 million blocks
Collection of segments is called table space.
We can create 3 Type of table spaces
1) Permanent tablespaces
2) Temporary tablespaces
3) Undo tablespaces
SQL>select property_value from database_properties
    where property_name='Default_TBS_Type';
SmallFile.
Create tablespace orcl_cdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1M;
Tablespace created
SQL> alter session set contain=PDB1;
 session altered.
Create tablespace orcl_pdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1M;
create table t1( id int generated always as identity,
           column varchar2(32)
)tablespace orcl_pdb_users;
SQL>Begin
For counter IN 1..100 loop
    insert into t1(column1)
    values(sys_guid())
End loop;
End;
sql procedure successfully completed.
select tablespace_name,
 bytes /1024/1024 MB
 from
 dba_free_space
where tablespace_name='ORCL_PDB_USERS';
TableSpaceName,"MB"
ORCL_PDB_USERS, 1.2
We can increase the size of ORCL_PDB_USERS01.dbf file from 1MB to 10MB using following command
SQL>Alter database datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' resize 10m;
SQL>Create tablespace orcl_pdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1M
AUTOEXTEND ON NEXT 10M MAXSIZE 3G OR UNLIMITED
Create BigFILE tablespace orcl_pdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1G;
select tablespace_name,
 status, Bigfile
 from
 dba_free_space
Create tablespace orcl_pdb_encrypt
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf'
size 100M
Encryption using '3DES168' --,AES128,AES192
default storage (encrypt)
view related to tablespace are
1) cdb_tablespaces
2) dba_tablespaces
3) v$tablespace
data files information will in following view
Cdb_data_files
dba_data_files
V$datafiles
Temp file information
1)cdb_temp_files
2) dba_temp_files
3) v$tempfile
Alter TableSpace
Increase/Decrease File Size
Alter database datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' resize 10m;
Add Data Files to existing Table Space
Alter tablespace ORCL_PDB_USERS
 datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users02.dbf' resize 10m;
Alter tablespace ORCL_PDB_USERS
 datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users02.dbf' resize 10m;
 autoextend on
next 512K
Maxsize 2G
--To check the files associate to tablespace ORCL_PDB_USERS
select tablespace_name,file_name, bytes /1024 /1024 MB from dba_data_files

Change Satee of Table space
Alter tablespace ORCL_PDB_USERS Read Only;
Online/offline
Alter tablespace ORCL_PDB_USERS Read Write;
Alter tablespace ORCL_PDB_USERS offline Normal;
Move Data Files
Take TS offline
Move Files
 mkdir /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1_extra
 mv    /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users02.dbf /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1_extra/orcl_pdb1_users02.dbf
Alter TableSpace Rename DataFiles
Take TS onlin
----------------------------------------------------------------------------------
Oracle Managed Files
Parameters                    Description
DB_CREATE_FILE_DEST          (define the location of the default file system directory for data files&temporary files)
DB_CREATE_ONLINE_LOG_DEST    Define the location for redo log files and control file creation
DB_RECOVERY_FILE_DEST        Gives the default location for the fast recovery area
SQL>alter system set db_create_file_dest='/u01/app/oracle/product/19.0.0/db_1/mydbfiles' scope=Both
SQL CREATE tablespace orcl_CDB_OK
-------------------------------------
Enable ARCHIVELOG mode
---------------------------------------
SQL>archive log list;
Database log mode                 No Archive Mode
Automatic archival                Disabled
Archive destination               USE_DB_RECOVERY_FILE_DEST
Oldest Online log sequence         114
Current log sequence               116
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
SQL>select log_mode from v$database;
Log_mode=ARCHIVELoG
SQL>select flashback_on from v$database
Flashback_on
----
NO
SQL>alter database flashback on;
SQL>show con_name;
con_name
----------
pdb
Show user
User is HrS
SQL>create table us_states(id number(8) not null, name varchar2(50) not null);
    table created
SQL>Insert into us_states vlaues(101,'SANDI');
commit;
wait for some time ///Run FLASHBACK Query to get uno data by specifing SCN
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual
to_char(sysdate,'DD
--------------------
select * from hr.us_states as of timestamp to_timestamp('15-10-2021 06:15:00','dd-mm-yyyy hh24:mi:ss')
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ddate, dbms_flashback.get_system_change_number scn from dual;
DDATE                   SCN
----------              -----
14-10-2022 05:30:41      6664607

select scn_to timestamp(6664607) ddate, timestamp_to_scn(to_timestamp('15-10-2021 06:15:00','dd-mm-yyyy hh24:mi:ss')) scn  from dual;
UNDO management
-------------
alter session set container=pdb1;
show parameter undo_management;
select tablespace_name,content,status from dba_tablespaces where contents='UNDO'
to view all the datafile used by undoTBS1
SQL>select file_name from dba_data_files where tablespace_name=undotbs1' order by 1;
DBA_UNDO_EXTENTS
select * from V$UNDOSTAT
A-------------------------------------------------------------------------------------------------------------

(FAST Recovery Area) Backup_Recovery Parameter

db_recovery_file_dest_

db_recovery_file_dest_size (used to control the max of space to store backup file in side FRA)

db_recovery_file_dest-control (the location of FRA)

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,10G

To increase the size of FRA

----------------------------

SQL>Alter System set db_recovery_file_dest_size=15G SCOPE=BOTH 

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,15G

To monitor FRA you can query two Views

V$Recovery_File_Dest and V$Recovery_Area_Usage

Other thing We can do the following use

Multiplex control files, traced back ups and may be used to view the content of control files.

Control files can be back up to a trace file

Lets create a backup of control File,

to a trace file to see the information store in a control files

SQL>show con_name

CDB_$Root

SQL>alter database backup controlfile to trace;

Database altered

Step Now check the record in the alert log ,

     that point to the file name on the O.C for our backup

oracle@racle]tail /u01/app/oracle/diag/rdbms/orcldb/orcl/trace/alert_orcl.log

backup written to control as follows

/u01/app/oracle/diag/rdbms/orcldb/orcl/trace/orcl_ora_14147.trc

Current setting of our control files

--------------------------------------

show parameter control files

name,         type,            value

control files,string,/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl,

                     /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl

Creating 3rd copy of control file for your self 

------------------------------------------------

Alter system set control_file='/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl' SCOPE=SPFILE;

System altered.

SQL>shutdown immediate.

database closed

database dismounted.

oracle instance shut down.

[oracle@oracle]cp 

Then copy an existing control file to the another location by your selection.

[oracle@oracle~]cp /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl

[oracle@oracle~]ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control0*

SQL>startup 

Next step to multiplex files and redo log group

---------------------------------------------------

Instance/Disk failure is recoverdusing redo log information is used to roll data file forward

Redo log group are made up of redo log files

Redo log is duplicate to group at least two file per group

Join V$log and V$logfile to see all redo log member and their status

--------------------------------------------------------------------

select b.group#,a.status, b.status,b.member from V$log , V$logfile where a.group#=b.group# order by 1,2

Group#,status,status,member

Add addition member to each group

--------------------------------

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1;

exit;

[oracle@oracle~]mkdir /u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk

[oracle@oracle~]sqlplus / as sysdba

SQL>set markup csv on;

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1

SQL>add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo02.log'

to  group 2

Next Step is to enable archiving of the redo logs

------------------------------------------------

Note Every single 

1)Insert,

2)delete

3)update statemetn is recorded in the redo logs files.

------------------------------------------------------

Redo log Switch

------------------------------------------------------

When Oracle finished writing to  a redo log grop that is group number 1,

It performs a redo log Switch.

After that redo log group 1 is archiving to redo log backup to some external storage for recovery purpose.

----------

Note:-We rely on the DB archive redo logs that contains/records the history of all committed DB Transactions

---

SQL>archive log list; 

----------

(FAST Recovery Area) Backup_Recovery Parameter

db_recovery_file_dest_

db_recovery_file_dest_size (used to control the max of space to store backup file in side FRA)

db_recovery_file_dest-control (the location of FRA)

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,10G

To increase the size of FRA

----------------------------  FRA------------------------------------------------------                

SQL>Alter System set db_recovery_file_dest_size=15G SCOPE=BOTH 

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,15G

To monitor FRA you can query two Views

V$Recovery_File_Dest and V$Recovery_Area_Usage

Other thing We can do the following use

Multiplex control files, traced back ups and may be used to view the content of control files.

Control files can be back up to a trace file

Lets create a backup of control File,

to a trace file to see the information store in a control files

SQL>show con_name

CDB_$Root

SQL>alter database backup controlfile to trace;

Database altered

Step Now check the record in the alert log ,

     that point to the file name on the O.C for our backup

oracle@racle]tail /u01/app/oracle/diag/rdbms/orcldb/orcl/trace/alert_orcl.log

backup written to control as follows

/u01/app/oracle/diag/rdbms/orcldb/orcl/trace/orcl_ora_14147.trc

Current setting of our control files

--------------------------------------

show parameter control files

name,         type,            value

control files,string,/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl,

                     /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl

Creating 3rd copy of control file for your self 

------------------------------------------------

Alter system set control_file='/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl' SCOPE=SPFILE;

System altered.

SQL>shutdown immediate.

database closed

database dismounted.

oracle instance shut down.

[oracle@oracle]cp 

Then copy an existing control file to the another location by your selection.

[oracle@oracle~]cp /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl

[oracle@oracle~]ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control0*

SQL>startup 

Next step to multiplex files and redo log group

---------------------------------------------------

Instance/Disk failure is recoverdusing redo log information is used to roll data file forward

Redo log group are made up of redo log files

Redo log is duplicate to group at least two file per group

Join V$log and V$logfile to see all redo log member and their status

--------------------------------------------------------------------

select b.group#,a.status, b.status,b.member from V$log , V$logfile where a.group#=b.group# order by 1,2

Group#,status,status,member

Add addition member to each group

--------------------------------

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1;

exit;

[oracle@oracle~]mkdir /u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk

[oracle@oracle~]sqlplus / as sysdba

SQL>set markup csv on;

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1

SQL>add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo02.log'

to  group 2

Next Step is to enable archiving of the redo logs

-------------------------------------------------

Note Every single 

1)Insert,

2)delete

3)update statemetn is recorded in the redo logs files.

------------------------------------------------------

Redo log Switch

------------------------------------------------------

When Oracle finished writing to  a redo log grop that is group number 1,

It performs a redo log Switch.

After that redo log group 1 is archiving to redo log backup to some external storage for recovery purpose.

----------

Note:-We rely on the DB archive redo logs that contains/records the history of all committed DB Transactions

---

SQL>archive log list; 

----------

RMAN is the primary backup technology of oracle.

that manages 

1)Backup

2)Restore

3)Recovery process

4)RMAN can store backup on disk for Quick recovery.

5)RMAN can do incremental,full backup and whole complete database backup

6)RMAN support Full backup(copy of all Data Blocks),incremental backup(only copy data block of DB which is changed compared to previous Backup)

7)RMAN have built in configuration options.

RMAN>

Backup incremental level 0 database;

Backup incremental level 1 database

Backup incremental level 1 CUMULATIVE database

DB 

Data Files  

Control Files 

Archived Log goes into the --->RMAN-->Backup set/Backup Pieces

Image copies are exact byte for the byte copies of files and recorded in RMAN respository.

RMAN creates image copies only on disk.

The file protected by RMAN includes to reconstruct the database.

1)data files

2) control files

3)Server parameter files

4)Archived redo log files

Logical backup includes

exporting database object such as  

1) table 

2)table space 

which become supplyment to physical backups for database.

[root@oracl] su oracle

[oracle@oracl]$ cd

[oracle@oracl]$ rman target=/

Recovery Manager: Release ----

Connected to target db:abcDB

RMAN>show all; --to see the available configuration option of RMAN

configure backup retention policy to redundancy 1

RMAN>show retention policy;

RMAN configuration parameters for db with unique_name abcDB are

configure backup retention policy to redundancy 1; #default

RMAN>configure retention policy to recovery window of 7 days

new RMAN configuration parameters:

RMAN>show retention policy;

 RMAN>configure retention policy clear

---------------------------------------------

To take full backup of the database

---------------------------------------------

[root@oracl] su oracle

[oracle@oracl]$ cd

[oracle@oracl]$ rman target=/

Recovery Manager: Release ----

Connected to target db:abcDB

RMAN>backup database plus archivelog;

starting backup at date-------

using target database control file instead of recovery catalog

allocated channel:ora_disk_1

RMAN>backup database root;

RMAN>backup pluggable database pdb;

[oracle@oracl]$ lsnrctl status

-------------

Directly connect to pdb database through RMAN and do backup

----------------

[oracle@oracl]$rman traget=sys / or_2022@localhost/pdb

RMAN> backup database;

or

RMAN> backup database tag 'pdb_test_Bckup'

-----

Incremental backups

------

RMAN> backup incremental level 0                database tag 'cdb_incm_level0' plus archivelog;

RMAN> backup incremental level 1                database tag 'cdb_incm_level1' plus archivelog;

RMAN> backup incremental level 1  cumulative     database tag 'cdb_incm_level1' plus archivelog;

RMAN> list backup

RMAN> list backup summary;

RMAN> list backup of database pdb2

RMAN> list backup of datafile 10

RMAN> list backup of datafile 'u01/app/oracle/product/19.0.0/db_1/yourdbfiles/orcDB/pdb2/system01.dbf'(show only the image copy of datafile)

RMAN> list backup by file;

RMAN> list archivelog all;

RMAN> list backup of controlfile

---------------------------------

RMAN Views

---------------------------------

V$RMAN_Backup_job_details

V$RMAN_status

V$backup_set

V$pdbs(contain all PDEs in CDB)