Monday, December 29, 2008

How to create a physical STANDBY database "Simple command" series

Purpose:

We'd want to have a transactionally consistent copy of an Oracle production database that would initially be created from a backup copy of the primary database. A Failover for our mission critical production databases. When production database crashes, applications can quickly switchover to the standby databases. Being part of "Simple command" series (old procedures collection) it wont touch new 11g features.

Versions:
Oracle 9i, 10g

Description:

A physical standby database is physically identical to the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are identical. We can create, maintain and monitor the log transport and log apply services of a physical standby database by SQL/Shell Scripts (we can also use Data Guard or EM). When it is not performing recovery, a physical standby database can be opened in read-only mode.
A physical standby database provides the following benefits:
  • Disaster recovery and high availability
  • Data protection
  • Reduction in primary database workload (w/RMAN - off-load backups from the primary database)
  • Performance (propagate changes among databases)
Note: A logical standby database is initially created as an identical copy of the primary database, but it later can be altered to have a different structure. The logical standby database is updated by executing SQL statements. This allows users to access the standby database for queries and reporting at any time. Thus, the logical standby database can be used concurrently for data protection and reporting operations. A logical standby database has some restrictions on datatypes, types of tables, and types of DDL and DML operations and is not part of the scope of this article.

Pre-Procedure:- Create STBY DB folders structure (audit_file_dest, background_dump_dest, core_dump_dest, user_dump_dest, file directories, etc.)

- Create a password file on both primary DB, standby DB with the same sys password.

remote_login_passwordfile='EXCLUSIVE'


Note: I prefer to work over an init.ora file while setting and create a spfile after finished installation:
create pfile from spfile;
Rename spfile to old make modifications to init.ora file and restart the instance after modified.

- Set Primary DB on archivelog mode

SQL> Select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#,     ARCHIVE_CHANGE# from V$DATABASE;

SQL> ALTER SYSTEM ARCHIVE LOG START;
- Shutdown and BK

set LOG_ARCHIVE_DEST, LOG_ARCHIVE_FORMAT on init.ora


SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
SQL> archive log all;

SQL> Select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#,     ARCHIVE_CHANGE# from V$DATABASE;



SQL> select database_role from v$database;
- Modify initialization parameters

Setting Primary Database Initialization Parameters
Primary Role Initialization Parameters
DB_NAME='Primary_DB'
DB_UNIQUE_NAME='Primary_DB'
SERVICE_NAMES='Primary_DB'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(Primary_DB,Standby_DB)'
CONTROL_FILES='/path_to_files/Primary_DB/control1.ctl',
'/path_to_files/Primary_DB/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/path_to_arch/Primary_DB/arch1/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=Primary_DB'
LOG_ARCHIVE_DEST_2=
 'SERVICE=Standby_DB
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=Standby_DB'
LOG_ARCHIVE_DEST_STATE_1='ENABLE'
LOG_ARCHIVE_DEST_STATE_2='ENABLE'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=arc_Primary_DB_%t_%s_%r.arc
Note: These parameters control how log transport services transmit redo data to the standby system and the archiving of redo data on the local file system. Note that the example assumes the use of the ARCn processes (the default) to transmit redo data. If you specify the LGWR process to transmit redo data to both the local and remote destinations, also include the NET_TIMEOUT attribute on the LOG_ARCHIVE_DEST_2 initialization parameter.

Standby Role Initialization Parameters
FAL_SERVER='Primary_DB'
FAL_CLIENT='Standby_DB'
DB_FILE_NAME_CONVERT='/path_to_files/Primary_DB',
'/path_to_files/Standby_DB'
LOG_FILE_NAME_CONVERT='/path_to_files/Primary_DB/redo',
'path_to_files/Standby_DB/redo'
STANDBY_ARCHIVE_DEST='/path_to_arch/Standby_DB/arch1/'
STANDBY_FILE_MANAGEMENT=AUTO
Prepare an Initialization Parameter File for the Standby Database
(SQL> CREATE PFILE='/tmp/init.ora' FROM SPFILE;)
Modifying Initialization Parameters for a Physical Standby Database

DB_NAME='Primary_DB'
DB_UNIQUE_NAME='Standby_DB'
SERVICE_NAMES='Standby_DB'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(Standby_DB,Primary_DB)'
CONTROL_FILES=
'/path_to_files/Standby_DB/controlStandby_DB1.ctl',
'/path_to_files/Standby_DB/controlStandby_DB1.ctl'
DB_FILE_NAME_CONVERT='/path_to_files/Primary_DB/',
'/path_to_files/Standby_DB/'
LOG_ARCHIVE_FORMAT=arc_Primary_DB_%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
'LOCATION=/path_to_arch/Standby_DB/arch1/
      VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
      DB_UNIQUE_NAME=Standby_DB'
