SQL Server database capacity planning notes

It is best not to rely on auto-growth of the database because it negatively affects the performance and also causes database files to become fragmented because during auto-growth large chunks of disk space are being allocated.

One can set an auto-growth just as a way to prevent any issues but still monitor database growth proactively. Also it is best to set auto-growth percentage to a reasonable number in order to keep the database from using more space than it needs.

The best time to grow a database is during offline maintenance window. Allocate new space to the database files (mdf and ldf), close the dtaabase and defragment the filesystem on which database resides.

Rebuilding indexes also helps to combat fragmentation by deleting an old index and building a fresh one, leaving space for new data.

How to grant a user read-only rights to a schema in Oracle 11g

There are two ways to grant a user read-only privileges on a single schema in Oracle:

1) Retrieve all the objects and grant SELECT privileges on each object to the user in question

2) Create a role and to that role grant SELECT privileges on each object in the schema and then grant that role to the user.

Script below can spool all the necessary GRANT statements to a file, given username of the user (or role) to grant priileges to and given the nschema name:

set pages 0;
set linesize 100;
set feedback off; 
set verify off; 

spool C:\Test\GET_ALL_SCHEMA_OBJECTS.sql

SELECT 'GRANT SELECT ON ' || table_name || ' TO &&new_user;' FROM dba_tables WHERE owner=upper('&&schema_name');
 
SELECT 'GRANT SELECT ON ' || view_name || ' TO &&new_user;' FROM dba_views WHERE owner=upper('&&schema_name');
 
set serveroutput off;
spool off;

How to retrieve all permissions granted to a particular user

Below is the query that will give you all the permissions granted to a particular user for the current database:

SELECT class_desc [Permission Level], CASE WHEN class = 0 THEN DB_NAME()
         WHEN class = 1 THEN OBJECT_NAME(major_id)
         WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
  , USER_NAME(grantee_principal_id) [User]
  , permission_name [Permission]
  , state_desc
FROM sys.database_permissions
WHERE USER_NAME(grantee_principal_id)='user1'

ORA-00600: internal error code, arguments: [13013], [5001], [121347], [12771893], [1], [12771893], [17]. Non-fatal internal error happenned while SMON was doing logging scn->time mapping.

Error: ORA-00600: internal error code, arguments: [13013], [5001], [121347], [12771893], [1], [12771893], [17], [], [], [], [], []. Non-fatal internal error happenned while SMON was doing logging scn->time mapping.

Details: Error occurred in the alert log

Cause: Occurs when smon_scn_time has been corrupted

Solution:. We have 2 choices here

1. Restore and recover the system tablespace
or
2. Recreate smon_scn_time

More on the solution here

ORA-24324: service handle not initialized

Error: ORA-24324: service handle not initialized

Details: This error occurs when starting up the database by executing STARTUP NOMOUNT or STARTUP MOUNT from SQL*Plus

Cause: Occurs when you try to startup from the same SQL*Plus session you did the shutdown from

Solution:Exit and re-enter SQL*Plus after shutdown and before executing startup again.

Oracle Data Guard 11G: Steps to switch Primary to Standby

Analyzing physical RAM on the server to determine whether an increase needed

Check the total physical RAm on the server by running

lsattr -El sys0 -a realmem

Result would look something like this:

> lsattr -El sys0 -a realmem
realmem 25165824 Amount of usable physical memory in Kbytes False

The go through each end every database running on the server and do the following steps:

1. Login to sqlplus
2. Check MEMORY_TARGET parameter:

   SQL>SHOW PARAMETER MEMORY_TARGET
   

   If value is not 0, then use it, but if it is 0, then use sum of values of SGA and PGA

   SQL>SHOW PARAMETER SGA_TARGET
   

   and

   SQL>SHOW PARAMETER PGA_AGGREGATE_TARGET
   

3. After adding up all the values for each and every running database, calculate the percentage currently taken up. If it is no more than 50-70%, no change needed.