Title Changed -- reflects my journey

 

The title "Evolving Architect: Combining Data, Design, and Project Management" captures my journey as I grow from data-centric expertise to a broader role that encompasses infrastructure design and project management. It reflects a dynamic progression and emphasizes a well-rounded skill set, likely resonating with all of you readers interested in comprehensive IT and infrastructure insights. This title is concise yet reflective of my expanded focus across multiple facets of IT architecture.

My Technologies Journey

 It’s fascinating how my passion for databases has shaped my journey. From the early days of exploring data structures to understanding complex database systems, my love for this field has only deepened. I continue to marvel at the rapid technological advances not only in databases but across various domains. Each innovation inspires me, pushing me to stay curious and engaged with emerging trends that can transform the way we manage and utilize data.

As I reflect on my personal and professional growth, I realize that my skills have significantly expanded. I’ve transitioned from focusing solely on databases to embracing broader concepts like infrastructure architecture and design. This evolution has opened up new horizons, allowing me to approach challenges with a more holistic mindset. I’m excited to dive deeper into these areas, exploring how they intersect with my foundational knowledge of databases.

With this expanded skill set in mind, I believe it’s time to update my blog’s heading to better reflect my current interests and expertise. As I tackle infrastructure architecting and project management, I want my content to resonate with those who share a similar passion for technology and innovation. While I’m considering various headings, I’m open to suggestions and would love to hear any ideas that could help capture this exciting new chapter of my journey.

Briefly: ZPTR

 Zero Trust Packet Routing is an advanced cybersecurity concept that applies the Zero Trust framework to network traffic. Unlike traditional models that rely on perimeter defenses, Zero Trust assumes that no traffic, internal or external, is inherently trusted. It verifies every packet at each stage to ensure authenticity, integrity, and compliance with security policies.

Central to this approach is the principle of least-privilege access and rigorous identity verification. Every packet, even from authenticated users, undergoes inspections based on user identity, device posture, and contextual factors like location and time. This continuous verification employs mechanisms such as segmentation and context-based routing decisions.

Key Components of Zero Trust Packet Routing:

Microsegmentation

Context-Aware Routing

Identity and Access Management (IAM)

Advanced Packet Filtering

Encryption and Data Integrity

SQL Server on AWS

  SQL Server on AWS

Good Option available to install  MS SQL server binaries on EC2.

Advantages:

Full Control: You can launch an EC2 instance and install SQL Server manually. This gives you more control over configurations.

Customization: Choose your operating system and SQL Server version.

Licensing: You can either bring your own license (BYOL) or use AWS's license-included options.

Data Loss Prevention (DLP) Tools

Data Loss Prevention (DLP) tools are crucial for protecting sensitive information/ data from unauthorized access, misuse, or loss. Compliance with Industry Standard security requirements  are met with these tools. They monitor and control data movement across various systems.

Some prominent DLP tools:

Symantec Data Loss Prevention (DLP): Offers comprehensive data protection across endpoints, networks, and storage. It includes advanced content inspection and contextual analysis to safeguard sensitive data.

McAfee Total Protection for Data Loss Prevention: Provides robust protection for sensitive data with policy enforcement across endpoints, networks, and cloud environments. It integrates well with McAfee’s broader security solutions.

Forcepoint Data Loss Prevention: Utilizes behavioral analytics to detect and prevent data breaches. It focuses on user activity and data usage patterns to enforce security policies.

Microsoft 365 Data Loss Prevention: Integrated into Microsoft 365, it offers protection for data within Office apps, email, and OneDrive. It provides policy templates and real-time monitoring capabilities.

Digital Guardian Data Loss Prevention: Specializes in protecting intellectual property and sensitive data across endpoints, networks, and cloud environments with flexible policy management.

Trend Micro Data Loss Prevention: Provides endpoint and network DLP solutions with advanced threat intelligence and integration with Trend Micro’s broader security suite.

