1. For single database in ASM:
# srvctl status database -d <db_unique_name>
# srvctl status listener
# srvctl stop database -d <db_unique_name> -o immediate
# srvctl stop listener
# srvctl start listener
# srvctl start database -d <db_unique_name>
# srvctl status database -d <db_unique_name>
# srvctl status listener
2. For RAC database in Filesystem or ASM
If service name is PROD, instance name is PROD1 (hostname is HOST1) and PROD2 (hostname is HOST2)
# srvctl status database -d PROD
# srvctl status listener
# srvctl stop database -d PROD -o immediate
or
# srvctl stop instance -d PROD -i PROD1,PROD2 -o immediate
# srvctl stop listener
or
# srvctl stop listener -n HOST1
# srvctl start listener
or
# srvctl start listener -n HOST1
# srvctl start database -d PROD
or
# srvctl start instance -d PROD -i PROD1,PROD2
# srvctl status database -d <db_unique_name>
# srvctl status listener
Reference:
https://docs.oracle.com/html/E25494_01/restart005.htm#srvstartdb1
2017年7月24日 星期一
2015年1月4日 星期日
Oracle ERP Daily Check
-- DB
invalid object
blocking lock
unusable index
enable but invalid trigger
top 5 event
long running session
session count
tablespace free status
tablespace space compare with the previous day
segment space compare with the previous day
-- AP
concurrent program enable run alone
concurrent program enable trace
diable User Has Pending concurrent request
concurrent request running but completed
concurrent request running
concurrent manager queue status
invalid object
blocking lock
unusable index
enable but invalid trigger
top 5 event
long running session
session count
tablespace free status
tablespace space compare with the previous day
segment space compare with the previous day
-- AP
concurrent program enable run alone
concurrent program enable trace
diable User Has Pending concurrent request
concurrent request running but completed
concurrent request running
concurrent manager queue status
2014年12月30日 星期二
"先 outer join 再 filter " 的結果不一定等於 "先 filter 再 outer join "
這是個很弔詭的問題，直覺上我們會覺得先join 再filter 跟先filter 再 join 的結果是相同的，的確，這情況會發生在 inner join 身上，所以想當然爾，我們也會認為outer join 的結果也會相同，但是事實卻不是如此。
舉例來說，如果是先 left outer join 再 filter 的話，right table會因為 filter 的欄位而缺少配對的數值，換個角度看，因為缺少配對的數值，所以應該要存在的值就變成是 NULL，所以這也是為何加上 ..... OR column is NULL 會變成正確的原因。
老實說，這很難理解，但是我們如果先 filter 再做 left outer join 的話，問題就會變的簡單多了
先 filter column = 'Y' 是我們想要的第一層結果，再做 left outer join 是我們想要的第二層結果，讓 left table 全數呈現。
結論是，今後遇到 outer join 加上 filter 的問題，先使用 sub-query 做 filter ，再做 outer join。
舉例來說，如果是先 left outer join 再 filter 的話，right table會因為 filter 的欄位而缺少配對的數值，換個角度看，因為缺少配對的數值，所以應該要存在的值就變成是 NULL，所以這也是為何加上 ..... OR column is NULL 會變成正確的原因。
老實說，這很難理解，但是我們如果先 filter 再做 left outer join 的話，問題就會變的簡單多了
先 filter column = 'Y' 是我們想要的第一層結果，再做 left outer join 是我們想要的第二層結果，讓 left table 全數呈現。
結論是，今後遇到 outer join 加上 filter 的問題，先使用 sub-query 做 filter ，再做 outer join。
2014年12月17日 星期三
How To Start OSWatcher Black Box (OSWBB) Every System Boot
APPLIES TO:
Reference from Oracle Doc ID 580513.1Linux OS - Version Oracle Linux 4.6 to Oracle Linux 6.3 [Release OL4U6 to OL6U3]
Oracle VM - Version 2.1 to 3.1.1 [Release OVM21 to OVM31]
Information in this document applies to any platform.
GOAL
Oracle support often recommends that the OSWatcher Black Box(*) tool be run for an extended period. Should the system reboot during this time, the system administrator must manually restart the OSWatche Black Boxr, and allow it to run until the necessary data have been collected.
* NOTE:
Depending on the version you have, the OSWatcher Black Box tool may also be referred to as simply OSWatcher; the nomenclature has been changed.
To automate this procedure, a simple shell script can be used. Care must be taken to avoid accidentally overwriting the log data upon a restart. The script must also ensure that the OSWatcher Black Box tool be run using the correct user privileges.
The osw-service RPM package does not include the OSWatcher Black Box tool. OSWatcher Black Box must be downloaded separately.
SOLUTION
The osw-service RPM package provides a script to run the OSWatcher at system boot, and to stop it down gracefully at system shutdown. It provides an "osw" service that can be controlled using the standard Linux init(1) script controls:
# /sbin/chkconfig oswbb on
# /sbin/service oswbb start
# /sbin/service oswbb start
The osw-service RPM package is available as an attachment to this note. Download and install it as any other RPM package. A source RPM is provided for completeness.
Before starting the service, first change the settings in the /etc/oswbb.conf configuration file to fit your situation:
# Set OSW_HOME to the directory where your OSWatcher tools are installed
OSW_HOME='/opt/osw'
# Set OSW_INTERVAL to the number of seconds between collections
OSW_INTERVAL='60'
# Set OSW_RETENTION to the number of hours logs are to be retained
OSW_RETENTION='48'
# Set OSW_USER to the owner of the OSWHOME directory
OSW_USER='root'
# Set OSW_COMPRESSION to the desired compression scheme
OSW_COMPRESSION='bzip2'
# Set OSW_ARCHIVE to the location to generate the logs
OSW_ARCHIVE='archive'
OSW_HOME='/opt/osw'
# Set OSW_INTERVAL to the number of seconds between collections
OSW_INTERVAL='60'
# Set OSW_RETENTION to the number of hours logs are to be retained
OSW_RETENTION='48'
# Set OSW_USER to the owner of the OSWHOME directory
OSW_USER='root'
# Set OSW_COMPRESSION to the desired compression scheme
OSW_COMPRESSION='bzip2'
# Set OSW_ARCHIVE to the location to generate the logs
OSW_ARCHIVE='archive'
Once this is done, the command:
# /sbin/service oswbb start
will start the OSWatcher tool upon every boot.
The OSWatcher logs will be stored in ${OSW_ARCHIVE); the default value is the archive/ subdirectory as in earlier versions
How To Collect an Sosreport on Oracle Linux
APPLIES TO:
Reference from Oracle Doc ID 1500235.1Linux OS - Version Oracle Linux 4.4 and later
Oracle VM - Version 3.2.7 to 3.2.7 [Release OVM32]
Information in this document applies to any platform.
GOAL
The "sosreport" is a tool to collect troubleshooting data on an Oracle Linux system. It generates a compressed tarball of debugging information that gives an overview of the most important logs and configuration of a Linux system, to be sent to Oracle Support.
Among other things, the sosreport includes information about the installed rpm versions, syslog, network configuration, mounted filesystems, disk partition details, loaded kernel modules and status of all services
It has a plugin-based architecture that enables features to be enabled or disabled, and additional functionality added.
Why support needs sosreport
The sosreport collects system information from an Oracle Linux system by capturing various log files, configuration files and command outputs that helps in diagnosing a problem faster.
The sosreport collects system information from an Oracle Linux system by capturing various log files, configuration files and command outputs that helps in diagnosing a problem faster.
Since this collects most of the commonly sort information while troubleshooting problems, collecting a sosreport helps in reducing the number of iterations of data request from the customer.
The logs, configuration files and related command outputs provides a better picture about the system environment and thus it is very helpful for cases about Root cause analysis and on going issues.
The sosreport helps the support to identify configuration errors and make proactive recommendations too.
SOLUTION
Main Content
To run sosreport, the package "sos" must be installed. This is usually installed by default, unless the system was installed with a custom package set. If it is not installed, it can be installed from ULN with up2date, the public Oracle Linux repository or from installation media. It is also a good idea to make sure it is up to date.
To create the sosreport can be as simple as running the command in a terminal, without arguments, as root:
# sosreport
It will ask for some information related to a support case:
# sosreport
sosreport (version 1.7)
This utility will collect some detailed information about the
hardware and setup of your Enterprise Linux system.
The information is collected and an archive is packaged under
/tmp, which you can send to a support representative.
This information will be used for diagnostic purposes ONLY
and it will be considered confidential information.
This process may take a while to complete.
No changes will be made to your system.
Press ENTER to continue, or CTRL-C to quit.
Please enter your first initial and last name [testsystem-2]: jdoe
Please enter the case number that you are generating this report for: 3-1234567890
sosreport (version 1.7)
This utility will collect some detailed information about the
hardware and setup of your Enterprise Linux system.
The information is collected and an archive is packaged under
/tmp, which you can send to a support representative.
This information will be used for diagnostic purposes ONLY
and it will be considered confidential information.
This process may take a while to complete.
No changes will be made to your system.
Press ENTER to continue, or CTRL-C to quit.
Please enter your first initial and last name [testsystem-2]: jdoe
Please enter the case number that you are generating this report for: 3-1234567890
and will then show a progress bar with estimated time:
Progress [# 3% ][00:33/19:54]
On completion, a bzip2-compressed tarball will be created in /tmp, along with a file containing the md5sum so that the file's integrity can be verified by the support representative. The filename will be printed to the terminal:
Progress [###################100%##################][03:15/03:15]
Creating compressed archive...
Your sosreport has been generated and saved in:
/tmp/sosreport-jdoe.3123456789-182843-6a3d9a.tar.bz2
The md5sum is: 17be8be4266b06efd9726825ff6a3d9a
Please send this file to your support representative.
Creating compressed archive...
Your sosreport has been generated and saved in:
/tmp/sosreport-jdoe.3123456789-182843-6a3d9a.tar.bz2
The md5sum is: 17be8be4266b06efd9726825ff6a3d9a
Please send this file to your support representative.
It is possible to have the sosreport created somewhere other than /tmp by setting the TMPDIR environment variable when running the sosreport command:
# TMPDIR=/home/jdoe sosreport
Your sosreport has been generated and saved in:
/home/jdoe/sosreport-jdoe.31234567890-904562-30011c.tar.bz2
/home/jdoe/sosreport-jdoe.31234567890-904562-30011c.tar.bz2
Additional options:
To list available plugins:
# sosreport -l
sosreport (version 1.7)
The following plugins are currently enabled:
apache Apache related information
autofs autofs server-related information
bootloader Bootloader information
devicemapper device-mapper related information (dm, lvm, multipath)
emc EMC related information (PowerPath, Solutions Enabler CLI and Navisphere CLI)
filesys information on filesystems
general basic system information
hardware hardware related information
ipsec ipsec related information
kernel kernel related information
ldap LDAP related information
libraries information on shared libraries
mail mail server related information
memory memory usage information
networking network related information
nfsserver NFS server-related information
pam PAM related information
printing printing related information (cups)
process process information
rpm RPM information
samba Samba related information
sendmail sendmail information
ssh ssh-related information
startup startup information
system core system related information
systemtap SystemTap pre-requisites information
x11 X related information
xen Xen related information
yum yum information
The following plugins are currently disabled:
amd Amd automounter information
cluster cluster suite and GFS related information
ftp FTP server related information
initrd initrd related information
named named related information
openswan ipsec related information
radius radius related information
rhn RHN Satellite related information
s390 s390 related information
selinux selinux related information
squid squid related information
xinetd xinetd information
The following plugin options are available:
devicemapper.lvmdump off collect raw metadata from PVs
general.syslogsize 15 max size (MiB) to collect per syslog file
kernel.modinfo on gathers module information on all modules
kernel.sysrq off trigger sysrq+[m,p,t] dumps
networking.traceroute off collects a traceroute to linux.oracle.com
rpm.rpmq on queries for package information via rpm -q
rpm.rpmva on runs a verify on all packages
yum.yumlist off list repositories and packages
sosreport (version 1.7)
The following plugins are currently enabled:
apache Apache related information
autofs autofs server-related information
bootloader Bootloader information
devicemapper device-mapper related information (dm, lvm, multipath)
emc EMC related information (PowerPath, Solutions Enabler CLI and Navisphere CLI)
filesys information on filesystems
general basic system information
hardware hardware related information
ipsec ipsec related information
kernel kernel related information
ldap LDAP related information
libraries information on shared libraries
mail mail server related information
memory memory usage information
networking network related information
nfsserver NFS server-related information
pam PAM related information
printing printing related information (cups)
process process information
rpm RPM information
samba Samba related information
sendmail sendmail information
ssh ssh-related information
startup startup information
system core system related information
systemtap SystemTap pre-requisites information
x11 X related information
xen Xen related information
yum yum information
The following plugins are currently disabled:
amd Amd automounter information
cluster cluster suite and GFS related information
ftp FTP server related information
initrd initrd related information
named named related information
openswan ipsec related information
radius radius related information
rhn RHN Satellite related information
s390 s390 related information
selinux selinux related information
squid squid related information
xinetd xinetd information
The following plugin options are available:
devicemapper.lvmdump off collect raw metadata from PVs
general.syslogsize 15 max size (MiB) to collect per syslog file
kernel.modinfo on gathers module information on all modules
kernel.sysrq off trigger sysrq+[m,p,t] dumps
networking.traceroute off collects a traceroute to linux.oracle.com
rpm.rpmq on queries for package information via rpm -q
rpm.rpmva on runs a verify on all packages
yum.yumlist off list repositories and packages
If the system has a lot of packages installed, and sosreport takes a long time to complete, support may request that you disable the rpm database verification (verifies all packaged files on the filesystem against the rpm database)
# sosreport -k rpm.rpmva=off
In older versions of sosreport, the kernel.sysrq option was enabled by default. On heavily loaded systems, this can take a long time and can cause problems; particularly on clustered systems where it may cause a node eviction. It may be wise to check if this option is disabled by default on your system, or to manually disable it when running sosreport:
# sosreport -k kernel.sysrq=off
For further information, see the manual page
man sosreport or the help text sosreport --helpKnown issues
1. In earlier versions, sosreport could fail with:
# /usr/sbin/sosreport -k rpm.rpmva=off -n kernel.sysrq
Traceback (most recent call last):
File "/usr/sbin/sosreport", line 31, in <module>
import sos.policyredhat
ImportError: No module named sos.policyredhat
Traceback (most recent call last):
File "/usr/sbin/sosreport", line 31, in <module>
import sos.policyredhat
ImportError: No module named sos.policyredhat
This usually occurs when a 3rd-party version of python is installed and has been fixed in version 1.7-6 and newer
2. Run sosreport may change the /usr/java/latest symbolic link
sosreport (or rpm -Va, by default part of a sosreport) may change /usr/java/latest symbolic link if it does not point to latest installed release (Doc ID 1597124.1)
REFERENCES
NOTE:1597124.1 - sosreport (or rpm -Va, by default part of a sosreport) may change /usr/java/latest symbolic link if it does not point to latest installed release2014年12月16日 星期二
ORA-02070: database does not support in this context
現象：
insert into remote_table@dblink(columns.....)
select columns..... from local_view ;
==> ORA-02070: database does not support in this context
解決方式：
1. 將 select columns..... from local_view 換成 select columns..... from local_table.....，不要使用view，改成table
2. 將procedure 由 local DB執行改為 remote DB執行，例如
insert into local_table(columns...) select columns... from remote_view@dblink ;
3. 將 procedure 改成 cursor 運作
參考來源：
ORA-2070 during an remote Insert over Database link while selecting from local dictionary view (Doc ID 744219.1)
Information in this document applies to any platform.
//
The operation is unsupported which is clear from Bug 469264 "INSERT INTO REMOTE TABLE SELECT FROM LOCAL DICTIONARY VIEW GIVES 2070"
"The error is not a bug, but I've changed the message to print the name ofthe operation (in this case we will print "USERENV" instead of "operator 105") to make diagnosing possible."
Here Dblink is the name of Database Link that connects to Database where information of table V$instance are taken.
declare
cursor c1 is select * from V$instance;
begin
for i in c1 loop
insert into T2@Dblink values(i.INSTANCE_NUMBER ,
i.INSTANCE_NAME ,
i.HOST_NAME,
i.VERSION ,
i.STARTUP_TIME ,
i.STATUS ,
i.PARALLEL ,
i.THREAD# ,
i.ARCHIVER ,
i.LOG_SWITCH_WAIT ,
i.LOGINS ,
i.SHUTDOWN_PENDING,
i.DATABASE_STATUS ,
i.INSTANCE_ROLE ,
i.ACTIVE_STATE ,
i.BLOCKED ) ;
end loop;
end;
/
commit;
Here "Dblink" is the name of Database link that connects
from Local database (where information on V$instance is taken)
to Remote database(where table T2 exists).
insert into remote_table@dblink(columns.....)
select columns..... from local_view ;
==> ORA-02070: database does not support in this context
解決方式：
1. 將 select columns..... from local_view 換成 select columns..... from local_table.....，不要使用view，改成table
2. 將procedure 由 local DB執行改為 remote DB執行，例如
insert into local_table(columns...) select columns... from remote_view@dblink ;
3. 將 procedure 改成 cursor 運作
參考來源：
ORA-2070 during an remote Insert over Database link while selecting from local dictionary view (Doc ID 744219.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 7.2.3.0 to 11.2.0.2 [Release 7.2.3 to 11.2]Information in this document applies to any platform.
SYMPTOMS
ORA-2070 Error occurs when inserting into remote Table with Select from local Dictionary Views.
For Example :"Ora10g" is Local Database and "Dblink" is name of Database link that connects to Remote Database where Table T2 exists..T2 has same DDL structure as V$instance.
On executing below Remote Insert Statement, ORA-02070 Error occurs
For Example :"Ora10g" is Local Database and "Dblink" is name of Database link that connects to Remote Database where Table T2 exists..T2 has same DDL structure as V$instance.
On executing below Remote Insert Statement, ORA-02070 Error occurs
insert into t2@ora10g(select *from v$instance)
*
ERROR at line 1:
ORA-02070: database does not support in this context
*
ERROR at line 1:
ORA-02070: database does not support in this context
CHANGES
CAUSE
02070, 00000, "database %s%s does not support %s in this context"
// *Cause: The remote database does not support the named capability in
// the context in which it is used.
// *Action: Simplify the SQL statement.
// *Cause: The remote database does not support the named capability in
// the context in which it is used.
// *Action: Simplify the SQL statement.
//
The operation is unsupported which is clear from Bug 469264 "INSERT INTO REMOTE TABLE SELECT FROM LOCAL DICTIONARY VIEW GIVES 2070"
"The error is not a bug, but I've changed the message to print the name ofthe operation (in this case we will print "USERENV" instead of "operator 105") to make diagnosing possible."
SOLUTION
Workaround
You can use any one of 2 Solutions as shown below to resolve the issue:
1) Use Insert Statement in Remote Database and use Database links in Select statements .
1) Use Insert Statement in Remote Database and use Database links in Select statements .
insert into t2 (select * from v$instance@Dblink);
commit;
commit;
Here Dblink is the name of Database Link that connects to Database where information of table V$instance are taken.
2) Use a PL/Sql cursor in local Database to fetch Data from Local Database and then Insert into remote Database as shown in below Example :
declare
cursor c1 is select * from V$instance;
begin
for i in c1 loop
insert into T2@Dblink values(i.INSTANCE_NUMBER ,
i.INSTANCE_NAME ,
i.HOST_NAME,
i.VERSION ,
i.STARTUP_TIME ,
i.STATUS ,
i.PARALLEL ,
i.THREAD# ,
i.ARCHIVER ,
i.LOG_SWITCH_WAIT ,
i.LOGINS ,
i.SHUTDOWN_PENDING,
i.DATABASE_STATUS ,
i.INSTANCE_ROLE ,
i.ACTIVE_STATE ,
i.BLOCKED ) ;
end loop;
end;
/
commit;
Here "Dblink" is the name of Database link that connects
from Local database (where information on V$instance is taken)
to Remote database(where table T2 exists).
2014年12月8日 星期一
RMAN Backup and Recovery (Oracle 10g on AIX)
- Install Oracle 10g software for RMAN catalog DB
Environment:
OS Level = AIX 5.3
Oracle DB Version = 10.2.0.3
Oracle Catalog DB SID = CATDB
Oracle RAC DB SID = PROD
Oracle RAC DB SID1 = PROD1
Oracle RAC DB SID2 = PROD2
TDPO node name = dbsrv_oracle
1. Install 10.2.0.1 software DB
login as root
cd /source/Disc1/cdrom/database/rootpre
./rootpre.sh
login as oracle
vi .profile
export TMP=/oracle/tmp
export TMPDIR=/oracle/tmp
export ORACLE_HOME=/oracle/10.2.0/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_SID=catdb
cd /source/Disc1/cdrom/database
export DISPLAY=catdbsrv:1.0
mkdir /oracle/tmp
./runInstaller
install software only
2. Apply 10.2.0.2 patchset
cd /source/patch/Disk1
./runInstaller
3. Create catalog database
dbca (SID=catdb)
4. Create Listener
netca
5. Create alias for PROD db
netca
- Configure Catalog Database
OS Level = AIX 5.3
Oracle DB Version = 10.2.0.3
Oracle Catalog DB SID = CATDB
Oracle RAC DB SID = PROD
Oracle RAC DB SID1 = PROD1
Oracle RAC DB SID2 = PROD2
TDPO node name = dbsrv_oracle
1. Install 10.2.0.1 software DB
- Configure Catalog Database
1. Creating tablespace to store backup information
SQL> create tablespace rmancat datafile
'/rman/catdb/rmancat01.dbf' size 4g;
2. Creating the Recovery Catalog Owner
SQL> CREATE USER rman IDENTIFIED BY cat
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rmancat
QUOTA UNLIMITED ON rmancat;
SQL> grant CONNECT,RESOURCE,RECOVERY_CATALOG_OWNER to rman;
3. Creating the Recovery Catalog
oracle> rman catalog rman/cat
oracle> rman catalog rman/cat
RMAN> CREATE CATALOG;
4. Verify the user
sqlplus rman/cat
SQL> SELECT TABLE_NAME FROM USER_TABLES;
- Configure Production Database
1. Login to db server using oracle account
2. Create tnsnames alias for catdb
netca
3. Link TSM Data Protection for Oracle
ln -s /usr/lib/libobk64.a $ORACLE_HOME/lib/libobk.a
4. Define Data Protection for Oracle options
login root
vi /usr/tivoli/tsm/client/oracle/bin64/tdpo.opt
dsmi_orc_config /usr/tivoli/tsm/client/oracle/bin64/dsm.opt
dsmi_log /usr/tivoli/tsm/client/oracle/bin64
tdpo_node dbsrv_oracle
tdpo_pswdpath /usr/tivoli/tsm/client/oracle/bin64
vi /usr/tivoli/tsm/client/oracle/bin64/dsm.opt
SErvername tdpo
vi /usr/tivoli/tsm/client/ba/bin/dsm.sys
SErvername tdpo
vi /usr/tivoli/tsm/client/ba/bin/dsm.sys
SErvername tdpo
CHANGINGRETRIES 0
NODENAME dbsrv_oracle
PASSWORDACCESS prompt
COMMMethod TCPip
TCPPort 1600
TCPServeraddress xx.xx.xx.xx
cd /usr/tivoli/tsm/client/api/bin64
ln -s /usr/tivoli/tsm/client/ba/bin/dsm.sys dsm.sys
login root
dsmadmc
REG NODE dbsrv_oracle oracleprod maxnummp=2 passexp=0
cd /usr/tivoli/tsm/client/oracle/bin64
tdpoconf password (enter <password> three times)
tdpoconf showenvironment (confirm the configuration)
rm /usr/tivoli/tsm/client/oracle/bin64/tdpoerror.log
- Managing Target Database in the Recovery Catalog
1. Registering a Database in the Recovery Catalog
login oracle
rman TARGET / CATALOG rman/cat@catdb
RMAN> REGISTER DATABASE;
RMAN> REPORT SCHEMA;
2. Configure Default RMAN Setting
RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt
PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE sbt
CONNECT 'SYS/<pwd>@PROD1'
PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE sbt
CONNECT 'SYS/<pwd>@PROD2'
PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> crosscheck archivelog all;
- PROD Database Backup Scripts
1. Level 0 Backup
rman target / catalog rman/cat@catdb
RMAN> backup incremental level 0
filesperset 50
format "PROD_df_level0_%t_%s_%p" database
plus archivelog format "PROD_arch_%t_%s_%h"
delete all input;
RMAN> delete noprompt obosolete;
(The script is create under $ORACLE_HOME/scripts/rman_bck_level0.sh)
2. Level 1 Backup
rman target / catalog rman/cat@catdb
RMAN> backup incremental level 1 cumulative
filesperset 50
format "PROD_df_level1_%t_%s_%p" database
plus archivelog format "PROD_arch_%t_%s_%h”
delete all input;
(The script is create under $ORACLE_HOME/scripts/rman_bck_level1.sh)
3. Archive Log Files Backup
rman target / catalog rman/cat@catdb
RMAN> backup
filesperset 10
format "PROD_arch_%t_%s_%h" archivelog all delete all input;
- Catalog Database Backup Scripts(Use DataPump)
1. Create Directory Definition for DataPump
sqlplus “/ as sysdba”
SQL> create directory dpump_dir as ‘/rman/bck_catdb’;
SQL> grant READ, WRITE ON DIRECTORY dpump_dir TO rman;
2. Use DataPump Export to backup the catalog
expdp rman/cat DUMPFILE=dpump_dir:exp_rman.dmp \
logfile=dpump_dir:exp_rman.log
- Restore & Recovery Scripts
1. Loss all database files, but control files, and online redo log files
are not loss
rman target / catalog rman/cat@catdb
RMAN> run {
startup mount;
restore database;
recover database;
alter database open;
}
2. Loss all database files, control files, and online redo log files
For this situation, you need to perform incomplete recovery. Please try to
find the latest archived log file information in the alert file, and check the
timing information
rman target / catalog rman/cat@catdb
RMAN> run {
startup nomount;
ALLOCATE CHANNEL T1 DEVICE TYPE sbt
PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
ALLOCATE CHANNEL T2 DEVICE TYPE sbt
PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
restore controlfile from autobackup;
alter database mount;
set until time = “to_date(‘<TIME_INFO>’,’YYYY/MM/DD HH24:MI:SS’)”;
restore database;
recover database;
alter database open resetlogs;
}
If the above recovery failed due to no latest archivelog file to be applied, then perform the following statements to open the database.
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> run { alter database open resetlogs; }
Remove all 2PC pending transactions
Generate the scripts first if there are many 2PC pending transactions, then edit the scripts
to add ‘commit;’ statement for every DBMS_TRANSACTION statement
sqlplus “/ as sysdba”
set linesize 120
set pages 24
set head off
spool remove_2pc_pending.sql
select 'exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('''||
LOCAL_TRAN_ID||''');' from DBA_2PC_PENDING;
spool off
Remove all expired archivelogs
$ cd /prodarch
remove restored archivelogs
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;
3. All the machines crash, need to find a new machine to startup the database
Install Oracle 10g Software on the new machine
Setup TSM and Data Protection for Oracle
Please refer the “Configure Production Database” Section
Create initial parameter initPROD1.ora with the following setting
background_dump_dest =/oracle/10.2.0/db/admin/PROD1/bdump
core_dump_dest =/oracle/10.2.0/db/admin/PROD1/cdump
user_dump_dest =/oracle/10.2.0/db/admin/PROD1/udump
db_name ="PROD"
instance_name =PROD1
control_files =('/prodora/control01.ctl','/proddata1/control02.ctl','/prodindx1/control03.ctl')
#Set the below to the same as the production target
undo_management =AUTO
undo_tablespace =APPS_UNDOTBS1
db_block_size = 8192
log_archive_format=%t_%s_%r.arc
log_archive_dest_1=’location=/prodarch/’
log_archive_format=%t_%s_%r.arc
sga_target=8G
db_cache_size=4G
shared_pool_size=2G
pga_aggregate_target=16G
compatible = 10.2.0.
# To clone RAC db to non-RAC db need to add this parameter
_no_recovery_through_resetlogs=TRUE
Perform the Recovery
rman target / catalog rman/cat@catdb
RMAN> run {
startup nomount;
ALLOCATE CHANNEL T1 DEVICE TYPE sbt
PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
ALLOCATE CHANNEL T2 DEVICE TYPE sbt
PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
restore controlfile from autobackup;
alter database mount;
set until time = “to_date(‘<TIME_INFO>’,’YYYY/MM/DD HH24:MI:SS’)”;
restore database;
recover database;
alter database open resetlogs;
}
If the above recovery failed due to no latest archivelog file to be applied, then perform the
following statements to open the database.
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> run { alter database open resetlogs; }
Remove all 2PC pending transactions
Generate the scripts first if there are many 2PC pending transactions, then edit the scripts
to add ‘commit;’ statement for every DBMS_TRANSACTION statement
sqlplus “/ as sysdba”
set linesize 120
set pages 24
set head off
spool remove_2pc_pending.sql
select 'exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('''||
LOCAL_TRAN_ID||''');' from DBA_2PC_PENDING;
spool off
Remove all expired archivelogs
$ cd /prodarch
remove restored archivelogs
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;
- Duplicate Database
1. create an alias entry in tnsnames.ora file for PROD and catdb(optional)
2. confirm the connection to PROD and catdb(optional) working
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = db1srv_vip.domain)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = PROD)
(INSTANCE_NAME = PROD1)
)
)
CATDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = catdbsrv.domain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = catdb)
)
)
sqlplus system/<pwd>@prod
sqlplus rman/cat@catdb
3. determine the files location and space to be restored
this information is used to set ‘db_file_name_convert’ parameter in
init<SID>.ora file
4. create the init<SID>.ora parameter file and administration directories
for the duplicate database, the parameter file should have the following
setting:
background_dump_dest =/newprod/10.2.0/db/admin/NEWPROD_otsrv/bdump
core_dump_dest =/newprod/10.2.0/db/adminNEWPROD_otsrv/cdump
user_dump_dest =/newprod/10.2.0/db/admin/NEWPROD_otsrv/udump
db_name ="NEWPROD"
instance_name =NEWPROD
control_files =('/newprod/data/control01.ctl','/newprod/data /control02.ctl','/newprod/data /control03.ctl')
db_file_name_convert =("/proddata1/","/newprod/data/", "/prodindx1/","/newprod/data/", "/prodindx1/","/
newprod/data/", "/prodora/","/newprod/data/")
log_file_name_convert =("/prodora/PROD/", "/newprod/data/",”/proddata1/”,”/newprod/data/”)
#Set the below to the same as the production target
undo_management =AUTO
undo_tablespace =APPS_UNDOTBS1
db_block_size = 8192
log_archive_format=%t_%s_%r.arc
log_archive_dest_1=’location=/arch/’
compatible = 10.2.0.
# To clone RAC db to non-RAC db need to add this parameter
_no_recovery_through_resetlogs=TRUE
5. Startup the duplicate database instance
Sqlplus “/ as sysdba”
SQL> startup nomount;
6. Prepare RMAN duplicate script
run {
allocate auxiliary channel t1 type sbt
parms="ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)";
allocate auxiliary channel t2 type sbt
parms="ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)";
set until time "to_date('<the_time_within_RMAN_backup>','YYYY/MM/DD
HH24:MI:SS')";
duplicate target database to NEWPROD;
}
7. run RMAN to duplicate the database
rman target sys/<pwd>@prod nocatalog auxiliary /
RMAN> @duplicate.script
2014年11月4日 星期二
Oracle 資料庫轉換字元集工具：csscan
# 主題
CSSCAN (Character Set Scanner)
# 適用版本
Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Oracle Database - Standard Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.
# 現象/目的
在從前建置Oracle DB時，我們會將字元集設定成BIG5，但是網路促成的無國界情境使得種種軟體包含資料庫本身都必須容納多國語系，所以公司內部的資料庫大多早早已轉換成UTF8，但是受限於商業軟體，或者是其他考量，少部分資料庫還是停留在BIG5，倘若有朝一日當資料庫需要做轉換時，如果來源端跟目的端的資料庫字元集不一致時，必須先使用csscan 工具來確認對資料的影響性。
# 解決方式/內容
1. 準備動作
1.1 確認目前字元集:
SQL> select value
from NLS_DATABASE_PARAMETERS
where parameter='NLS_CHARACTERSET';
NLS_CHARACTERSET 定義存放在資料庫的是何種字元，而非由 NLS_LANGUAGE 或是 NLS_TERRITORY 決定的
1.2 DB Version小於10.2.0.4或是11.1.0.6，impdp會有資料毀損狀況，expdp不受影響，apply patch 5874989可解決此bug。這問題在10.2.0.4 and 11.1.0.7 patch set，或是11.2.0.1 以上版本被解決。
1.3 清除recyclebin
$ sqlplus / as sysdba
SQL> SELECT OWNER, ORIGINAL_NAME, OBJECT_NAME, TYPE
FROM dba_recyclebin ORDER BY 1,2;
SQL> purge dba_recyclebin;
1.4 compile invalid objects
$ sqlplus / as sysdba
SQL> SELECT owner,object_name,object_type,status
FROM dba_objects
WHERE status ='INVALID';
SQL> @?/rdbms/admin/utlrp.sql
1.5 可以移除sample schema：'HR', 'OE', 'SH', 'PM', 'IX', 'BI' and 'SCOTT'，如果沒有用到APEX / HTML DB也可移除FLOWS_XXX 以及APEX_XXX Users。
2. 參考 Note 745809.1 安裝必要物件 (10g / 11g)
$ export ORACLE_SID=ORCL
$ sqlplus /nolog
SQL> conn / as sysdba
SQL> set termout on
SQL> set echo on
SQL> spool csminst.log
SQL> -- note the drop user
SQL> drop user csmig cascade;
SQL> @?/rdbms/admin/csminst.sql
3. 執行 csscan
$ csscan \"system/password as sysdba\" USER=schema TOCHAR=UTF8 ARRAY=1024000 PROCESS=3 LOG=csscan_log
4. 檢查產出的報表並修正
4.1 csscan_log.out ==> 執行csscan的過程
4.2 csscan_log.txt ==> tables / column 有無異常的總表
4.3 csscan_log.err ==> tables / column 有異常的資料明細
4.4 通常BIG5轉UTF8欄位大小是除以2乘以3：column_size / 2 * 3 (因為一個中文字在BIG5中佔2個bytes，在UTF8中佔3個bytes)
# 參考文件
Note 225912.1
Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET )
Note 458122.1
Installing and Configuring Csscan in 8i and 9i (Database Character Set Scanner)
Note 745809.1
Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner)
Note 444701.1
Csscan output explained
(Doc ID 260192.1)
Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g
CSSCAN (Character Set Scanner)
# 適用版本
Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Oracle Database - Standard Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.
# 現象/目的
在從前建置Oracle DB時，我們會將字元集設定成BIG5，但是網路促成的無國界情境使得種種軟體包含資料庫本身都必須容納多國語系，所以公司內部的資料庫大多早早已轉換成UTF8，但是受限於商業軟體，或者是其他考量，少部分資料庫還是停留在BIG5，倘若有朝一日當資料庫需要做轉換時，如果來源端跟目的端的資料庫字元集不一致時，必須先使用csscan 工具來確認對資料的影響性。
# 解決方式/內容
1. 準備動作
1.1 確認目前字元集:
SQL> select value
from NLS_DATABASE_PARAMETERS
where parameter='NLS_CHARACTERSET';
NLS_CHARACTERSET 定義存放在資料庫的是何種字元，而非由 NLS_LANGUAGE 或是 NLS_TERRITORY 決定的
1.2 DB Version小於10.2.0.4或是11.1.0.6，impdp會有資料毀損狀況，expdp不受影響，apply patch 5874989可解決此bug。這問題在10.2.0.4 and 11.1.0.7 patch set，或是11.2.0.1 以上版本被解決。
1.3 清除recyclebin
$ sqlplus / as sysdba
SQL> SELECT OWNER, ORIGINAL_NAME, OBJECT_NAME, TYPE
FROM dba_recyclebin ORDER BY 1,2;
SQL> purge dba_recyclebin;
1.4 compile invalid objects
$ sqlplus / as sysdba
SQL> SELECT owner,object_name,object_type,status
FROM dba_objects
WHERE status ='INVALID';
SQL> @?/rdbms/admin/utlrp.sql
1.5 可以移除sample schema：'HR', 'OE', 'SH', 'PM', 'IX', 'BI' and 'SCOTT'，如果沒有用到APEX / HTML DB也可移除FLOWS_XXX 以及APEX_XXX Users。
2. 參考 Note 745809.1 安裝必要物件 (10g / 11g)
$ export ORACLE_SID=ORCL
$ sqlplus /nolog
SQL> conn / as sysdba
SQL> set termout on
SQL> set echo on
SQL> spool csminst.log
SQL> -- note the drop user
SQL> drop user csmig cascade;
SQL> @?/rdbms/admin/csminst.sql
3. 執行 csscan
$ csscan \"system/password as sysdba\" USER=schema TOCHAR=UTF8 ARRAY=1024000 PROCESS=3 LOG=csscan_log
4. 檢查產出的報表並修正
4.1 csscan_log.out ==> 執行csscan的過程
4.2 csscan_log.txt ==> tables / column 有無異常的總表
4.3 csscan_log.err ==> tables / column 有異常的資料明細
4.4 通常BIG5轉UTF8欄位大小是除以2乘以3：column_size / 2 * 3 (因為一個中文字在BIG5中佔2個bytes，在UTF8中佔3個bytes)
# 參考文件
Note 225912.1
Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET )
Note 458122.1
Installing and Configuring Csscan in 8i and 9i (Database Character Set Scanner)
Note 745809.1
Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner)
Note 444701.1
Csscan output explained
(Doc ID 260192.1)
Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g
2014年10月30日 星期四
執行Procedure 對Remote Database 做DML 動作時發生ORA-2069 錯誤訊息
Ora-02069 When Using a Local Function While Updating a Remote Table (Doc ID 342320.1) 這篇文章有提到：無法使用 Local Function 針對 Remote Database做 DML ( insert / update / delete) 動作，否則就會發生 ORA-2069 錯誤訊息。
暫時解決方式如下：
1. 將 session level 的 global_name 設成 true：alter session set global_names=true
2. 將 function 建在 Remote Database 上面
3. PL/SQL Code 中，盡可能的不使用function
4. 先將要處理的資料放在Local Table / Local Temp Table 中，當對 Remove Database 做DML 時，再拿出來引用
PS：
Doc ID 342320.1：
Information in this document applies to any platform.
where pkg_date.Fn_GetDate is a function of a local package that returns a sysdate,
and t1 is a synonym that points to a remote table, defined through a database link.
returns
暫時解決方式如下：
1. 將 session level 的 global_name 設成 true：alter session set global_names=true
2. 將 function 建在 Remote Database 上面
3. PL/SQL Code 中，盡可能的不使用function
4. 先將要處理的資料放在Local Table / Local Temp Table 中，當對 Remove Database 做DML 時，再拿出來引用
PS：
Doc ID 342320.1：
APPLIES TO:
Oracle Server - Enterprise Edition - Version 9.2.0.1 to 11.2.0.2 [Release 9.2 to 11.2]Information in this document applies to any platform.
SYMPTOMS
In Oracle Server executing the following update:
update t1
set c1=c1
where c2=pkg_date.Fn_GetDate;
where pkg_date.Fn_GetDate is a function of a local package that returns a sysdate,
and t1 is a synonym that points to a remote table, defined through a database link.
returns
"ORA-02069: global_names parameter must be set to TRUE for this operation"
on the function pkg_date.Fn_GetDate on the where condition.
CHANGES
CAUSE
Because of a limitation, it is not possible to use a local function when doing a dml operation on a remote table .When this is attempted, the ora-2069 is raised.
SOLUTION
These are possible workaround to avoid the ora-2069 error in the described scenario
1. Use global_names=true. This can be done on a session basis: "alter
session set global_names=true". If you are having problems getting this
to work, it is no doubt a configuration problem. Please open a separate
TAR for this if you can't figure it out.
session set global_names=true". If you are having problems getting this
to work, it is no doubt a configuration problem. Please open a separate
TAR for this if you can't figure it out.
2. Put the function to be used at the remote site.
3. Put a wrapper function at the remote site which calls the actual function
over a database link back to the local site.
over a database link back to the local site.
4. Include in the "from" the "dual" table. You'll have a Cartesian product (with dual) and the functions will be applied in the calling side, hence some performance issues can be raised.
REFERENCES
BUG:671775 - GET A ORA-2069 OR ORA-2019 WHEN TRYING TO USE A FUNCTION WITH A DB_LINK
訂閱：
文章 (Atom)