ORACLE FAL_CLIENT and FAL_SERVER explained

FAL_CLIENT and FAL_SERVER are initialization parameters used to configure log gap detection and resolution at the standby database side of a physical database configuration. This functionality is provided by log apply services and is used by the physical standby database to manage the detection and resolution of archived redo logs.

FAL_CLIENT and FAL_SERVER only need to be defined in the initialization parameter file for the standby database(s). It is possible; however, to define these two parameters in the initialization parameter for the primary database server to ease the amount of work that would need to be performed if the primary database were required to transition its role.


Sample setup:

In Primary site:

FAL_SERVER=STANDBY
FAL_CLIENT=PRIMARY

In Standby site:

FAL_SERVER=PRIMARY
FAL_CLIENT=STANDBY

UNIX Permissions - table

Unix/Linux Permissions:

Here is the list of values and what they stand for in unix folder/directory permissions.Basically it is a simple you can count the value by using octa decimal binary numbers 421 :

See below image 

Value 0 directory listing is 000 = 0+0+0 

Value 1 directory listing is 001 = 0+0+1

Value 2 directory listing is 020 = 0+0+0  

This is where they gain their permission from :

so simple chmod 1 will give execute only permision on file/folder

so simple chmod 2 will give write only permision on file/folder

so simple chmod 3 will write & execute permision on file/folder

chmod 775 test.txt >> this mean 7 for 

UNIX Permissions

Value	Permission	Directory Listing
0	No read, no write, no execute	---
1	No read, no write, execute	--x
2	No read, write, no execute	-w-
3	No read, write, execute	-wx
4	Read, no write, no execute	r--
5	Read, no write, execute	r-x
6	Read, write, no execute	rw-
7	Read, write, execute	rwx

Configure second physical standby database - Oracle

I know there are various cases where in we have to setup a second physical standby database in our environments to have a redundant fail over strategy. I don't think there are enough documents out there that outline the process of adding the second standby database. I though I will share these steps today.

Parameters that should be considered while setting up the 2 node (single instance) standby Database.
In my case I will explain the parameters and their usage in setup. I am not going to explain the entire setup here. I you don't know how to setup physical standby database, please refer to this Physical_standby_setup.

At this point I am considering you know how to setup physical standby database and you are looking to add additional node to your setup.

Environments:

ORCL is PRIMARY INSTANCE and is on host        LINUX01   >> Primary Instance
ORCLSTB1 is STANDBY INSTANCE and is on host LINUX02  >> Standby already exists
ORCLSTB2 is STANDBY INSTANCE and is on host LINUX03  >>>  new instance to be added

PARAMETERS to be considered :

ARCHIVE_LOG_DEST_N   >>> these needs to be set to ship your logs to the new node,

This is a simple setup that can be used to makes sure all the three nodes will ship logs in case of switch over. In the below image I have configured such a way that each instance will send log files to other when they act as PRIMARY INSTANCE.

Note : I am using round robin process to ship logs between DB servers.

Example of Archive_Log_Dest setup

Adding Archive_log Dests:

ON DB SERVER Linux01 (ORCLPRIM):

alter system set log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLSTB1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=ORCLSTB2 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB2';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

ON DB SERVER Linux02 (ORCLSTB1):

alter system set log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLPRIM NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=ORCLSTB2 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB2';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

ON DB SERVER Linux03 (ORCLSTB2):

alter system set log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLPRIM NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=ORCLSTB1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

OTHER PARAMETER FILE SETTINGS: 

PFILE DB_* PARAMETERS on DB SERVER1 (Linux01)(ORCL): 

log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';  >> This is to tell oracle that what all instances are part of this config

*.db_file_name_convert='/ORCLSTB1/','/ORCL/','/ORCLSTB2/','/ORCL/'        -- > add both servers so oracle knows where to create file. 

*.db_name='ORCL'  ### same across all Databases 

*.db_unique_name='ORCL'  ### Unique across each Database 

FAL_SERVER=ORCLSTB1, ORCLSTB2-- > add both servers so oracle knows where to get the archive files from incase of switchover. 

FAL_CLIENT=ORCLThis is always the Current DB server typically standby DB. This is ignored when the DB is in Primary mode 

PFILE DB_* PARAMETERS on DB SERVER2 (Linux02)(ORCLSTB1): 

log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';
*.db_file_name_convert='/ORCL/','/ORCLSTB1/','/ORCLSTB2/','/ORCLSTB1/' 

*.db_name='ORCL'### same across all Databases 

*.db_unique_name='ORCLSTB1'                    ### Unique across each Database 

*.FAL_CLIENT=STANDBY_SERVER 

