Server Maintenance Tasks for a DBA

To delete files older than 60 days

$  find . -name "*.gz" -depth -mtime +60 -exec rm {} \;

To list files dated May 18

ls -lrt | grep 'May 18' | awk -F' ' '{print$9}'

To delete files dated May 18

rm -rf `ls -lrt | grep ‘May 18' | awk -F' ' '{print$9}'`

To list files of Month May

$  ls -lrt | grep 'May' | awk -F' ' '{print$9}'

$  ls -lrt *.trc|grep 'May'|xargs rm -rf  {}\;

Things need to check during - - Server reboot

Suppose you are working on Dataguard setup or normal Stand-Alone Database,

Following steps mandatory just take backup of following three steps and store it in separate notepad.

Once the server reboot activity completed.. then cross check once which was taken before server reboot.

$  ps -ef|grep pmon

$  ps -ef|grep pmon|wc -l

$  ps -ef|grep inh (OR) ps -ef|grep tns

$  ps -ef|grep inh|wc -l

$  ps -ef |grep d.bin

$  hostname

$  date

$  uname -a

$  cat /etc/oratab  /  cat /var/opt/oracle/oratab

$  uptime

$  who -b

$  df -h  / df –gt

Data Guard Database  Shutdown Procedure

Login as oracle

source the environment

$  sqlplus “/ as sysdba”

SQL >alter database recover managed standby database cancel;

                                                                                ------> to cancel MRM mode

SQL> shutdown immediate;

SQL> exit

Data Guard Db Startup Procedure

$  Sqlplus “/ as sysdba”

SQL> startup nomount;

SQL> alter database mount standby database;

SQL>  recover managed standby database disconnect from session;

                                                                ----------->To put it in MRM MODE

SQL>  exit;



See Also

 Health Check – When the Application Users complaints for an issue, what to do ?

Health Check – When the Application Users complaints for an issue

FREE Sign-Up with Gmail and Receive Pdf's and Updates via Mail -Click Here

Check-List for Performing Database health checks, when there is an issue reported by Application users.

1. Check the Database details

2. Monitor the consumption of resources

3. Check the Alert Log

4. Check Listener log

5. Check Filesystem space Usage

6. Generate AWR Report

7. Generate ADDM Report

8. Finding Locks,Blocker Session and Waiting sessions in a oracle database

9. Check for alerts in OEM

Hands-On

1.  Monitor the consumption of resources :-

select * from v$resource_limit where resource_name in ('processes','sessions');

The v$session views shows current sessions (which change rapidly),

while the v$resource_limit shows the current and maximum global resource utilization for some system resources.

2. Check the Alert Log :-

$locate alert_<ORACLE_SID>

--- OR ---

UNIX/Linux command to locate the alert log file

-----------------------------------------------

$ find / -name 'alert_*.log' 2> /dev/null

Open the Alert log using VI Editor :

vi <alert_log_location_of_the_above_output>

“press shift+g”

?ORA-   ---> press enter key

press 'n' to check backwards/up side and 'N' for forward/down side search.

:q! --and press enter, for exiting vi editor

3. Check Listener log :-

$locate listener.log

Or

$lsnrctl status

4. Check Filesystem space Usage :-

$ df -h (Linux / UNIX)

$ df -g (AIX)

5. Finding Locks,Blocker Session and Waiting sessions in a oracle database :-

SQL>Select * from v$lock;

A fast way to check blocking/waiting situations

SQL > SELECT * FROM v$lock WHERE block > 0 OR request > 0;

6. Check for alerts in OEM :-

Login to Oracle Enterprise Manager with valid username and password

click on "Alerts" tab

then select the below tabs one by one to see the alerts generated

Targets Down/Critical/Warning/Errors/


See also 

Server Maintenance Tasks as a DBA  Click Here 

 Follow us on Facebook..!!

See Also : 

How to Prepare for DBA Interview

General Tips to Prepare for an Oracle DBA Job Interview

How to Convert Physical Standby to Snapshot Standby and Vice Versa ( using DGMGRL )

In DataGuard Broker, this process is very simple, you just type a few commands, re-start your DB and you are ready to test this amazing feature!!!

What Happens in Snapshot Standby Mode ??

The Redo data will continue to be received by the database while it is operating as a snapshot standby database, but it will not be applied until the snapshot standby is converted back into a physical standby database.

Note : The Flashback Database feature is required to create a snapshot standby database.

If Flashback database is disabled, it is automatically enabled during conversion to a snapshot standby database.

The Snapshot Standby database in turn uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to.

So we need to keep in mind that whatever limitations are present in the Flashback database technology, the same will also be inherent in the Snapshot database feature.

The broker automatically restarts the database to the mounted state if it had been opened with Flashback Database disabled. No user action is required.

Step 1- Connect to DG Broker

$ dgmgrl

DGMGRL> connect sys
 

DGMGRL> show configuration

Step 2- Convert the DB.

After the conversion takes place, internally a guaranteed restore point has been created and the database has been opened in read-write mode

DGMGRL> convert database 'STDBY' to snapshot standby;

Step 3- Verify that the database was successfully converted by issuing the

SHOW CONFIGURATION command:

DGMGRL> show configuration

Configuration - prod_dg
 

  Protection Mode: MaxPerformance
 

Databases:

    PROD  - Primary database

    STDBY – Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status: SUCCESS

Now Let’s see,

How to Convert Snapshot Standby back to Physical Standby with DGMGRL

The Redo data received while the database was a snapshot standby database will be automatically applied when Redo Apply is started.

Note:

A snapshot standby database must be opened at least once in read-write mode before it can be converted into a physical standby database.

Step 1- Connect to DG Broker.

You should be on the primary server to complete this step.

$ dgmgrl

DGMGRL > connect sys

Step 2- Convert the DB back to physical

DGMGRL > convert database 'stdby' to physical standby;

 Step 3- Verify the status, that the database was successfully converted by issuing the SHOW CONFIGURATION command:

DGMGRL> show configuration

Configuration - prod_dg
 

  Protection Mode: MaxPerformance
 

Databases:

    PROD  - Primary database

    STDBY – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status: SUCCESS

NOTE:

You may get the following message during the conversion process:

“Converting database "stdby" to a Physical Standby database, please wait...

Operation requires shutdown of instance "STDBY" on database "stdby"

Shutting down instance "STDBY"...

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "STDBY" on database "stdby"

Starting instance "STDBY"...

Unable to connect to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:

        start up and mount instance "STDBY" of database "stdby“

If so, then connect to the STDBY and startup and mount the instance:

SQL> startup mount

Click here to Join Our Blog and Stay Updated