burn mp3 to audio cd on linux with command line

1. convert mp3 to wav format using online service

https://online-audio-converter.com/

2. find device

[root@hanli gymnastic_song]# wodim -devices
wodim: Overview of accessible drives (1 found) :
-------------------------------------------------------------------------
 0  dev='/dev/sg2'      rwrw-- : 'HL-DT-ST' 'DVDRAM GH24NSB0'
-------------------------------------------------------------------------

3. burn

cdrecord -v -nofix -eject dev='/dev/sg2' -audio -pad 5_Clubs_ELE.wav

reference:

https://linuxconfig.org/burn-your-music-files-from-any-format-to-audio-cd-using-command-line

CSSD start failed on one node of two-nodes RAC database cluster after reboot

Symptom:

One node has CSSD start failed after reboot
The other node is OK.

Log file show disk HB is godd and network HB failed

Checking :

olsnodes -n -i -s -t # check nodes

node1 1 active unpinned
node2 2 inactive unpinned ## unpinned is not an issue.

According Metalink doc Top 5 Grid Infrastructure Startup Issues (Doc ID 1368382.1)
#3

check
ping -s 8900 hostname/IP # check jumbo frame setting
ping -s 1500 hostname/IP # check if default value MTU 1500 is good

root cause:

MTU set as 9000 on unix OS level, but network is not enabled jumbo frame on both private and public network interfaces.

Fix:

network team need enable jumbo frame as MTU 9000 and make sure "ping -s 9000 IP" work.
restart cluster/reboot server.


reference:

Troubleshooting Clusterware startup problems with detailed debugging info

https://www.hhutzler.de/blog/troubleshooting-clusterware-startup-problems/

SSL setup for oracle RAC database.

SSL setup for oracle RAC database.

Step by Step Guide: How to Configure SSL/TLS on ORACLE RAC (with SCAN) (Doc ID 1448841.1)

1. configure TCPS protocal endpoints

 crsctl stat res -p | grep -i endpoints

 srvctl modify listener -p "TCP:1521/TCPS:35050";

 srvctl config listener 

 srvctl modify scan_listener -p "TCP:1521/TCPS:35050"

 srvctl config scan_listener

 

 

2. create SSL certificataes and wallets for cluster and for client.

2.1  create self-signed CA (NO need if exsited CA is used. )

mkdir -p /u01/app/oracle/CA

cd /u01/app/oracle/CA 

export CA_HOME=/u01/app/oracle/CA

orapki wallet create -wallet $CA_HOME

orapki wallet remove -trusted_cert_all -wallet $CA_HOME

orapki wallet add -wallet $CA_HOME -self_signed -dn "CN=DEV-CA,O=MYORG,C=MYOFFICE" -keysize 2048 -validity 3650 -sign_alg sha256 -pwd <password>

orapki wallet export -wallet $CA_HOME -dn "CN=DEV-CA,O=MYORG,C=MYOFFICE" -cert devCA.cer -pwd <password>

orapki wallet display -wallet $CA_HOME -summary

2.2 create cluster wallet and cert

mkdir -p /u01/app/oracle/wallet/myrac

export W_HOME=/u01/app/oracle/wallet/myrac

orapki wallet create -wallet $W_HOME

orapki wallet remove -trusted_cert_all -wallet $W_HOME -pwd <password>

orapki wallet add -wallet $W_HOME -dn "CN=myrac" -keysize 2048 -pwd <password>

orapki wallet export -wallet $W_HOME -dn "CN=myrac" -request $W_HOME/myrac.req -pwd <password>

orapki cert create -wallet $CA_HOME -request $W_HOME/myrac.req -cert $W_HOME/myrac.cer -validity 3650 -sign_alg sha256 -pwd <password>

orapki wallet add -wallet $W_HOME -trusted_cert -cert $CA_HOME/devCA.cer -pwd <password> 

orapki wallet add -wallet $W_HOME -user_cert -cert $W_HOME/myrac.cer -pwd <password>

orapki wallet display -wallet $W_HOME -summary -pwd <password>

orapki wallet create -wallet $W_HOME -auto_login