*.FAL_SERVER=PRIMARY_SERVERS 

PFILE DB_* PARAMETERS on DB SERVER3 (Linux03)(ORCLSTB2): 

log_archive_config='dg_config=(ORCL,ORCLSTB1,ORCLSTB2)';
*.db_file_name_convert='/ORCLSTB1/','/ORCLSTB2/','/ORCL/','/ORCLSTB2/' 

*.db_name='ORCL'### same across all Databases 

*.db_unique_name='ORCLSTB2'                 ### Unique across each Database 

*.FAL_CLIENT=STANDBY_SERVER 

*.FAL_SERVER=PRIMARY_SERVERS 

DATABASE NAME MAX SIZE in ORACLE

This is for 11gR2:

The length of DB_NAME is limited to 8 characters;

DB_UNIQUE_NAME is limited to 30 characters. DB_UNIQUE_NAME  can contain alphanumeric, underscore (_), dollar ($), and pound (#) characters, but must begin with an alphabetic character.

INSTANCE_NAME defaults to ORACLE_SID. Oracle document does not specify the limit for INSTANCE_NAME length. However if you describe V$INSTANCE view you can see that column INSTANCE_NAME  has VARCHAR2(16), indicating the  limit of 16 characters.

Because INSTANCE_NAME defaults to ORACLE_SID you need to be careful when deciding SID before creating a database. Oracle does not clearly define the limit for SID (system identifier). Following are what I found from two 11gR2 documents about ORACLE_SID.

Exporting partitions from a Partitioned tables

Exporting few partitions from a Partitioned table:

There might scenerios where you dont want to export the entire partitioned table for backup purposes. In this situations you can use simple expdp to do that.. Here is an example of

 Here is a sample expdp cmd to export part of entire partitioned tables.

I have a table named AUDIT_LOGONS in my database, which has partitions as below image ,

I want to export a part of it. partitions AUDIT_LOGONS1,AUDIT_LOGONS2 only


oracle@Linux1 $ expdp username/password directory=DPUMP dumpfile=AUDIT_PART1.dmp logfile=AUDIT_PART1.log TABLES=arvind.AUDIT_LOGONS:AUDIT_LOGONS4,arvind.AUDIT_LOGONS:AUDIT_LOGONS3


Export: Release 11.2.0.4.0 - Production on Mon Oct 12 16:29:59 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "arvind"."SYS_EXPORT_TABLE_01":  arvind/******** directory=DPUMP dumpfile=AUDIT_PART1.dmp logfile=AUDIT_PART1.log TABLES=arvind.AUDIT_LOGONS:AUDIT_LOGONS4,arvind.AUDIT_LOGONS:AUDIT_LOGONS3
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 120 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
:
:
:
:
Dump file set for arvind.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/AUDIT_PART1.dmp
Job "arvind"."SYS_EXPORT_TABLE_01" successfully completed at Mon Oct 12 16:32:02 2015 elapsed 0 00:01:56

ARCHIVE LOG Switch - shell script (for Oracle)

I think many of database admins will agree that it is always good idea to switch log file atleast every 20 mins or so. Especially when you have standby server setup. Doing this will ensure that redo logs are cleared up every 20 mins or so and they are shipped and applied onto standby database.

Script Benefits:

This is a simple shell script that uses plsql for checking the mount state of Database (oracle) and switch the archive log file when the open_mode of database is Read Write.  This script can be setup on both Primary and standby DB servers. This will avoid the enabling and disabling process on both servers after switchover/failover. You can setup this on crontab job on both Primary and standby DB servers, This will only switch log file if the DB is on Read Write mode

----------    Script Starts Here    -----------

#!/usr/bin/ksh

## ---- Created by Arvind Toorpu
## ----   Create Date 10/15/2015
## ----   This script can be scheduled on both PRIMARY AND STANDBY as it checks the role and switches logs
## ---- only if it is in PRIMARY ROLE
## ---- This script needs to be executed as DBA role user or user that has access to switch log file
## ---- Always good idea to switch logfile atleast every 20mins
## ----  Configure this using crontab 


mydate=`date "+%d%b%Y-%H:%M"`;
export ORACLE_HOME="/u01/app/oracle/product/11.2/db_1"
export ORACLE_SID="ORCL"
PATH=$PATH:$ORACLE_HOME/bin;export PATH;

sqlplus -s "/as sysdba" > /u01/app/oracle/admin/bin/logs/arch_switch/ARCH_SWITCH_$mydate.log <<EOF

SET SERVEROUTPUT ON;
SET FEED OFF;
declare
DB_UQ_NAME  varchar2(20);
CUR_STATUS  varchar2(20);
DB_ROLE     varchar2(20);
strQuery    varchar2(100);
CURR_ARCH number;
BEGIN
select DB_UNIQUE_NAME,open_mode,DATABASE_ROLE into DB_UQ_NAME,CUR_STATUS,DB_ROLE from v\$database;
select max(SEQUENCE#) into CURR_ARCH from  V\$ARCHIVED_LOG;
if CUR_STATUS='READ WRITE' and DB_ROLE='PRIMARY'
then
strQuery :=' alter system switch logfile';
DBMS_OUTPUT.PUT_LINE('DB_UNIQUE_NAME '||DB_UQ_NAME||' is currently in : '||CUR_STATUS ||' mode with Current ARch :'||CURR_ARCH);
EXECUTE immediate strQuery;
DBMS_OUTPUT.PUT_LINE('Switch logfile completed SUCESSFULLY !! ');
else
DBMS_OUTPUT.PUT_LINE('ERROR - DB_UNIQUE_NAME '||DB_UQ_NAME||' is currently in : '||CUR_STATUS||'.CANT SWITCH LOGFILE !!');
end if;
END;
/

EOF

Split Fullname into firstname and last name thru sql - Oracle

Today I was working around some queries and I had a requirement where in the data in table was being stored a FULLNAME. Now I have Split Fullname into First Name and Last Name. I have all the fullnames in the column seperated by a empty space ' '.
We can get the desired output by using the combination of SUBSTR and INSTR.


Lets create a sample table :

create table TESTTAB (ID number, FULLNAME varchar2(100));

Insert some data into it:

Insert into TESTTAB (ID,FULLNAME) values (1,'Jaff Schdt');
Insert into TESTTAB (ID,FULLNAME) values (2,'Bradee Will');
Insert into TESTTAB (ID,FULLNAME) values (3,'Kuck Dahl');
Insert into TESTTAB (ID,FULLNAME) values (5,'Melyssa man');
Insert into TESTTAB (ID,FULLNAME) values (6,'Melyssa man');
Insert into TESTTAB (ID,FULLNAME) values (7,'Shart Elarpre');
Insert into TESTTAB (ID,FULLNAME) values (8,'Rock Dihl');
Insert into TESTTAB (ID,FULLNAME) values (9,'Hia Dodd');
Insert into TESTTAB (ID,FULLNAME) values (10,'Pegy Sith');

select * from TESTTAB;

ID FULLNAME
-- -------------
1 Jaff Schdt
2 Bradee Will
3 Rock Dihl
5 Melyssa man
6 Melyssa man
7 Shart Elarpre
8 Rick Dihl
.
.


sample query :

SELECT
  ID,
  FULLNAME,
  SUBSTR(FULLNAME,0,(INSTR(FULLNAME,' ')       -1))  AS FIRST_NAME,
  SUBSTR(FULLNAME,(INSTR(FULLNAME,' ')         +1))  AS LAST_NAME,
  (SUBSTR(FULLNAME,0,(INSTR(FULLNAME,' ')      -1))
  ||SUBSTR(FULLNAME,(INSTR(FULLNAME,' ')))) as CONC_RESLT
FROM
  TESTTAB;

Sample Code Ouput :

ID FULLNAME FNAME LNAME CONCAT_RESLT
--- ------------ ------ ------ --------------

1 Jaff Schdt Jaff Schdt Jaff Schdt
2 Bradee Will Bradee Will Bradee Will
3 Kuck Dahl Kuck Dahl Kuck Dahl
5 Melyssa man Melyssa man Melyssa man
6 Melyssa man Melyssa man Melyssa man
7 Shart Elarpre Shart Elarpre Shart Elarpre
8 Rock Dihl Rock Dihl Rock Dihl
.
.

Export table data with filtered clause - Query based export (oracle expdp)

This parameter is used in conjunction with TABLE parameter of exp (export) utility of Oracle. This parameter will enable selection of rows from the list of tables mentioned in TABLE parameter. The value to this parameter is a WHERE clause for a SELECT statement which you would normally issue.

For Example: 

If you want to query all records of employees for a particular department you will use:

SELECT *
FROM HR.EMPLOYEES
WHERE dept = 10;


EXPDP UTILITY CMD :

$ expdp HR/XXXXX@ORCL directory=DPUMP dumpfile=TEST_EMP.dmp logfile=TEST_EMP.log include="HR.EMPLOYEES:\" in (where dept =10)\""



NORMAL EXPORT UTILITY CMD :

$ exp HR/XXXXX@ORCL file=/u01/app/oracle/dpump/TEST_EMP.dmp log=/u01/app/oracle/dpump/TEST_EMP.log tables=HR.EMPLOYEES query='"where dept =10"'