DBA classic interview questions (2)

 
========================================================== ==========================================================
 
1: how to classify backups
 
Logical backup: exp/imp
 
Physical backup:
 
RMAN backup
 
Full backup/incremental backup (accumulation/difference)
 
Hot backup: alter tablespace begin/end backup;
 
Cold backup: offline backup (database shutdown)
 
 
 
2: What does archive mean?
 
Archive logs: Archive logs (archived redo logs) must be created when Oracle wants to archive all online log File groups ).
 
It is useful for database backup and recovery:
 
Database backup and online and archive log files Ensure that all submitted items can be recovered in the case of operating system and disk faults.
 
If the archive log is permanently saved when the database is opened and used by the normal system, it can be used online backup.
 
　　
 
Databases can run in NOARCHIVELOG or ARCHIVELOG modes.
 
Online logs cannot be archived when used in NOARCHIVELOG mode. If the database runs in ARCHIVELOG mode, online logs can be archived.
 
 
 
3: If a table is dropped at 10:30:00, how can we restore it with comprehensive archiving and backup?
 
Manually copy back all backup data files
 
SQL> startup mount;
 
SQL> alter database recover automatic until time '2017-08-04: 10: 30: 00 ';
 
SQL> alter database open resetlogs;
 
 
 
4: What is rman and what are its characteristics?
 
RMAN is an important DBA tool used to back up, restore, and restore oracle databases. RMAN can be used to back up and restore database files, archive logs, control files, and system parameter files, it can also be used to perform full or incomplete database recovery.
 
RMAN has three different user interfaces: command line, GUI (Backup manager integrated in OEM), and API (used for integration into third-party backup software ).
 
It has the following features:
 
1) functions are similar to physical backup, but N times more powerful than physical backup;
 
2) Empty blocks can be compressed;
 
3) increment can be achieved at the block level;
 
4) The backup output can be packaged into a backup set, or the backup set can be split by fixed size;
 
5) the backup and recovery process can be automatically managed;
 
6) scripts can be used (in Recovery catalog)
 
7) Monitoring of Bad blocks
 
 
 
5: Features of standby
 
Standby database: a high availability database solution launched by ORACLE. Data synchronization between the master and slave nodes ensures data synchronization, backup of the slave node as the master node
 
It enables fast switchover and catastrophic recovery. It also supports physical and logical backup servers starting from 920.
 
 
 
The three data protection modes in 9i are:
 
1) maximize protection: maximum data PROTECTION and no data difference, LGWR will be transferred to the slave node at the same time,
 
The slave node must also completely receive log data before the master node confirms the transaction. If the network is poor, LGWR cannot transmit data, which may cause serious performance problems and cause the master node to go DOWN.
 
2) maximize availability: No data loss mode. Data divergence is allowed and asynchronous transmission is allowed.
 
Normally, the instance runs in the maximum protection mode. When the network between the master node and the slave node is disconnected or the connection is abnormal, the instance is automatically switched to the maximum performance mode,
 
The operation on the master node can continue. Poor network performance has a greater impact.
 
3) maximize performance: This mode can be said to be an alternative server mode inherited from 8i, Which is asynchronously transmitted,
 
No data synchronization check, data may be lost, but the maximum performance of the master node can be obtained. 9i is maximize performance by default when configuring data guard.
 
 
 
6: How do you design a backup policy for a system with a short recovery time (Database 50 GB, archiving 5 GB every day)
 
Rman: level 1, 0, per month
 
Level 1 per weekend/Wednesday
 
Other levels 2 per day
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Iv. System Management
 
========================================================== ==========================================================
 
1: For a system with system performance, describe your diagnosis and processing ideas.
 
1. Perform statspack to collect system information
 
Measure the test taker's understanding about the system.
 
Determine if there is any inappropriate parameter settings
 
View top 5 events
 
View top SQL
 
 
 
2. Check v $ system_event/v $ session_event/v $ session_wait.
 
Determine the required resources (db file sequential read) from v $ system_event.
 
Study v $ session_event in depth to determine the session involved in the wait event
 
Determine detailed resource contention from v $ session_wait (p1-p3 value: file_id/block_id/blocks, etc)
 
3. Use the v $ SQL/v $ sqltext/v $ sqlarea table to determine the SQL statements with large disk_reads and (buffer_gets/executions) values.
 
 
 
2: lists several methods for diagnosing IO, CPU, and performance conditions.
 
Top/vmstat
 
Statspack
 
SQL _trace/tkprof
 
Check v $ system_event/v $ session_event/v $ session_wait
 
Check v $ sqlarea (disk_reads or buffer_gets/executions large SQL)
 
 
 
3: What do you know about statspack?
 
StapSpack is a software package provided by Oracle to collect database running performance indicators. This software package has been significantly enhanced in 9i and 10g since 8i. The number of auxiliary tables for the software package (tables that store related parameters and collected performance indicators) has increased from 25 to 43. Collection-level parameters increase from 3 (0, 5, 10) to 5 (0, 5, 6, 7, 10) by analyzing collected performance indicators, the database administrator can learn in detail the current running status of the database, and optimize and adjust the database instance, wait event, and SQL. Snapshot collected by statspack can be used to generate statistical trend charts for various performance indicators of the database.
 
 
 
4: If the system needs to create an index on a large table, you will consider those factors and how to minimize the impact on the application.
 
When the system is relatively idle;
 
Nologging option (nologging cannot be used if there is a consumer uard );
 
Large sort_area_size or pga_aggregate_target is large;
 
 
 
5: What do you know about raid1 + 0 and raid5?
 
RAID 10 (or RAID 1 + 0) is different from RAID 0 + 1. It uses a hard drive to form a RAID 1 array and then a RAID 0 array between RAID 1 arrays. RAID 10 has the same good data transmission performance as RAID 0 + 1, but has higher reliability than RAID 0 + 1. The actual capacity of the RAID 10 array is M × n/2, and the disk utilization is 50%. RAID 10 also requires at least four hard drives, which is expensive. The reliability of RAID 10 is the same as that of RAID 1,