set dbid 21417441799
startup nomount
-restore your spfile or pfile
restore controlfile from '/obackup/oracle/mtrx/hot/c-1651548318-20071129-00';
shutdown abort;
startup mount;
catalog start with '/obackup/oracle/mtrx/hot/';
run {
allocate channel c1 device type disk;
set newname for datafile 1 to '/ofs/prrs02/mtrx22/system01.dbf';
set newname for datafile 2 to '/ofs/prrs02/mtrx22/undotbs01.dbf';
set newname for datafile 3 to '/ofs/prrs02/mtrx22/sysaux01.dbf';
set newname for datafile 4 to '/ofs/prrs02/mtrx22/users01.dbf';
set newname for datafile 5 to '/ofs/prrs02/mtrx22/tools01.dbf';
set newname for datafile 6 to '/ofs/prrs04/mtrx22/indx01.dbf';
set newname for datafile 7 to '/ofs/prrs04/mtrx22/mtrxdata01.dbf';
set newname for datafile 8 to '/ofs/prrs04/mtrx22/mtrxdata02.dbf';
set newname for datafile 9 to '/ofs/prrs04/mtrx22/mtrxdata03.dbf';
set newname for datafile 10 to '/ofs/prrs03/mtrx22/mtrxdata04.dbf';
set newname for datafile 11 to '/ofs/prrs03/mtrx22/mgrtdata01.dbf';
set newname for datafile 12 to '/ofs/prrs03/mtrx22/proddata01.dbf';
set newname for datafile 13 to '/ofs/prrs03/mtrx22/reporter01.dbf';
set newname for datafile 14 to '/ofs/prrs04/mtrx22/mtrxdata05.dbf';
set newname for datafile 15 to '/ofs/prrs05/mtrx22/mtrxdata06.dbf';
set newname for datafile 16 to '/ofs/prrs05/mtrx22/mtrxdata07.dbf';
set newname for datafile 17 to '/ofs/prrs05/mtrx22/proddata02.dbf';
set newname for datafile 18 to '/ofs/prrs05/mtrx22/METRIX_INDX01.dbf';
sql "alter database rename file ''/metrix2/mtrx10g01/oracle/mtrx10g/mtrx10g/redo01.log''
to ''/ofs/prrs03/mtrx22/redo01.log'' ";
sql "alter database rename file ''/metrix2/mtrx10g01/oracle/mtrx10g/mtrx10g/redo02.log''
to ''/ofs/prrs03/mtrx22/redo02.log'' ";
sql "alter database rename file ''/metrix2/mtrx10g01/oracle/mtrx10g/mtrx10g/redo03.log''
to ''/ofs/prrs03/mtrx22/redo03.log'' ";
set until scn 21417441799;
restore database;
switch datafile all;
recover database;
}
alter database open resetlogs;
Friday, November 30, 2007
ORA-19951: cannot modify control file until DBNEWID is completed
nid system/manager dbname=mtrx22
while running nid on a cloned database I forgot to check the tempfile which resulted into following error
NID-00111: Oracle error reported from target database while executing
begin dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged,
:nmchged); end;
ORA-01116: error in opening database file /metrix2/mtrx10g02/oracle/mtrx10g/temp01.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6164
ORA-06512: at line 1
After that no matter How I try to open the db or add the tempfile it didnt allow me .somehow not sure what I missed but the REVERT option didnt help me much.
A lesson learned.
make sure all the datafiles,tempfiles
are in place before you run nid. it is better if you have a fulll backup.
while running nid on a cloned database I forgot to check the tempfile which resulted into following error
NID-00111: Oracle error reported from target database while executing
begin dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged,
:nmchged); end;
ORA-01116: error in opening database file /metrix2/mtrx10g02/oracle/mtrx10g/temp01.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6164
ORA-06512: at line 1
After that no matter How I try to open the db or add the tempfile it didnt allow me .somehow not sure what I missed but the REVERT option didnt help me much.
A lesson learned.
make sure all the datafiles,tempfiles
are in place before you run nid. it is better if you have a fulll backup.
Tuesday, November 20, 2007
oracle 11g installed on ubuntu /vmware on Dell laptop
hardware - Dell Latitude D600 with 1 GB RAM
Host O/S - Windows XP
vmware 2.0
Ubuntu 7.10
filesystem xfs
Oracle 11g Enterprise edition
Reference.
http://www.pythian.com/blogs/654/installing-oracle-11g-on-ubuntu-linux-710-gutsy-gibbon
Host O/S - Windows XP
vmware 2.0
Ubuntu 7.10
filesystem xfs
Oracle 11g Enterprise edition
Reference.
http://www.pythian.com/blogs/654/installing-oracle-11g-on-ubuntu-linux-710-gutsy-gibbon
Monday, November 19, 2007
ubuntu vmware networking /etc/network/interfaces
cat /etc/network/interfaces
auto lo
iface lo inet loopback
#to enable dhcp
# The primary network interface - use DHCP to find our address
iface eth0 inet dhcp
gateway: 10.247.16.253
auto eth0
it fixed these things
1- now i am able to ping from host machine to the guest OS ubuntu inside vmware 2.0.
2- when I try to logon to oracle using sqlplus '/as sysdba' it used to give me TNS error .
3- trying to start lsnrctl start was just hanging it was not even creating trace files.
auto lo
iface lo inet loopback
#to enable dhcp
# The primary network interface - use DHCP to find our address
iface eth0 inet dhcp
gateway: 10.247.16.253
auto eth0
it fixed these things
1- now i am able to ping from host machine to the guest OS ubuntu inside vmware 2.0.
2- when I try to logon to oracle using sqlplus '/as sysdba' it used to give me TNS error .
3- trying to start lsnrctl start was just hanging it was not even creating trace files.
Friday, November 16, 2007
generate user creation script
http://toolkit.rdbms-insight.com/gen_cre_users.php
set pagesize 0
set escape on
select 'create user ' || U.username || ' identified ' ||
DECODE(password,
NULL, 'EXTERNALLY',
' by values ' || '''' || password || ''''
)
|| chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||
'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace ||
decode (account_status,'LOCKED', ' account lock',
'EXPIRED', ' password expire',
'EXPIRED \& LOCKED', ' account lock password expire',
null)
||
';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system & default users
where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
set pagesize 100
set escape off
generate tablespace creation script
Ref# http://www.blacksheepnetworks.com/security/resources/www.think-forward.com/sql/gents.htm
spool gents.lst
set serveroutput on size 1000000
DECLARE
CURSOR get_ts IS SELECT * FROM dba_tablespaces
WHERE tablespace_name != 'SYSTEM';
CURSOR get_df (p_ts VARCHAR2) IS
SELECT * from dba_data_files
WHERE tablespace_name = p_ts;
l_str VARCHAR2(10);
BEGIN
FOR ts_rec IN get_ts LOOP
dbms_output.put_line ('CREATE TABLESPACE '||ts_rec.tablespace_name);
-- For each tablespace loop through the datafiles
FOR df_rec IN get_df (ts_rec.tablespace_name) LOOP
IF get_df%ROWCOUNT = 1 THEN
l_str := 'DATAFILE';
ELSE
l_str := ',';
END IF;
dbms_output.put_line (l_str||' '
||chr(39)||df_rec.file_name||chr(39)
||' SIZE '||df_rec.bytes||' REUSE ');
if df_rec.autoextensible = 'YES' then
dbms_output.put_line (' AUTOEXTEND ON'
||' NEXT '||df_rec.increment_by );
if df_rec.maxbytes = 68719443968 then
dbms_output.put_line (' MAXSIZE UNLIMITED');
else
dbms_output.put_line (' MAXSIZE '||df_rec.maxbytes);
end if;
end if;
END LOOP;
/* Extent Management Clause */
dbms_output.put_line ('EXTENT MANAGEMENT ' ||ts_rec.extent_management );
if ts_rec.extent_management = 'LOCAL' then
if ts_rec.allocation_type = 'SYSTEM' then
dbms_output.put_line (' AUTOALLOCATE ');
else
dbms_output.put_line (' UNIFORM SIZE '||ts_rec.initial_extent);
end if;
end if;
if ts_rec.extent_management = 'DICTIONARY' then
dbms_output.put_line ('DEFAULT STORAGE (INITIAL '||ts_rec.initial_extent
||' NEXT '||ts_rec.next_extent
||' MINEXTENTS '||ts_rec.min_extents
||' MAXEXTENTS '||ts_rec.max_extents
||' PCTINCREASE '||ts_rec.pct_increase||' ) ');
end if;
dbms_output.put_line (' ONLINE;');
dbms_output.new_line;
END LOOP;
END;
/
spool off
Add extra space to ubutu guest os in vmware
sudo apt-get install gparted
1-Select your virtual machine Ubuntu and then "right-click",
choose "Settings" -> Add -> Hard Disk -> choose IDE/SCSI -> decide the capacity -> disk file nameubuntu(2).vmdk [your new virtual disk]
2. boot up your ubuntu
sudo apt-get install xfsprogs
mkfs.xfs /dev/sdb
fdisk -l
mkdir /oracle
mount /dev/sdb /oracle
modify your "/etc/fstab" file, and adding
/dev/sdb /oracle xfs defaults 0 0
chmod 777 /oracle
1-Select your virtual machine Ubuntu and then "right-click",
choose "Settings" -> Add -> Hard Disk -> choose IDE/SCSI -> decide the capacity -> disk file nameubuntu(2).vmdk [your new virtual disk]
2. boot up your ubuntu
sudo apt-get install xfsprogs
mkfs.xfs /dev/sdb
fdisk -l
mkdir /oracle
mount /dev/sdb /oracle
modify your "/etc/fstab" file, and adding
/dev/sdb /oracle xfs defaults 0 0
chmod 777 /oracle
Thursday, November 15, 2007
install rpm file
Ref#http://www.ubuntugeek.com/install-rpm-files-in-ubuntu.html
install alien in Ubuntu
install alien in Ubuntu
sudo apt-get install alien
Example
Suppose we have a avg antivirus avg71lms-r30-a0782.i386.rpm file
To convert .rpm to debian
sudo alien -k avg71lms-r30-a0782.i386.rpm
Now you should be having avg71lms-r30-a0782.i386.deb file
To install .deb file
sudo dpkg -i avg71lms-r30-a0782.i386.deb
If you don’t use -k option you should see avg71lms_r30-1_i386.deb file the difference is it will add 1
ubuntu samba
sudo apt-get install samba
sudo gedit /etc/samba/smb.conf
change browsable and writable to yes
sudo /etc/init.d/samba start
sudo smbpasswd -a your_username
and create a folder name sharesoftware and make it shared in ubuntu
and then connect from windows -
map network drive
\\10.247.16.140\sharesoftware
and access using your username and password
To mount a remote windows system directly upon your current ubuntu system you'll need:
* The smbfs kernel module
* The smbfs package.
* A mountpoint - this is just an empty directory.
* The Name/IP address of the host to connect to.
* Login credentials to the remote share - both username and password.
* Local root privileges to do the mount, and load the kernel module.
apt-get install smbfs
as a small example lets assume I wish to mount the "C:" drive of remote machine "recurring.my.flat" to the local machine mystery.my.flat
I would begin by loading the kernel module:
mount -t smbfs -o username=Administrator,password=Password //recurring/c$ /mnt/recurring
5232: session setup failed: ERRDOS - ERRnoaccess (Access denied.)
SMB connection failed
See the following link: will help you with samba and windows devices
http://www.debian-administratrion.org/articles/165
sudo gedit /etc/samba/smb.conf
change browsable and writable to yes
sudo /etc/init.d/samba start
sudo smbpasswd -a your_username
and create a folder name sharesoftware and make it shared in ubuntu
and then connect from windows -
map network drive
\\10.247.16.140\sharesoftware
and access using your username and password
To mount a remote windows system directly upon your current ubuntu system you'll need:
* The smbfs kernel module
* The smbfs package.
* A mountpoint - this is just an empty directory.
* The Name/IP address of the host to connect to.
* Login credentials to the remote share - both username and password.
* Local root privileges to do the mount, and load the kernel module.
apt-get install smbfs
as a small example lets assume I wish to mount the "C:" drive of remote machine "recurring.my.flat" to the local machine mystery.my.flat
I would begin by loading the kernel module:
mount -t smbfs -o username=Administrator,password=Password //recurring/c$ /mnt/recurring
5232: session setup failed: ERRDOS - ERRnoaccess (Access denied.)
SMB connection failed
See the following link: will help you with samba and windows devices
http://www.debian-administratrion.org/articles/165
ubuntu Networking
http://www.ubuntugeek.com/ubuntu-networking-configuration-using-command-line.html
The basics for any network based on *nix hosts is the Transport Control Protocol/ Internet Protocol (TCP/IP) combination of three protocols. This combination consists of the Internet Protocol (IP),Transport Control Protocol (TCP), and Universal Datagram Protocol (UDP).
By Default most of the users configure their network card during the installation of Ubuntu. You can however, use the ifconfig command at the shell prompt or Ubuntu’s graphical network configuration tools, such as network-admin, to edit your system’s network device information or to add or remove network devices on your system
Configure Network Interface Using Command-Line
You can configure a network interface from the command line using the networking utilities. You configure your network client hosts with the command line by using commands to change your current settings or by editing a number of system files.
Configuring DHCP address for your network card
If you want to configure DHCP address you need to edit the /etc/network/interfaces and you need to enter the following lines replace eth0 with your network interface card
sudo vi /etc/network/interfaces
# The primary network interface - use DHCP to find our address
auto eth0
iface eth0 inet dhcp
Configuring Static IP address for your network card
If you want to configure Static IP address you need to edit the /etc/network/interfaces and you need to enter the following lines replace eth0 with your network interface card
sudo vi /etc/network/interfaces
# The primary network interface
auto eth0
iface eth0 inet static
address 192.168.3.90
gateway 192.168.3.1
netmask 255.255.255.0
network 192.168.3.0
broadcast 192.168.3.255
After entering all the details you need to restart networking services using the following command
sudo /etc/init.d/networking restart
Setting up Second IP address or Virtual IP address in Ubuntu
If you are a server system administrator or normal user some time you need to assign a second ipaddress to your Ubuntu machine.For this you need to edit the /etc/network/interfaces file and you need to add the following syntax.Below one is the only example you need to chnage according to your ip address settings
sudo vi /etc/network/interfaces
auto eth0:1
iface eth0:1 inet static
address 192.168.1.60
netmask 255.255.255.0
network x.x.x.x
broadcast x.x.x.x
gateway x.x.x.x
You need to enter all the details like address,netmask,network,broadcast and gateways values after entering all the values save this file and you need to restart networking services in debian using the following command to take effect of our new ipaddress.
After entering all the details you need to restart networking services using the following command
sudo /etc/init.d/networking restart
Setting your ubuntu stytem hostname
Setting up your hostname upon a ubuntu installation is very straightforward. You can directly query, or set, the hostname with the hostname command.
As an user you can see your current hostname with
sudo /bin/hostname
Example
To set the hostname directly you can become root and run
sudo /bin/hostname newname
When your system boots it will automatically read the hostname from the file /etc/hostname
If you want to know more about how to setup host name check here
Setting up DNS
When it comes to DNS setup Ubuntu doesn’t differ from other distributions. You can add hostname and IP addresses to the file /etc/hosts for static lookups.
To cause your machine to consult with a particular server for name lookups you simply add their addresses to /etc/resolv.conf.
For example a machine which should perform lookups from the DNS server at IP address 192.168.3.2 would have a resolv.conf file looking like this
sudo vi /etc/resolv.conf
enter the following details
search test.com
nameserver 192.168.3.2
NOTE#-
my ubuntu is installed on vmware on host os windows xp so I switched to bridge networking when I am on dhcp at work and I switch to NAT when I am working at home on wireless networking. not sure if it is the right way?Monday, November 12, 2007
ORA-07445: exception encountered: core dump [opidsa()+480] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
we hit these errors after upgrading to solaris 10.2.0.3 . metalink suggest a regular shutdown or flushing shared pool . of applying patch Patch 5648872
Wednesday, November 7, 2007
nfsstat - solaris
nfsstat -m
/obackup/obackup_share from asb-sac-nms-001:/obackup
Flags: vers=3,proto=tcp,sec=sys,hard,intr,link,symlink,acl,rsize=32768,wsize=32768,retrans=5,timeo=600
Attr cache: acregmin=3,acregmax=60,acdirmin=30,acdirmax=60
/etc/vfstab
/obackup/obackup_share from asb-sac-nms-001:/obackup
Flags: vers=3,proto=tcp,sec=sys,hard,intr,link,symlink,acl,rsize=32768,wsize=32768,retrans=5,timeo=600
Attr cache: acregmin=3,acregmax=60,acdirmin=30,acdirmax=60
/etc/vfstab
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
After moving the backup directory to a NFS for one 10gR2 db I started getting this error.
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/07/2007 14:16:10
ORA-19504: failed to create file "/obackup/oracle/mtrx/hot/ffj0f358_1_1"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2
Soln-
Error is documented in Metalink Doc Id : Note:387700.1
ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS
Solution
To implement the solution, please execute the following steps:
From the erorrs that we see from the RMAN stack this looks like Bug 5146667
This behaviour has been observed on Solaris and AIX Platform.
WORKAROUND:
As suggested in the bug the workaround recommended is to use the Event 10298.
1) set the Event 10298 in the init file
event="10298 trace name context forever, level 32"
if you are using the spfile then the following can be done
SQL> alter system set event='10298 trace name context forever, level 32'scope= spfile ;
Once you set the above parameter restart the instance check as follows
SQL> select name, value from v$parameter where name = 'event';
NAME VALUE
---------- ------------------------------------------------------------
Event 10298 trace name context forever, level 32
1 row selected.
Then try the backups again
2) PATCH
Check if a one off patch for 10.2.0.1 for your platform is available
Please follow next steps to download and test it :
1. Login to Metalink
2. Go to Patches and Updates -> Simple Search
3. Enter patch number 5146667 and platform you are on ( your version of OS )
4. Download the patch
5. Read the README file for installation instructions and test it to see if it fixes your problem
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/07/2007 14:16:10
ORA-19504: failed to create file "/obackup/oracle/mtrx/hot/ffj0f358_1_1"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2
Soln-
Error is documented in Metalink Doc Id : Note:387700.1
ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS
Solution
To implement the solution, please execute the following steps:
From the erorrs that we see from the RMAN stack this looks like Bug 5146667
This behaviour has been observed on Solaris and AIX Platform.
WORKAROUND:
As suggested in the bug the workaround recommended is to use the Event 10298.
1) set the Event 10298 in the init file
event="10298 trace name context forever, level 32"
if you are using the spfile then the following can be done
SQL> alter system set event='10298 trace name context forever, level 32'scope= spfile ;
Once you set the above parameter restart the instance check as follows
SQL> select name, value from v$parameter where name = 'event';
NAME VALUE
---------- ------------------------------------------------------------
Event 10298 trace name context forever, level 32
1 row selected.
Then try the backups again
2) PATCH
Check if a one off patch for 10.2.0.1 for your platform is available
Please follow next steps to download and test it :
1. Login to Metalink
2. Go to Patches and Updates -> Simple Search
3. Enter patch number 5146667 and platform you are on ( your version of OS )
4. Download the patch
5. Read the README file for installation instructions and test it to see if it fixes your problem
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
After moving the rman backup for a solaris 5.8 to a nfs it started complaining
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/07/2007 14:16:10
ORA-19504: failed to create file "/obackup/oracle/mtrx/hot/ffj0f358_1_1"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/07/2007 14:16:10
ORA-19504: failed to create file "/obackup/oracle/mtrx/hot/ffj0f358_1_1"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2
Monday, November 5, 2007
impdp
expdp system/managerpopv dumpfile=reporter.dmp directory=tmp schemas=reporter
impdp system\acomp directory=tmp dumpfile=opvdb.dmp remap_schema=opvdb:dest_schema remap_tablespace=source_tablespace:dest_tablespace
impdp system/manager directory=temp_dir dumpfile=ovdb.dmp remap_schema=ovdb:popv_ovdb remap_tablespace=owstage:afc_data logfile=ovdb.log
ref http://www.oracle-dba-online.com/data_pump_import_utility.htm
*importing full dump file
impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp FULL=y LOGFILE=full_imp.log
impdp hr/hr DUMPFILE=dpump_dir1:expfull.dmp FULL=y LOGFILE=dpump_dir2:full_imp.log
*Importing Objects of One Schema to another Schema
The following example loads all tables belonging to hr schema to scott schema
$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott
If SCOTT account exist in the database then hr objects will be loaded into scott schema.
If scott account does not exist, then Import Utility will create the SCOTT account with an
unusable password because, the dump file was exported by the user SYSTEM and imported by
the user SYSTEM who has DBA privileges.
*Loading Objects of one Tablespace to another Tablespace.
You can use remap_tablespace option to import objects of one tablespace to another tablespace
by giving the command
$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_TABLESPACE=users:sales
*Generating SQL File containing DDL commands using Data Pump Import
You can generate SQL file which contains all the DDL commands which Import would
have executed if you actually run Import utility
The following is an example of using the SQLFILE parameter.
$ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp SQLFILE=dpump_dir2:expfull.sql
A SQL file named expfull.sql is written to dpump_dir2.
*Importing objects of only a Particular Schema
If you have the IMP_FULL_DATABASE role, you can use this parameter to perform a schema-mode import
by specifying a single schema other than your own or a list of schemas to import.
First, the schemas themselves are created (if they do not already exist),
including system and role grants, password history, and so on.
Then all objects contained within the schemas are imported.
Nonprivileged users can specify only their own schemas.
In that case, no information about the schema definition is imported,
only the objects contained within it.
Example
The following is an example of using the SCHEMAS parameter.
You can create the expdat.dmp file used in this example by running the example provided for the
Export SCHEMAS parameter.
$impdp hr/hr SCHEMAS=hr,oe DIRECTORY=dpump_dir1 LOGFILE=schemas.log
DUMPFILE=expdat.dmp
The hr and oe schemas are imported from the expdat.dmp file.
The log file, schemas.log, is written to dpump_dir1
*Importing Only Particular Tables
The following example shows a simple use of the TABLES parameter to import only
the employees and jobs tables from the expfull.dmp file.
You can create the expfull.dmp dump file used in this example by running the example
provided for the Full Database Export in Previous Topic.
$impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees,jobs
This will import only employees and jobs tables from the DUMPFILE.
*Running Import Utility in Interactive Mode
Similar to the DATA PUMP EXPORT utility the Data Pump Import Jobs can also be suspended, resumed or killed. And, you can attach to an already existing import job from any client machine.
For Example, suppose a DBA starts a importing by typing the following command at one client machine CLNT1 by typing the following command
$impdp scott/tiger@mydb FULL=y DIRECTORY=data_pump_dir
DUMPFILE=full.dmp LOGFILE=myfullexp.log JOB_NAME=myfullJob
After some time, the DBA wants to stop this job temporarily. Then he presses CTRL+C to enter into interactive mode. Then he will get the Import> prompt where he can type interactive commands
Now he wants to stop this export job so he will type the following command
Import> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([y]/n): y
The job is placed in a stopped state and exits the client.
After finishing his other work, the DBA wants to resume the export job and the client machine from where he actually started the job is locked because,
the user has locked his/her cabin. So now the DBA will go to another client machine and he reattach
to the job by typing the following command
$impdp hr/hr@mydb ATTACH=myfulljob
After the job status is displayed, he can issue the CONTINUE_CLIENT command to resume logging mode
and restart the myfulljob job.
Import> CONTINUE_CLIENT
A message is displayed that the job has been reopened, and processing status is output to the client.
Note: After reattaching to the Job a DBA can also kill the job by typing KILL_JOB, if he doesn’t
want to continue with the import job.
impdp system\acomp directory=tmp dumpfile=opvdb.dmp remap_schema=opvdb:dest_schema remap_tablespace=source_tablespace:dest_tablespace
impdp system/manager directory=temp_dir dumpfile=ovdb.dmp remap_schema=ovdb:popv_ovdb remap_tablespace=owstage:afc_data logfile=ovdb.log
ref http://www.oracle-dba-online.com/data_pump_import_utility.htm
*importing full dump file
impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp FULL=y LOGFILE=full_imp.log
impdp hr/hr DUMPFILE=dpump_dir1:expfull.dmp FULL=y LOGFILE=dpump_dir2:full_imp.log
*Importing Objects of One Schema to another Schema
The following example loads all tables belonging to hr schema to scott schema
$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott
If SCOTT account exist in the database then hr objects will be loaded into scott schema.
If scott account does not exist, then Import Utility will create the SCOTT account with an
unusable password because, the dump file was exported by the user SYSTEM and imported by
the user SYSTEM who has DBA privileges.
*Loading Objects of one Tablespace to another Tablespace.
You can use remap_tablespace option to import objects of one tablespace to another tablespace
by giving the command
$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_TABLESPACE=users:sales
*Generating SQL File containing DDL commands using Data Pump Import
You can generate SQL file which contains all the DDL commands which Import would
have executed if you actually run Import utility
The following is an example of using the SQLFILE parameter.
$ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp SQLFILE=dpump_dir2:expfull.sql
A SQL file named expfull.sql is written to dpump_dir2.
*Importing objects of only a Particular Schema
If you have the IMP_FULL_DATABASE role, you can use this parameter to perform a schema-mode import
by specifying a single schema other than your own or a list of schemas to import.
First, the schemas themselves are created (if they do not already exist),
including system and role grants, password history, and so on.
Then all objects contained within the schemas are imported.
Nonprivileged users can specify only their own schemas.
In that case, no information about the schema definition is imported,
only the objects contained within it.
Example
The following is an example of using the SCHEMAS parameter.
You can create the expdat.dmp file used in this example by running the example provided for the
Export SCHEMAS parameter.
$impdp hr/hr SCHEMAS=hr,oe DIRECTORY=dpump_dir1 LOGFILE=schemas.log
DUMPFILE=expdat.dmp
The hr and oe schemas are imported from the expdat.dmp file.
The log file, schemas.log, is written to dpump_dir1
*Importing Only Particular Tables
The following example shows a simple use of the TABLES parameter to import only
the employees and jobs tables from the expfull.dmp file.
You can create the expfull.dmp dump file used in this example by running the example
provided for the Full Database Export in Previous Topic.
$impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees,jobs
This will import only employees and jobs tables from the DUMPFILE.
*Running Import Utility in Interactive Mode
Similar to the DATA PUMP EXPORT utility the Data Pump Import Jobs can also be suspended, resumed or killed. And, you can attach to an already existing import job from any client machine.
For Example, suppose a DBA starts a importing by typing the following command at one client machine CLNT1 by typing the following command
$impdp scott/tiger@mydb FULL=y DIRECTORY=data_pump_dir
DUMPFILE=full.dmp LOGFILE=myfullexp.log JOB_NAME=myfullJob
After some time, the DBA wants to stop this job temporarily. Then he presses CTRL+C to enter into interactive mode. Then he will get the Import> prompt where he can type interactive commands
Now he wants to stop this export job so he will type the following command
Import> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([y]/n): y
The job is placed in a stopped state and exits the client.
After finishing his other work, the DBA wants to resume the export job and the client machine from where he actually started the job is locked because,
the user has locked his/her cabin. So now the DBA will go to another client machine and he reattach
to the job by typing the following command
$impdp hr/hr@mydb ATTACH=myfulljob
After the job status is displayed, he can issue the CONTINUE_CLIENT command to resume logging mode
and restart the myfulljob job.
Import> CONTINUE_CLIENT
A message is displayed that the job has been reopened, and processing status is output to the client.
Note: After reattaching to the Job a DBA can also kill the job by typing KILL_JOB, if he doesn’t
want to continue with the import job.
oracle TDE
connect / as sysdba
or creating a wallet a folder named wallet should be present in the directory
$ORACLEBASE/admin/$ORACLESID
Location of wallet can be changed if required. This can be done by entering the directory path for WALLET_LOCATION parameter in sqlnet.ora file as shown below
WALLET_LOCATION = (SOURCE=
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY=/u02/wallets)
)))
alter system set key identified by "welcome1";
For later sessions, you do not want to use the command given above; you need the wallet to be open (it has been closed when you shut down your database), but you don't want to create a new master key. Then the command is:
alter system set wallet open identified by "welcome1";
create table cust_payment_info
(first_name varchar2(11),
last_name varchar2(10),
order_number number(5),
credit_card_number varchar2(16) ENCRYPT NO SALT,
active_card varchar2(3));
insert into cust_payment_info values
('Jon', 'Oldfield', 10001, '5446959708812985','YES');
insert into cust_payment_info values
('Chris', 'White', 10002, '5122358046082560','YES');
insert into cust_payment_info values
('Alan', 'Squire', 10003, '5595968943757920','YES');
insert into cust_payment_info values
('Mike', 'Anderson', 10004, '4929889576357400','YES');
insert into cust_payment_info values
('Annie', 'Schmidt', 10005, '4556988708236902','YES');
insert into cust_payment_info values
('Elliott', 'Meyer', 10006, '374366599711820','YES');
insert into cust_payment_info values
('Celine', 'Smith', 10007, '4716898533036','YES');
insert into cust_payment_info values
('Steve', 'Haslam', 10008, '340975900376858','YES');
insert into cust_payment_info values
('Albert', 'Einstein', 10009, '310654305412389','YES');
/devel/app/oracle/admin/dev10g/wallets
view sqlnet.ora
WALLET_LOCATION = (SOURCE=
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY=/devel/app/oracle/admin/dev10g/wallets)
)))
SQL> alter system set key identified by "pass";
System altered.
Example 3-3 Adding Salt to an Encrypted Column
ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);
Example 3-4 Removing Salt from an Encrypted Column
ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);
expdp system/manager dumpfile=test.dmp directory=tmp_dir tables=scott.emp encryption_password=pass
impdp system/manager tables=emp dumpfile=test.dmp directory=tmp_dir encryption_password=pass
Import: Release 10.2.0.1.0 - Production on Tuesday, 18 September, 2007 12:17:55
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39180: unable to encrypt ENCRYPTION_PASSWORD
ORA-28365: wallet is not open
SQL> create directory test as 'c:\temp';
Directory created.
SQL> grant all on directory test to public;
Grant succeeded.
impdp system/manager tables=emp dumpfile=test.dmp directory=tmp_dir encryption_password=pass REMAP_SCHEMA=scott:system remap_tablespace=users:system
after reboot of database
SQL> alter system set encryption wallet open identified by "pass";
System altered.
or creating a wallet a folder named wallet should be present in the directory
$ORACLEBASE/admin/$ORACLESID
Location of wallet can be changed if required. This can be done by entering the directory path for WALLET_LOCATION parameter in sqlnet.ora file as shown below
WALLET_LOCATION = (SOURCE=
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY=/u02/wallets)
)))
alter system set key identified by "welcome1";
For later sessions, you do not want to use the command given above; you need the wallet to be open (it has been closed when you shut down your database), but you don't want to create a new master key. Then the command is:
alter system set wallet open identified by "welcome1";
create table cust_payment_info
(first_name varchar2(11),
last_name varchar2(10),
order_number number(5),
credit_card_number varchar2(16) ENCRYPT NO SALT,
active_card varchar2(3));
insert into cust_payment_info values
('Jon', 'Oldfield', 10001, '5446959708812985','YES');
insert into cust_payment_info values
('Chris', 'White', 10002, '5122358046082560','YES');
insert into cust_payment_info values
('Alan', 'Squire', 10003, '5595968943757920','YES');
insert into cust_payment_info values
('Mike', 'Anderson', 10004, '4929889576357400','YES');
insert into cust_payment_info values
('Annie', 'Schmidt', 10005, '4556988708236902','YES');
insert into cust_payment_info values
('Elliott', 'Meyer', 10006, '374366599711820','YES');
insert into cust_payment_info values
('Celine', 'Smith', 10007, '4716898533036','YES');
insert into cust_payment_info values
('Steve', 'Haslam', 10008, '340975900376858','YES');
insert into cust_payment_info values
('Albert', 'Einstein', 10009, '310654305412389','YES');
/devel/app/oracle/admin/dev10g/wallets
view sqlnet.ora
WALLET_LOCATION = (SOURCE=
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY=/devel/app/oracle/admin/dev10g/wallets)
)))
SQL> alter system set key identified by "pass";
System altered.
Example 3-3 Adding Salt to an Encrypted Column
ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);
Example 3-4 Removing Salt from an Encrypted Column
ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);
expdp system/manager dumpfile=test.dmp directory=tmp_dir tables=scott.emp encryption_password=pass
impdp system/manager tables=emp dumpfile=test.dmp directory=tmp_dir encryption_password=pass
Import: Release 10.2.0.1.0 - Production on Tuesday, 18 September, 2007 12:17:55
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39180: unable to encrypt ENCRYPTION_PASSWORD
ORA-28365: wallet is not open
SQL> create directory test as 'c:\temp';
Directory created.
SQL> grant all on directory test to public;
Grant succeeded.
impdp system/manager tables=emp dumpfile=test.dmp directory=tmp_dir encryption_password=pass REMAP_SCHEMA=scott:system remap_tablespace=users:system
after reboot of database
SQL> alter system set encryption wallet open identified by "pass";
System altered.
prereq- oracle 10g on linux 64bit
/usr/sbin/useradd nobody
/etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
/etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
/etc/pam.d/login
session required pam_limits.so
For the Bourne, Bash, or Korn shell, add the following lines to the
/etc/profile file (or the /etc/profile.local file on SUSE systems):
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
mkdir -p /mount_point/app/oracle_sw_owner
chown -R oracle:oinstall /mount_point/app/oracle_sw_owner
chmod -R 775 /mount_point/app/oracle_sw_owner
e.g /u01/app/oracle
[root@localhost oracle]# mkdir /u01
[root@localhost oracle]# mkdir /u01/app
[root@localhost oracle]# mkdir /u01/app/oracle
[root@localhost oracle]# chown -R oracle:oinstall /u01/app/oracle
[root@localhost oracle]# chmod -R 775 /u01/app/oracle
ASM
rpm -Uvh oracleasm-support-1.0.0-1.i386.rpm \
oracleasm-2.4.9-e-enterprise-1.0.0-1.i686.rpm \
oracleasmlib-1.0.0-1.i386.rpm
/etc/init.d/oracleasm configure
/etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
/etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
/etc/pam.d/login
session required pam_limits.so
For the Bourne, Bash, or Korn shell, add the following lines to the
/etc/profile file (or the /etc/profile.local file on SUSE systems):
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
mkdir -p /mount_point/app/oracle_sw_owner
chown -R oracle:oinstall /mount_point/app/oracle_sw_owner
chmod -R 775 /mount_point/app/oracle_sw_owner
e.g /u01/app/oracle
[root@localhost oracle]# mkdir /u01
[root@localhost oracle]# mkdir /u01/app
[root@localhost oracle]# mkdir /u01/app/oracle
[root@localhost oracle]# chown -R oracle:oinstall /u01/app/oracle
[root@localhost oracle]# chmod -R 775 /u01/app/oracle
ASM
rpm -Uvh oracleasm-support-1.0.0-1.i386.rpm \
oracleasm-2.4.9-e-enterprise-1.0.0-1.i686.rpm \
oracleasmlib-1.0.0-1.i386.rpm
/etc/init.d/oracleasm configure
Dataguard 9i
primary
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_FORMAT=sfpvtl_%t_%s.log
# Parameters for DataGuard Primary database role
LOG_ARCHIVE_DEST_1='LOCATION=/sfpvtl/archlogs01/oracle/sfpvtl'
# Location of the StandBy Database
LOG_ARCHIVE_DEST_2 = 'SERVICE=sfpvtl_stdby'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
# Primary Database 2 archive log file system
LOG_ARCHIVE_DEST_3 = 'LOCATION=/sfpvtl/archlogs02/oracle/sfpvtl'
LOG_ARCHIVE_DEST_STATE_3 = DEFER
standby
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_FORMAT=sfpvtl_%t_%s.log
# Parameters for DataGuard Primary database role
LOG_ARCHIVE_DEST_1='LOCATION=/sfpvtl/archlogs01/oracle/sfpvtl'
# Location of the StandBy Database
LOG_ARCHIVE_DEST_2 = 'SERVICE=sfpvtl_stdby'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
# Primary Database 2 archive log file system
LOG_ARCHIVE_DEST_3 = 'LOCATION=/sfpvtl/archlogs02/oracle/sfpvtl'
LOG_ARCHIVE_DEST_STATE_3 = DEFER
#LOG_ARCHIVE_DEST=/sfpvtl/archlogs01/oracle/sfpvtl
fal_client='sfpvtl_pri'
fal_server='sfpvtl_stdby'
standby_archive_dest='/sfpvtl/standbylogs01/oracle/sfpvtl'
standby_file_management='auto'
rman-
BACKUP DATABASE
INCLUDE CURRENT CONTROLFILE FOR STANDBY
PLUS ARCHIVELOG;
rsync -avvzrlpogt * --rsync-path=/opt/csw/bin/rsync -e ssh oracle@asb-con-dbs-001:/obackup/oracle/aug24/
rman target / auxiliary sys/syspopv@popv_stdby
duplicate target database for standby nofilenamecheck;
startup nomount
ALTER DATABASE MOUNT STANDBY DATABASE;
alter database recover managed standby database disconnect from session;
read only
startup nomount
alter database mount standby database;
alter database open read only;
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_FORMAT=sfpvtl_%t_%s.log
# Parameters for DataGuard Primary database role
LOG_ARCHIVE_DEST_1='LOCATION=/sfpvtl/archlogs01/oracle/sfpvtl'
# Location of the StandBy Database
LOG_ARCHIVE_DEST_2 = 'SERVICE=sfpvtl_stdby'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
# Primary Database 2 archive log file system
LOG_ARCHIVE_DEST_3 = 'LOCATION=/sfpvtl/archlogs02/oracle/sfpvtl'
LOG_ARCHIVE_DEST_STATE_3 = DEFER
standby
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_FORMAT=sfpvtl_%t_%s.log
# Parameters for DataGuard Primary database role
LOG_ARCHIVE_DEST_1='LOCATION=/sfpvtl/archlogs01/oracle/sfpvtl'
# Location of the StandBy Database
LOG_ARCHIVE_DEST_2 = 'SERVICE=sfpvtl_stdby'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
# Primary Database 2 archive log file system
LOG_ARCHIVE_DEST_3 = 'LOCATION=/sfpvtl/archlogs02/oracle/sfpvtl'
LOG_ARCHIVE_DEST_STATE_3 = DEFER
#LOG_ARCHIVE_DEST=/sfpvtl/archlogs01/oracle/sfpvtl
fal_client='sfpvtl_pri'
fal_server='sfpvtl_stdby'
standby_archive_dest='/sfpvtl/standbylogs01/oracle/sfpvtl'
standby_file_management='auto'
rman-
BACKUP DATABASE
INCLUDE CURRENT CONTROLFILE FOR STANDBY
PLUS ARCHIVELOG;
rsync -avvzrlpogt * --rsync-path=/opt/csw/bin/rsync -e ssh oracle@asb-con-dbs-001:/obackup/oracle/aug24/
rman target / auxiliary sys/syspopv@popv_stdby
duplicate target database for standby nofilenamecheck;
startup nomount
ALTER DATABASE MOUNT STANDBY DATABASE;
alter database recover managed standby database disconnect from session;
read only
startup nomount
alter database mount standby database;
alter database open read only;
RMAN "memory fault"
saw this in a oracle-l post.
From Mr Dave . Herring
In working with Oracle on this issue, they had me recreate my controlfiles, then add a second channel allocation as part of the DELETE OBSOLETE, this one to disk.
All backups perform the following as their last step:
SQL 'ALTER SESSION SET tracefile_identifier=CONTROLFILE';
SQL 'ALTER DATABASE BACKUP CONTROLFILE TO trace';
... and for some reason not being able to automatically remove obsolete versions of this controlfile backup was part of the problem
follows the situation
> I've got a regular job to delete obsolete pieces from our RMAN catalog,
> which now is failing with "Memory fault". The system is Tru64 5.1b
> running Oracle 9.2.0.6 and the RMAN catalog is 10.2.0.2. Backups are to
> tape using Legato Networker 7.1.1.
>
> The procedure is very simple, just connecting to the target and catalog,
> allocating a channel and deleting:
>
> CONNECT CATALOG ../..@..
> CONNECT TARGET ../..@..
> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt_tape;
> DELETE NOPROMPT OBSOLETE;
>
> The ONLY thing I get back is:
>
> RMAN retention policy will be applied to the command
> RMAN retention policy is set to redundancy 4
> Memory fault
>
> No Oracle errors show up in either database. I can't find messages or
> info anywhere as to why this is happening. I tried setting my retention
> to larger numbers, thinking there's too many pieces to delete, but that
> didn't work. Current retention is 4 copies. I also tried using RECOVERY
> WINDOW TO xxx DAYS, which failed too. I even truss'ed the session and it
> ends with:
>
> ...
> write(10, "\0BD\0\006\0\0\0\0\003 ^".., 189) = 189
> read(10, "01 m\0\006\0\0\0\0\00701".., 2064) = 365
> write(10, "\0BD\0\006\0\0\0\0\003 ^".., 189) = 189
> read(10, "01 t\0\006\0\0\0\0\00701".., 2064) = 372
> Incurred fault #32, FLTBOUNDS %pc = 0x000000012105DD84 addr =
> 0x000000011FFF4D00
> Received signal #11, SIGSEGV [default]
> siginfo: SIGSEGV SEGV_MAPERR addr=0x0000000000000058
> Err#11 Error 11 occurred.
> *** process killed ***
From Mr Dave . Herring
In working with Oracle on this issue, they had me recreate my controlfiles, then add a second channel allocation as part of the DELETE OBSOLETE, this one to disk.
All backups perform the following as their last step:
SQL 'ALTER SESSION SET tracefile_identifier=CONTROLFILE';
SQL 'ALTER DATABASE BACKUP CONTROLFILE TO trace';
... and for some reason not being able to automatically remove obsolete versions of this controlfile backup was part of the problem
follows the situation
> I've got a regular job to delete obsolete pieces from our RMAN catalog,
> which now is failing with "Memory fault". The system is Tru64 5.1b
> running Oracle 9.2.0.6 and the RMAN catalog is 10.2.0.2. Backups are to
> tape using Legato Networker 7.1.1.
>
> The procedure is very simple, just connecting to the target and catalog,
> allocating a channel and deleting:
>
> CONNECT CATALOG ../..@..
> CONNECT TARGET ../..@..
> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt_tape;
> DELETE NOPROMPT OBSOLETE;
>
> The ONLY thing I get back is:
>
> RMAN retention policy will be applied to the command
> RMAN retention policy is set to redundancy 4
> Memory fault
>
> No Oracle errors show up in either database. I can't find messages or
> info anywhere as to why this is happening. I tried setting my retention
> to larger numbers, thinking there's too many pieces to delete, but that
> didn't work. Current retention is 4 copies. I also tried using RECOVERY
> WINDOW TO xxx DAYS, which failed too. I even truss'ed the session and it
> ends with:
>
> ...
> write(10, "\0BD\0\006\0\0\0\0\003 ^".., 189) = 189
> read(10, "01 m\0\006\0\0\0\0\00701".., 2064) = 365
> write(10, "\0BD\0\006\0\0\0\0\003 ^".., 189) = 189
> read(10, "01 t\0\006\0\0\0\0\00701".., 2064) = 372
> Incurred fault #32, FLTBOUNDS %pc = 0x000000012105DD84 addr =
> 0x000000011FFF4D00
> Received signal #11, SIGSEGV [default]
> siginfo: SIGSEGV SEGV_MAPERR addr=0x0000000000000058
> Err#11 Error 11 occurred.
> *** process killed ***
Friday, November 2, 2007
solaris upgrade to 10.2.0.3 from 10.2.0.1
install the patch p5337014_10203_SOLARIS64.zip
startup upgrade
@?/rdbms/admin/catupgrd.sql
shutdown immediate
as this was a primary db on a dataguard config.
we just created a tar out from the 10.2.0.3 in primary and extracted into the standby db
and started the standby db in standby recovery mode and automatically all the archivelog were transmitted from primary to standby and the db was automatically upgraded . we didnt have to run catupgrd on standby.
startup upgrade
@?/rdbms/admin/catupgrd.sql
shutdown immediate
as this was a primary db on a dataguard config.
we just created a tar out from the 10.2.0.3 in primary and extracted into the standby db
and started the standby db in standby recovery mode and automatically all the archivelog were transmitted from primary to standby and the db was automatically upgraded . we didnt have to run catupgrd on standby.
rsync
rsync -avvzrlpogt p5337014_10203_SOLARIS64.zip --rsync-path=/opt/csw/bin/rsync -e ssh oracle@asb-sac-nms-001:/obackup/oracle/popv/
check the status
oracle@asb-con-dbm-001:~/software> rsync -avvzrlpogt p5337014_10203_SOLARIS64.zip --stats --progress --rsync-path=/opt/csw/bin/rsync -e ssh oracle@asb-sac-nms-001:/obackup/oracle/popv/
rsync from remote to local
/opt/csw/bin/rsync --progress -avvzrlpogt --rsync-path=/opt/csw/bin/rsync -e ssh oracle@10.247.48.18:/obackup/obackup2/PRRS/cold/20071109/prrs03/syst*.dbf .
nohup rsync
nohup rsync -avvzrlpogt --stats --progress * --rsync-path=/usr/local/bin/rsync -e ssh oracle@172.16.4.135:/obackup/oracle/mtrx/hot/
ctrl+z
bg %1
tail -f nohup.out
check the status
oracle@asb-con-dbm-001:~/software> rsync -avvzrlpogt p5337014_10203_SOLARIS64.zip --stats --progress --rsync-path=/opt/csw/bin/rsync -e ssh oracle@asb-sac-nms-001:/obackup/oracle/popv/
rsync from remote to local
/opt/csw/bin/rsync --progress -avvzrlpogt --rsync-path=/opt/csw/bin/rsync -e ssh oracle@10.247.48.18:/obackup/obackup2/PRRS/cold/20071109/prrs03/syst*.dbf .
nohup rsync
nohup rsync -avvzrlpogt --stats --progress * --rsync-path=/usr/local/bin/rsync -e ssh oracle@172.16.4.135:/obackup/oracle/mtrx/hot/
ctrl+z
bg %1
tail -f nohup.out
color your windows command editor
set the properties -> edit option -> quick edit mode.
create a DWORD value in Windows registry HKEY_CURRENT_USER\Console\EnableColorSelection and set it to 1
now select something on the cmd.exe editor and press ctrl or alt +0..9
create a DWORD value in Windows registry HKEY_CURRENT_USER\Console\EnableColorSelection and set it to 1
now select something on the cmd.exe editor and press ctrl or alt +0..9
tkprof
alter session set "_dump_qbc_tree"=1;alter session set "_simple_view_merging"=false;
SQL> alter session set events '10046 trace name context
forever,level 12';
Session altered.
SQL> alter session set events '10046 trace name context off';
Session altered.
tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
Sort options: default
tkprof output
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
rman restore validate database taking a long time
One of the database was taking really long time while doing the rman restore validate database.
somehow I found out the oracle process for this db was running under oinstall . and the oracle user which was only part of dba group was doing the rman. not sure if it is the cause but after making oracle user also as part of oinstall it started to validate as expected.
solaris
bash-2.03$ id -a
uid=600(oracle) gid=600(dba) groups=600(dba),1005(oinstall)
still confused....
somehow I found out the oracle process for this db was running under oinstall . and the oracle user which was only part of dba group was doing the rman. not sure if it is the cause but after making oracle user also as part of oinstall it started to validate as expected.
solaris
bash-2.03$ id -a
uid=600(oracle) gid=600(dba) groups=600(dba),1005(oinstall)
still confused....
truss a os process
$>ps -ef| grep 16069| grep -v grep
oracle 16069 1 0 14:07:17 ? 0:00 ora_d000_mtrx
$>truss -o a.log -p 16069
$>cat a.log
poll(0x106785F40, 2, 60000) (sleeping...)
few other tools that helps
ps
prstat
pstatck -
for linux - strace
tru64 trace
oracle 16069 1 0 14:07:17 ? 0:00 ora_d000_mtrx
$>truss -o a.log -p 16069
$>cat a.log
poll(0x106785F40, 2, 60000) (sleeping...)
few other tools that helps
ps
prstat
pstatck -
for linux - strace
tru64 trace
trace over dblink
CONNECT / AS SYSDBA
grant alter session to <>;
alter system set timed_statistics=true;
alter system set max_dump_file_size=unlimited;
CONNECT <>/<>
/* create this procedure on local and remote site */
CREATE OR REPLACE PROCEDURE SETTRACE_ON
as
c1 integer;
r1 integer;
BEGIN
c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'alter session set timed_statistics=true', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set max_dump_file_size=unlimited', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set tracefile_identifier=''mv''', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set events ''10046 trace name context for
ever, level 12''',dbms_sql.v7);
r1:=dbms_sql.execute(c1);
END;
/
CREATE OR REPLACE PROCEDURE SETTRACE_OFF
as
c1 integer;
r1 integer;
BEGIN
c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'alter session set events ''10046 trace name context off''',dbms_sql.v7);
r1:=dbms_sql.execute(c1);
END;
/
connect/
execute SETTRACE_ON
execute SETTRACE_ON@
execute dbms_mview.refresh(....);
--wait for 15 mins and CTRIL+C
exit;
grant alter session to <
alter system set timed_statistics=true;
alter system set max_dump_file_size=unlimited;
CONNECT <
/* create this procedure on local and remote site */
CREATE OR REPLACE PROCEDURE SETTRACE_ON
as
c1 integer;
r1 integer;
BEGIN
c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'alter session set timed_statistics=true', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set max_dump_file_size=unlimited', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set tracefile_identifier=''mv''', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set events ''10046 trace name context for
ever, level 12''',dbms_sql.v7);
r1:=dbms_sql.execute(c1);
END;
/
CREATE OR REPLACE PROCEDURE SETTRACE_OFF
as
c1 integer;
r1 integer;
BEGIN
c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'alter session set events ''10046 trace name context off''',dbms_sql.v7);
r1:=dbms_sql.execute(c1);
END;
/
connect
execute SETTRACE_ON
execute SETTRACE_ON@
execute dbms_mview.refresh(....);
--wait for 15 mins and CTRIL+C
exit;
trace oracle session
dbms_system.set_bool_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'timed_statistics',
bval => true)
dbms_system.set_int_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'max_dump_file_size',
intval => 2147483647)
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> alter session set events '10046 trace name context off';
Session altered.
sid => 42,
serial# => 1215,
parnam => 'timed_statistics',
bval => true)
dbms_system.set_int_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'max_dump_file_size',
intval => 2147483647)
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> alter session set events '10046 trace name context off';
Session altered.
oradebug
SELECT p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid =<397>
sql>oradebug setospid
sql>oradebug unlimit
sql>oradebug dump errorstack 3;
sql>oradebug dump errorstack 3;
sql>oradebug dump errorstack 3;
sql>oradebug tracefile_name
sql>exit
s.osuser, s.machine, s.program, s.logon_time, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid =<397>
sql>oradebug setospid
sql>oradebug unlimit
sql>oradebug dump errorstack 3;
sql>oradebug dump errorstack 3;
sql>oradebug dump errorstack 3;
sql>oradebug tracefile_name
sql>exit
Thursday, November 1, 2007
firefox magic
Type about:config in the address bar
network.http.max-connections-per-server =32
network.http.max-persistent-connections-per-proxy =16
network.http.max-connections = 64
network.http.max-persistent-connections-per-server = 10
network.http.pipelining = true
network.http.pipelining.maxrequests = 200
network.http.request.max-start-delay = 0
network.http.proxy.pipelining = true
network.http.proxy.version = 1.0
network.http.max-connections-per-server =32
network.http.max-persistent-connections-per-proxy =16
network.http.max-connections = 64
network.http.max-persistent-connections-per-server = 10
network.http.pipelining = true
network.http.pipelining.maxrequests = 200
network.http.request.max-start-delay = 0
network.http.proxy.pipelining = true
network.http.proxy.version = 1.0
rman backup of controlfile
restoring from archivelog backupset
list backup of archivelog logseq 3113;
change backupset 271837 available;
restore archivelog from logseq 3113;
recover database
list backup of archivelog until scn 21253467415
delete backup of archivelog until scn 21253467415;
-
RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;
RESTORE DATAFILE 3 PREVIEW;
RESTORE ARCHIVELOG FROM LOGSEQ 200 PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;
change backupset 271837 available;
restore archivelog from logseq 3113;
recover database
list backup of archivelog until scn 21253467415
delete backup of archivelog until scn 21253467415;
-
RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;
RESTORE DATAFILE 3 PREVIEW;
RESTORE ARCHIVELOG FROM LOGSEQ 200 PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;
Subscribe to:
Posts (Atom)