Latest Post
Edit Add Modify Delete Oem 12c Agent Registration Password
Written By askMLabs on Saturday, January 19, 2013 | 10:44 AM
Sun Oracle Servers Animated App For Your iPhone
Written By askMLabs on Thursday, January 17, 2013 | 1:02 PM
I have an interesting iPhone app on my phone which shows animated graphical representation of all the Sun Oracle servers. It reminds me of my old working experience on telephone exchanges installation. Particularly OCB and E10B telephone exchanges racks and their cabling between different racks are similar to the sun oracle server racks. It gives me more interest to go through these server details.
But its a worth looking at this app if you are working on oracle technologies. Neatly animated , compact and small application with good valuable content.
https://itunes.apple.com/us/app/oracle-hardware-virtual-tour/id535920742?mt=8
Thanks
SRI
But its a worth looking at this app if you are working on oracle technologies. Neatly animated , compact and small application with good valuable content.
https://itunes.apple.com/us/app/oracle-hardware-virtual-tour/id535920742?mt=8
Thanks
SRI
How To Setup CISCO VPN On iPhone
Written By askMLabs on Thursday, January 3, 2013 | 12:58 PM
Apple announced that it can support cisco SSL VPN on iPhone from iOS 4 onwards.
http://blogs.cisco.com/news/apple_announces_cisco_ssl_vpn_support_on_iphone_os_4/
Now in this article, i am going to guide, the steps involved in configuring CISCO VPN on your iPhone , iPod Touch and in iPad.
Navigate to "Settings -> General -> VPN" and then tap "ON". It will give you different options to configure VPN. Tap on "IPSec"
For the configuration of the VPN on iPhone, we need to be ready with the following details. All these details are obtained from the VPN profile provided to us. It is a normal file with ".pcf" extention and you can open it with any notepad.
Description : This is the smal description of the
Server : Host value from .pcf file
Account : username used to connect to VPN
Password : Password provided to you to connect to VPN using above username
Group Name : This is value of "GroupName" in vpn profile. ( .pcf file )
Secret : This is the most important part of this article. This is the value for "enc_GroupPwd" in encrypted form in "<vpnprofile>.pcf" file. We need to decrypt this value before entering in iPhone. I used the following url to decrypt the password and it worked for me all the time.
http://www.unix-ag.uni-kl.de/~massar/bin/cisco-decode/
You can use any tool that can decrypt this password for you. Just open google and type "ipsec password decrypter" and you will get number of options.
After entering the secret value, save the values. It will then ask you for the password, enter your vpn password.
You should all be fine to use your private network now.
Thanks
SRI
http://blogs.cisco.com/news/apple_announces_cisco_ssl_vpn_support_on_iphone_os_4/
Now in this article, i am going to guide, the steps involved in configuring CISCO VPN on your iPhone , iPod Touch and in iPad.
Navigate to "Settings -> General -> VPN" and then tap "ON". It will give you different options to configure VPN. Tap on "IPSec"
For the configuration of the VPN on iPhone, we need to be ready with the following details. All these details are obtained from the VPN profile provided to us. It is a normal file with ".pcf" extention and you can open it with any notepad.
Description : This is the smal description of the
Server : Host value from .pcf file
Account : username used to connect to VPN
Password : Password provided to you to connect to VPN using above username
Group Name : This is value of "GroupName" in vpn profile. ( .pcf file )
Secret : This is the most important part of this article. This is the value for "enc_GroupPwd" in encrypted form in "<vpnprofile>.pcf" file. We need to decrypt this value before entering in iPhone. I used the following url to decrypt the password and it worked for me all the time.
http://www.unix-ag.uni-kl.de/~massar/bin/cisco-decode/
You can use any tool that can decrypt this password for you. Just open google and type "ipsec password decrypter" and you will get number of options.
After entering the secret value, save the values. It will then ask you for the password, enter your vpn password.
You should all be fine to use your private network now.
Thanks
SRI
Blocking And Unblocking OEM Agent in OEM 12c
Written By askMLabs on Monday, December 31, 2012 | 9:02 AM
In this present short demo, i am going to show you how to block and unblock an oem agent manually in oem 12c. There might be various reasons an oem agent may into a blocked state and this demo shows you how to unblock it.
The reasons that the agent may go into blocked state are ..
If there is any network break between oem agent and oms server
If there is any space issue on oms server and oem agent can't upload the metrics data to oms server
This demo does not contain any voice and the navigations are self explanatory. Leave your comments if you have any doubts.
Thanks
SRI
The reasons that the agent may go into blocked state are ..
If there is any network break between oem agent and oms server
If there is any space issue on oms server and oem agent can't upload the metrics data to oms server
This demo does not contain any voice and the navigations are self explanatory. Leave your comments if you have any doubts.
Thanks
SRI
Oracle OpenWorld 2012 Update
Written By askMLabs on Monday, October 1, 2012 | 5:16 PM
Larry Ellison's major announcements in OracleOpenWorld-2012
IaaS => Infrastructure as a Service
Private Clouds
Oracle Database 12c
Exadata X3 machine.
Thanks
SRIKRISHNA MURTHY
COPY Schema into Same Database with impdp
Written By askMLabs on Thursday, June 7, 2012 | 4:10 PM
In this article I am going to explain the procedure to copy a schema to a different schema in the same database without exporting the source schema. This procedure works fine if you want to copy the schema to a different database.
Verify the database link
We are eliminating the creation of dump file, by using database link with the parameter network_link
Hope it helps
SRI
- Create a directory pointing to any physical directory on the server
- Create a loopback database link. It means, you have to create a database link pointing to the same database.
- Use the impdp to copy schema to another schema.
Assume that the database name is DB-A. I am trying to copy a schema SCOTT to a different schema SCOTT_NEW in the same database.
SQL> create a directory TEST_DIR as ‘/xxxx/exp-dir/’;If you are trying to copy schema to a different database , say DB-B , create database link as follows.
SQL> create a database link SCOTT_DB_LINK connect to SYSTEM identified by xxxxxxx using ‘DB-A’;
SQL> create a database link SCOTT_DB_LINK connect to system identified by xxxxx using ‘DB-B’;(NOTE : We should be able to resolve the alias DB-A to database A and DB-B to database B.)
Verify the database link
SQL> select * from dual@SCOTT_DB_LINK;Use the following syntax to copy schema,
impdp system/xxxxxx schemas=SCOTT directory=TEST_DIR network_link=SCOTT_DB_LINK remap_schema=SCOTT:SCOTT_NEW logfile=TEST_DIR:SCOTT_NEW.logVerify the new schema with the source schema with following commands.
SQL> select object_type,count(1) from dba_objects where owner='SCOTT' and status='INVALID' group by object_type;Observations:
SQL> select object_type,count(1) from dba_objects where owner='SCOTT_NEW' and status='INVALID' group by object_type;
SQL> select object_name,object_type from dba_objects where owner='SCOTT' and status='INVALID';
SQL> select object_name,object_type from dba_objects where owner='SCOTT_NEW' and status='INVALID';
We are eliminating the creation of dump file, by using database link with the parameter network_link
Hope it helps
SRI
12c oem template export and import using emcli
Written By askMLabs on Tuesday, May 15, 2012 | 2:28 PM
This demo explains the steps to export and import a template in oem 12c using emcli command line tool.
Thanks
SRI
Thanks
SRI
Oracle VM templates for Cloud Control
Written By askMLabs on Wednesday, May 2, 2012 | 12:22 PM
Oracle VM templates for Cloud Control oem 12c are now available for download from edelivery.
Oracle VM Template for Enterprise Manager Cloud Control 12c Media Pack for x86 (64 bit)
Oracle VM Template for Enterprise Manager Cloud Control 12c (Agent only) Media Pack for x86 (64 bit)
Hope it helps
SRI
Oracle VM Template for Enterprise Manager Cloud Control 12c Media Pack for x86 (64 bit)
Oracle VM Template for Enterprise Manager Cloud Control 12c (Agent only) Media Pack for x86 (64 bit)
Hope it helps
SRI
CLOUD CONTROL INTEGRATION WITH OVM
Written By askMLabs on Tuesday, May 1, 2012 | 8:47 AM
OEM 12C CLOUD CONTROL INTEGRATION WITH OVM
Upon addition of the location, a job executes to configure the Software Library. Under this location, all required entities for EMs features like Provisioning, Patching, and Cloud Management are populated.
Once the job is successful, you will see the components configured in the Software Library.
To view the Software Library, navigate to: Enterprise Manager > Patching and Provisioning > Software Library
Select one of the above plugins (list specified above) , then click Download. The Schedule Download dialog appears. In this example, "Oracle Virtualization" plug-in is selected.
Then it changes the status from "Available" to "Downloaded"
Repeat the same step to all the other plugins (Oracle Cloud Application, Oracle Chargeback and Capacity Planning, Oracle Exalogic)
Once an entity has been downloaded to the Software Library, it is ready to be applied to your installation. Select an update from the list whose status is Downloaded, then click Apply.
You will be redirected to the plug-in deployment page.
Click on Add to add the management agent where plugin will be deployed
- Configure Oracle Software Library
- Setting up the infrastructure for Online mode
- Setup MOS(My Oracle Support) Credentials
- Deploying the Cloud Plug-ins
- Deploying the Cloud Plug-ins to the Management Server
- Deploying the Oracle Virtualization Plug-in to the Management Agent
- Oracle VM Manager 3.0 Registration Prerequisites
- Registering the Oracle VM Manager in oem 12c
- Synchronizing the OVM Manager Targets
- Oracle Software Library (Software Library) is a repository that stores software patches, virtual appliance images, reference gold images, application software and their associated directive scripts.
Verify whether you already have the Software Library configured. If not follow the below steps to configure:
From the Enterprise menu, select Provisioning and Patching, then click Software Library.
From there choose actions > administration.
If there is nothing configured yet, click Add then choose OMS Shared Filesystem location.
Upon addition of the location, a job executes to configure the Software Library. Under this location, all required entities for EMs features like Provisioning, Patching, and Cloud Management are populated.
Once the job is successful, you will see the components configured in the Software Library.
To view the Software Library, navigate to: Enterprise Manager > Patching and Provisioning > Software Library
Setting up the infrastructure for Online mode
If the enterprise manager is configured to connect to MOS , plugins or patches can be automatically downloaded. Follow the following sequence of steps ..- Set up connection to MOS (Online Mode)Check to see if the connection to My Oracle Support is set to "Online". Navigate to
Setup > Provisioning and Patching > Offline Patching.
- The connection setting has to be "Online". If not set, set it to Online and click Apply.
- Select the adjacent tab My Oracle Support and Proxy Connection
- Configure your Proxy Connection to connect to My Oracle Support. Provide the required Proxy Server, Port and Realm details, then click Apply.
- Once the configuration is done, click Test to check if the connectivity works correctly.
Setup MOS(My Oracle Support) Credentials
Deploying the Cloud Plug-ins
Plug-ins must first be downloaded to the OMS using Self Update before they can be deployed. To enable monitoring of virtualization targets, you must download the plug-ins and deploy them. The following plug-ins must be deployed on the Management Server:
- Oracle Virtualization
- Oracle Cloud Application
- Oracle Chargeback and Capacity Planning
- Oracle Exalogic
Select one of the above plugins (list specified above) , then click Download. The Schedule Download dialog appears. In this example, "Oracle Virtualization" plug-in is selected.
Then it changes the status from "Available" to "Downloaded"
Repeat the same step to all the other plugins (Oracle Cloud Application, Oracle Chargeback and Capacity Planning, Oracle Exalogic)
Once an entity has been downloaded to the Software Library, it is ready to be applied to your installation. Select an update from the list whose status is Downloaded, then click Apply.
You will be redirected to the plug-in deployment page.
Deploying the Cloud Plug-ins to the Management Server
Complete the below steps to all the plugins mentioned in step 4.
Select the plugin and deploy it on OMS. Right click the plugin ->Deploy on->Management server
Plugin pre-requisites will be executed. After successful pre-requisite checks, click on deploy.
NOTE : OMS will be bounced now.
Once the OMS is up, navigate to Setup->Extensibility->Plug-in . The Plugin version on OMS is now 12.1.0.2.
Deploying the Oracle Virtualization Plug-in to the Management Agent
Click on Add to add the management agent where plugin will be deployed
Once you have selected the agents, click continue
It runs the plugin pre-requisites and click next
Click Deploy, to deploy the plug-in on the agents
Oracle VM Manager 3.0 Registration Prerequisites
The following prerequisites must be meet before Oracle VM Manager can be registered in Oracle Enterprise Manager 12c Cloud Control.- The Oracle Virtualization plug-in must be install and enabled on the Oracle Management Service.
- The Oracle VM Manager host must have the Oracle Management Agent (OMA) and be a monitored target in Oracle Enterprise Manager 12c Cloud Control.
- The Oracle VM Manager host must have the Oracle Virtualization plug-in.
- The preferred credentials for the Oracle VM Manager host must be configured in Oracle Enterprise Manager 12c Cloud Control.
- The Oracle Management Service and the Oracle VM Manager host must have consistent name resolution using DNS with both forward and reverse lookups.
Registering the Oracle VM Manager in oem 12c
| Oracle VM Manager Registration Page Title | Entry Selections and Entry Syntax |
| The name entered in the "Name" text box is displayed on the Infrastructure Cloud page. |
| The EM Agent URL is selected by clicking the magnifying glass icon. A pop-up window will appear, select the Oracle VM Manager agent from the list. The EM Agent URL syntax is: "https://<AGENT-FQDN>:3872/emd/main/" Note: The Oracle VM Manager host can be selected as long as the host is managed target by OEM with the Oracle Virtualization plug-in. |
| Enter "http://<ORACLE VM MANAGER HOST FQDN>:7001" in the Oracle VM Manager Core URL text box. |
| Select the Automatic Synchronization check box to enable automated synchronization between OEM and the Oracle VM API Core. |
| Enter the Oracle VM Manager admin user name and the admin password in the Monitoring Credentials for Oracle VM Manager text fields. |
| Enter the Oracle VM Manager admin user name and the admin password in the Administration Credentials for Oracle VM Manager text fields. Select the Use Administration Credentials check box. |
| Enter the non-SSL Oracle VM Manager URL, i.e.http://<ORACLE VM MANAGER HOST FQDN>:7001/ovm/console/faces/login.jspx in the Oracle VM Manager Console URL text box. |
| Once all of the information has been entered, click the Submit button to start the registration job. |
Synchronizing the OVM Manager Targets
Cloud Control OEM 12c Installation
Written By askMLabs on Saturday, April 28, 2012 | 6:30 PM
OEM 12C CLOUD CONTROL INSTALLATION
- Software
- OS installation
- Database Installation
- Oem 12c Prerequisites
- Hardware Requirements
- Operating System Requirements
- Package Requirements
- Kernel Parameter Requirements
- Library Requirements
- Operating System Groups and Users Requirements
- Unique Host Name and Static IP Address Requirements
- Temporary Directory Space Requirements
- Central Inventory Requirements
- UMASK Value Requirements
- File Descriptor Requirements
- Existing Database Version Requirements
- Host File Requirements
- Port Requirements
- Internet Connection Requirements
- 5.Oem 12c Installation Procedure
- 6.Verification Checks
- 7.Starting and Stopping oem 12c Services
The required software for the oem 12c
cloud control are :
- Oracle Linux 6.1 (x86_64) Download
- Oracle Database 11gR2 (x86_64) Download
- Enterprise Manager Cloud Control 12c (12.1.0.1) (x86_64) with BP1 patch Download
Oracle Linux 6.1 (x86_64) installation :
3. Database Installation :
Oracle Database 11gR2 installation :
Below links help to install database
4.1. Hardware Requirements :
CPU, RAM, Heap Size, and Hard Disk Space
requirements vary depending on the type of installation we are choosing. Please
review the here
to know the exact hardware requirements.
4.2. Operating System Requirements :
Ensure that you install Enterprise
Manger Cloud Control only on certified operating system as mentioned in the Enterprise
Manager Certification Matrix.
4.3. Package Requirements :
make-3.81binutils-2.17.50.0.6gcc -4.1.1libaio-0.3.106glibc-common-2.3.4libstdc++ -4.1.1setarch-1.6sysstat-5.0.5rng-utils-2.0libXtst-1.0.1-3.1(x86_64)xorg-x11-utils (This package is required only for GUI-based interactive installation, and not for silent installation)
In addition,
install the 32-bit version as well as the 64-bit version of glibc-devel-2.5-49
to avoid any linking errors while installing the OMS. The installer checks for
this package, so if this package is not installed, the installer displays an
error.
4.4 Kernel Parameter Requirements:
For installing an OMS or a Management Agent on Unix
operating systems (32-bit or 64-bit), set the kernel.shmmax parameter to a
value 1 byte less than 4 GB or 4294967295.
To verify the value assigned to kernel.shmmax
parameter, run the following command:
cat /proc/sys/kernel/shmmax
To set the value for kernel.shmmax parameter, run
the following command:
sysctl -w kernel.shmmax=<new value>
4.5.
Library Requirements
:
Install glibc-2.5.12 package
4.6 Operating System Groups and Users Requirements:
# /usr/sbin/groupadd oinstall# /usr/sbin/useradd -g oinstall oracle# passwd oracle
4.7. Unique Host Name and
Static IP Address Requirements :
Ensure that you check the network configuration to
verify that the host on which you are installing resolves to a unique host name
and a static IP address that is visible to other hosts in the network.
You must use a static IP address. If you use a
dynamic IP address, the installation might fail.
Ensure that the host name specified in /etc/hosts
file is unique, and that it maps to the correct IP address. Following is the
recommended format of the /etc/hosts file:
<ip address> <fully_qualified_host_name>
<short_host_name>
If the DNS server is configured, then it should
resolve the host name for the OMS machine correctly. For example, all these
command must return the same output:
nslookup host1nslookup host1.foo.comnslookup 141.81.5.71
4.8. Temporary Directory Space Requirements :
Ensure that you allocate 400 MB of space
for a temporary directory where the executables can be copied.
4.9. Central Inventory
Requirements :
Ensure that you allocate 100 MB of space
for the central inventory directory.
Also ensure that the central inventory
directory is not on a shared file system. If it is already on a shared file
system, then switch over to a non-shared file system by following the
instructions outlined in My Oracle Support note 1092645.1.
To determine whether the Oracle
Inventory group exists, enter the following command:
$ more /etc/oraInst.loc If the oraInst.loc file exists, then the output from this command looks like: inventory_loc=/u01/app/oracle/oraInventory inst_group=oinstall
4.10.UMASK Value
Requirements:
Ensure that you set the default file
mode creation mask (umask) to 022 in the shell startup file.
4.11.File Descriptor
Requirements :
If the current value is less than 4096, then as root
user, update the /etc/security/limits.conf file as shown below:
<UID> soft nofile 4096 <UID> hard nofile 4096
4.12. Existing Database
Version Requirements :
Ensure that the database is patched with
all the Patch Set Updates (PSU) or Critical Patch Updates (CPU) released for
that release. Also ensure that you apply the patches.
If you use Oracle Database 11g Release 2
(11.2.0.1), then ensure that you apply the patches for bugs 10014178 and
8799099.
If you use Oracle Database 11g Release 1
(11.1.0.7), then ensure that you apply Patch Set Update 2 and the patches for
bugs 8644757 and 7525072.
The database can also have the Automatic
Memory Management (AMM) feature enabled.
4.13. Host File Requirements
:
Ensure that the host name specified in
the /etc/hosts file is unique, and ensure that it maps to the correct IP
address of that host. Otherwise, the installation can fail on the
product-specific prerequisite check page.
The following is the recommended format
of the /etc/hosts file:
<ip>
<fully_qualified_host_name> <short_host_name>
4.14. Port Requirements
:
Ensure that the ports you assign (within the given
range) to the following components are free and available:
Admin Server HTTP SSL Port = 7101 - 7200
Enterprise Manager Upload HTTP Port = 4889 - 4898
Enterprise Manager Upload HTTP SSL Port = 1159, 4899 - 4908
Enterprise Manager Central Console HTTP Port = 7788 - 7798
Enterprise Manager Central Console HTTP SSL Port = 7799 - 7809
Oracle Management Agent Port = 3872, 1830 - 1849
To verify if a port is free, run the following command:
Admin Server HTTP SSL Port = 7101 - 7200
Enterprise Manager Upload HTTP Port = 4889 - 4898
Enterprise Manager Upload HTTP SSL Port = 1159, 4899 - 4908
Enterprise Manager Central Console HTTP Port = 7788 - 7798
Enterprise Manager Central Console HTTP SSL Port = 7799 - 7809
Oracle Management Agent Port = 3872, 1830 - 1849
To verify if a port is free, run the following command:
On Unix:
netstat -anp | grep <port no>
4.15. Internet Connection
Requirements :
Oracle recommends that the host from
where you are running the installer have a connection to the Internet so that
the configuration information can be automatically collected and uploaded to My
Oracle Support.
5. Oem 12c Installation Procedure :
6.Verification Checks :
Access the following URL with sysman user and navigate to different pages to verify everything looks good.
https://<hostname with FQDN>:7803/em
7. Starting and Stopping oem 12c services :
Use the following commands to start all components :
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ export OMS_HOME=/u01/app/oracle/Middleware/oms
$ export AGENT_HOME=/u01/app/oracle/Middleware/agent/core/12.1.0.1.0
# Start everything
$ $ORACLE_HOME/bin/sqlplus '/as sysdba'
SQL> startup
$lsnrctl start listener
$ $OMS_HOME/bin/emctl start oms
$ $AGENT_HOME/bin/emctl start agent
Use the following commands to stop all components :
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ export OMS_HOME=/u01/app/oracle/Middleware/oms
$ export AGENT_HOME=/u01/app/oracle/Middleware/agent/core/12.1.0.1.0
# Stop everything
$ $OMS_HOME/bin/emctl stop oms -all
$ $AGENT_HOME/bin/emctl stop agent
$ $ORACLE_HOME/bin/sqlplus '/as sysdba'
SQL> shut immediate
$ $lsnrctl stop listener
Hope it helps
SRI
Renaming A Datafile In A Physical Standby Environment
Written By askMLabs on Monday, February 20, 2012 | 2:45 PM
In this article we will see how to rename a datafile in a physical standby setup. Most structural changes made to a primary database are automatically propagated through redo data to a physical standby database. Some structural and configuration changes at primary database requires manual intervention at a physical standby database.
When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
1. Verify primary and standby databases and also change the initialization parameter STANDBY_FILE_MANAGEMENT from AUTO to MANUAL.
On Primary :
SQL> select name,db_unique_name,database_role,switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL orcl PRIMARY TO STANDBY
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence 524
Next log sequence to archive 526
Current log sequence 526
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
On Standby :
SQL> select name,db_unique_name,database_role,switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL sbyorcl PHYSICAL STANDBY NOT ALLOWED
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence 524
Next log sequence to archive 0
Current log sequence 526
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
517 YES
518 YES
519 YES
520 YES
521 YES
522 YES
523 YES
524 YES
525 IN-MEMORY
9 rows selected.
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL>
2. Verify the tablespace to which the datafile belongs and make it offline. This step is performed on primary database.
SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf
SQL> alter tablespace example offline;
Tablespace altered.
3. Rename the datafile to new location and bring tablespace back online. This step is performed on primary database.
[oracle@dgaskmpri01 OPatch]$ mv /home/oracle/app/oracle/oradata/orcl/example01.dbf /tmp/askm/example01_temp.dbf
[oracle@dgaskmpri01 OPatch]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:47:17 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter tablespace example rename datafile '/home/oracle/app/oracle/oradata/orcl/example01.dbf' to '/tmp/askm/example01_temp.dbf';
Tablespace altered.
SQL> alter tablespace example online;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';
FILE_NAME
--------------------------------------------------------------------------------
/tmp/askm/example01_temp.dbf
SQL>
4. Verify the same tablespace on standby database.
SQL> select ts#,name from v$tablespace where name='EXAMPLE';
TS# NAME
---------- ------------------------------
6 EXAMPLE
SQL> select name from v$datafile where ts#=6;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf
SQL>
5. Stop recovery on standby database and shut it down.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
6. Rename the datafile on standby database.
[oracle@dgaskmsby01 askm]$ mv /home/oracle/app/oracle/oradata/sbyorcl/example01.dbf /tmp/askm/example01_temp.dbf
[oracle@dgaskmsby01 askm]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:53:49 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 343935672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> alter database rename file '/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf' to '/tmp/askm/example01_temp.dbf';
Database altered.
SQL> select name from v$datafile where ts#=6;
NAME
--------------------------------------------------------------------------------
/tmp/askm/example01_temp.dbf
7. Keep standby database in recovery mode.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
8. Set initialization parameter STANDBY_FILE_MANAGEMENT value back to AUTO.
On Primary :
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
On Standby :
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
Reference :
Renaming a Datafile in the Primary Database
MOS ID : 733796.1
Hope It Helps
SRI
When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
1. Verify primary and standby databases and also change the initialization parameter STANDBY_FILE_MANAGEMENT from AUTO to MANUAL.
On Primary :
SQL> select name,db_unique_name,database_role,switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL orcl PRIMARY TO STANDBY
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence 524
Next log sequence to archive 526
Current log sequence 526
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
On Standby :
SQL> select name,db_unique_name,database_role,switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL sbyorcl PHYSICAL STANDBY NOT ALLOWED
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence 524
Next log sequence to archive 0
Current log sequence 526
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
517 YES
518 YES
519 YES
520 YES
521 YES
522 YES
523 YES
524 YES
525 IN-MEMORY
9 rows selected.
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL>
2. Verify the tablespace to which the datafile belongs and make it offline. This step is performed on primary database.
SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf
SQL> alter tablespace example offline;
Tablespace altered.
3. Rename the datafile to new location and bring tablespace back online. This step is performed on primary database.
[oracle@dgaskmpri01 OPatch]$ mv /home/oracle/app/oracle/oradata/orcl/example01.dbf /tmp/askm/example01_temp.dbf
[oracle@dgaskmpri01 OPatch]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:47:17 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter tablespace example rename datafile '/home/oracle/app/oracle/oradata/orcl/example01.dbf' to '/tmp/askm/example01_temp.dbf';
Tablespace altered.
SQL> alter tablespace example online;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';
FILE_NAME
--------------------------------------------------------------------------------
/tmp/askm/example01_temp.dbf
SQL>
4. Verify the same tablespace on standby database.
SQL> select ts#,name from v$tablespace where name='EXAMPLE';
TS# NAME
---------- ------------------------------
6 EXAMPLE
SQL> select name from v$datafile where ts#=6;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf
SQL>
5. Stop recovery on standby database and shut it down.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
6. Rename the datafile on standby database.
[oracle@dgaskmsby01 askm]$ mv /home/oracle/app/oracle/oradata/sbyorcl/example01.dbf /tmp/askm/example01_temp.dbf
[oracle@dgaskmsby01 askm]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:53:49 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 343935672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> alter database rename file '/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf' to '/tmp/askm/example01_temp.dbf';
Database altered.
SQL> select name from v$datafile where ts#=6;
NAME
--------------------------------------------------------------------------------
/tmp/askm/example01_temp.dbf
7. Keep standby database in recovery mode.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
8. Set initialization parameter STANDBY_FILE_MANAGEMENT value back to AUTO.
On Primary :
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
On Standby :
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
Reference :
Renaming a Datafile in the Primary Database
MOS ID : 733796.1
Hope It Helps
SRI
Using expdp To Export From Physical Standby Database
Written By askMLabs on Thursday, February 16, 2012 | 2:08 PM
In this article, we will discuss the steps to use expdp on physical standby database. There may be some situations where you don’t want your production database loaded with the expdp operation. The other option you have in a standby setup is that you can use your standby database for export operations. But in a physical standby database, the database is in recovery mode and if we want to open the database for expdp operation, we can open the physical standby database in read only mode. Expdp operation needs to create and maintain a master table which needs requires the physical standby database opened in read write mode.
So we have an option in expdp to export standby database. We can use expdp parameter NETWORK_LINK from any non standby database to export database.
1. Verify primary and Standby Databases :
On Primary :
SQL> select name,db_unique_name,database_role,switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL orcl PRIMARY TO STANDBY
SQL> arcvhive log list
SP2-0734: unknown command beginning "arcvhive l..." - rest of line ignored.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence 522
Next log sequence to archive 524
Current log sequence 524
SQL>
On Standby :
SQL> select name,user,database_role,switchover_status from v$database;
NAME USER DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL SYS PHYSICAL STANDBY NOT ALLOWED
SQL> select name,db_unique_name,database_role,switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL sbyorcl PHYSICAL STANDBY NOT ALLOWED
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence 522
Next log sequence to archive 0
Current log sequence 524
SQL>
2. Cancel recovery and Open standby database in Read only Mode :
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
sbyorcl MOUNTED
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
sbyorcl OPEN
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL>
3. Create a physical directory on primary server to keep export dump files :
[oracle@dgaskmpri01 ~]$ mkdir /tmp/askm
[oracle@dgaskmpri01 ~]$ chmod 777 /tmp/askm
[oracle@dgaskmpri01 ~]$
4. Create DB Link, Oracle Directory and test it :
[oracle@dgaskmpri01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 04:25:18 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn system/xxxxx
Connected.
SQL> create database link db_sbyorcl connect to system identified by oracle using 'sbyorcl';
Database link created.
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------
orcl
SQL> select db_unique_name from v$database@db_sbyorcl;
DB_UNIQUE_NAME
------------------------------
sbyorcl
SQL> create directory sby_dump_dir as '/tmp/askm';
Directory created.
SQL>
5. Execute expdp on primary server :
[oracle@dgaskmpri01 askm]$ expdp system/oracle full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
Export: Release 11.2.0.2.0 - Production on Tue Nov 22 04:50:05 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 183.1 MB
Processing object type DATABASE_EXPORT/TABLESPACE
.....
.....
....
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 7564
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP('OE',0,1,'11.02.00.00.00',newblock)
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_CUBE_EXP
ORA-16000: database open for read-only access
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_CUBE_EXP"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 7564
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PROCACT_SCHEMA:"OE"]
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_METADATA", line 5466
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358
----- PL/SQL Call Stack -----
object line object
handle number name
0x34f7b2c0 19208 package body SYS.KUPW$WORKER
0x34f7b2c0 8385 package body SYS.KUPW$WORKER
0x34f7b2c0 2935 package body SYS.KUPW$WORKER
0x34f7b2c0 9054 package body SYS.KUPW$WORKER
0x34f7b2c0 1688 package body SYS.KUPW$WORKER
0x34d28650 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 04:55:56
[oracle@dgaskmpri01 askm]$ expdp system/oracle attach=SYS_EXPORT_FULL_01
Export: Release 11.2.0.2.0 - Production on Tue Nov 22 05:03:32 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: B2533E2386512016E040A8C022010D27
Start Time: Tuesday, 22 November, 2011 5:03:43
Mode: FULL
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /tmp/askm/sby_db.dmp
bytes written: 950,272
Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Object Schema: OE
Object Type: DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Completed Objects: 36
Worker Parallelism: 1
Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes
[oracle@dgaskmpri01 askm]$ clear
[oracle@dgaskmpri01 askm]$
I got error while exporting the database. This is identified as bug in 10g and 11gR1. As per the oracle documentation, it is rectified in 11gR2, but I am still getting this issue on 11gR2. I need to further investigate it. Comments and Feedbacks are accepted pls
Reference:
How to use Data Pump Export (expdp) to export from Physical Standby Database [ID 1356592.1]
Thanks
SRI
So we have an option in expdp to export standby database. We can use expdp parameter NETWORK_LINK from any non standby database to export database.
- Verify primary and Standby Databases
- Cancel recovery and Open standby database in Read only Mode
- Create a physical directory on primary server to keep export dump files
- Create DB Link, Oracle Directory and test it.
- Execute expdp on primary server.
1. Verify primary and Standby Databases :
On Primary :
SQL> select name,db_unique_name,database_role,switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL orcl PRIMARY TO STANDBY
SQL> arcvhive log list
SP2-0734: unknown command beginning "arcvhive l..." - rest of line ignored.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence 522
Next log sequence to archive 524
Current log sequence 524
SQL>
On Standby :
SQL> select name,user,database_role,switchover_status from v$database;
NAME USER DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL SYS PHYSICAL STANDBY NOT ALLOWED
SQL> select name,db_unique_name,database_role,switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL sbyorcl PHYSICAL STANDBY NOT ALLOWED
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence 522
Next log sequence to archive 0
Current log sequence 524
SQL>
2. Cancel recovery and Open standby database in Read only Mode :
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
sbyorcl MOUNTED
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
sbyorcl OPEN
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL>
3. Create a physical directory on primary server to keep export dump files :
[oracle@dgaskmpri01 ~]$ mkdir /tmp/askm
[oracle@dgaskmpri01 ~]$ chmod 777 /tmp/askm
[oracle@dgaskmpri01 ~]$
4. Create DB Link, Oracle Directory and test it :
[oracle@dgaskmpri01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 04:25:18 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn system/xxxxx
Connected.
SQL> create database link db_sbyorcl connect to system identified by oracle using 'sbyorcl';
Database link created.
SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------
orcl
SQL> select db_unique_name from v$database@db_sbyorcl;
DB_UNIQUE_NAME
------------------------------
sbyorcl
SQL> create directory sby_dump_dir as '/tmp/askm';
Directory created.
SQL>
5. Execute expdp on primary server :
[oracle@dgaskmpri01 askm]$ expdp system/oracle full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
Export: Release 11.2.0.2.0 - Production on Tue Nov 22 04:50:05 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 183.1 MB
Processing object type DATABASE_EXPORT/TABLESPACE
.....
.....
....
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 7564
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP('OE',0,1,'11.02.00.00.00',newblock)
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_CUBE_EXP
ORA-16000: database open for read-only access
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_CUBE_EXP"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 7564
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PROCACT_SCHEMA:"OE"]
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_METADATA", line 5466
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358
----- PL/SQL Call Stack -----
object line object
handle number name
0x34f7b2c0 19208 package body SYS.KUPW$WORKER
0x34f7b2c0 8385 package body SYS.KUPW$WORKER
0x34f7b2c0 2935 package body SYS.KUPW$WORKER
0x34f7b2c0 9054 package body SYS.KUPW$WORKER
0x34f7b2c0 1688 package body SYS.KUPW$WORKER
0x34d28650 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 04:55:56
[oracle@dgaskmpri01 askm]$ expdp system/oracle attach=SYS_EXPORT_FULL_01
Export: Release 11.2.0.2.0 - Production on Tue Nov 22 05:03:32 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: B2533E2386512016E040A8C022010D27
Start Time: Tuesday, 22 November, 2011 5:03:43
Mode: FULL
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /tmp/askm/sby_db.dmp
bytes written: 950,272
Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Object Schema: OE
Object Type: DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Completed Objects: 36
Worker Parallelism: 1
Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes
[oracle@dgaskmpri01 askm]$ clear
[oracle@dgaskmpri01 askm]$
I got error while exporting the database. This is identified as bug in 10g and 11gR1. As per the oracle documentation, it is rectified in 11gR2, but I am still getting this issue on 11gR2. I need to further investigate it. Comments and Feedbacks are accepted pls
Reference:
How to use Data Pump Export (expdp) to export from Physical Standby Database [ID 1356592.1]
Thanks
SRI
Creating Snapshot Standby Database
Written By askMLabs on Tuesday, January 17, 2012 | 12:00 PM
In this article we will see the detailed steps to create a snapshot standby database. Snapshot standby database is new in database 11g and we will create snapshot standby database from physical standby database. This snapshot standby database is fully updatable database and it is opened for read write operation. When the snapshot database is created from physical standby database, redo logs are still transferred to standby site but are not applied.
The practical use of snapshot standby database is in the situation where if you want to clone a production database for testing something, you can convert physical standby database to snapshot standby database which is as close as to the production database wrt data, do required testing on the snapshot standby database and convert it back to physical standby database. When you convert the snapshot standby database back to the physical standby database, all the modifications done to snapshot database will be lost and it is synchronized with the primary database by applying all the pending redo logs transferred but not applied on the standby database.
I assume that I have a physical standby database setup and data guard broker is configured to manage standby database.
1. Convert Physical Standby Database To Snapshot Database :
2. Verify Snapshot Standby Database :
The practical use of snapshot standby database is in the situation where if you want to clone a production database for testing something, you can convert physical standby database to snapshot standby database which is as close as to the production database wrt data, do required testing on the snapshot standby database and convert it back to physical standby database. When you convert the snapshot standby database back to the physical standby database, all the modifications done to snapshot database will be lost and it is synchronized with the primary database by applying all the pending redo logs transferred but not applied on the standby database.
I assume that I have a physical standby database setup and data guard broker is configured to manage standby database.
- Convert Physical Standby Database To Snapshot Database
- Verify Snapshot Standby Database
- Do Some Testing In Snapshot Database
- Convert Snapshot Database Back To Physical Standby Database
- Verify the Modifications Done In Step 3.
1. Convert Physical Standby Database To Snapshot Database :
[oracle@dgaskmpri01 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.2.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> show configuration;
Configuration - askm_config
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
sbyorcl - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database sbyorcl;
Database - sbyorcl
Role: PHYSICAL STANDBY Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
sbyorcl
Database Status:
SUCCESS
DGMGRL> convert database sbyorcl to snapshot standby;
Converting database "sbyorcl" to a Snapshot Standby database, please wait...
Database "sbyorcl" converted successfully
DGMGRL> show configuration;
Configuration - askm_config
Protection Mode: MaxPerformance
Databases:
orcl - Primary databasesbyorcl - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database sbyorcl;
Database - sbyorcl
Role: SNAPSHOT STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 3 minutes 43 seconds
Instance(s):
sbyorcl
Database Status:
SUCCESS
DGMGRL>
2. Verify Snapshot Standby Database :
[oracle@dgaskmsby01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 27 04:00:41 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
3. Do Some Testing In Snapshot Database :
For testing on snapshot standby database, I will do the following modifications on the snapshot standby database and verify the same changes again after converting snapshot database back to physical standby database.
4. Convert Snapshot Database Back To Physical Standby Database :
Please leave me your comments if you have any doubts.
Hope this helps
SRI
For testing on snapshot standby database, I will do the following modifications on the snapshot standby database and verify the same changes again after converting snapshot database back to physical standby database.
- Drop any existing user
- Create a new tablespace
- Resize datafile size
[oracle@dgaskmsby01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 27 04:01:51 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> drop user scott cascade;
User dropped.
SQL> select file_name,tablespace_name,bytes/1024/1024 from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/home/oracle/app/oracle/oradata/sbyorcl/users01.dbf
USERS 210
/home/oracle/app/oracle/oradata/sbyorcl/undotbs01.dbf
UNDOTBS1 143
/home/oracle/app/oracle/oradata/sbyorcl/sysaux01.dbf
SYSAUX 1105
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/home/oracle/app/oracle/oradata/sbyorcl/system01.dbf
SYSTEM 806
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf
EXAMPLE 82
/home/oracle/app/oracle/oradata/sbyorcl/APEX_1246426611663638.dbf
APEX_1246426611663638 2
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/home/oracle/app/oracle/oradata/sbyorcl/APEX_1265209995679366.dbf
APEX_1265209995679366 8
7 rows selected.
SQL> create tablespace test datafile '/home/oracle/app/oracle/oradata/sbyorcl/test01.dbf' size 10m;
Tablespace created.
SQL> !ls -lrt /home/oracle/app/oracle/oradata/sbyorcl/test01.dbf
-rw-rw---- 1 oracle oracle 10493952 Nov 27 04:06 /home/oracle/app/oracle/oradata/sbyorcl/test01.dbf
SQL> alter database datafile '/home/oracle/app/oracle/oradata/sbyorcl/users01.dbf' resize 500m;
Database altered.
SQL> select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='USERS';
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/home/oracle/app/oracle/oradata/sbyorcl/users01.dbf
USERS 500
SQL>
4. Convert Snapshot Database Back To Physical Standby Database :
DGMGRL> show configuration;5. Verify the Modifications Done In Step (3) :
Configuration - askm_config
Protection Mode: MaxPerformance
Databases:
orcl - Primary databasesbyorcl - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database sbyorcl;
Database - sbyorcl
Role: SNAPSHOT STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 3 minutes 43 seconds
Instance(s):
sbyorcl
Database Status:
SUCCESS
DGMGRL> convert database sbyorcl to physical standby;
Converting database "sbyorcl" to a Physical Standby database, please wait...
Operation requires shutdown of instance "sbyorcl" on database "sbyorcl"
Shutting down instance "sbyorcl"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "sbyorcl" on database "sbyorcl"
Starting instance "sbyorcl"...
ORACLE instance started.
Database mounted.
Continuing to convert database "sbyorcl" ...
Operation requires shutdown of instance "sbyorcl" on database "sbyorcl"
Shutting down instance "sbyorcl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "sbyorcl" on database "sbyorcl"
Starting instance "sbyorcl"...
ORACLE instance started.
Database mounted.
Database "sbyorcl" converted successfully
DGMGRL> show configuration;
Configuration - askm_config
Protection Mode: MaxPerformance
Databases:
orcl - Primary databasesbyorcl - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database sbyorcl;
Database - sbyorcl
Role: PHYSICAL STANDBY Intended State: APPLY-ON
Transport Lag: 27 seconds
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
sbyorcl
Database Status:
SUCCESS
DGMGRL>
SQL> connect /as sysdba
Connected.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select username from dba_users where username='SCOTT';
USERNAME
------------------------------
SCOTT
SQL> select tablespace_name from dba_tablespaces where tablespace_name='TEST';
no rows selected
SQL> !ls -lrt /home/oracle/app/oracle/oradata/sbyorcl/test01.dbfls: /home/oracle/app/oracle/oradata/sbyorcl/test01.dbf: No such file or directory
SQL> select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='USERS';
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/home/oracle/app/oracle/oradata/sbyorcl/users01.dbf
USERS 210
SQL>
Please leave me your comments if you have any doubts.
Hope this helps
SRI
Posts
