#DBAchallenge -3
How
many RMAN channels you will allocate for a database backup?
What
all resource you are going to check before allocating the RMAN channels?
To Make
it simple...
If I
have DB with 4 CPU assigned each CPU has single thread then how many RMAN
channel you will allocate? Do you consider PGA and SGA before allocating
channels?
Answer 1…
--- RMAN
channels should be allocated purely based on number of CPUs, No need to
consider SGA/PGA sizing while allocating the channel because automatic memory
management will take care of assigning the adequate large pool memory during
the RMAN backups.
--- In case if
you have 4 CPUs assigned for your database then allocate 2 channels for RMAN
and leave the other 2 channels for other your database and ASM operations.
--- Remember
in case if you undersize your SGA and PGA which is not only impacting on your
RMAN backups but also your database performance, make sure assign the adequate
memory (SGA/PGA) allocation for your database.
Case1:
--- I have 16
CPUs on my server and out of 16 CPUs I have assigned 8CPUs (Using CPU_Caging)
for my database in that case I can assign max 6 channels to RMAN backup and I
will leave 1 or 2 buffer CPUs for my database operation.
Case2:
--- I have 16
CPUs on my server and default my database will take all 16 CPUs in my database
if you don’t use CPU caging in that case I can allocate 12 channels to RMAN
backup and I will remaining 4 channels for my database, ASM and OS operations.
Case3:
--- I have 16
CPUs on my server and in case if you are running multiple databases then its
little challenging since you have to consider when the backups are running for
all these DBs, how many CPUs are assigning inside my database and I need to
assign some buffer CPUs for my database, ASM and OS operations.
SQL>show
parameter cpu_count
[root@oraclelab1]#
lscpu |grep "CPU(s):"
https://www.oracle.com/technetwork/database/performance/instance-caging-wp-166854.pdf
I got a
downtime
Today
for DB1,
Tomorrow
for DB2.
I want
to patch these both the databases,
Remember
both the DBs are running on a same server.
How to
accomplish this task?
Answer 2…
--- We can deal
this situation in different scenarios
Case1:
--- In case both
the DBs are running from different Oracle Home then no need to worry, you can
patch these databases independently irrespective of whether these are RAC
database or standalone databases.
Case2:
--- In case both
the DBs are running from same Oracle Home and in case both are RAC databases
then you no need to worry about downtime you can do rolling patching today or
tomorrow anytime which does not need a downtime.
Case3:
--- In case both
the DBs are running from same Oracle Home and in this situation, we can clone
Oracle Home and patch that cloned oracle Home and move 1 database on cloned
Oracle Home and run that database with datapatch and we can patch the other
Oracle Home & respective database on tomorrow’s downtime
Note:
Point to
considering during this clone process is In case both the DBs are using the
same listener then you have to create a new listener for that cloned Oracle
Home and you need to register that database with that new listener.
I have
a server with 10GB RAM 4 CPU/cores with single thread for each core.
I want
to create a new database in it,
How
much SGA, I can allocate?
How
much PGA, I can allocate?
How
much CPU/Core I can allocate for the new DB?
Answer 3…
--- Default 40%
of RAM will be allocated to SGA
Cal: 40* Total RAM = SGA
--- PGA
allocation depends on number of process you defined on database
Cal: 3MB * Number of Process = PGA
--- We no need to
assign any CPUs for my database default it will take all the CPUs available on
my server. We can limit the number of CPUs for my database using parameter
called cpu_count inside my database.
--- This CPUs
allocation inside my database depends on How many DBs you are running and how
critical are these DBs.
[oracle@oraclelab1]$
grep -i SGA $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc
<percentageMemTOSGA>40</percentageMemTOSGA>
SGA:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:30011178429375
PGA:
https://oracle-base.com/articles/12c/pga_aggregate_limit_12cR1
Seems
this is commonly asked question in most of the interview...
My
query is running fine till yesterday but all of sudden today it is taking more
time?
How
will you troubleshoot this situation?
Answer 5…
We should ask
cross question:
--- Is only this
query is running slow or all other queries running slow?
--- all queries are having issue then its
DB related issue if not then its only this query has issue
--- Only this user
facing this issue or all other facing this issue.
--- In case only this user having issue
then it might be this user level permission or access issue otherwise this is
query issue
--- We drilled
down the issue whether it is at DB level issue or this query level issue
--- In case of if its DB level issue then
we can check load on DB, check any backups or expdp/impdp running on server,
load on Server, IO stats, memory stats, vmstats, AWR report and other OS
analysis.
--- In case of its query level issue then
we can troubleshoot further like
--- table_stats on tables involved in this
query (Gather stats)
--- Index status on these tables (Rebuild
Index)
--- Fragmentation on these tables (Table
Move or Table shrink)
--- Check for execution plan and verify any
change in the execution plan
--- In case change in plan then use SQL
profile to peg the best execution plan.
Password file... 📂
What is
the use of password file in database?
What
are the use cases of password file?
What is
the impact in case if I lost or corrupt password file?
Answer 5…
--- Password file
is needed for remote administration activities from your sysdba user.
--- If the DBA
wants to start up an Oracle or ASM instance there must be a way for Oracle to
authenticate the DBA. Obviously, DBA password cannot be stored in the database,
because Oracle cannot access the database before the instance is started up.
Therefore, the authentication of the DBA must happen outside of the database.
User Cases:
--- Remote
administration activities
--- DR or Standby
sync
--- In case
passwordfile corrupt or lost then there is no impact on running database, you
can recreate the passwordfile online using orapdw utility
--- In case of DR
database if passwordfile corrupt or lost then DR will goes out of sync.
SQL> desc
v$pwfile_users;
[oracle@oraclelab1
dbs]$ ls -ltr $ORACLE_HOME/dbs/orapw*
Regards,
Mallik
