How to calculate optimal SYSAUX tablespace size ON ORACLE DATABASE?

we have oracle provided script to check optimal  sysaux tablespace size.
script located under rdbms/admin

$ORACLE_HOME/rdbms/admin/utlsyxsz.sql;


SQL> @utlsyxsz.sql;


This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: awr.log


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
12:01:02 on Sep 27, 2016 ( Tuesday ) in Timezone -05:00


DB_NAME     HOST_PLATFORM                             INST STARTUP_TIME      PAR
----------- ---------------------------------------- ----- ----------------- ---
* TEST      servername                1 11:22:14 (07/17)  NO

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size:                         180.9 MB
|
| Total size of SM/AWR                        76.0 MB (  42.0% of SYSAUX )
| Total size of SM/OPTSTAT                    71.8 MB (  39.7% of SYSAUX )
| Total size of SM/ADVISOR                    10.3 MB (   5.7% of SYSAUX )
| Total size of LOGMNR                         5.9 MB (   3.3% of SYSAUX )
| Total size of SM/OTHER                       4.8 MB (   2.6% of SYSAUX )
| Total size of EM_MONITORING_USER             1.6 MB (   0.9% of SYSAUX )
| Total size of LOGSTDBY                       0.9 MB (   0.5% of SYSAUX )
| Total size of XSOQHIST                       0.8 MB (   0.4% of SYSAUX )
| Total size of AO                             0.8 MB (   0.4% of SYSAUX )
| Total size of STREAMS                        0.5 MB (   0.3% of SYSAUX )
| Total size of JOB_SCHEDULER                  0.4 MB (   0.2% of SYSAUX )
| Total size of TSM                            0.3 MB (   0.1% of SYSAUX )
| Total size of Others                         7.1 MB (   3.9% of SYSAUX )
|

~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)
| in SYSAUX, we need the following values:
|
|     - Interval Setting (minutes)
|     - Retention Setting (days)
|     - Number of Instances
|     - Average Number of Active Sessions
|     - Number of Datafiles

|
| For 'Interval Setting',
|   Press <return> to use the current value:     60.0 minutes
|   otherwise enter an alternative
|
Enter value for interval: 30

**   Value for 'Interval Setting': 30

|
| For 'Retention Setting',
|   Press <return> to use the current value:   7.00 days
|   otherwise enter an alternative
|
Enter value for retention: 7

**   Value for 'Retention Setting': 7

|
| For 'Number of Instances',
|   Press <return> to use the current value:   1.00
|   otherwise enter an alternative
|
Enter value for num_instances: 1

**   Value for 'Number of Instances': 1

|
| For 'Average Number of Active Sessions',
|   Press <return> to use the current value:   0.00
|   otherwise enter an alternative
|
Enter value for active_sessions: 10

**   Value for 'Average Number of Active Sessions': 10

| ***************************************************
| Estimated size of AWR:                     310.5 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        30 minutes
|           Retention -      7.00 days
|       Num Instances -         1
|     Active Sessions -     10.00
|           Datafiles -         7
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optimizer Stat History Space Estimation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Optimizer Statistics History
| we need the following values:
|
|     - Number of Tables in the Database
|     - Number of Partitions in the Database
|     - Statistics Retention Period (days)
|     - DML Activity in the Database (level)

|
| For 'Number of Tables',
|   Press <return> to use the current value:    663.0
|   otherwise enter an alternative <a positive integer>
|
Enter value for number_of_tables:

**   Value for 'Number of Tables': 663

|
| For 'Number of Partitions',
|   Press <return> to use the current value:   0.00
|   otherwise enter an alternative <a positive integer>
|
Enter value for number_of_partitions:

**   Value for 'Number of Partitions': 0

|
| For 'Statistics Retention',
|   Press <return> to use the current value:     31.0 days
|   otherwise enter an alternative <a positive integer>
|
Enter value for stats_retention:

**   Value for 'Statistics Retention': 31

|
| For 'DML Activity',
|   Press <return> to use the current value:        2 <medium>
|   otherwise enter an alternative <1=low, 2=medium, 3=high>
|
Enter value for dml_activity:

**   Value for 'DML Activity': 2

| ***************************************************
| Estimated size of Stats history             23.8 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables -     663
|                        Indexes -   1,313
|                        Columns -   4,282
|                     Partitions -       0
|          Indexes on Partitions -       0
|          Columns in Partitions -       0
|        Stats Retention in Days -      31
|          Level of DML Activity -  Medium
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR:                     310.5 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        30 minutes
|           Retention -      7.00 days
|       Num Instances -         1
|     Active Sessions -     10.00
|           Datafiles -         7
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history             23.8 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables -     663
|                        Indexes -   1,313
|                        Columns -   4,282
|                     Partitions -       0
|          Indexes on Partitions -       0
|          Columns in Partitions -       0
|        Stats Retention in Days -      31
|          Level of DML Activity -  Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|   For all the other components, the estimate
|   is equal to the current space usage of
|   the component.
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
| ***************************************************
| Summary of SYSAUX Space Estimation
| ***************************************************

