Memory Size in ASMM

Here is How you know memory component size when use automatic storage memory managenent(ASMM)

Oracle Database 10g includes the Automatic Shared Memory Management feature which simplifies the SGA memory management significantly.

When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration. The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory.

DBA can check all memory component size with dynamic view V$SGA_DYNAMIC_COMPONENT

Dataguar Switchover Steps

1. Initiate the switchover operation on the primary database (On the original primary database)

- Verify database role

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
PRIMARY

- Switch database role from primary to standby database

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN WAIT;

Database altered.

- Shutdown database

SQL> SHUTDOWN IMMEDIATE

- Startup database

SQL> STARTUP

- Verify database role

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

- Start apply log process for standby database

SQL> ALTER DATABASE RECOVER MANAGE STANDBY DATABASE DISCONNECT;

2. Switch standby database to primary database (On the original standby database)
- Verify database role

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

- Switch database role from standby to primary database

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

- Shutdown database

SQL> SHUTDOWN IMMEDIATE

- Startup database

SQL> STARTUP

- Verify database role

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
PRIMARY

Creating a Function based Index

-- syntax for creating a function-based index:

CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];


-- for example:

CREATE INDEX supplier_metrics
ON supplier (UPPER(supplier_city));

-- to be sure that the Oracle optimizer uses this index
-- when executing your SQL statements, be sure that
-- UPPER(supplier_city) does not evaluate to a NULL value.
-- To ensure this, add UPPER(supplier_city) IS NOT NULL to
-- your WHERE clause as follows:

SELECT supplier_id, supplier_city, UPPER(supplier_city)
FROM supplier
WHERE UPPER(supplier_city) IS NOT NULL
ORDER BY UPPER(supplier_city);