## the wallet with cert need be copy to all nodes.

2.3 create client wallet and cert

## unique ssl cert is required for client with the same CA for successful SSL connection.

mkdir -p /home/oracle/ben/wallet

export W_HOME=/home/oracle/ben/wallet

orapki wallet create -wallet $W_HOME -pwd <password>

orapki wallet remove -trusted_cert_all -wallet $W_HOME -pwd <password>

orapki wallet add -wallet $W_HOME -trusted_cert -cert $W_HOME/devCA.cer -pwd <password> 

orapki wallet create -wallet $W_HOME -auto_login

orapki wallet add -wallet $W_HOME -dn "CN=devUser" -keysize 2048 -pwd <password>

orapki wallet export -wallet $W_HOME -dn "CN=devUser" -request $W_HOME/devUser.req -pwd <password>

orapki wallet add -wallet $W_HOME -user_cert -cert $W_HOME/devUser.cer -pwd <password>

3. configure listener.ora and sqlnet.ora for cluster

# both PMON and the listener processes of each node must be able to access the wallets.

# This is in oracle home.

3.1 in sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

SSL_VERSION = 0

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/wallet/myrac)

    )

  )

3.2 in listener.ora   ## this is in Grid home.

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/wallet/myrac)

    )

  )

4.Restart instances and listeners. 

srvctl stop listener

srvctl start listener

srvctl stop scan_listener

srvctl start scan_listener

srvctl stop database -d testdb

srvctl start database -d testdb

/** OR shut instance one by one

srvctl stop instance -db testdb -instance testdb1 -stopoption immediate -force

srvctl start instance -db testdb -instance testdb1 

srvctl stop instance -db testdb -instance testdb2 -stopoption immediate -force

srvctl start instance -db testdb -instance testdb2

 */

  

5. configure sqlnet.ora for client

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/wallet/devUser)

    )

  )

6. test connection

6.1 create tns entry

testdbSSL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCPS)(HOST = myrac-scan)(PORT = 35050))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = testdb)

    )

  )

6.2 ping ssl entry

tnsping testdbSSL

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 07-FEB-2018 16:17:03

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = myrac-scan)(PORT = 35050)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb)))

OK (130 msec)

6.3 connect  with sqlplus

[oracle@sp-ud-oitord01 ben]$ sqlplus system@testdbssl

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 7 16:17:51 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Last Successful login time: Wed Feb 07 2018 16:00:09 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL>

6. verify connection protocol

SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;

SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

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

tcps

SQL>

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

# troubelshooting

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

###ORA-28865: SSL connection closed  or ORA-28864: SSL CONNECTION CLOSED GRACEFULLY 

for rac both grid_home and oracle_home need have below setup on sqlnet.ora

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/wallet/myrac)

    )

  )

####   ORA-28860: Fatal SSL error

this is because client side sqlnet.ora has 

SL_CLIENT_AUTHENTICATION=FALSE

comment it out.

Xming X server : No protocol defined issue

symtem:

in log: client was rejected from IP xxx.xxx.xxx.xxx

This is caused by client not accept setting.

solution:

1. edit Xming host file to add ip
2. start Xming with -ac option
edit shortcut and add -ac for target field.


http://theunixtips.com/xming-client-4-rejected-from-ip/

DATAGUARD: Oracle Physical standby database Log shipping issue

Symptom:
  Physical standby was sync with primary a day ago, but stopped receiving logs. All logs are still available on primary site.

Error message:
   'ORA-01031: insufficient privileges'

Cause:
  sys password was changed on primary side.

Solution:
  copy password file from primary to standby site

Check sql:
select sequnece#, applied from v$archived_log;
select process, status, sequence# from v$managed_standby;
select dest_id, status, error from v$archive_dest;
alter database recover managed standby database using current logfile disconnect;
(alter database recover managed standby database disconnect from session;)
alter database recover managed standby database cancel;
show parameter fal_client
show parameter fal_server
show parameter log
alter system witch logfile;
alter system set log_archive_dest_state_3=enable;
alter database register logfile 'path/filename';
rman> catalog start with '/var/arch';