| Est size of SM/ADVISOR                      10.3 MB
| Est size of LOGMNR                           5.9 MB
| Est size of SM/OTHER                         4.8 MB
| Est size of EM_MONITORING_USER               1.6 MB
| Est size of LOGSTDBY                         0.9 MB
| Est size of XSOQHIST                         0.8 MB
| Est size of AO                               0.8 MB
| Est size of STREAMS                          0.5 MB
| Est size of JOB_SCHEDULER                    0.4 MB
| Est size of TSM                              0.3 MB
| Est size of Others                           7.1 MB

| Est size of SM/AWR                         310.5 MB
| Est size of SM/OPTSTAT                      23.8 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size:               367.4 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report

MS SQL server mirroring concepts

All about SQL server mirroring
pre-request for SQL server mirroring

1.    mirroring available after 2005

2.     Primary Db should be in full recovery mode

3.     mirrored Db in recovery mode

4.     Primary & Mirrored Db server has same version & same Sservic pack same version (either Standard or Enterprise)

5.     witness server has been  anything sql express also

6.     Server should be on same domain name. becoz it communicates with AD

7.     you cannot use this as a secondary read-only database to achieve scalability

8.     Database name same on both servers

9.     Operating mode

High-performance mode
Transaction safety
Witness state
OFF
NULL (no witness)2
High-safety mode without automatic failover
FULL
NULL (no witness)
High-safety mode with automatic failover1
FULL
CONNECTED

10) to check the mirror state

SELECT mirroring_safety_level_desc, mirroring_witness_name, mirroring_witness_state_desc FROM sys.database_mirroring
11) protection modes
high performance
high safety
high safety with automatic fail-over


12)Restrictions for DB mirroring:
Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases.
A mirrored database cannot be renamed during a database mirroring session.
Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
Database mirroring is not supported with either cross-database transactions or distributed transaction
Maximum 10 databases per instance can support on a 32-bit system.
b) Database mirroring is not supported with either cross-database transactions or distributed transactions.


Advantages of mirroring include automatic fixing of corrupted pages and storage is not a SPOF


13)What is End Point? How u create end point?
An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.
Creation of an end point:-
Create endpoint <endpoint name> State=started/stopped/disabled
as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness)

14)What is the default of end points (port numbers) of principal, mirror and witness servers? How to find the Port numbers?
The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024.
To Find Port Number:- SELECT name, port FROM sys.tcp_endpoints

15)In which Recovery model we can use in Mirroring?
In mirroring the principal and mirror databases are used only full recovery model

16)What is the syntax to stop the Database Mirroring?
Alter database <database name> set partner off

17)How to monitoring Mirroring?

There are six methods are available for monitoring the Database Mirroring
a) Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.
To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.
b) SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.
c) SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occurs it will be logged to SQL Server log and Windows event log.
d) Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.
e) Profiler:- Profiler many events are providing the status of the Database mirroring
f) System Stored Procedures:-
? sp_dbmmonitoraddmonitoring
? sp_dbmmonitorchangemonitoring
? sp_dbmmonitorhelpmonitoring
? sp_dbmmonitordropmonitoring


18)What are the Database Mirroring states?

1) SYNCHRONIZING:-
The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.
At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
2) SYNCHRONIZED:-
When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.
If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.
If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
3) SUSPENDED:-
The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.
  A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session
  SUSPENDED is a persistent state that survives partner shutdowns and startups.
4) PENDING_FAILOVER:-
  This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.
  When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
5) DISCONNECTED:-
  The partner has lost communication with the other partner

Database mirroring provides protection at the database level, whereas a cluster solution provides protection at the SQL Server instance level

19)What are the Disadvantages of Database Mirroring?

Potential data lost is possible in asynchronous operation mode. RTO will vary and depend on several factors, such as propagation interval time and bandwidth speed.
It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.
Automatic server failover may not be suitable for application using multiple databases.

20)query to check mirroring status.



select  mirroring_state,mirroring_state_desc,mirroring_role,mirroring_role_desc,mirroring_safety_level_desc,mirroring_partner_name,mirroring_witness_name  from sys.database_mirroring;

ORA-19588: archived log RECID 2986343 STAMP 4532923 is no longer valid

ORA-19588: archived log RECID 2986343 STAMP 4532923 is no longer valid

check if the SEQ log backed up

LIST BACKUP OF ARCHIVELOG FROM SEQUENCE <SEQ Number>,if its backed up you can ignore it

cause:
Root cause could be due to 2 simultaneous backup sessions where one refers to and the other had already backed up and not needed to be backed up.

when the execution plan change for oracle database for single SQL?

How Execution Plans Can Change occur by the below reasons:

1. Schema changes (usually changes in indexes & table ) between the two operations.
2. Different Costs
3. Even if the schema are the same, the optimizer can choose different execution plans when the costs are different. Some factors that affect the costs include the following:

• Data volume and statistics
• Bind variable types and literal values