IBM Security Guardium: Focuses on database activity monitoring and data protection, offering robust analytics and real-time alerts to safeguard sensitive data.

Varonis Data Security Platform: Monitors data access and usage across file systems and email systems, providing insights into potential risks and ensuring compliance with data protection regulations.

Proofpoint Enterprise DLP: Delivers content inspection and monitoring to protect data across email, cloud, and endpoints, with a focus on preventing data breaches and compliance violations.

Check Point Data Loss Prevention: Integrates with Check Point’s security infrastructure to provide policy enforcement and data protection across various platforms and applications.

These few listed tools are versatile to ensure that sensitive information or as we say data remains protected and complaint with Industry regulations.

Sharing a script that logs all long running queries, kills them :) and sends out an email alert to my inbox. I generally modify it according to the production requirement. In my environment there are a couple of production servers where I would setup this script to only alert and Not kill any query.

Please double test before implementing in realtime production environment.Follow the general thumb rule to implement all changes/scripts from lower to higher env. :) this script comes with disclaimer that person using has complete ownership for it’s results.

Ensure postfix installed and configured for emails to work. The script works for postgre user since postgre has privs over all databases in the server

############################

if [ `whoami` != "postgres" ]; then

exit 0;

fi

# selecting non-idle queries which are running since at least 6 minutes.

psql -c "select pid, client_addr, query_start, current_query from pg_stat_activity

where current_query != '<IDLE>' and current_query != 'COPY' and current_query != 'VACUUM' and query_start + '6 min'::interval < now()

and substring(current_query, 1, 11) != 'autovacuum:'

order by query_start desc" > $LOGFILE

NUMBER_OF_STUCK_QUERIES=`cat $LOGFILE | grep "([0-9]* row[s]*)" | sed 's/(//' | awk '{ print $1}'`

if [ $NUMBER_OF_STUCK_QUERIES != 0 ]; then

# Getting the first column from the output discarding alphfanumeric values (table elements in psql's output).

STUCK_PIDS=`cat $LOGFILE | sed "s/([0-9]* row[s]*)//" | awk '{ print $1 }' | sed "s/[^0-9]//g"`

for PID in $STUCK_PIDS; do

echo -n "Cancelling PID $PID ... " >> $LOGFILE

# "t" means the query is successfully cancelled.

SUCCESS=`psql -c "SELECT pg_cancel_backend($PID);" | grep " t"`

if [ $SUCCESS ]; then

SUCCESS="OK.";

else

SUCCESS="Failed.";

fi

echo $SUCCESS >> $LOGFILE

done

cat $LOGFILE | mail -s "Stuck PLpgSQL processes detected and killed that were running over 6 minutes." youremail@whatever.com;

fi

rm $LOGFILE

#######################################

MERGE Feature in PostgreSQL

From PostgreSQL 9.1, onwards user can implement the feature of Merge using the writable CTE (Common Table Expressions)

WITH  provides a way to write auxiliary statements for use in a larger query. This can be thought of as defining temporary tables that exist just for one query.Each auxiliary statement in a WITH clause can be a SELECT,INSERT,UPDATE or DELETE and the clause WITH witself is attached to a primary statement that can also cause a SELECT,INSERT,UPDATE or DELETE

Created 2 tables and insert some data into it. Now merge the 2 tables 

[root@ip--- ~]# psql tejidatabase

psql (9.2.4.8)

Type "help" for help.

tejidatabase=# select * from testmerge;

 pid | age |  name  

-----+-----+--------

   2 |  48 | RAM

   4 |  61 | SHYAM

   6 |  85 | SONIA

   8 |  44 | RAHUL

  10 |  34 | MAMTA

  12 |  45 | SURJIT

  14 |  21 | ISHIKA

  16 |  19 | IPSA

(8 rows)

tejidatabase=# select * from merge2;

 pid | age |  name  

-----+-----+--------

  18 |  56 | YASUDA

   8 |   0 | RAHUL

  14 |   5 | ISHIKA