Check step:
1. check network
tnsping fal_client and fal_server and service_name of log_archive_dest_state_3
2. check instance are up
3. check password
login as sys to both primary and standby database.
4. check error for log_dest

Processes:
primary database

There are a number of Oracle background processes that play a key role, first the primary database
•LGWR - log writer process flushes from the SGA to the ORL files
•LNS - LogWriter Network Service reads redo being flushed from the redo buffers by the LGWR    and performs a network send of the redo to the standby
•ARCH - archives the ORL files to archive logs, that also used to fulfill gap resolution requests, one ARCH processes is dedicated to local redo log activity only and never communicates with a

standby database

The standby database will also have key processes
•RFS - Remote File Server process performs a network receive of redo transmitted from the primary and writes the network redo to the standby redo log (SRL) files.
•ARCH - performs the same as the primary but on the standby
•MRP - Managed Recover Process coordinates media recovery management, recall that a physical standby is in perpetual recovery mode
•LSP - Logical Standby Process coordinates SQL apply, this process only runs in a logical standby
•PR0x - recovery server process reads redo from the SRL or archive log files and apply this redo to the standby database.

reference:
http://www.dba-oracle.com/t_physical_standby_missing_log_scenario.htm
http://www.datadisk.co.uk/html_docs/oracle_dg/cheatsheet.htm
http://arup.blogspot.com/2009/12/resolving-gaps-in-data-guard-apply.html
http://jarneil.wordpress.com/2008/05/16/registering-archive-logfiles-on-a-standby/
http://www.syksky.com/oracle/oracle-11g-data-guard-log-shipping-fails-with-error-ora-16191.html

Change Monitor resolution from terminal

Problem:
default resolution is not support by my moniter. There is black screen on the monitor but remote ssh login is available.

Solution:

1. check /var/log/xorg.0.log to find out monitor name and allowed resolution. or run command to findout

[root@test log]# xrandr -d :0
Screen 0: minimum 320 x 200, current 1280 x 1024, maximum 8192 x 8192
HDMI-0 disconnected (normal left inverted right x axis y axis)
DVI-0 disconnected (normal left inverted right x axis y axis)
VGA-0 connected 1280x1024+0+0 (normal left inverted right x axis y axis) 384mm x 306mm
   1280x1024      56.3*+   60.0
   1280x960       60.0
   1152x864       75.0
   1024x768       75.1     70.1     60.0
   832x624        74.6
   800x600        72.2     75.0     60.3     56.2
   640x480        72.8     75.0     66.7     60.0
   720x400        70.1



2. change resolution with command from termial

  xrandr -d :0 --output VGA-0 --mode 1152x864

reference:
http://askubuntu.com/questions/405645/how-to-enable-monitor-from-terminal

Generate multiple files using each line of a file in Linux

ISSUE:

Sometimes a file or a command need be used against to a list of servers or databases. So a general file or command need be generated with specific information as a general pattern such as database name.

SOLUTION:

A simple While statement can do this task.  So general while statement is provided for easy access

Statement:

while read line
do
host_name=`echo $line | cut -d ' ' -f 1` #get first field of input line
host_ip=`echo $line | cut -d ' ' -f 2`       # get second field of input line
short_hname=${host_name%%\.*}      # remove domain port of hostname
alias_hname=${short_hname:9:5}        # extract 5 character starting at 9 as alias name
if [ ${short_hname:0:3} == "tet" ] ; then # extract 3 character to setup environment name
env_name=test
else
env_name=${short_hname:4:3}
fi
echo "sshpass -e ssh -o StrictHostKeyChecking=no test@$host_ip" > ${env_name}_${alias_hname}_${short_hname}.sh  # generate a sh file for ssh login for each server
chmod 700 ${env_name}_${alias_hname}_${short_hname}.sh # make gererated file executable
done < serverlist.txt

Xlib: Client is not authorized to connect to Server Exceed

ISSUE:

Remote display is refused by local exceed server.

Cause:

host acceptence ACL is not configured for the remote server.

Solution:

configure exceed to accept anyhost or the remote servers you want