what are the various modes to run ADDM in oracle database?

running modes for ADDM

DB
instance
partial

How to disable/remove AWR snapshot genration and ADDM job from oracle database

Disabling Oracle Performance Pack

   Disabling Performance Tuning & Diagnostic pack:

Method 1:  Revoking Management Pack Access through OEM
Home Page>setup>Management Pack Access <Remove Access> <Apply>




Method 2:
  Step 1

Disable Automatic/manual AWR snapshots                    

SQL> @dbmsnoawr.plb  <This Package is downloaded from My Support Article ID 436386.1>

SQL> begin dbms_awr.disable_awr(); end;

Impact:

·         Stops Automatic AWR snapshots
·         Does not allow taking manual snapshot.
·         All  <report>.sql files will work for old snaps

Step 2
To Disable Automatic ADDM
ALTER SYSTEM set "_addm_auto_enable"=false;
(Dynamic parameter)

Impact:
·         Disables automatic ADDM which gets run automatically after every Automatic AWR snapshots
CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables ADDM.

PostgreSQL database basic administration commands

1. How to change PostgreSQL root user password?

$ /usr/local/pgsql/bin/psql postgres postgres
Password: (oldpassword)
# ALTER USER postgres WITH PASSWORD 'tmppassword';

$ /usr/local/pgsql/bin/psql postgres postgres
Password: (tmppassword)

Changing the password for a normal postgres user is similar as changing the password of the root user. Root user can change the password of any user, and the normal users can only change their passwords as Unix way of doing.

# ALTER USER username WITH PASSWORD 'tmppassword';

2. How to setup PostgreSQL SysV startup script?

$ su - root

# tar xvfz postgresql-8.3.7.tar.gz

# cd postgresql-8.3.7

# cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql

# chmod a+x /etc/rc.d/init.d/postgresql

3. How to check whether PostgreSQL server is up and running?

$ /etc/init.d/postgresql status
Password:
pg_ctl: server is running (PID: 6171)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
[Note: The status above indicates the server is up and running]

$ /etc/init.d/postgresql status
Password:
pg_ctl: no server running
[Note: The status above indicates the server is down]

4. How to start, stop and restart PostgreSQL database?

# service postgresql stop
Stopping PostgreSQL: server stopped
ok

# service postgresql start
Starting PostgreSQL: ok

# service postgresql restart
Restarting PostgreSQL: server stopped
ok

5. How do I find out what version of PostgreSQL I am running?

$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)

test=#

6. How to create a PostgreSQL user?

There are two methods in which you can create user.

Method 1: Creating the user in the PSQL prompt, with CREATE USER command.

# CREATE USER ramesh WITH password 'tmppassword';
CREATE ROLE

Method 2: Creating the user in the shell prompt, with createuser command.

$ /usr/local/pgsql/bin/createuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

7. How to create a PostgreSQL Database?

There are two metods in which you can create two databases.

Method 1: Creating the database in the PSQL prompt, with createuser command.

# CREATE DATABASE mydb WITH OWNER ramesh;
CREATE DATABASE
Method 2: Creating the database in the shell prompt, with createdb command.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh
CREATE DATABASE
* -O owner name is the option in the command line.

8. How do I get a list of databases in a Postgresql database?

# \l  [Note: This is backslash followed by lower-case L]
List of databases
Name | Owner | Encoding
----------+----------+----------
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

9. How to Delete/Drop an existing PostgreSQL database?

# \l
List of databases
Name | Owner | Encoding
----------+----------+----------
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

# DROP DATABASE mydb;
DROP DATABASE

10. Getting help on postgreSQL commands

\? will show PSQL command prompt help. \h CREATE will shows help about all the commands that starts with CREATE, when you want something specific such as help for creating index, then you need to give CREATE INDEX.

# \?

# \h CREATE

# \h CREATE INDEX

11. How do I get a list of all the tables in a Postgresql database?

# \d
On an empty database, you’ll get “No relations found.” message for the above command.

12. How to turn on timing, and checking how much time a query takes to execute?

# \timing — After this if you execute a query it will show how much time it took for doing it.

# \timing
Timing is on.

# SELECT * from pg_catalog.pg_attribute ;
Time: 9.583 ms

13. How To Backup and Restore PostgreSQL Database and Table?

We discussed earlier how to backup and restore postgres database and tables using pg_dump and psql utility.

14. How to see the list of available functions in PostgreSQL?

To get to know more about the functions, say \df+

# \df

# \df+

15. How to edit PostgreSQL queries in your favorite editor?

# \e
\e will open the editor, where you can edit the queries and save it. By doing so the query will get executed.

16. Where can I find the PostgreSQL history file?

Similar to the Linux ~/.bash_history file, postgreSQL stores all the sql command that was executed in a history filed called ~/.psql_history as shown below.

$ cat ~/.psql_history
alter user postgres with password 'tmppassword';
\h alter user
select version();
create user ramesh with password 'tmppassword';
\timing
select * from pg_catalog.pg_attribute;