(3 rows)

tejidatabase=# WITH upsert as (update merge2 m set age=d.age+100 ,name=d.name from testmerge d where m.pid=d.pid RETURNING m.*) insert into merge2 select a.pid,a.age,'NEW' from testmerge a where a.pid not in ( select b.pid from upsert b);

INSERT 0 6

tejidatabase=# select * from merge2;

 pid | age |  name  

-----+-----+--------

  18 |  56 | YASUDA

   8 | 144 | RAHUL

  14 | 121 | ISHIKA

   2 |  48 | NEW

   4 |  61 | NEW

   6 |  85 | NEW

  10 |  34 | NEW

  12 |  45 | NEW

  16 |  19 | NEW

(9 rows)

tejidatabase=# 

As you can see all the rows of test merge are now added in merge2 with name=’NEW’ and the matching pid of test merge and merge2, the ages have been added by 100.

The magic of Writable CTE which can make UPSERT in PostgreSQL

:-)

A few practical required PostgreSQL commands.

1. Check PostgreSQL server Status, stop and start.

Depending on what version of PostgreSQL

Check Service status ( # /etc/init.d/ppas-9.2 status

Stop Service (#  service ppas-9.2 stop)

Start Service (#  service ppas-9.2 stop)

[root@ip—~]# /etc/init.d/ppas-9.2 status

pg_ctl: no server running

[root@ip-~]# service ppas-9.2 start

Starting Postgres Plus Advanced Server 9.2: 

waiting for server to start.... done

server started

Postgres Plus Advanced Server 9.2 started successfully

[root@ip-~]# /etc/init.d/ppas-9.2 status

pg_ctl: server is running (PID: 8595)

/opt/PostgresPlus/9.2AS/bin/edb-postgres "-D" "/opt/PostgresPlus/9.2AS/data"

2. Confirm PostgreSQL version

# select version();

[root@ip- ~]# psql

psql (9.2.4.8)

Type "help" for help.

edb=# select version();

                                                      version                   

                                   

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

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

 EnterpriseDB 9.2.4.8 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 2

0080704 (Red Hat 4.1.2-52), 64-bit

(1 row)

edb=# 

3. List out the databases in your connected PostgreSQL server

# \l 

Note: above is slash with lowercase L

# select datname from pg_database;

edb=# select datname,datcollate,datctype,datconnlimit from pg_database;

   datname    | datcollate |  datctype  | datconnlimit 

--------------+------------+------------+--------------

 template1    | en_US.UTF8 | en_US.UTF8 |           -1

 template0    | en_US.UTF8 | en_US.UTF8 |           -1

 postgres     | en_US.UTF8 | en_US.UTF8 |           -1

 edb          | en_US.UTF8 | en_US.UTF8 |           -1

 tejidatabase | en_US.UTF8 | en_US.UTF8 |           -1

 testdb       | en_US.UTF8 | en_US.UTF8 |           -1

(6 rows)

edb=# \l

                                  List of databases

     Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   

--------------+----------+----------+------------+------------+-----------------------

 edb          | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | 

 postgres     | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | 

 tejidatabase | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | =Tc/postgres         +

              |          |          |            |            | postgres=CTc/postgres+

              |          |          |            |            | teji=CTc/postgres    +

              |          |          |            |            | testuser=CTc/postgres

 template0    | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/postgres          +

              |          |          |            |            | postgres=CTc/postgres

 template1    | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/postgres          +

              |          |          |            |            | postgres=CTc/postgres

 testdb       | teji     | UTF8     | en_US.UTF8 | en_US.UTF8 | 

(6 rows)

4. Last but not the least is to be able to get psql commands help and information.

# \?

Note: will show command prompt help

# \h SELECT

Note: will show details about the select command

This can be used for checking syntax of any psql commands.

edb=# \h select

Command:     SELECT

Description: retrieve rows from a table or view

Syntax:

[ WITH [ RECURSIVE ] with_query [, ...] ]

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]

    * | expression [ [ AS ] output_name ] [, ...]

    [ FROM from_item [, ...] ]

    [ WHERE condition ]

    [ GROUP BY expression [, ...] ]

    [ HAVING condition [, ...] ]

    [ WINDOW window_name AS ( window_definition ) [, ...] ]

    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]

    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]

    [ LIMIT { count | ALL } ]

    [ OFFSET start [ ROW | ROWS ] ]

    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]

    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]

    function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )

    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