1.  click Start -> all program -> Hummingbird Connectivity 2008 x64 -> exceed tools -> Xconfig

2. click icon "Security, Access Control and System Administration" category.

3. select file if you need input a list of servers or any host access for all hosts

4. save and exit

ERROR OGG-01224 Address already in use.

PROBLEM:
mgr cannot start. "ERROR   OGG-01224  Address already in use and ERROR   OGG-01668  PROCESS ABENDING." appear in mgr.rpt

CAUSE:
Previous mgr process is still runing, which used port 7810 according to parameter file.

SOLUTION:

as root:
root> netstat -nap | grep 7810
tcp        0      0 :::7810                     :::*                        LISTEN      7590/./mgr
root> kill -9 7590

restart mgr process.

ERROR OGG-01668 Oracle GoldenGate Manager for Oracle, mgr.prm: PROCESS ABENDING.

PROBLEM:
mgr process can not start. "ERROR   OGG-01668  Oracle GoldenGate Manager for Oracle, mgr.prm:  PROCESS ABENDING" appears in ggserr.log. "ERROR   OGG-01091  Oracle GoldenGate Manager for Oracle, mgr.prm:  Unable to open file "/app/oracle/gg/bin/dirrpt/MGR.rpt" (error 2, No such file or directory)." appears in ggserr.log.

CAUSE:
subdirectory is not created.

SOLUTION:
create subdirectories.

utlrp error with ORA-04045: errors during recompilation/revalidation of GGUSER.DDLREPLICATION

CAUSE:
Goldent Gate DDL is enabled on database. Trigger sys.GGS_DDL_TRIGGER_BEFORE will be fired for utlrp, but the trigger is failed because some Golden Gate object is invalid.

Solution:
1. disable the trigger as sysdba
alter trigger sys.GGS_DDL_TRIGGER_BEFORE disable ;
2. run utlrp
@?/rdbms/admin/utlrp.sql
3. enable the trigger as sysdba
alter trigger sys.GGS_DDL_TRIGGER_BEFORE enable ;


reference:
http://harikrishnakm.wordpress.com/2013/08/19/catuppst-sql-fails-with-gguser-ddlreplication-does-not-exist-after-upgrading-the-database/

Duplicate Database using RMAN backup without connecting to target for oracle 10g version on windows

PURPOSE:
Duplicate oracle 10g database in new server for testing without connecting to target.

STEPS:

STEP 1. make a backup

1.1 backup database
rman target /
run
{
allocate channel c1 type disk format 'F:\rman\rman_db_%d_%T_%U.rman';
backup as compressed backupset full database;
release channel c1;
}

1.2 backup controlfile
rman target /
run
{
allocate channel c1 type disk format 'F:\rman\rman_ctl_%d_%T_%U.rman';
backup as compressed backupset current controlfile;
release channel c1;
}

1.3 backup required archive logs
run
{
allocate channel c1 type disk format 'F:\rman_test\rman_arch_%d_%T_%U.rman';
backup archivelog sequence between 100 and 200 ;
release channel c1;
}

STEP 2. Create required directory for new database in new host

2.1 data file, log file and tempfile location
select name from v$datafile;
select member from v$logfile;
select name from v$tempfile;

mkdir D:ORACLE\ORADATA\TEST

2.2 other locations ( dump dest and archivelog dest)
show parameter dest

STEP 3. copy pfile and make modification

STEP 4. create windows service

radim -new -sid test -intpwd test

STEP 5. start instance

startup mount

STEP 6. restore control file and mount database

rman target /
set dbid=123456789;
restore controlfile from 'F:\rman\RMAN_ctl_test_20131011_95OM4JNU_1_1.RMAN';
alter database mount;

STEP 7. restore database from backup
new location need be specified by using "set new name"
catalog start with 'F:\rman'

rman target /
run
{
set newname for datafile 1 to 'D:\ORACLE\ORADATA\TEST1\SYSTEM01.DBF';
set newname for datafile 2 to 'D:\ORACLE\ORADATA\TEST1\UNDOTBS01.DBF';
set newname for datafile 3 to 'D:\ORACLE\ORADATA\TEST1\SYSAUX01.DBF';
set newname for datafile 4 to 'D:\ORACLE\ORADATA\TEST1\USERS01.DBF';
allocate channel c1 type disk format 'F:\rman\\RMAN_DB_%d_%T_%U.RMAN';
restore database  ;
release channel c1;
}

