不想錯過任何事？

通過認證考試的技巧

最新考試新聞和折扣資訊

由我們的專家策劃和更新

是的，請向我發送時事通訊

查看其他面試題
1
參考答案
ALL_OBJECTS
2
參考答案
To increase redo log size, recreate redo log groups with larger size (can't resize existing online redo logs). Steps: - Add new redo log group with desired size: - Switch logfile and drop old group(s) when they are inactive: - Alternatively, create new groups for all member groups and then drop old ones, ensuring at least two or three groups are available. - For RAC, add members on each node or use ASM: Notes: - Plan size based on redo generation rate and checkpoint frequency. - Do this in low activity or maintenance window if possible.
職涯加速

考取認證，讓履歷脫穎而出。

數據分析顯示，持有 IT 認證的從業者年薪平均比求職者高出 26%。在 SPOTO，您可以同時備考認證與準備面試，加速職涯成長。

1 100% 通過率
2 2 週題庫練習
3 通過認證考試
全球認可 100 萬+ 已認證 邊工作邊學習
制定學習計劃
3
參考答案
Zoom out to the big picture. Tell me why you're in this chair right now. Is this some sort of career plan? Have they heard good things about the environment from another employee? Are they looking for an employee discount on products? (It doesn't work at wine & spirits companies, just for the record.) Or are they just desperate? Nothing wrong with desperation, but remember that desperate employees are desperate for something, and you'd better find out what it is. If they're desperate for money, it's going to color the judgements they make.
4
參考答案
You can create DB LINK and query from the remote table on remote database.
5
參考答案
Applicants may be trying to improve technical and/or soft skills to enhance their performance and complete projects more easily. For instance, they may focus on improving their communication or data protection and GDPR knowledge. Check with your applicants to see which methods they are using to improve their skills.
6
參考答案
No impact on the Data Guard setup, but FSFO (automatic failover) is disabled until the observer is restored
7
參考答案
The mysqlbinlog utility is used to display the contents of binary log files and to apply binary log events to other servers.
8
參考答案
A clustered index physically reorders the table data and stores it in sorted order based on the index key. Each table can only have one clustered index because the data can only be physically sorted in one way. I typically use clustered indexes on primary keys or columns that are frequently used in range queries. Non-clustered indexes create a separate structure that points back to the actual data rows. You can have multiple non-clustered indexes per table. I use these for frequently searched columns that aren't the primary key, like email addresses or last names in a user table. In my last role, I added a non-clustered index on a customer lookup field that reduced query time from 3 seconds to 200 milliseconds.
9
參考答案
Look for: Migration planning and execution skills.
10
參考答案
The model database acts as the blueprint for all newly created user databases in SQL Server. Any structural or configuration changes made to the model database will be inherited by every subsequent new database. It's also critical because the tempdb system database is recreated from the model every time the SQL Server instance restarts, ensuring consistent temporary storage behavior.
11
參考答案
The table is the first level of the physical unit in the database. Oracle uses tables of a database to store data into rows and columns. The table is the first level of the physical unit in the database.
12
參考答案
SQL Server Management Studio T SQL script Tech Brother client SQLmitter
13
參考答案
FAL_SERVER: Specifies the Oracle Net service name of the primary database from which the standby requests missing archive logs. FAL_CLIENT: Specifies the standby database's service name (deprecated in recent versions). Example: fal_server='primdb', fal_client='stdbydb'. Used for archive gap resolution on the standby.
14
參考答案
Securing SQL Server 2022 involves multiple layers of security. At the network level, ensuring the database engine is not exposed directly to the internet and using firewall rules to limit access are crucial steps. Authentication should be managed via Windows authentication mode where possible for better security, complemented by strong password policies for SQL logins. Encrypting data at rest using Transparent Data Encryption (TDE) and securing data in transit with SSL/TLS are also recommended. Additionally, regularly applying updates and patches and conducting audits with SQL Server Audit or Extended Events to track and analyze activities are essential for maintaining security.
15
參考答案
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
16
參考答案
Yes, enabling BCT improves incremental backup efficiency for the standby database.
17
參考答案
Database sharding involves partitioning a large database into smaller, more manageable pieces called shards, which can be distributed across multiple servers. This approach is necessary when dealing with massive datasets to improve performance and scalability, especially in high-traffic applications.
18
參考答案
I implement role-based access control (RBAC) to ensure users have the appropriate permissions based on their roles. Regular audits and updates of user access levels help maintain security, and I use monitoring tools to detect and address any unauthorized access promptly.
19
參考答案
SELECT f.group_number, f.file_number, bytes, space, space/(1024*1024) AS "Size_MB", a.name FROM v$asm_file f, v$asm_alias a;
20
參考答案
Look for: Practical experience with security implementations.
21
參考答案
The model database consists of the template for all databases created in the SQL system. If the initial model database is modified, all subsequent databases created in the systems will reflect the changes. However, databases created earlier will not.
22
參考答案
SQL injection is a technique used to extract user data by injecting web page inputs as statements through SQL commands. Basically, malicious users can use these instructions to manipulate the application's web server. - SQL injection is a code injection technique that can compromise your database. - SQL injection is one of the most common web hacking techniques. - SQL injection is the injection of malicious code into SQL statements via web page input.
23
參考答案
You need to set db_recovery_file_dest and db_recovery_file_dest_size parameter on standby and then enable flashback.
24
參考答案
Soft Parsing: Reuses previously parsed SQL from shared pool. Hard Parsing: New parse required; more CPU/resource-intensive.
25
參考答案
Start by identifying core entities: Users, Products, Orders, Order Items, Categories, Inventory, Payments. Establish relationships—Users have many Orders, Orders have many Order Items, Products belong to Categories. Consider normalization: Separate user addresses into a separate table if users can have multiple addresses. Think about performance: You might denormalize product ratings or create summary tables for reporting. Plan for scalability: Consider partitioning large tables like Orders by date, indexing strategy for common queries (product searches, order lookups), and potential sharding strategies as the system grows.
26
參考答案
To find the default temporary tablespace for your Oracle database, you can query the DBA_USERS view or theV$PARAMETER view.- Using the DBA_USERS view:This will show the default temporary tablespace for each user, including the database default:The temporary_tablespace column will show the default temporary tablespace for the user. The database default is typically used unless overridden by a user-specific setting. - Using the V$PARAMETER view:You can also query theV$PARAMETER view to find the overall default temporary tablespace for the database: This query will return the name of the default temporary tablespace for the entire database. - Using the -
27
參考答案
ROWS=N.
28
參考答案
Use the CREATE USER, ALTER USER, and GRANT statements to manage user accounts and privileges.
29
參考答案
SQL databases are relational, structured, and use predefined schemas, suitable for complex queries and transactions. NoSQL databases are non-relational, flexible, and handle unstructured data, ideal for scalability and big data applications. I use SQL for structured data and NoSQL for high-velocity or varied data.
30
參考答案
INNER JOIN The INNER JOIN keyword selects all rows from both tables as long as the condition is satisfied. This keyword will create the result set by combining all rows from both the tables where the condition satisfies i.e. the value of the common field will be the same. Syntax: SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column; table1: First table. table2: Second table matching_column: Column common to both the tables. Note: We can also write JOIN instead of INNER JOIN. JOIN is the same as INNER JOIN. LEFT JOIN This join returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN. Syntax: SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column; table1: First table. table2: Second table matching_column: Column common to both the tables. Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same.
31
參考答案
An SQL Database Administrator (SQL DBA) is responsible for installing, maintaining, securing, and managing SQL databases. They handle backups, recovery, performance tuning, user access, and troubleshooting database issues. SQL DBAs make sure that databases stay reliable, fast, and available to the applications and users that depend on them every day.
32
參考答案
A mechanism in Oracle RAC to share data blocks between instances using the interconnect, avoiding disk I/O.
33
參考答案
LMT (Locally Managed Tablespaces) store extent allocation information within the tablespace itself, whereas DMT (Dictionary Managed Tablespaces) rely on the data dictionary for extent tracking. LMT is preferred due to improved performance and reduced contention.
34
參考答案
No, this will grant unlimited quota on user default tablespace.
35
參考答案
Use a profile with PASSWORD_LIFE_TIME = 30 (or 1 month). Create user with that profile. Example: Notes: - PASSWORD_LIFE_TIME accepts days (30) or UNLIMITED . You can also set PASSWORD_GRACE_TIME, FAILED_LOGIN_ATTEMPTS, PASSWORD_REUSE_TIME, etc. - Use ALTER PROFILE monthly_pwd LIMIT PASSWORD_LIFE_TIME 30; to change for existing profiles. - For calendar months, you must approximate (30 days) — Oracle stores numeric days.
36
參考答案
Restore from backup if available. Otherwise, reapply the patches to regenerate the necessary files.
37
參考答案
No, renaming a database will not change its physical file names. You need to manually perform additional steps to change the database physical file names.
38
參考答案
Example parameters:shared_pool_size ,db_cache_size ,java_pool_size ,large_pool_size ,log_buffer . To automatically manage memory pools, setSGA_TARGET andSGA_MAX_SIZE .
39
參考答案
The recovery method depends on the status of the lost or corrupted redo log file. The appropriate steps should be taken based on its availability and whether it was archived.
40
參考答案
Database Security means keeping sensitive information safe and preventing the loss of data. The security of the database is controlled by Database Administrator (DBA). The following are the main control measures used to provide the security of data in databases: - Authentication - Access control - Inference control - Flow control - Database Security applying Statistical Method - Encryption
41
參考答案
Check the SCN numbers between primary and standby databases.Identify missing archive logs and manually apply them. Use RMAN to fetch missing logs and apply them to synchronize standby.
42
參考答案
Certainly. The five fundamental database objects include: Tables, which are where the actual data is stored; Views, which are virtual tables based on query results; Indexes, used to speed up data retrieval; Stored Procedures, which are pre-compiled SQL code blocks for specific tasks; and Triggers, which automatically execute in response to data changes.
43
參考答案
Tactics to make RMAN backups faster: - Use parallel channels: - Use compression (may reduce I/O but increases CPU). Use BACKUP AS COMPRESSED BACKUPSET . - Use incremental backups (reduce full backups), cumulative incremental. - Use multiplexing to write multiple datafiles to a single backup set. - Use faster disks / SSD or dedicated backup network. - Use BACKUP ... PLUS ARCHIVELOG efficiently and skip unnecessary files. - Use MAXOPENFILES and tunedBT buffer sizes as appropriate. - Use RMAN block change tracking for incremental backups (ALTER DATABASE ENABLE BLOCK CHANGE TRACKING ). - Exclude large but nonessential objects or backup particular tablespaces. - Use Backup-as-copy for very large files (on fast storage). - Backup to multiple locations simultaneously (two channels to two targets). - Tune retention policies and avoid unnecessary duplicates. - Use vendor appliances (Data Domain, ExaGrid) integrated with RMAN. - Network: ensure dedicated network for backups with adequate bandwidth.
44
參考答案
Yes, using flashback. Enable flashback, open the standby read-write, perform tests, then flashback to the pre-test SCN and resume as a standby.
45
參考答案
No, we cannot change block size.
46
參考答案
If you are going to do batch installations, it is best to do it with Oracle Universal Installer in silent mode. For single installations.it is best to start the installer in "interactive mode" and set installation options at each window. However, in batch installations, this will take longer. You need to do the installations in "silent" mode with a "response file". In a silent installation, you start the Oracle Universal Installer from a command prompt and specify the location of the "response file". The installation files and the response file can be shared among the servers via NFS so that you won't have to copy the setup files to each server.
47
參考答案
Steps:Shutdown the Database: First, shut down the Oracle instance you want to rename. Edit Initialization File: Locate the spfile or pfile in your Oracle home. Change the db_name and instance_name parameters in the initialization file. Example:Set the ORACLE_SID: Set the environment variable ORACLE_SID to the new instance name. Restart the Database: Restart the database instance with the new name. Test the Change: Verify that the new instance name works by connecting to the database using the new SID.
48
參考答案
Add a datafile to a tablespace.
49
參考答案
Everyone is human. If you face a candidate giving you the perfect, straight-out-of-the-book answers, believe it or not, that might be a red flag. Instead of the “No, I haven't made any mistakes, I'm perfect” kind of answer, look for someone honest. The trick of this question isn't only about the mistake itself but also about the ‘how.' Learning how they worked under pressure and how they solved it. Additionally, knowing how to prevent it it's also a good sign that they understood exactly what had happened, and because of this, it's very unlikely it will happen to them again.
50
參考答案
We can query v$syspw_file view.
51
參考答案
Refreshing a non-production database (such as test or dev) from a production copy involves updating the non-prod DB with a recent snapshot of production data. Typical process includes: • Taking a backup or RMAN clone of the production database. • Restoring the backup on the non-prod server or using RMAN duplicate to create a fresh copy. • Optionally masking or anonymizing sensitive data in the non-prod copy for compliance. • Adjusting environment-specific settings such as connection strings, mail servers, or application configurations to avoid impacting production systems. • Synchronizing configurations, users, and roles as needed. • Running any necessary scripts to prepare the environment. • Scheduling refreshes regularly to keep non-prod environments relevant for testing and development. Proper refresh procedures ensure developers and testers work with real data scenarios while maintaining data security.
52
參考答案
I wasn't familiar with limits.conf specifically, but I understand it's used to configure user-level resource limits on Linux systems, especially for applications like Oracle. I've worked with similar system resource configurations, such as adjusting ulimit settings for performance tuning. I'm always eager to learn more about Linux administration and would certainly dive into the documentation and practical resources to become more familiar with limits.conf if I encountered it in a production environment.
53
參考答案
DBCC CheckDB is a command that checks the logical and physical integrity of all objects in a database. The 'Suspect_Pages' table in the msdb database records pages that have been identified as potentially corrupted during I/O operations. It provides an 'early warning' by alerting administrators to potential corruption before it causes significant data loss, allowing proactive remediation.
54
參考答案
Run the following SQL command:SELECT log_mode FROM v$database; If the output is ARCHIVELOG, the database is in ARCHIVELOG mode; otherwise, it is in NOARCHIVELOG mode.
55
參考答案
Difference Between Role and Privilege: A privilege is a specific right or permission granted to a user to perform a particular action in the database. A role is a collection of multiple privileges grouped together for easier management.Example: Instead of granting individual privileges likeCREATE TABLE ,SELECT ,INSERT , andDELETE to multiple users, you can create a role named DATA_MANAGER and assign these privileges to the role. Then, you can grant the DATA_MANAGER role to users, simplifying access management.Types of Privileges: - System Privileges – These allow users to perform administrative tasks on the database. Examples:CREATE SESSION ,CREATE USER ,DROP USER ,ALTER SYSTEM - Object Privileges – These control access to specific objects like tables, views, and sequences. Examples:SELECT ,INSERT ,UPDATE ,DELETE ,ALTER ,INDEX ,REFERENCES on a table - System Privileges – These allow users to perform administrative tasks on the database. -
56
參考答案
A deadlock occurs when two or more sessions are waiting for each other to release locks, causing the processes to be stuck indefinitely. To handle a deadlock, I would first identify and capture the deadlock events using SQL Server Profiler or by enabling the trace flag 1222 to log deadlock information in the SQL Server error log. Once identified, I would analyze the deadlock graph to understand the resources and queries involved. The most common solutions to resolve deadlocks in general include: - Optimizing queries: Reviewing and optimizing the queries involved to ensure they are acquiring locks in the same order to avoid circular wait conditions. - Reducing transaction scope: Keeping transactions as short as possible to minimize the time locks are held. - Implementing deadlock retry logic: Modifying the application code to catch deadlock exceptions and retry the transaction, as SQL Server will automatically choose one of the processes as the deadlock victim. - Using query hints: Using query hints like NOLOCK for read operations that do not require strict consistency or using ROWLOCK to acquire finer-grained locks.
57
參考答案
The control file is a small but critical file that tracks the overall state of the database. Without it, the database cannot start. It stores: • Database name and unique ID • Names and locations of datafiles and redo logs • SCN (System Change Number) info • Checkpoint data • Backup and archive log history Oracle reads the control file during the MOUNT stage of startup. If the control file is missing or corrupted, the instance won't proceed. Oracle recommends having multiplexed control files—multiple copies on different disks—specified in the parameter file. Backups are also critical. You can use RMAN (BACKUP CURRENT CONTROLFILE) or manually create a text backup (ALTER DATABASE BACKUP CONTROLFILE TO TRACE). In case of control file loss, you can restore it from a backup and recover the database. Managing this file properly is key to database stability and recoverability.
58
參考答案
If the image is stored in a column of type VARBINARY(MAX) or IMAGE (deprecated), use a SELECT query like: SELECT ImageData FROM Products WHERE ProductID = 101; The result returns binary data, which must be handled in your application layer (e.g., C#, Python) to decode and display the image properly.
59
參考答案
Blocking sessions cause other sessions to wait, leading to performance problems. To find blocking sessions: • Query views like V$SESSION and V$LOCK to identify which session is holding locks and which are waiting. • Use SQL like: • SELECT blocking_session, sid, serial#, wait_class, event FROM v$session WHERE blocking_session IS NOT NULL; • Oracle Enterprise Manager (OEM) also shows blocking sessions visually. To kill a blocking session: • Use the command: • ALTER SYSTEM KILL SESSION ‘sid,serial#'; • Sometimes, if the session does not terminate, use OS commands to kill the session process. • After killing, monitor the system to ensure locks are released and performance returns to normal.
60
參考答案
Transportable Tablespaces (TTS) allow you to quickly move large data between Oracle databases by copying tablespace datafiles instead of exporting all rows. Steps: make tablespace read-only → export metadata withexpdp transport_tablespaces → copy datafiles → import metadata withimpdp . It's much faster than full export/import and is often used for migrations and large data transfers.
61
參考答案
To check the default block size for an Oracle database, you can use the following SQL query: Explanation: v$parameter : This view contains database initialization parameters.db_block_size : This parameter defines the default database block size in bytes. Example Output: This means the default block size for the database is 8 KB (8192 bytes).
62
參考答案
My approach to documentation is that it should be clear, concise, and kept current. I ensure all critical database structures like schemas, tables, and indexes are documented, along with important stored procedures and scripts. For operational processes like backups, restores, and maintenance routines, I create step-by-step guides. I use version control for scripts and store documentation in a centralized, easily accessible knowledge base. This promotes consistency, facilitates troubleshooting, and helps onboard new team members effectively.
63
參考答案
No, you cannot convert the standby to a snapshot standby in Maximum Protection mode if it's the only standby. Maximum Protection requires redo to be written to the standby redo log before committing transactions, which conflicts with snapshot standby's read-write mode.
64
參考答案
To prevent unauthorized data access: 1) Define and enforce row-level security or column-level security using views, stored procedures, or database roles. 2) Implement application-level authentication and authorization to control what data users can see. 3) Use encryption for sensitive columns. 4) Regularly review and revoke unnecessary permissions. 5) Set up auditing to track data access and detect violations. 6) Educate users on data handling policies and enforce strict access controls through the application layer.
65
參考答案
sqlnet.ora.
66
參考答案
1. Acknowledge the Common Types of Issues: In my daily work, I often come across a variety of issues. These can include performance-related issues, user errors, system failures, or complaints regarding usability or functionality. Depending on the type of issue, I address it by first understanding its root cause and then implementing a suitable solution. 2. Performance Issues: One of the most common performance-related issues I deal with is ensuring that applications or systems run efficiently. This could include slow response times, high resource usage, or system overloads. For example, if users complain about a website's slow loading speed, I typically analyze server performance, database queries, and optimize the code to improve performance.f the issue requires a deeper technical fix or if it's something beyond my expertise, I collaborate with the development team or escalate it to higher levels of support to ensure timely resolution. I'm always focused on resolving issues efficiently and keeping communication open with users to maintain a positive experience.
67
參考答案
No
68
參考答案
OLTP databases use small block sizes (2K, 4K, 8K) to optimize performance for small transactions.Data warehouse databases use larger block sizes (8K, 16K, 32K) for efficient large table scans. Hybrid databases require multiple block sizes and tablespaces with different BLOCKSIZE settings.
69
參考答案
A Physical Standby Database is an exact copy of the primary database and is kept in sync through Redo Apply.? Architecture Overview: 1. Primary Database generates redo logs. 2. Log Transport Services send these redo logs to the standby over the network. 3. Remote File Server (RFS) on standby receives redo and writes to standby redo logs. 4. MRP (Managed Recovery Process) applies redo to the standby datafiles.? Sync Process: – Redo data is shipped in real time or at intervals. – Standby applies the redo to remain consistent with the primary. – This enables disaster recovery and high availability.
70
參考答案
Backup Piece: A backup piece is a physical file created by RMAN that stores the actual data from the database. It is an essential component of a backup set.Backup Set: A backup set is a collection of backup pieces that RMAN groups together to efficiently store and manage backups. It is the preferred method for backup operations. Image Copy: An image copy is an exact duplicate of a database file created at the operating system level. It provides an alternative backup method, often used for fast recovery.
71
參考答案
✅ If RMAN is used: We use RMAN for database backups, as it provides block-level backups, incremental backups, and automated recovery options. It also integrates well with Oracle features like Data Guard and ASM.✅ If Export (Data Pump) is used: We use Data Pump export for logical backups, mainly for schema-level, table-level, or full database exports. However, this is not a complete database backup solution since it does not capture physical structures like RMAN.
72
參考答案
Users can connect to SQL Server Management Studio using Windows Authentication by selecting "SQL Server Authentication" and providing their username and password.
73
參考答案
Only at the time of startup after abort shutdown
74
參考答案
I start by gathering baseline metrics using performance monitoring tools like SQL Profiler or MySQL Performance Schema. First, I identify whether the issue is CPU, memory, I/O, or network related using system performance counters. For query-specific issues, I analyze execution plans to identify expensive operations like table scans or nested loops. I check for missing indexes, outdated statistics, or poorly written queries. In one case, I discovered a missing index on a join condition that was causing a report to run for 45 minutes. Adding the index reduced runtime to 3 minutes.
75
參考答案
V$CONTROLFILE_RECORD_SECTION.
76
參考答案
The Data Guard Broker simplifies management of Data Guard configurations via the dgmgrl utility. The DMON process handles switchovers, failovers, FSFO, and configuration updates across primary and standbys, improving administration efficiency.
77
參考答案
I'm not asking if they regularly attend, I'm not asking if they speak, I'm not asking if they run for office. At the senior DBA level, I'd just be thankful if they were at least vaguely aware that user groups existed. Huge massive bonus points if they've been involved with the community, and I'd skip the next question.
78
參考答案
A composite key is made by the combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness of a row is guaranteed, but when it is taken individually it does not guarantee uniqueness, or it can also be understood as a primary key made by the combination of two or more attributes to uniquely identify every row in a table. Note: - A composite key can also be made by the combination of more than one candidate key. - A composite key cannot be null.
79
參考答案
You might be failed to connect to SQL Server instance because of various reasons such as: - Ensure you are entering the right connection details like server name and port no - Ensure SQL Server port is open from the machine you are connecting to the instance - Make sure SQL Server instance is running - TCP or named pipe protocols are enabled through SQL Server configuration manager - SQL browser service is not running
80
參考答案
The database administrator's salary depends on various factors, such as location, company, experience, skills, and background. The average database administrator salary in the US is $90,681/ year (source: indeed.com). Companies such as Apple, EY, Oracle, TCS, Spectrum, and others provide the finest salaries to their database administrators. The average salaries in these companies range between $150,000 to $95,000. Moreover, cities like Dallas, New York, Phoenix, Chicago, and others offer top salaries to DBAs.
81
參考答案
The OPTIMIZE TABLE statement is used to reclaim space and optimize table storage after deleting or updating data.
82
參考答案
UNIQUE, SLIM SIZE (small width in bytes), EVER INCREASING, fixed width, not null
83
參考答案
During a critical system failure, our database became inaccessible, leading to a significant disruption in operations. I immediately initiated the incident response plan, and assembling a cross-functional team to investigate and resolve the issue. We quickly identified the root cause as a hardware failure and initiated the necessary steps for recovery, such as restoring from backups and implementing failover mechanisms. While the system was down, I maintained transparent communication with stakeholders, provided regular updates, and set realistic expectations. Through our swift actions and effective collaboration, we were able to restore the database and minimize downtime to just four hours, ensuring minimal impact on business operations.
84
參考答案
Force logging ensures all operations (including nologging ones) generate redo, preventing data inconsistencies on the standby due to missing redo for nologging operations.
85
參考答案
DELETE statements can be prevented to replicate any articles in SQL Server replication. We can get this done by accessing the properties window of the identified article and choose not to replicate delete statements there.
86
參考答案
When a user connects to a database, he sends SQL queries to the database to execute. These SQL queries are executed by a "server process" and the result is returned back to the user. In the "dedicated server" architecture, the instance will create one server process for each connected user. That process will be "dedicated" to that user and will only serve that client. However, in "shared server" architecture, a single server process will serve multiple clients. In shared server architecture, the total memory consumption will be less. However, certain operations like DBA activities can only be performed on a dedicated server.
87
參考答案
Indexes improve data retrieval speed by creating a data structure that allows faster searching and sorting.
88
參考答案
We must grant CONNECT role so that user can connect to database. We must also grant other privileges as per environment.
89
參考答案
Central Inventory: Located in the oraInst.loc file (/etc/oraInst.loc on Linux or C:\Program Files\Oracle\Inventory on Windows). Local Inventory: Found inside $ORACLE_HOME/inventory.
90
參考答案
Blocking happens when one process locks resources and others wait. Common causes include missing indexes, long-running transactions, and poor query design. I fix blocking by adding proper indexes, splitting large transactions, and reviewing isolation levels like using Read Committed Snapshot Isolation.
91
參考答案
- Maximum Protection: Ensures zero data loss by writing redo to both primary and standby redo logs before commit. If the standby is unreachable, the primary shuts down. - Maximum Availability: Balances protection and availability. Commits wait for redo to reach a synchronized standby but revert to Maximum Performance if the standby is unavailable. - Maximum Performance (default): Commits after writing redo to the primary's online redo log, with asynchronous redo transport to the standby, risking minimal data loss.
92
參考答案
Introduced in 12c. Consists of: - CDB (Container DB): Central DB container. - PDB (Pluggable DBs): User-created DBs inside CDB. - Enables easy cloning, patching, and management.
93
參考答案
To maintain archive log files: 1) Configure automatic archiving to a dedicated location with sufficient disk space. 2) Set up a retention policy to delete or compress old archive logs after a specified period. 3) Monitor archive log generation rates and adjust the archive destination size accordingly. 4) Use backup scripts to archive logs to off-site storage. 5) Regularly check for archive log space warnings and take proactive action. 6) Implement log shipping or replication to offload logs to a secondary server.
94
參考答案
GROUP BY keyword is an aggregate function such as SUM, MULTIPLE, etc and without this function sum for each individual group value can not be calculated.
95
參考答案
Take an incremental backup of the primary starting from the standby's current SCN, transfer it to the standby, and apply it to recover the standby database.
96
參考答案
The backup process involves taking periodic snapshots of database data to ensure recovery in case of failure. This typically includes full, incremental, and archive log backups using RMAN (Recovery Manager) or third-party tools. Additionally, automated scripts schedule backups and store them in secure locations for disaster recovery.
97
參考答案
Use more channels Enable parallelism Use multi-section backups
98
參考答案
Run the following SQL query: 1. Query V$PARAMETER View- If issys_modifiable is IMMEDIATE, the parameter can be changed dynamically. - If issys_modifiable is DEFERRED, the change takes effect at the next session login. - If issys_modifiable is FALSE, the database must be restarted for the change to take effect. 2. Query V$SYSTEM_PARAMETER Another method to check: ISSES_MODIFIABLE = TRUE → Can be changed at the session level.ISSYS_MODIFIABLE = IMMEDIATE → Can be changed dynamically at the system level. 3. Query V$PARAMETER2 for More Details 4. Check with SHOW PARAMETER If you want to see all parameters, use: - If -
99
參考答案
Prerequisites- Primary Database: Ensure the primary database is in ARCHIVELOG mode and hasFORCE LOGGING enabled. - Network Configuration: Ensure proper network connectivity between the primary and standby sites. - Oracle Software: Install the same version of Oracle software on the standby server. - Storage: Ensure sufficient storage is available on the standby server. Steps to Create a Standby Database 1. Enable Archiving and Force Logging on the Primary Database -- Connect to the primary database as SYSDBA SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; SQL> ALTER DATABASE FORCE LOGGING; 2. Create a Password File on the Primary Database Ensure the primary database has a password file. If not, create one: orapwd file=$ORACLE_HOME/dbs/orapw password= entries=10 3. Configure the Primary Database for Data Guard Add the following parameters to the primary database's spfile orpfile :ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary_db,standby_db)'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_db VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby_db'; ALTER SYSTEM SET FAL_SERVER=standby_db; ALTER SYSTEM SET FAL_CLIENT=primary_db; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 4. Create a Backup of the Primary Database Use RMAN to create a backup of the primary database: rman target / RMAN> BACKUP DATABASE PLUS ARCHIVELOG; 5. Copy the Backup to the Standby Server Transfer the backup files to the standby server using scp ,rsync , or any other file transfer method.6. Create an Initialization Parameter File for the Standby Database Create a pfile for the standby database:CREATE PFILE='/path/to/standby_init.ora' FROM SPFILE; Edit the pfile to reflect the standby database's settings - Primary Database: Ensure the primary database is in -
100
參考答案
Storage engines are responsible for handling data storage, retrieval, and indexing in MySQL. Examples include InnoDB, MyISAM, and MEMORY.
101
參考答案
Database Console Commands (DBCC) is a set of Transact-SQL commands for database management and maintenance in Microsoft SQL Server. Here are a few key uses of DBCC commands: - Checking Database Consistency: DBCC commands, such as DBCC CHECKDB, are used to verify the logical and physical integrity of a database. They can assist in identifying and resolving corruption, missing indexes, and other structural issues. - Performance Tuning: Some DBCC commands provide information about database performance and can be used to tune the performance of SQL Server. For example, DBCC SQLPERF provides information about various performance-related counters. - Statistics Updates: DBCC procedures like as DBCC UPDATEUSAGE and DBCC SHOW_STATISTICS are used to update and show statistics about database objects. Keeping statistics up to current is critical for the query optimizer to build effective execution plans.
102
參考答案
To optimize a slow-running query, I would first analyze the query execution plan to identify any bottlenecks or areas causing delays. I look for things like full table scans, missing indexes, or inefficient joins. If the query is performing a full table scan, adding appropriate indexes to the columns used in the WHERE clause or JOIN operations can significantly improve performance. For instance, if the query frequently filters on a column, an index on that column can reduce the data retrieval time. I also consider rewriting the query to simplify it or break it down into smaller parts if possible. For example, using subqueries or temporary tables helps streamline complex queries. Additionally, I check for other factors, such as the proper use of joins, avoiding unnecessary columns in the SELECT statement, and ensuring that the statistics on the tables are up-to-date. These steps help ensure the query runs as efficiently as possible.
103
參考答案
Managing multiple SQL Server instances involves using SQL Server Configuration Manager to start, stop, and configure services, as well as using SQL Server Management Studio (SSMS) to connect to and administer each instance separately. It also requires careful planning for resource allocation, such as memory and CPU, to avoid conflicts between instances.
104
參考答案
To switch users in SQL Server, press and hold Shift while right-clicking an ODBC window, select your driver for SQL server and assign any friendly names you desire; uncheck any bar and click Save, if applicable or navigate directly to client configuration as appropriate.
105
參考答案
Yes, using the BACKUP DURATION command: BACKUP DURATION 00:40 DATABASE;
106
參考答案
lsnrctl status
107
參考答案
A response file is a plain text file, where options to create a database are stored. It is possible to create it manually from scratch but that would take longer and would be erroneous. Installation media comes with a template response file. It is rather easier to customize it manually. This file also contains notes about the parameters. However, the easiest and most reliable way to create a response file is by using Oracle Universal Installer. If you start the installer in "record" mode, every option you choose at each step is automatically recorded in a response file in the correct format. After the installer completes in "record" mode, you'll have a complete response file with all the options set in it.
108
參考答案
ORDER BY keyword is used for sorting the results. It returns the sorted results to your program.
109
參考答案
rman target /: Connects RMAN to the target database for backup and recovery. rman auxiliary /: Connects RMAN to an auxiliary database for duplication and related tasks.
110
參考答案
1. Check network connectivity: Verify network stability and configure network settings for optimal performance. 2. Adjust archive log transmission parameters: Modify parameters like LOG_ARCHIVE_MAX_PROCESSES, LOG_ARCHIVE_DEST_1, and LOG_ARCHIVE_DEST_2 to optimize archive log transmission. 3. Clear archive log backlog: Manually delete or move archive logs from the primary database to clear the backlog. 4. Re-sync standby database: Use Oracle's built-in re-sync feature or manually re-create the standby database to re-establish synchronization. 5. Monitor and adjust: Continuously monitor the sync process and adjust parameters as needed to maintain optimal performance.
111
參考答案
When a database is open, any user with the "CREATE SESSION" privilege can make a connection. However, it is possible to open the database in "restricted" mode. When a database is open in restricted mode, only users with the "RESTRICTED SESSION" privilege can make a connection to the database. By default, only DBAs have the "RESTRICTED SESSION" privilege and it should not be granted to regular users. Opening a database in "restricted" mode is a good way to prevent regular users from accessing the database during maintenance.
112
參考答案
I approach database security by implementing access controls, encryption, regular audits, and patch management. Measures include using strong authentication, encrypting data at rest and in transit, and monitoring for unauthorized access.
113
參考答案
Use strong passwords, disable root access from remote hosts, and restrict user privileges to minimum necessary access.
114
參考答案
The database schema is a two-dimensional representation of how each table talks to each other, a way to view the design of a database as a single entity and not as a mixture of tables. They are Visio diagrams with 40 different tables connected through particular columns on either side.
115
參考答案
PMON (Process Monitor), SMON (System Monitor), DBWR (Database Writer), LGWR (Log Writer), CKPT (Checkpoint).
116
參考答案
SQL Server offers three recovery models: Simple, Full, and Bulk-Logged. Each model dictates how transaction logs are maintained and impacts how you can restore and recover data.
117
參考答案
This behavioral question allows candidates to demonstrate problem-solving skills. A good answer details a specific incident, the steps taken to diagnose the problem, and the outcome that improved the database environment.
118
參考答案
The mysql_upgrade utility is used to upgrade system tables and databases when upgrading MySQL to a newer version.
119
參考答案
System tablespace is created at the time of database creation. This tablespace holds all the data dictionary tables and hence it is used for the creation of various database objects. System tablespace must stay online for the database to function smoothly.
120
參考答案
The database will hang. We must have archive log backup scripts to take archive log backup and delete to release space.
121
參考答案
The catalog.sql and catproc.sql scripts created necessary views and procedures.
122
參考答案
When a SELECT query is issued while a job is running and deleting a row, but the transaction hasn't yet committed or rolled back, the result you get can depend on a few factors.– Undo Segments: Oracle uses undo segments to maintain a consistent view of the data for active transactions. Even though a row is marked for deletion by the transaction that hasn't committed, the undo segments will still store the previous version of the row (the version before the delete). – Consistent Read: Oracle ensures a consistent read for a SELECT query. So, even though the delete operation is in progress, the query will fetch the row as it was at the time the query started, not as it is at the time of the commit.– Your SELECT query would return the row as it existed before the deletion because Oracle would use the undo information to provide a consistent view of the data. – You would not see the row as deleted until the transaction is committed.
123
參考答案
DBA_FREE_SPACE
124
參考答案
Look for: Understanding of schema design principles and normalization techniques.
125
參考答案
The information_schema database contains metadata about all other databases, tables, columns, and privileges in MySQL.
126
參考答案
Use the MATCH and AGAINST keywords in the SELECT statement to perform full-text searches on text-based columns.
127
參考答案
Checkpoint Actions: 1. All modified database buffers are written to disk: All dirty buffers in the buffer cache are written to their corresponding datafiles on disk. 2. The SCN (System Change Number) is updated: The SCN is incremented and updated in the control file, datafile headers, and redo logs. 3. The checkpoint position is updated: The checkpoint position is updated in the control file to reflect the new SCN. 4. The redo logs are switched: The redo logs are switched, and a new redo log is opened for writing. 5. The database is marked consistent: The database is marked consistent, indicating that all changes have been written to disk
128
參考答案
System databases are the core databases that come with the SQL Server installation; they're essential for the server's operation, holding crucial configuration and metadata. In contrast, a user database is created by an administrator or application to store actual business data. The main difference lies in their purpose: one manages the server itself, while the other stores an organization's specific information.
129
參考答案
I. Backup your database. II. Empty recycle bin & gather stats. III. Run oracle's database pre-upgrade utility. IV. Install the oracle's 19c database s/w. V. Run the DBUA Utility. VI. Run post-upgrade script & restart database.
130
參考答案
A foreign key is a column or a set of columns that references a primary key in another table. It establishes a relationship between two tables and ensures referential integrity. Foreign keys are used to enforce constraints and maintain consistency across related tables.
131
參考答案
A deadlock occurs when two or more sessions are waiting for each other to release locks, in a circular chain, so that they can proceed with their transaction. SQL Server detects deadlocks automatically and resolves them by choosing one process as a deadlock victim and rolling back its transaction, allowing other transactions to proceed.
132
參考答案
Normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves dividing a database into tables and defining relationships between them to ensure data consistency and efficient querying.
133
參考答案
The my.cnf file is the configuration file for MySQL server settings, including database directories, buffer sizes, and other parameters.
134
參考答案
DBA_SEGMENTS
135
參考答案
Use a brochure from any third party application (like Microsoft Project Server or Blackberry Enterprise Server) and say the project manager wants to set this up. I want the DBA to ask questions like: - How big will the database be? (Leading to questions about whether we can add the database to an existing server) - How critical is the database? (Leading to questions about clustering, disaster recovery, high availability) - What's the company standard on virtualization? (Can we save money by using a virtual server) If the senior DBA candidate comes back with a shocked look and doesn't know where to begin, then they haven't done a lot of deployments.
136
參考答案
Sharding is a database architecture pattern that horizontally partitions data across multiple databases or servers, called shards, based on a shard key. It improves scalability and performance by distributing load, but it requires careful design for data distribution and query routing.
137
參考答案
To create a new database, use the SQL command: sqlCopy code CREATE DATABASE dbname;
138
參考答案
First, I take a full backup from the source server. Then, I restore it on the target server with the right recovery options. After restoring, I fix orphaned users, update server-specific settings, and test the application connection.
139
參考答案
SQL Server 2022 enhances disaster recovery planning through its improved Always On Availability Groups, which now support automatic page repair for additional reliability and easier management of disaster recovery configurations. The Accelerated Database Recovery feature significantly reduces recovery times by maintaining a persistent version store in the database, speeding up the rollback and recovery processes. SQL Server 2022 also includes better integration with Azure for disaster recovery, allowing databases to be backed up directly to Azure Blob Storage and facilitating geo-replication for global distribution and redundancy.
140
參考答案
Data recovery is an essential skill for any DBA. Discuss a scenario where you successfully recovered data from a backup, the approach you took, and any lessons learned from the experience. Once, due to a system failure, we lost some data. Fortunately, we had a backup strategy in place, including regular full and incremental backups. I restored the full backup and then applied the incremental backups in the order they were created until the point of failure. This experience emphasized the importance of a robust backup strategy and regular testing.
141
參考答案
Backup the database. Gather statistics and clean up unnecessary objects. Run Oracle's pre-upgrade utility. Install the new database software. Execute the upgrade scripts. Perform post-upgrade checks and testing.
142
參考答案
40k
143
參考答案
An INNER JOIN returns only matching rows from both tables, while a LEFT JOIN returns all rows from the left table and matching rows from the right table.
144
參考答案
For such big number of installations, we can go with silent mode installation using response file.
145
參考答案
Yes.
146
參考答案
Redo log files in Oracle are used to record all changes made to the database. Their primary purposes are: - Crash Recovery – Helps recover the database in case of a system failure. - Data Integrity – Ensures committed transactions are not lost. - Replication & Standby – Used for redo shipping in Data Guard and replication setups. - Rollback & Roll-Forward – Enables rolling back uncommitted transactions and rolling forward committed changes.
147
參考答案
No, once the database is opened with the RESETLOGS option, you cannot flashback to a point before the resetlogs operation.
148
參考答案
FULL, LOG, and optionally DIFFERENTIAL
149
參考答案
Most businesses are currently moving to cloud infrastructure. If that's your case, then it's important to learn more about a candidate's experience working with cloud databases and know what environment they work best. Additionally, suppose your organization has an on-premises database and is looking to shift towards a hybrid database. In that case, you need to find out if a candidate will be able to support your long-term goals.
150
參考答案
Hot backup is the process of taking database backup while the database is in running mode. Hence, it is also known as Online Backup. While cold backup can be taken only when the database is in shut down mode and hence it is known as Offline Backup as well. There are few websites like banking & trading ones, which are 24 hours operational and hence, cannot support bringing the database down. Hence, DBAs need to take the backup in online mode only.
151
參考答案
The SELECT statement is used to select the set of specific values from a table in a database depending on the various conditions specified in a SQL query.
152
參考答案
Base tables are created when you create a database. If you do not run those scripts, we will not be able to query any data dictionary view or dynamic performance view.
153
參考答案
Row Compression can create massive fragmentation because it changes the physical layout of data pages, potentially causing page splits and increased logical fragmentation. Page compression can also affect fragmentation by reorganizing data within pages, but it may reduce fragmentation in some cases. Both compression types should be tested and monitored for fragmentation impacts.
154
參考答案
When a SELECT query is executed:1. Parser: Parses the query and generates an abstract syntax tree (AST). 2. Query Optimizer: Analyzes the query and generates an optimal execution plan. 3. Execution Engine: Executes the plan, retrieving data from storage or memory. 4. Data Processing: Performs aggregations, transformations, and generates the result set. 5. Result Set Return: Returns the result set to the client application.Key architecture components: – Parser – Query Optimizer – Execution Engine – Data Retrieval – Data Processing – Result Set Generator – Storage
155
參考答案
If the undo information for the deleted row has been overwritten or is no longer available, Oracle will not be able to return the row. The transaction's consistent view will be lost, and either the query will return no data or the system might raise an ORA-01555 error.
156
參考答案
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
157
參考答案
Some key measures I take are: - Data encryption: Enable encryption both at rest and in transit. For at-rest encryption, I use the cloud provider's encryption services like AWS KMS or Azure Key Vault to manage encryption keys. For data in transit, I use SSL/TLS to encrypt connections between the application and the database. - Access control: Implement the principle of least privilege by granting only the necessary permissions to users and applications. Use Identity and Access Management (IAM) roles and policies to control access to the database and its resources. Additionally, enable multi-factor authentication (MFA) for administrative access. - Network security: Utilize Virtual Private Cloud (VPC) or Virtual Network (VNet) configurations to isolate databases within a secure network. Use security groups, firewalls, and network ACLs to restrict access to the database to trusted IP addresses or subnets. - Monitoring and auditing: Enable database logging and monitoring features to track access and query execution. Use services like AWS CloudTrail, Azure Monitor, or Google Cloud Audit Logs to maintain an audit trail of database activities. - Compliance and regular security audits: Ensure the database complies with relevant regulations like GDPR or HIPAA by configuring data protection settings and performing regular security audits and vulnerability assessments.
158
參考答案
Yes we can set LOG_ARCHIVE_TRACE parameter to trace redo transport and apply services on primary and standby.possible values are 0(default means no tracing) , 1,2,4,8,16,32,64 …. 8192
159
參考答案
A sequence generates a serial list of unique numbers for numerical columns of database tables. We can use the sequence on columns for data where we want to insert data in a sequential manner.
160
參考答案
Use the mysqldump command for backups and the mysql command or a tool like mysqlimport for restores. Example: bashCopy code mysqldump -u username -p dbname > backup.sql mysql -u username -p dbname < backup.sql
161
參考答案
The DATABASE DEFAULT TABLESPACE will become user default tablespace.
162
參考答案
Add more archive destinations, increase disk space, or delete/move old archived logs after confirming backups.
163
參考答案
Once for the Entire Databasecreation.
164
參考答案
A full-text search enables powerful linguistic searches such as finding word variations, phrases, or words near each other. It supports CONTAINS, FREETEXT, and inflectional forms (e.g., “run” also matches “running”). In contrast, regular searches (like WHERE Name LIKE '%run%') only match literal string patterns, which limits flexibility and performance on large text fields.
165
參考答案
Backup Sets: Advantages: Efficient in terms of storage, supports compression, and improves backup and restore performance. Disadvantages: Takes more time to create and restore compared to image copies. Image Copies: Advantages: Faster restoration since files can be directly copied back to their original location. Disadvantages: Requires more storage space and is generally slower for backup operations compared to backup sets.
166
參考答案
Strategies include enforcing foreign key and unique constraints, using transactions with appropriate isolation levels, implementing triggers for validation, periodic consistency checks, and maintaining thorough backup and restore procedures.
167
參考答案
ORACLE_BASE: The root directory for Oracle software installations. ORACLE_HOME: The directory under ORACLE_BASE where Oracle products are installed.
168
參考答案
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
169
參考答案
Policies and procedures involve performing pre- and post-migration integrity checks, validating data transformations, running reconciliation reports, thorough testing in pre-production environments, and maintaining rollback plans to ensure data consistency.
170
參考答案
First, remote into the server where SQL Server instances are running, next, use Task Manager's "processes" column to observe SQL Server Processes if one or multiple instances on the same server exist; for CPU utilisation analysis, use Task Manager again by viewing all applications sold and their usage.
171
參考答案
In a typical *disaster recovery setup, I would configure Oracle Data Guard to have the Primary and Standby databases located in different cities or geographic regions. This setup helps ensure that even if there's a localized disaster (like a natural disaster, power outage, or network failure) affecting one location, the other location can take over, minimizing downtime and preventing data loss.
172
參考答案
Automatic Storage Management (ASM) is Oracle's integrated volume manager and file system designed for Oracle database files. It simplifies storage management by abstracting the physical disks into logical disk groups. ASM automatically manages striping and mirroring of data across disks to improve performance and provide redundancy. It reduces the complexity of managing individual files and disks manually.ASM is used because it: • Simplifies database storage administration. • Improves I/O performance via balanced striping. • Provides fault tolerance with different redundancy levels (normal, high). • Supports dynamic resizing and addition/removal of disks. • Integrates fully with Oracle tools like RMAN and Grid Infrastructure. • Enables easier scaling of storage with less downtime. ASM is the preferred storage solution in Oracle RAC and large database environments.
173
參考答案
There are two types of optimizers: 1. Rule-Based Optimizer (RBO): If the referenced objects don't maintain any internal statistics, RBO is used. 2. Cost-Based Optimizer (CBO): If the referenced objects maintain internal statistics, CBO will check all the possible execution plans and select the one with the lowest cost.
174
參考答案
TRUE
175
參考答案
– B-tree indexes are preferred in OLTP environment. Refer Q31 – Preferred when cardinality is high
176
參考答案
CREATE USER PADDU IDENTIFIED BY PADDU DEFAULT TABLESPACE TESTTBS1 PROFILE TEST; GRANT CONNECT, RESOURCE TO PADDU;
177
參考答案
To create a new database in SQL Server, you can use the CREATE DATABASE statement in T-SQL, specifying the database name and optional parameters like file locations and sizes. Alternatively, you can use the graphical interface in SSMS by right-clicking on the Databases folder and selecting 'New Database'.
178
參考答案
The primary will shutdown .Primary crashes immediately because standby can't apply redo in Maximum Protection mode
179
參考答案
A data dictionary table contains metadata about database objects such as tables, indexes, users, privileges, and schemas. It provides essential information for database management but is not modifiable by users. In contrast, normal tables store user data and can be read, modified, and deleted by authorized users.
180
參考答案
By restoring from a backup and using RECOVER DATABASE USING BACKUP CONTROLFILE
181
參考答案
MS SQL SERVER | POSTGRESQL | |---|---| | Developed by Microsoft Corporation and initially released on April 24, 1989 | Developed by PostgreSQL Global Development Group on 1989. | | MS SQL server is written in C++ language. | PostgreSQL is written in C language. | | It is a Microsoft relational DBMS. | It is a widely used open-source RDBMS. | | The primary database model for MS SQL Server is Relational DBMS. | The primary database model for PostgreSQL is also Relational DBMS. | | It also has two Secondary database models – Document Store and Graph DBMS. | It has a Document store as a Secondary database model. |
182
參考答案
Using RMAN: - Take backup of source DB. - Copy backups to clone server. - Restore controlfile. - Restore and recover datafiles. - Open database with RESETLOGS.
183
參考答案
There are several types of databases, including relational databases, NoSQL databases, cloud databases, and data warehouses. Relational databases use tables to store data, while NoSQL databases use key-value pairs, document stores, or graph databases. Cloud databases are hosted on cloud platforms, and data warehouses are used for analytical processing.
184
參考答案
ANALYZE statement can be used to collect the statistics of various database objects like tables, indexes, partitions, cluster or object references. Using this statement we can also identify migrated as well as chained rows within a table or cluster.
185
參考答案
LOG_ARCHIVE_DEST_n (e.g., LOG_ARCHIVE_DEST_2) specifies the standby service name, transport mode (SYNC/ASYNC), and attributes like AFFIRM/NOAFFIRM.
186
參考答案
– Setting Kernel Parameters: It adjusts various kernel parameters in /etc/sysctl.conf to optimize the system for Oracle Database performance. – Setting Resource Limits: It modifies /etc/security/limits.conf to configure limits for the Oracle user (e.g., file descriptors, processes). – Configuring Groups and Users: It creates the oracle user and relevant groups (e.g., oinstall, dba). – Disk Partition and Directory Creation: It sets up Oracle directories and ensures permissions are correctly set. – Enabling Required Services: It prepares the system by configuring necessary services like swap, shared memory, and file system. Pre-Installation Package Actions:1. Installing the Pre-Installation Package (RHEL/CentOS/Oracle Linux): bash sudo yum install -y oracle-database-preinstall-19c This package will automatically adjust various system settings, and you won't need to manually adjust kernel parameters unless you want to customize them further.2. The Pre-Installation Package Automatically Configures Kernel Parameters -
187
參考答案
We can query DBA_DATA_FILES to check tablespace related information.
188
參考答案
Monitoring ASM involves: • Using views like V$ASM_DISKGROUP, V$ASM_DISK, and V$ASM_OPERATION to check disk group space, health, and ongoing operations. • Checking ASM alert logs for errors or warnings. • Using Oracle Enterprise Manager ASM plugin for graphical monitoring. • Tracking space usage, free space, and rebalance progress. • Monitoring I/O statistics and disk failures to detect bottlenecks or faults early. • Setting up alerts for disk failures, space thresholds, or rebalancing issues. • Regularly reviewing ASM metadata and disk status to maintain high availability
189
參考答案
Situation: Our main reporting system was taking 2+ hours to generate daily executive reports, causing delays in business decision-making. Task: I needed to optimize performance while maintaining data accuracy and keeping the system online. Action: I analyzed query execution plans and discovered missing indexes on key join columns. I also identified several inefficient queries that were doing full table scans. I implemented index changes during maintenance windows and worked with developers to rewrite problematic queries. Result: Report generation time dropped to 15 minutes—an 87% improvement—and we could now run reports multiple times per day, giving executives more timely business insights.
190
參考答案
Terminology: Primary with two standbys (Data Guard). If one standby is synchronized (REDO APPLY / SYNC) and the other lagging, switchover feasibility depends: - For a switchover, the target standby must be a valid switchover target (healthy, consistent with archived redo) and must be in READY state. Only one standby is required for switchover. - You can switchover to the in-sync standby. The out-of-sync standby will need later re-synchronization. Switch-over statuses you can check: Important details: - If using Data Guard broker ( DGMGRL ),show database 'standby_db' status will showSTANDBY /SUCCESS /NEED-LOG etc. - Use v$database :OPEN_MODE ,DATABASE_ROLE . - Use v$dataguard_stats for gaps. If the chosen standby is in sync (APPLY or NO GAP), you can perform switchover. Ensure logs shipped and applied and that the standby can be opened read/write after switchover. After switchover, reconfigure and resync the out-of-sync standby (maybe by reinstatement or full resync).
191
參考答案
DBA_SEGMENTS
192
參考答案
I implement high availability using a combination of Always On Availability Groups for SQL Server or Master-Slave replication for MySQL. I configure automatic failover for critical systems with health monitoring and alerting. For our e-commerce platform, I set up a three-node cluster with synchronous replication to a secondary node and asynchronous replication to a disaster recovery site. This configuration provides an RTO of under 60 seconds for automatic failover and has maintained 99.97% uptime over the past two years.
193
參考答案
One common approach is to utilize the cloud provider's managed database services, like Amazon RDS, Azure SQL Database, or Google Cloud SQL, which offer built-in HA features. These services provide multi-AZ (Availability Zone) deployments, automatic failover, and backup solutions. For example, in AWS, I would set up an Amazon RDS instance with Multi-AZ deployment, which automatically replicates data to a standby instance in a different Availability Zone. In case of a failure, the system will automatically failover to the standby instance, minimizing downtime. Another method is to implement replication and clustering. For instance, using PostgreSQL on a cloud VM, I could set up streaming replication and a failover mechanism with tools like pgPool or Patroni to ensure database availability. I also configure regular automated backups and monitor the database with alerting mechanisms for proactive issue detection. This table illustrates different high availability (HA) strategies in cloud-based database environments: | HA Strategy | Description | Example cloud provider feature | | Multi-AZ deployment | Automatically replicates data across multiple availability zones for failover. | AWS RDS Multi-AZ | | Read replicas | Creates replicas in different regions or AZs for load balancing and failover. | AWS Aurora Read Replicas | | Automated backups & snapshots | Regular automated backups for disaster recovery and point-in-time recovery. | Google Cloud SQL Backups | | Active-passive failover | A secondary server takes over if the primary server fails, ensuring availability. | Azure SQL Database Failover Groups |
194
參考答案
Stored procedures are prepared SQL code that you save so you can reuse it over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure and call it to run it.
195
參考答案
Log shipping is a high-availability feature in SQL Server that automatically sends transaction log backups from a primary database to one or more secondary databases. It involves backing up the transaction log on the primary server, copying it to the secondary server, and restoring it to keep the secondary database synchronized.
196
參考答案
One is enough but it is recommended to have separate listeners for each database.
197
參考答案
Monitoring involves tracking key metrics like CPU usage, query response times, disk I/O, and memory usage. Tools like Oracle Enterprise Manager, SQL Server Profiler, or third-party monitoring software help identify bottlenecks and optimize performance.
198
參考答案
A far sync instance is a lightweight Oracle instance (control file only, no datafiles) that receives redo synchronously from the primary and forwards it asynchronously to remote standbys (up to 30). It's used when standbys are geographically distant, requiring Active Data Guard licensing.
199
參考答案
WITH max_dates AS (SELECT user_id, account_id, MAX(record_date) AS max_date FROM sf_events GROUP BY user_id, account_id), dec_2020 AS (SELECT DISTINCT account_id, user_id FROM sf_events WHERE FORMAT(record_date, 'yyyy-MM-01') = '2020-12-01'), jan_2021 AS (SELECT DISTINCT account_id, user_id FROM sf_events WHERE FORMAT(record_date, 'yyyy-MM-01') = '2021-01-01'), retention_dec AS (SELECT d.account_id, COUNT(DISTINCT CASE WHEN m.max_date > '2020-12-31' THEN d.user_id END) * 1.0 / COUNT(DISTINCT d.user_id) AS retention_dec FROM dec_2020 d JOIN max_dates m ON d.user_id = m.user_id AND d.account_id = m.account_id GROUP BY d.account_id), retention_jan AS (SELECT j.account_id, COUNT(DISTINCT CASE WHEN m.max_date > '2021-01-31' THEN j.user_id END) * 1.0 / COUNT(DISTINCT j.user_id) AS retention_jan FROM jan_2021 j JOIN max_dates m ON j.user_id = m.user_id AND j.account_id = m.account_id GROUP BY j.account_id) SELECT rj.account_id, ISNULL(rj.retention_jan, 0) / NULLIF(rd.retention_dec, 0) AS ratio FROM retention_dec rd LEFT JOIN retention_jan rj ON rd.account_id = rj.account_id;
200
參考答案
Give the user access to the SQL Server database Enable the user to use other schemas Restrict the user's access to the SQL Server database Create a new schema for the user


Copyright © 2024. Designer by SPOTO Official Website. All Rights Reserved.