edb=# 

TNS Listener Posion attack.

Oracle rushes out a security advisory with workarounds for a dangerous Database Server security flaw. It released Security Alert CVE-2012-1675
The vulnerability was orginally discovered by Joxean Koret in 2008. Oracle Database Server  versions  released in the past 13 years contain a bug that allows hackers to  monitor all data passing between the server and end users who are connected to it. Koret is said to have commented that Oracle learned of the bug in 2008 and indicated in a recent e-mail that it had no plans to fix current supported versions of the enterprise product because of concerns it could cause "regressions" in the code base.
Interestingly , the security alert provide customers with a number of technical measures to provide effective defense against this vulnerability in all deployment scenarios. It doesnot contain any patch. It is urging customers make the configuration changes documented in the  mentioned My Oracle Support Notes as soon as possible.

What is Oracle GoldenGate?

I was looking for a short & crisp answer to the question what is Oracle Golden Gate which could be called a defination of the product as well.

Oracle GoldenGate replication technology that’s now part of the Oracle framework, is a high-performance software application for real-time transactional change data capture, transformation, and delivery, offering log-based bidirectional data replication. The application enables you to ensure that your critical systems are operational 24/7, and the associated data is distributed across the enterprise to optimize decision-making.
Oracle GoldenGate filled a gap which we did not have - heterogeneous replication, replication from Oracle to different databases and vice versa.Oracle Goldengate can be used as a replication tool, ETL, and even as a DR solution.One can move data between similar or dissimilar supported Oracle versions, or one can move data between an Oracle database and a database of another type. GoldenGate supports the filtering, mapping, and transformation of data.

A quick checklist for Oracle upgrade 9i To 10gR2

1. Take a consistent backup of your database.
2. Install Oracle 10gR2
3. Check objects status in the db.
Compile invalid objects.
@?/rdbms/admin/ utlrp.sql
4. Create SYSAUX tablespace.
A manadatory requirement for Oracle 10G
5. Run utlu102i.sql script which checks the db readiness to upgrade to 10g.
This script will be available in oracle10g home. So specify complete path for oracle 10g rdbms/admin directory
6. Shutdown immediate the database.
7. Copy the parameter file & password file from existing home to new Oracle 10g Home.
8. Edit oratab and rerun oraenv to bring the changes in affect.
Oratab would be in /etc/oratab OR /var/opt/oracle/oratab
9. Startup the database in upgrade mode.
Command: startup upgrade
10. Now upgrade for which run catupgrd script. Spool the output.
This can take upto 40 mins to complete.
@?/rdbms/admin/catupgrd.sql
11. Now recompile invalid objects.
Run utlrp.sql
Remember to compare the status of objects with the one before upgrade. Current should be same or Less.
12. Check the status of upgrade
@?/rdbms/admin/utlu102s.sql
13. Alter / reset the parameter file and set the compatibility parameter.

The upgrade of Oracle 9i To 10g is completed successfully.

For further detailed reading refer Oracle documentation & metalink docs.
Useful read about Optimizer stats while upgrading:
http://optimizermagic.blogspot.com/2008/02/upgrading-from-oracle-database-9i-to.html

Metalink Doc: Complete Checklist for Manual Upgrades to 10gR2 [ID 316889.1]

Basic Properties of a Database Transaction ( Part 2)