STEP 8. rename datafile and logfile
alter database rename file 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF' to  'D:\ORACLE\ORADATA\TEST1\SYSTEM01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF' to 'D:\ORACLE\ORADATA\TEST1\UNDOTBS01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF' to  'D:\ORACLE\ORADATA\TEST1\SYSAUX01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\USERS01.DBF' to   'D:\ORACLE\ORADATA\TEST1\USERS01.DBF';

alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO01.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO01.LOG';
alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO02.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO02.LOG';
alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO03.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO03.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO01B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO01B.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO02B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO02B.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO03B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO03B.LOG';


STEP 9. recover database to consistent state
rman target /
recover database until sequence 201;

STEP 10. open database with resetlogs and drop, recreate temp file
alter database open resetlogs;
alter tablespace temp add tempfile 'D:\ORACLE\ORADATA\TEST1\TEMP01.DBF' size 3G ;

ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\TEST\TEMP01.DBF' DROP INCLUDING DATAFILES;

STEP 11. set up network connection
sqlplus sys/test@test as sysdba

STEP 12. copy pfile of test to pfile of test1
chagne db name from test to test1

STEP 13. rename database
orapwd FILE=pwdtest.ora PASSWORD=test
startup mount
nid TARGET=SYS/test@test DBNAME=test1

STEP 14. set oracle_sid as test1 and open database with resetlogs
startup mount
alter database open restlogs;


REFERENCE:
http://dbaregistry.blogspot.com/2011/04/rman-duplicate-without-connecting-to.html
http://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm
http://docs.oracle.com/cd/B14117_01/server.101/b10825/dbnewid.htm
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupad.htm
http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmdupdb.htm
https://forums.oracle.com/thread/2421213
http://www.dba-oracle.com/t_rename_database_oracle_sid.htm
http://docs.oracle.com/html/B13831_01/admin.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14231/dfiles.htm
http://ss64.com/ora/rman_format_string.html
http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams112.htm
http://linuxtechres.blogspot.com/2011/10/how-to-create-oracle-database-service.html

TNS-12518, TNS-12540, TNS-12582, TNS-12615 Listener ERROR caused by swingbench OLTP test

SYMPTOM:
In listener.log file, below error emssage appear.

TNS-12518: TNS:listener could not hand off client connection
 TNS-12540: TNS:internal limit restriction exceeded
09-OCT-2013 13:46:31 * establish * 12540
TNS-12540: TNS:internal limit restriction exceeded
09-OCT-2013 13:46:31 * 12582
TNS-12582: TNS:invalid operation
 TNS-12615: TNS:preempt error

CAUSE:
Too many concurrent connection requests are received by listener.

Solution:

Increase the queuesize for listener. There is no more TNS error in log file.

listener_name=
 (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521)(QUEUESIZE=1000)))

TRACE LISTERNER in 11g:

TRACE_LEVLE_LISTENER_NAME=16 ( 0, no trace; 4, user; 10, admin; 16, support)

OTHER THOUGHT:
There is article (1399677.1) on metalink for the same set of errors. That is a bug cause by ADR check.
work around is to set _diag_adr_enabled=FALSE and/or apply Patch:9700115.
It look related to Unpublished Bug:9700115

Reference:
http://docs.oracle.com/cd/B28359_01/network.111/b28317/listener.htm
https://forums.oracle.com/thread/2520109

create table with only data using data pump

REQUIREMENT:
Snapshot of data in schema need be stored for comparison. Data snapshot is refreshed from time to time.

EASY SOLUTION:

1. create new schema
create user test1 identified by test;
grant connect to test1;

2. data pump schema including only table
expdp userid=system/test@test schemas=test include=table dumpfile=test_table_2013_10_01.dump logfile=test_table_2013_10_01.log

