#DBAchallenge -3
Interesting Question 1...
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
Interesting Question 2...
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.
Interesting Question 3...
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
Interesting Question 4...
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.
Interesting Question 5...
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*
No comments:
Post a Comment