LOG_ARCHIVE_DEST_2=
      'SERVICE=Primary_DB
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=Primary_DB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_ARCHIVE_DEST='/path_to_arch/Standby_DB/arch1/'
STANDBY_FILE_MANAGEMENT=AUTO
INSTANCE_NAME='Standby_DB'
FAL_SERVER='Primary_DB'
FAL_CLIENT='Standby_DB'

Add section to tnsnames.ora on Primary_DB

Standby_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Standby_host)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Standby_DB)
    )
  )

Add section to tnsnames.ora on Standby_DB

Primary_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Primary_host)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Primary_DB)
    )
  )

Add section to listener.ora on the Standby_DB and reload

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = Standby_DB)
      (ORACLE_HOME = /oracle/product/10.2.0)
      (SID_NAME = Standby_DB)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Standby_host)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
Command list:-Primary DB on archivelog mode (we'll use a hot backup copy)

-Enable Forced LoggingSQL> select name, FORCE_LOGGING from v$database;
SQL> ALTER DATABASE FORCE LOGGING;

-Create standby controlfile on primary DB:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/some_path_to_disk/controlStandby_DB1.ctl';

- Copy files from primary DB hot backup location to standby db location:
scp -C Primary_DB_server:/path_to_hotbackup/Primary_DB/*/path_to_files/Standby_DB/
(decompress files if necessary)

-Copy control files after generated:
scp -C Primary_DB_server:/some_path_to_disk/controlStandby_DB1.ctl /path_to_files/Standby_DB/
cd
/path_to_files/Standby_DB/
cp controlStandby_DB1.ctl controlStandby_DB2.ctl

Note: to avoid password prompt on scripts, we can generate a public ssh rsa key and copy it to primary DB server.
on the standby server:
cd /oracle/.ssh
ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /oracle/.ssh/id_rsa.
Your public key has been saved in /oracle/.ssh/id_rsa.pub.
The key fingerprint is:
5e:d5:97:f1:91:31:1b:e2:24:66:61:67:6f:a8:d2:61 oracle@standby


cat id_rsa.pub
and copy the entire content to the primary server /oracle/.ssh/authorized_keys2 file

Finally we must start
and synchronize the standby DB:

sqlplus /nolog
conn / as sysdba
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;

SQL> set AUTORECOVERY ONSQL> alter database recover managed standby database cancel;
SELECT thread#,MAX( SEQUENCE# ) AS LAST_RECIVED_LOG
FROM SYS.v$archived_log GROUP BY thread#;






SELECT thread#,MAX( SEQUENCE# ) AS LAST_APPLIED_LOG ,APPLIED
FROM SYS.v$archived_log WHERE APPLIED LIKE'YES' GROUP BY thread#,APPLIED;







SQL>
Recover standby database;SELECT thread#,MAX( SEQUENCE# ) AS LAST_RECIVED_LOG
FROM SYS.v$archived_log GROUP BY thread#;




SELECT thread#,MAX( SEQUENCE# ) AS LAST_APPLIED_LOG ,APPLIED
FROM SYS.v$archived_log WHERE APPLIED LIKE'YES' GROUP BY thread#,APPLIED;







SQL>
alter database recover managed standby database disconnect from session;
SQL> exit;

Test on primary DB if experience error with archives destination:

set lines 132
col Archive_dest for a40

SELECT dest_id "ID", status "DB_status", destination "Archive_dest", error "Error"FROM v$archive_dest;


Links, References:
http://download.oracle.com/docs/cd/B13789_01/server.101/b10823/standby.htm#52114
http://www.orafaq.com/node/957

Monday, September 15, 2008

How to create a MySQL database and associated USER with the right permissions, to be accessed externally - "Simple command" series

Purpose:
Many times we have to deal with databases other than Oracle and even being those DB simpler, they waste DBA's resources. In first place it happens because we have to seek reference manuals and regarded documentation. In other hand, those DB could have a different approach and behavior than Oracle.
In this simple case and like a first post of a similar "simple command" series, I'll add the necessary command order and description to create a "testdb" on MySQL with the right permissions to be accessed by our Apache frontends. We'll use a specially generated user to connect and not just *.* permissions on root.

Like you know, on MySQL there are many levels for granting privileges, we'll work only at global and table level. For a detailed guide, please visit the Mysql documentation
http://dev.mysql.com/doc/refman/5.0/en/grant.html

Versions: MySQL 4/5

Description:
Connecting locally to MySQL:
mysql -u -p (it asks password)
(see at the end, how to create an admin user other than root)

ie:
[root@host ~]# mysql -u admin -p
Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9218
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
CREATE DATABASE testdb CHARACTER SET = utf8;

mysql> GRANT
USAGE ON *.* TO 'testdb'@'10.140.134.%' IDENTIFIED BY 'testdbpass';
Note: 10.140.134.% is the Apache frontend network and could be more than one server.

mysql> GRANT ALL PRIVILEGES on `testdb`.* to 'testdb'@'10.140.134.%';

or more grained ...

mysql>
GRANT SELECT,INSERT on `testdb`.* to 'testdb'@'10.140.134.%';

Command list:
___________________________________________________
>mysql -u admin -p
>CREATE DATABASE testdb CHARACTER SET = utf8;
>GRANT USAGE ON *.* TO 'testdb'@'10.140.134.%' IDENTIFIED BY 'testdbpass';
>GRANT ALL PRIVILEGES on `testdb`.* to 'testdb'@'10.140.134.%';
___________________________________________________

If we've made a mistake or we want to clear everything:
>DROP USER 'testdb'@'10.140.134.%';
>DROP DATABASE testdb;

Query results (mysql.user):
>SELECT USER, HOST FROM mysql.user ORDER BY 1, 2;
>SHOW DATABASES;

how to create an admin user other than root
Please, be careful on production environments, it must be reviewed an tested in advance.

At Global level:

GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'admin';
GRANT ALL ON *.* TO 'admin'@'localhost';
GRANT GRANT OPTION ON *.* TO 'admin'@'localhost';


Note: "ALL" gives everything less than "GRANT OPTION"

Links, References:
http://dev.mysql.com/doc/refman/5.0/en/grant.html

Wednesday, August 13, 2008

DBA's resources and links

Images are a visual reference only (not links)

Oracle`s resources

Toad World Database resources, Toad resources
http://www.toadworld.com


















Search and Download Oracle Database, Application Server, and Collaboration Suite Documentationhttp://tahiti.oracle.com/














Oracle9i Database Online Documentation Release 2 (9.2)
http://download-west.oracle.com/docs/cd/B10501_01/index.htm









Oracle® Database Release Notes 10g Release 2 (10.2) for Linux x86 B15659-03
http://download-east.oracle.com/docs/html/B15659_03/toc.htm












Documentation library
10g Release 2 (10.2)












Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide
10g Release 2 (10.2)
Part Number B14197-08











Oracle® Process Manager and Notification Server Administrator's Guide
10g (9.0.4)

http://download.oracle.com/docs/cd/B13597_05/core.904/b12057/opmntrbl.htm#i1011956









Metalink
(CSI required)









OTN
Technical Articles for DBAs and Sysadmins
http://www.oracle.com/technology/pub/articles/tech_dba.html












Ask Tom - Development & ADM questions










Unixguide Config help with AIX, FreeBSD, HP-UX, LINUX(RedHat), SOLARIS, Tru64 http://www.unixguide.net/unixguide.shtml

Sun Microsytems

Solaris FAQ
The following is a list of questions that are frequently asked about Solaris 2.x and later.
Where the FAQ mentions "Solaris 2.x", it really refers to Solaris 2.x, Solaris 7 and later By Casper Dik














Sun online support center
https://osc-emea.eu.sun.com/OSCSW/svcportal?pageName=clselection










Sun troubleshooting advice & best practices
http://sunsolve.sun.com










Solaris Documentation (Solaris 8)
http://wallaby.cs.man.ac.uk:8888/











Sun Microsystems Solaris Documentation (Solaris 9)
http://intranet.cs.man.ac.uk/solaris/sun_docs/sundocs.html








Richard's UNIX Shell Scripting and Tutorials (See Tutorial)
http://www.injunea.demon.co.uk/index.htm











Tomcat

Jakarta Tomcat Tomcat 4 (JNDI Datasource HOW-TO)Here then are some example configurations that have been posted to tomcat-user for popular databases and some general tips for db useage
http://tomcat.apache.org/tomcat-4.1-doc/jndi-datasource-examples-howto.html












Tomcat Memory/FAQ
This page discusses various memory issues.
http://wiki.apache.org/tomcat/FAQ/Memory











Login in Tomcat 5
Tomcat 5.5 uses Commons Logging throughout its internal code allowing the developer to choose a logging configuration that suits their needs, e.g java.util.logging or Log4J. Commons Logging provides Tomcat the ability to log hierarchially across various log levels without needing to rely on a particular logging implementation.
http://tomcat.apache.org/tomcat-5.5-doc/logging.html











Microsoft












MsSqlServer:
Informacion stored procedures