3. data pump schema back excluding index, trigger, constraints
impdp userid=system/test@test schemas=test remap_schema=test:test1 exclude=trigger,constraint,index dumpfile=test_table_2013_10_01.dump logfile=test_table_2013_10_01_imp1.log

4. refresh by truncate and insert if data is some.
select ' truncate table test1.' || table_name || ';' from user_tables;
select ' insert into test1.' || table_name || ' select * from test.' || table_name || ';' from user_tables;

5. refresh by data pump data only if data is large.
expdp userid=system/test@test schemas=test include=table dumpfile=test_table_2013_10_03.dump logfile=test_table_2013_10_03.log
impdp userid=system/test@test schemas=test remap_schema=test:test1 exclude=trigger,constraint,index dumpfile=test_table_2013_10_03.dump logfile=test_table_2013_10_03_imp1.log content=data_only


reference:
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html

Rebuild Standby database through rman restore after open it

1. remove archivelogs from default archivelog location

2. make rman backup files accessible to standby database

3. make rman know backup files
sqlplus> shutdown immediate;
sqlplus> startup mount;

rman target /
rman> catalog start with 'file location directory';

4. restore database
rman > run {
sql 'alter session set optimizer_mode=RULE';
sql 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"';
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT 'file location directory\DBFULL_%d_%U.bkp';
restore database from TAG "backup tag"   ;
RELEASE CHANNEL d1;
}

5. recover database
5.1 use controlfile before open database
rman target /
rman> recover database until sequence ?????;

5.2 use controlfile after open database
rman target /
rman> list incarnation;
rman> reset database to incarnation 1; ( previous number)
rman> recover database unitl seequnece ????;

5.3 test standby database
sqlplus / as sysdba
sqlplus> alter database open;
sqlplus> select name, host_name, open_mode, database_role from v$database, v$instance;

reference:
http://oracledb-admin.blogspot.com/2011/10/ora-19909-datafile-1-belongs-to-orphan.html
http://www.dba-oracle.com/bk_recover_database_until_cancel_tips.htm

GoldenGate (GG) replicat abended process troubleshooting.

GoldenGate (GG) replicat abended process troubleshooting.

1. Try to start the replicate
ggsci > start replicate TEST

2. check the replicate
ggsci > info all

It should be abended

3. check the process report
cd $GG_DIR/bin/dirrpt
less test.rpt

4. find the trail file and RBA from report

5. get sql statement using logdump
/*
configure below setting on logdump
fileheader detail
ghdr on
detail on
usertoken detail
reclen 128
*/

cd $GG_DIR/bin
logdump
logdump> open app/oracle/gg/trail/test/dirdat/pt0001
logdump> pos 100000
logdump> fileheader detail
logdump> ghdr on
logdump> detail on
logdump> usertoken detail
logdump> reclen 128
logdump> n

___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :  1577  (x0629)   IO Time    : 2013/09/24 23:15:09.334.111
IOType     :   160  (xa0)     OrigNode   :     0  (x00)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :          0       AuditPos   : 0
Continued  :     N  (x00)     RecCount   :     1  (x01)

