Sharing knowledge is the ultimate key to gaining knowledge…
The only two things that stay with you for life are you & your knowledge...
Live while you can! Teach & inspire while you could & Smile while you have the teeth.
-
ASM
- Creating ASM Instance and diskgroups manually without DBCA
- 20 ASM Realtime | Interview Questions
- How to make a cold backup for database running on ASM ( using RMAN )
- Moving a Single Datafile from File System to ASM System (METHOD 1)
- Moving a Single Datafile from File System to ASM System (METHOD 2)
- Things to consider while adding new disk/ Rebalancing existing Disk Group
-
Dataguard
- Dataguard Modes
- Dataguard Modes - II
- Oracle DataGuard Setup
- invalid archived log deletion policy
- recover from the Loss of a Datafile on a Standby Database ?
- MRP terminated with ORA-00600: internal error code, arguments
- Recover archive gaps in standby database - using 2 methodss
- Gap Resolution in Dataguard
- Convert Physical Standby to Snapshot Standby and Vice Versa
- Re-create Redo-logs on Standby/ Primary
- Pre-check for DataGuard switchover Process
- ORA-19815: WARNING: db_recovery_file_dest_size
- How do you purge old archivelogs which are applied on Standby DB
- Pre-check For SWITCHOVER using DG Broker
- https://oracle-dba-help.blogspot.com/2016/03/data-guard-modes.html
- ORA-01274 : RENAME THE UNKNOW FILE IN THE STANDBY DATABASE
- Database Creation in Easy 6 Steps
- Oracle Networking
- Tablespace management
- Housekeep : Quick Tips for DBA
- Health check for DB
- ORA-12537 While Connecting To Database Via Listener
- How to identify the applied patche-set of your Oracle Home
- Accessing a schema without knowing the password
- ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
- Session Related Scripts
- How to check ORA Error on DB Server – Beginner Tip
- Working with Redo Logs
- Decommission of Oracle Database
- Roles in Oracle - Managing Users
- Oracle Release Number Format
- Troubleshooting Handy-Guide
- Understanding the Upgradation Process
- Query to find the session creating more redo
- Audit in Oracle
- Troubleshooting Internal Errors and Error-Look-up Tool on MOS
- Troubleshooting Issues with Undo Tablespace
- User Management
- User Management - II
- Data Pump Gets Better
- MEMORY_TARGET Parameter
- Installing PostgreSQL Server on CentOS, RHEL 6 / 7 Systems
- Creating a database in Postgres
- Initialize multiple Postgres instance on the same RHEL server
- How to stop starting of clusters while starting up services
- Setup a streaming replication
- Switchover- master server and warm standby (with streaming replication)
- All About Physical Replication and Log shipping
- PostgreSQL Upgrade and troubleshooting
- Timeline Switch issue
- Query to identify the lag in replication
- Drop table : ERROR: must be owner of relation table_name
- PgBadger
- PgCenter
- PgTune
- Internals of Toast Table
- Influencing Query Execution Plan
- Create Extension ERROR
- Find Uptime in PostgreSQL
- Set DB Timezone
- Clear RAM Cache, Buffer
- Read-Only User in Postgres
- Everything you need to know about Streaming Replication
- Ora2Pg Migrate Oracle to Postgres
- Total Page Cost
- PostgreSQL 13 - Everything you need to know
- Startup Failure - .conf file
- All About Postgres Upgrade
- Basic Guide
- Session Related Scripts
- AWR :: Beginners Guide
- All About ASH - Active Session History
- Wait Events : checkpoint busy waits or archiver busy waits
- Find sessions performing sort operations : TEMP Tablespace occupants
- Generate ADDM task and generate its report for Tuning
- Database Performance Tuning Scripts -Keep Handy
- Buffer Busy Wait
- Simulating the Deadlock
- Latches
- Steps to Schedule any RMAN Jobs In Windows Server
- ORA-01194: file 1 needs more recovery to be consistent
- Loss of Current Online Redo-Log
- RMAN "baby Steps"
- Recovery of ControlFiles
- Loss of Datafile
- Recovery from complete loss of all online redo log files
- Block Change Tracking
- RMAN Questions
- Instance Recovery
- Redundancy and Recovery window in RMAN
- Cold Backup
- Database Cloning
- Contact
- Drop Database using RMAN
- PITR to older Incarnation
Friday 13 May 2016
UNIX / LINUX Commands for Oracle DBA
Shutting Down a Database
SYSOPER
or SYSDBA.
Shutting Down with the NORMAL Clause
To shut down a database in normal situations, use the SHUTDOWN
command with
the NORMAL
clause:SQL> SHUTDOWN NORMAL
The NORMAL
clause is optional, because this is the default shutdown method if
no clause is provided.Normal database shutdown proceeds with the following conditions:
Shutting Down with the IMMEDIATE Clause
Immediate database shutdown proceeds with
the following conditions:
Shutting Down with the TRANSACTIONAL Clause
When you want to perform a planned shutdown
of an instance while allowing active transactions to complete first, use the SHUTDOWN
command with the TRANSACTIONAL
clause:SQL> SHUTDOWN TRANSACTIONAL
Transactional database shutdown proceeds with the following
conditions:SHUTDOWN IMMEDIATE
statement is submitted.
Shutting Down with the ABORT Clause
You can shut down a database
instantaneously by aborting the database instance.When you must do a database shutdown by aborting transactions and user connections, issue the
SHUTDOWN
command with the ABORT
clause:SQL> SHUTDOWN ABORT
An aborted database shutdown proceeds with the following conditions:Tuesday 10 May 2016
Recovery of ControlFiles to Another Location, without AutoBackup available
SQL> show parameter control_file
NAME TYPE VALUE
--------------------------------------- ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/orcl/contr
ol02.ctl
SQL>
[oracle@system1 ~]$ rm -rf /u01/app/oracle/oradata/orcl/control01.ctl
[oracle@system1 ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@system1 orcl]$ ls control
ls: cannot access control: No such file or directory
[oracle@system1 ~]$ rm -rf /u01/app/oracle/flash_recovery_area/orcl/control02.ctl
SQL> ! rman target /
RMAN> backup current controlfile to destination '/u01';
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 09-MAY-16
channel ORA_DISK_1: finished piece 1 at 09-MAY-16
piece handle=/u01/ORCL/backupset/2016_05_09/o1_mf_ncnnf_TAG20160509T162824_cm0vbkv4_.bkp tag=TAG20160509T162824 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 09-MAY-16
RMAN> exit
Recovery Manager complete.
SQL> shut immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 109054336 bytes
Database Buffers 306184192 bytes
Redo Buffers 6094848 bytes
SQL> !rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 9 16:30:05 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore controlfile to '/u01/app/oracle/oradata/orcl/control001.' from '/u01/ORCL/backupset/2016_05_09/o1_mf_ncnnf_TAG20160509T162824_cm0vbkv4_.bkp' ;
Starting restore at 09-MAY-16
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-MAY-16
RMAN> exit
SQL> alter system set control_files=/u01/app/oracle/oradata/orcl/control001. Scope=spfile;
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 100665728 bytes
Database Buffers 314572800 bytes
Redo Buffers 6094848 bytes
Database mounted.
SQL>
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
So, Now , I tried Another command, and Again Facing an Error
SQL> recover database using backup controlfile;
ORA-00279: change 792051 generated at 05/09/2016 16:23:23 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_09/o1_mf_1_4_%u_.arc
ORA-00280: change 792051 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_09/o1_mf_1_4_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_09/o1_mf_1_4_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs ;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
SQL> !rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 9 16:40:04 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1433520325, not open)
RMAN> recover database ;
Starting recover at 09-MAY-16
Starting implicit crosscheck backup at 09-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 09-MAY-16
Starting implicit crosscheck copy at 09-MAY-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 09-MAY-16
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/oradata/orcl/redo01.log
archived log file name=/u01/app/oracle/oradata/orcl/redo01.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-MAY-16
RMAN> exit
Recovery Manager complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
Tickiting Tool for DBA's
How to search for Specific text in Alert Log
vi and vim have powerful searching capabilities because they bring to bear the richness of regular expressions.
To search for the next occurence of the text ‘ORA’ from the current cursor position, type:
/ORA
Search backward from the current position by using ' ? ' instead of / in the command.
Once you have searched for something, you can find the next occurrence by pressing n or the previous occurrence with N .
Searching in vi/vim is enhanced with regular expressions.
For example, to find the next occurence of the text ‘ORA’ that occurs at the beginning of a line, use:
/^ORA
Monday 9 May 2016
Unix related Oracle DBA Interview
Questions:
1. How do you see how many instances are running?
2. How do you automate starting and shutting down of databases in Unix?
3. You have written a script to take backups. How do you make it run automatically every week?
4. What is OERR utility?
5. How do you see Virtual Memory Statistics in Linux?
6. How do you see how much hard disk space is free in Linux?
7. What is SAR?
8. What is SHMMAX?
9. Swap partition must be how much the size of RAM?
10. What is DISM in Solaris?
11. How do you see how many memory segments are acquired by Oracle Instances?
12. How do you see which segment belongs to which database instances?
13. What is VMSTAT?
14. How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?
15. How do you remove Memory segments?
16. What is the difference between Soft Link and Hard Link?
17. What is stored in oratab file?
18. How do you see how many processes are running in Unix?
19. How do you kill a process in Unix?
20. Can you change priority of a Process in Unix?
Saturday 7 May 2016
Installation/Configuration Interview Questions
Expected answer: The answer here should show an understanding of separation of redo and rollback, data and indexes and isolation os SYSTEM tables from other tables. An example would be to specify that at least 7 disks should be used for an Oracle installation so that you can place SYSTEM tablespace on one, redo logs on two (mirrored redo logs) the TEMPORARY tablespace on another, ROLLBACK tablespace on another and still have two for DATA and INDEXES. They should indicate how they will handle archive logs and exports as well. As long as they have a logical plan for combining or further separation more or less disks can be specified.
Expected Answer: adjust kernel parameters or OS tuning parameters in accordance with installation guide. Be sure enough contiguous disk space is available.
Expected Answer: Check to make sure that the archiver isn’t stuck. If archive logging is turned on during install a large number of logs will be created. This can fill up your archive log destination causing Oracle to stop to wait for more space.
Expected answer: INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file
Expected answer: SQLNET.ORA, TNSNAMES.ORA
Expected answer: SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.
Expected answer: You must use root first.
Expected answer: Never
Expected answer: At least 2 on separate disk spindles. Be sure they say on separate disks, not just file systems.
Expected answer: You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.
Expected answer: At least 7, see disk configuration answer above.