Oracle enforces ACID by means of Undo Segments & Redo Logs.
Undo Segments help enforce-- atomicity and consistency .
Isolation requires undo segments & locks.
Durability is enforced with redo logs.

Oracle provides the following transaction isolation levels.
Read committed
Default transaction level is Read Committed.
Each query executed will only see the committed data. In other words an Oracle query will never read uncommitted data.
Serializable
Serializable transactions can only see those changes that were committed at the time the transaction began and those changes that are being made by the transaction itself.
Read-only
Read-only transactions see only those changes that were committed at the time the transaction began

Isolation levels can be set at the begining of the transaction and at session level.
Commands for transaction level setting of isolation.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

Commands for session level setting;
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

Basic Properties of a database Transaction (Part 1)

Basic properties of any databse transaction should be Atomicity, Consistency, Isolation, and Durability.
In short referred to as ACID.
All Oracle database transactions are ACID complaint . However, I believe that Oracle's Berkeley DB database is not ACID-compliant.
I need to research more on this statement though.
In short ACID refers to:
Atomicity
The entire sequence of actions must be either completed or aborted. The transaction cannot be partially successful.
Consistency
The transaction takes the resources from one consistent state to another.
Isolation
A transaction's effect is not visible to other transactions until the transaction is committed.
Durability
Changes made by the committed transaction are permanent and must survive system failure.

Migrating Oracle db from 32bit to 64bit

Database: Oracle 10gR2

Migrating or upgrading the database bitsize can be at times as challenging as upgrading the db version.
I have here a quick steps list to guide through this process.
The bitsize of the Database file is to be increased. So, obviously, we have the 64bit software installed / binaries placed in the OS which is 64bit as well. More addressable size would require more memory as well. So, the SGA & various pools sizes to be doubled. This doubling of basic memory parameters calculation works well! I have read this in one of the Oracle support notes though I am not sure which one.
Steps:
1. Update the following parameters
Job_queue_processes=0
_system_trig_enabled=False
Aq_tm_processes=0
2. Double the SGA & Pools parameters values (eg: sga_max_size,java_pool_size..)
3. Now with all the files in same location as the 32bit server, you can follow below:
Startup upgrade
4. Execute file @?\rdbms\admin\utlu102i.sql
5. Check for any errors. Troubleshoot accordingly.
In case error:

ORA-06553: PLS-801: internal error [56319]

and no other message/error seen, then first check all the pool parameters if values in ok
6. Shutdown immediate;
7. Startup
8. @?\rdbms\admin\utlirp.sql
9. Shutdown immediate
10. Now comment out / remove two parameters from pfile
aq_tm_processes
_system_trig_enabled
11. Startup
12. Check all required components are valid
Select comp_name,status,substr(version,1,10) as version from dba_registry;

If all Valid... Migration is completed.
now update job_queue_processes to the value required.

Interestingly, I have migrated more than 10 databases bitsizes. First couple of them were cheese maybe since Olap was not installed.
The ora-600 I found in my next migration gave me a big run for my job since, the given downtime was reaching towards it's end.
So, If you get below error then be assured it is olap.

ORA-00600: internal error code, arguments: [XSOOPS], [xsOBJTYPE1], [], [], [], [], [], []

which I am sure must have been found as invalid while checking the components in pt 12 as well.
In such case, unistall Olap & reinstall.

To uninstall Olap run following scripts

SQL> @?/olap/admin/catnoamd.sql
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/olap/admin/catnoxoq.sql

To reinstall Olap

SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

Reference material: Oracle notes:Remove Invalid OLAP Objects [ID 565773.1]
How To Remove or To Reinstall the OLAP Option To 10g and 11g [ID 332351.1]

EMD upload error:

Oracle agent can throw an EMD error for disk full.
Basically it means that the Disk on which agent is showing used percentage more than 98%

Error Seen

EMD upload error: Upload was successful but collections currently disabled - disk full 