2013/09/24 23:15:09.334.111 DDLOP                Len  1000 RBA 100000
Name:
After  Image:                                             Partition 0   G  s
 2c43 353d 2733 3735 3139 272c 2c42 373d 2733 3735 | ,C5='37519',,B7='375
 3139 272c 2c42 323d 2727 2c2c 4233 3d27 4d4f 4e41 | 19',,B2='',,B3='TTT
 444d 494e 272c 2c42 343d 2744 4841 5f54 4d50 5f23 | TTT',,B4='TTT_TMP_#
 5447 5439 5f33 3138 3237 3133 3227 2c2c 4331 323d | TGT9_000001',,C12=
 2727 2c2c 4331 333d 2727 2c2c 4235 3d27 5441 424c | '',,C13='',,B5='TABL
 4527 2c2c 4236 3d27 4352 4541 5445 272c 2c42 383d | E',,B6='CREATE',,B8=
 2747 4755 5345 522e 4747 535f 4444 4c5f 4849 5354 | 'GGUSER.GGS_DDL_HIST
 272c 2c42 393d 274d 4f4e 4144 4d49 4e27 2c2c 4337 | ',,B9='TTTTTTT',,C7
 3d27 3130 2e32 2e30 2e34 2e30 272c 2c43 383d 2731 | ='10.2.0.4.0',,C8='1
 302e 322e 302e 332e 3027 2c2c 4339 3d27 272c 2c43 | 0.2.0.3.0',,C9='',,C
 3130 3d27 3127 2c2c 4331 313d 2764 7264 6976 6572 | 10='1',,C11='test
 7327 2c2c 4733 3d27 4e4f 4e55 4e49 5155 4527 2c2c | t',,G3='NONUNIQUE',,
 4331 343d 274e 4f27 2c2c 4331 353d 274e 4f27 2c2c | C14='NO',,C15='NO',,
 4331 393d 2731 3727 2c2c 4331 3728 2731 2729 3d27 | C19='17',,C17('1')='
 4e4c 535f 4c41 4e47 5541 4745 272c 2c43 3138 2827 | NLS_LANGUAGE',,C18('
 3127 293d 2741 4d45 5249 4341 4e27 2c2c 4331 3728 | 1')='AMERICAN',,C17(
 2732 2729 3d27 4e4c 535f 5445 5252 4954 4f52 5927 | '2')='NLS_TERRITORY'


/*
reset reclen to record length according to output
*/
logdump> reclen 1000
/*
position back to 100000
*/
logdump> pos 100000
logdump> n

/*
you will have output for whole record. The left Panel C1 variable is the SQL statement.
you can try it to find out the real error
*/

6. fixed the issue and restart the replicate
how to fix depends on errors and database settings and even data.

7. skip the transaction and restart the replicate
if you find out that the transaction can be skip.
find RBA position for next record using logdump

logdump> pos 100000
logdump> n
logdump> n

2013/09/24 23:15:09.334.111 DDLOP                Len  115 RBA 101000
Name:
After  Image:                                             Partition 0   G  s
 2c43 353d 2733 3735 3139 272c 2c42 373d 2733 3735 | ,C5='37519',,B7='375
 3139 272c 2c42 323d 2727 2c2c 4233 3d27 4d4f 4e41 | 19',,B2='',,B3='TTT
 444d 494e 272c 2c42 343d 2744 4841 5f54 4d50 5f23 | TTT',,B4='TTT_TMP_#
 5447 5439 5f33 3138 3237 3133 3227 2c2c 4331 323d | TGT9_000001',,C12=
 2727 2c2c 4331 333d 2727 2c2c 4235 3d27 5441 424c | '',,C13='',,B5='TABL
 4527 2c2c 4236 3d27 4352 4541 5445 272c 2c42 383d | E',,B6='CREATE',,B8=
 2747 4755 5345 522e 4747 535f 4444 4c5f 4849 5354 | 'GGUSER.GGS_DDL_HIST
 272c 2c42 393d 274d 4f4e 4144 4d49 4e27 2c2c 4337 | ',,B9='TTTTTTT',,C7
 3d27 3130 2e32 2e30 2e34 2e30 272c 2c43 383d 2731 | ='10.2.0.4.0',,C8='1
 302e 322e 302e 332e 3027 2c2c 4339 3d27 272c 2c43 | 0.2.0.3.0',,C9='',,C
 3130 3d27 3127 2c2c 4331 313d 2764 7264 6976 6572 | 10='1',,C11='test
 7327 2c2c 4733 3d27 4e4f 4e55 4e49 5155 4527 2c2c | t',,G3='NONUNIQUE',,
 4331 343d 274e 4f27 2c2c 4331 353d 274e 4f27 2c2c | C14='NO',,C15='NO',,
 4331 393d 2731 3727 2c2c 4331 3728 2731 2729 3d27 | C19='17',,C17('1')='
 4e4c 535f 4c41 4e47 5541 4745 272c 2c43 3138 2827 | NLS_LANGUAGE',,C18('
 3127 293d 2741 4d45 5249 4341 4e27 2c2c 4331 3728 | 1')='AMERICAN',,C17(
 2732 2729 3d27 4e4c 535f 5445 5252 4954 4f52 5927 | '2')='NLS_TERRITORY'


ggsci> alter replicat test, extrba 101000
ggsci>info all
Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     TEST       16:34:27      00:45:14

ggsci> start replicat test
ggsci> info all
Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TEST       16:34:27      00:45:14


reference:
http://blog.flimatech.com/2012/03/24/how-to-find-the-transaction-that-abended-oracle-goldengate/
http://satya-dba.blogspot.com/2012/02/ggsci-goldengate-command-interpreter.html

create directory for mapping drive in oracle database on windows

ISSUE:
data pump can not create files in directory created for mapping drive like S:\test_dir on windows.

CAUSE:
Mapping drive is specific for each account. The mapping drive like S:\test_dir must be recognized by the account running oracle service.

SOLUTION:

1. make sure the mapping drive you specified in " create directory statement" is available as the same mapping drive for the account running the oracle service. You can check services->properties -> logon to find the account name.

2. use direct network drive name like \\192.168.1.3\shearplace\testdir
    SQL: create directory test_dir as '\\192.168.1.3\shearplace\testdir';

reference:
https://forums.oracle.com/message/9981090

statistics manipulation commands

1. create local stats table
exec DBMS_STATS.CREATE_STAT_TABLE (ownname => 'TUSER', stattab => 'TEST_STAT_TABLE');

2. export current table stats to local table
exec DBMS_STATS.EXPORT_TABLE_STATS(ownname => 'TUSER',tabname => 'TTABLE',stattab => 'TEST_STAT_TABLE', statid => 'T1', cascade => true);

3. delete current table stats
exec DBMS_STATS.DELETE_TABLE_STATS(ownname => 'TUSER',tabname => 'TTABLE', cascade_columns => true, cascade_indexes => true);

4. import local stats to table
exec DBMS_STATS.IMPORT_TABLE_STATS(ownname => 'TUSER',tabname => 'TTABLE',stattab => 'TEST_STAT_TABLE', statid => 'T1', cascade => true);

5. check statistics

select statid, count(*) from TEST_STAT_TABLE group by statid;

6. statistics stored in table can be moved to other database and be imported.

7. Columns “C5” and “C1” of the statistics table represent schema owner and table name respectively. if owner and table_name is different when imported, update this two columns.

reference:
http://momendba.blogspot.com/2011/01/import-table-statistics-to-another.html

Swingbench installation

1.down load the file

2. unzip the file to the location you want it to be.

3. include java_home/bin into path variable

4. run oewizard to install soe schema for order entry test

5. check table record change for order entry test
select count(*) from CUSTOMERS ;
select count(*) from LOGON ;
select count(*) from ORDERS ;
select count(*) from ORDER_ITEMS ;

6. below table can be truncate and will be loaded when testing.

truncate table logon;
truncate table order_ITEMS;

reference:
http://www.dominicgiles.com/installation.html

create standby database from standby database

Task: create a new standby database from another standby database for standard edition_one version.

Since dataguard is not available, archived log need be manually applied.

Method: 

1. put standby database to read-only

alter database open (open read only);

2. copy all data file, control file, password file. parameter file  to new location.

You need rename them if location is changed.
alter database rename file 'J:\ORACLE\ORADATA\TEST\TEST_01.DBF' to    'O:\ORACLE\ORADATA\TEST\TEST_01.DBF' ;

3. start up database mount and apply log

sqlplus / as sysdba

startup mount

recover standy database.

Points. 

1. control file from a standby database can be directly copied and used for new standby database. You don't need create a standby control file from primary one.

2. if control file is not copied during read only time period, you can copy it later. However this will cause a problem if new file was added after datafile copy.

3. if latest control file is used and includes new added data files, the new data files can be copied from standby database again after it is put back to read only. Even though the timestamp is not same for the first copied data files and later copied ones, archive log can be applied to both and make them sync. 

4. if you use old control file and new file is added, you will have to add new file manually in 10g version.

alter database create datafile  'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00160' as 'I:\ORACLE\ORADATA\TEST_INDX38.DBF' ;