Reason for the above error is that the EMD disk system shows used percent more than 98%.The agent requires the space for upload files is 98% by default. The agent collections will stop when the space on the disk is used beyond the default.
Solution is to release the space on the disk.
Also update the parameter UploadMaxDiscUsedPct=99 & UploadMaxDiskUsedPctFloor=99 in the emd.properties file

./emctl stop agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
The Oracleagent10gAgent service is stopping.....
The Oracleagent10gAgent service was stopped successfully.

./emctl start agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
The Oracleagent10gAgent service is starting..............
The Oracleagent10gAgent service was started successfully.

./emctl upload agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Drop database; SQL*Plus command

This command was introduced in Oracle10g and is very efficient.
The command will clean up all datafiles, online redo log files, controlfiles and spfile. It will not touch pfile & password file. On windows server, the service need to be deleted manually.
In case, one wants to remove archivelogs & backups execute this command from RMAN using the 'Including backups' option.
Word of caution here:::All RMAN backups associated with target database will be deleted from all configured device types.
Steps:

shutdown abort;  
startup mount exclusive restrict;   
drop database;  

Oracle mentions in it's document... that user must have SYSDBA system privilege to issue this statement. The database must be mounted in exclusive and restricted mode, and it must be closed.

Link::

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_8009.htm#i215798

Logical Standby SQL apply not re-starting

Errors ORA-00604 & ORA-01425 while restarting SQL apply for logical standby.
When one starts the SQL apply on the database...

SQL>Alter database start logical standby apply immediate;  
Database altered 

Oracle starts the SQL apply but subsequently it fails. In the alert log one would find the following errors..

Errors detected in process 16, role LOGICAL STANDBY COORDINATOR.  
krvsqn2s: unhandled failure 604:  
ORA-00604: error occurred at recursive SQL level 1  
ORA-01425: escape character must be character string of length 1  

There would be an associated trace file, which would show...

*** SERVICE NAME:(SYS$BACKGROUND) 2011-03-08 12:21:04.572  
*** SESSION ID:(632.180) 2011-03-08 12:21:04.572  
ORA-00604: error occurred at recursive SQL level 1  
ORA-01425: escape character must be character string of length 1  
knahcapplymain: encountered error=604  
*** 2011-03-08 12:21:04.619  
ksedmp: internal or fatal error  
ORA-00604: error occurred at recursive SQL level 1  
ORA-01425: escape character must be character string of length 1  

This is due to a Bug: 5108158 which is fixed in Oracle11g. Well!! I have heard that this is when we set logical standby to skip some schemas. Anyhow, this was not the case in our standby database neither could I find this skip schemas reason documented by Oracle !! :)
There is a workaround which would get the SQL apply to re-start. The workaround is documented in metalink 748208.1

SQL>--Ensure SQL apply is stopped  
SQL> Alter database stop logical standby apply;  
SQL>set echo on   
SQL>set pagesize 100   
SQL>spool workaround.log   
SQL>select * from system.logstdby$skip;   
SQL>select distinct nvl(esc, 'NULL') from system.logstdby$skip;   
SQL>select * from system.logstdby$skip where esc is null;   
SQL>update system.logstdby$skip set esc = '\'  where esc is NULL;  
-- Following should return no rows (due to update above)   
SQL>select * from system.logstdby$skip where esc is null;   
-- should no longer see any NULL in output   
SQL>select distinct nvl(esc, 'NULL') from system.logstdby$skip;   
-- Capture a snapshot of the final results   
SQL>select * from system.logstdby$skip;   
-- commit changes   
SQL>commit;   
--Restart the SQL apply and check the alert log.  
SQL> Alter database start logical standby apply immediate;  
SQL>Spool Off;  

In case, still SQL apply does not start, then immediately contact oracle support.

Performance statistics of database

Interestingly, maybe not surprisingly, we tend to let certain things go in background since, we are not using/doing that action as part of our daily work .I am grateful to Syed who pushed me to move from my comfort zone.
Preserving performance data in different Oracle versions...
Oracle 9i=> Performance statistics of a database was generally created in perfstat schema. So all we had to do was export user perfstat data and this dumpfile could be used for importing in another database in case required for analysis.
Oracle 10g=> introduced a few new reports and performance statistics was by default enabled to take snapshots every hour. Automatic Workload Repository (AWR) for cumulative and delta values at all levels except session & active session history (ASH) for the current state of all active sessions. By default we have snapshots of the performance data once every hour and the statistics are retained in the workload repository for 7 days.
To preserve this performance data, we gotta use "awrextr.sql" script to extract data into a data pump export file and "awrload.sql" script to load this data from dump file.
Oracle 11g=> The same system holds. A few enhancements are of course seen. So, to preserve this Oracle database performance data, we gotta use "awrextr.sql" script to extract data into a data pump export file and "awrload.sql" script to load this data from dump file.

Case Sensitive Password

Passwords have become case sensitive from Oracle 11g onwards.
In Earlier releases password was not case sensitive.
The case sensitive feature is default feature for Oracle 11g databases. Of course this feature can be enabled/disabled with an initialization parameter SEC_CASE_SENSITIVE_LOGON

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
sec_case_sensitive_logon             boolean     TRUE

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
System altered.

The case sensitive password functionality can be seen below.

SEC_CASE_SENSITIVE_LOGON initialization parameter is TRUE and creates a new user with a mixed case password.
CONN / AS SYSDBA
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> CREATE USER testuser IDENTIFIED BY TestUser;
SQL> GRANT CONNECT TO testuser;

2. Trying to connect using different case passwords.

SQL> CONN testuser/TestUser
Connected.
SQL> CONN testuser/testuser
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL>

3. Changing the parameter SEC_CASE_SENSITIVE_LOGON to FALSE and we can connect.

CONN / AS SYSDBA
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
SQL> CONN testuser/TestUser
Connected.
SQL> CONN testuser/TESTUSER
Connected.
SQL>

An important point is that even when case sensitive passwords are not enabled, the original case of the password when it was created/modified is retained. Which means that the passwords case sensitivity can be used in subsequent settting of the parameter SEC_CASE_SENSITIVE_LOGON to TRUE

Oracle data guard – new additions in 10R2

Oracle Data Guard has improved functionality over last releases, which is hitting the point home that Oracle is serious about it’s view of making administration so easy that a child can handle it.

Hmm….. Mediocre DBA’s better start looking for another professional line.

In 10g release 2, Oracle data guard broker new features are:

·         Fast-Start Failover

DG broker can automatically fail over to a previously chosen standby database in the event of a loss of primary database. This would not require any manual steps. Moreover Oracle 10gR2 claims that the former primary database is automatically re-instated as a standby database in the new broker configuration when a connection to it is re-established.

I would test this rigorsly in my environment. Database is never a standalone in any production environment and lots of other connections and application requirements would be involved in case of an automatic failover. I would seek answers more towards the applications and environment requirements before using this feature.

·         Re-instatement of the primary database to a standby database after a failover

Oracle claims that the data guard observer can reinstate the former primary database as a standby database. Oracle documentation states “Reinstatement restores high availability to the broker configuration so that, in the event of a failure of the new primary database, another fast-start failover can occur….”

Currently, this feature has too many limitations making it highly unusable in actual production environment. In Oracle 11g this feature seems to have enhanced.

·         Data Guard enhancements in OEM grid control

Oracle 10g Data Guard broker features are only available in Enterprise Manager grid Control. So, one must install OEM grid control to ba able to access the data guard functionality.

Ø  Compression of backups during standby creation

Ø  Support for flash recovery area for physical standby databases

Ø  Support of standby databases in an Oracle Managed Files (OMF) or Automatic

Storage Management (ASM) configuration

Ø  New and improved apply statistics

Ø  Status alerts for non-broker configurations

Ø  Test redo generator