UNIX and Linux (Method 1)The previous methods works equally well in UNIX and Linux environments. For example, save the following
script in a file called "/u01/emp.sql".
CONNECT scott/tiger
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;
Next, create a shell script called "/u01/get_emp.ksh" containing the following lines.
#!/bin/ksh
sqlplus /nolog @/u01/emp.sql
The following command makes the file executable for the file owner.
chmod u+x /u01/get_emp.ksh
The resulting shell script can be run manually from the command line, or scheduled using CRON.For RMAN, place the following
RMAN commands in a file called "/u01/cmdfile.txt".
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT '/u01/backup/DB10G/%d_DB_%u_%s_%p';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}
EXIT;
Next create a batch file called "/u01/backup.ksh" containing the following lines.
#!/bin/ksh
rman target=/ @/u01/cmdfile.txt
This command can include a catalog= entry if a recovery catalog is used. Once again, resulting shell script must be made executable using the following command.
chmod u+x /u01/backup.ksh
UNIX and Linux (Method 2)
UNIX and Linux environments also allow the SQL*Plus and RMAN commands to be piped directly from the command line. For example, save the following commands in a file called "/u01/get_emp.ksh".
#!/bin/ksh
sqlplus /nolog << EOF
CONNECT scott/tiger
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;
EOF
chmod u+x /u01/get_emp.ksh
#!/bin/ksh
rman target=/ << EOF
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT '/u01/backup/DB10G/%d_DB_%u_%s_%p';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}
EXIT;
EOF
chmod u+x /u01/backup.ksh
miércoles, 27 de mayo de 2009
Oracle Shell Scripting Windows
WindowsTo run an SQL script using SQL*Plus, place the SQL along with any SQL*Plus commands in a file and save it on your operating system. For example, save the following script in a
file called "C:\emp.sql".
CONNECT scott/tiger
SPOOL C:\emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;
Next, create a batch file called "C:\get_emp.bat" containing the following command.
sqlplus /nolog @C:\emp.sql
The resulting batch file can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks) or the AT scheduler.The method is very similar when using Recovery Manager (RMAN). As an
example, place the following RMAN commands in a file called "C:\cmdfile.txt".
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT 'C:\oracle\backup\DB10G%d_DB_%u_%s_%p';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}
EXIT;
Next create a batch file called "C:\backup.bat" containing the following command.
rman target=/ @cmdfile.txt
file called "C:\emp.sql".
CONNECT scott/tiger
SPOOL C:\emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;
Next, create a batch file called "C:\get_emp.bat" containing the following command.
sqlplus /nolog @C:\emp.sql
The resulting batch file can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks) or the AT scheduler.The method is very similar when using Recovery Manager (RMAN). As an
example, place the following RMAN commands in a file called "C:\cmdfile.txt".
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT 'C:\oracle\backup\DB10G%d_DB_%u_%s_%p';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
}
EXIT;
Next create a batch file called "C:\backup.bat" containing the following command.
rman target=/ @cmdfile.txt
AutoNumber Table And Identity Functionality Trigger
Developers who are used to AutoNumber columns in MS Access or Identity columns in SQL Server often complain when they have to manually populate primary key columns using sequences.
CREATE TABLE departments (
ID NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);
ALTER TABLE departments ADD (
CONSTRAINT dept_pk PRIMARY KEY (ID));
CREATE SEQUENCE dept_seq;
Next we create a trigger to populate the ID column if it's not specified in the insert:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/
Finally we can test it using the automatic and manual population methods:
SQL> INSERT INTO departments (description)
2 VALUES ('Development');
1 row created.
SQL> SELECT * FROM departments;
ID DESCRIPTION
---------- --------------------------------------------------
1 Development
1 row selected.
SQL> INSERT INTO departments (id, description)
2 VALUES (dept_seq.NEXTVAL, 'Accounting');
1 row created.
SQL> SELECT * FROM departments;
ID DESCRIPTION
---------- --------------------------------------------------
1 Development
2 Accounting
2 rows selected.
SQL>
The trigger can be modified to give slightly different results. If the insert trigger needs to perform more functionality than this one task you may wish to do something like:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
SELECT NVL(:new.id, dept_seq.NEXTVAL)
INTO :new.id
FROM dual;
-- Do more processing here.
END;
/
To overwrite any values passed in you should do the following:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/
To error if a value is passed in you should do the following:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
IF :new.id IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified');
ELSE
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END IF;
END;
/
CREATE TABLE departments (
ID NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);
ALTER TABLE departments ADD (
CONSTRAINT dept_pk PRIMARY KEY (ID));
CREATE SEQUENCE dept_seq;
Next we create a trigger to populate the ID column if it's not specified in the insert:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/
Finally we can test it using the automatic and manual population methods:
SQL> INSERT INTO departments (description)
2 VALUES ('Development');
1 row created.
SQL> SELECT * FROM departments;
ID DESCRIPTION
---------- --------------------------------------------------
1 Development
1 row selected.
SQL> INSERT INTO departments (id, description)
2 VALUES (dept_seq.NEXTVAL, 'Accounting');
1 row created.
SQL> SELECT * FROM departments;
ID DESCRIPTION
---------- --------------------------------------------------
1 Development
2 Accounting
2 rows selected.
SQL>
The trigger can be modified to give slightly different results. If the insert trigger needs to perform more functionality than this one task you may wish to do something like:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
SELECT NVL(:new.id, dept_seq.NEXTVAL)
INTO :new.id
FROM dual;
-- Do more processing here.
END;
/
To overwrite any values passed in you should do the following:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/
To error if a value is passed in you should do the following:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
IF :new.id IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified');
ELSE
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END IF;
END;
/
Basic Enterprise Manager EMCA
USING EM CONFIGURATION ASSISTANT
CLUSTER
emca -reconfig dbcontrol -cluster -EM_NODE RACDBC1 -EM_SID_LIST RACDBC1 -EM_SID_LIST RACDBC2
emca -config dbcontrol db -repos create -cluster -EM_NODE RACDBC1 -EM_SID_LIST RACDBC1 -EM_SID_LIST RACDBC2
$ emca –config dbcontrol db –repos create
This example shows how to remove your database from Database Control:
$ emca –deconfig dbcontrol db –repos drop
This example instructs the EM configuration assistant to enable Database Control and configure backups for your database:
$ emca –config dbcontrol db –repos create –backup
$ emca –help
The web-based Enterprise Manager tool was introduced in Oracle 10g and has significant functionality improvements over previous versions. Even so, it seems there is not a day goes by without someone asking how to solve an Enterprise Manager configuration issue, so this article explains the first things you should look at. It's not meant to be an all encompassing guide. Just a first shot.
First, check the listener is running correctly.
lsnrctl statusIf you get something like the following, your listener is not running and you need to start it using the "lsnrctl start" command.
$ lsnrctl status
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 13-MAR-2009 13:32:49
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel5-11g.localdomain)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
$Next, check the Enterprise Manager service is running.
emctl start dbconsoleAssuming the console is already running, or fails to start, the next thing to do is to reconfigure Enterprise Manager. This is done using the Enterprise Manager Configuration Assistant (EMCA). For a first attempt, run the following command.
emca -config dbcontrol db -repos recreateIf it all goes well you would expect to see something like this.
$ emca -config dbcontrol db -repos recreate
STARTED EMCA at Mar 13, 2009 11:32:35 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Database Control is already configured for the database DB11G
You have chosen to configure Database Control for managing the database DB11G
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.1.0/db_1
Local hostname ................ oel5-11g.localdomain
Listener port number ................ 1521
Database SID ................ DB11G
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 11:33:19 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_11_32_35.log.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5540 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5520 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 1158 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 3938 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 13, 2009 11:33:22 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 13, 2009 11:35:12 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Mar 13, 2009 11:35:12 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 13, 2009 11:40:06 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 13, 2009 11:40:11 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 13, 2009 11:41:01 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 13, 2009 11:41:07 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Mar 13, 2009 11:41:07 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Mar 13, 2009 11:41:16 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Mar 13, 2009 11:41:16 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Mar 13, 2009 11:41:28 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 13, 2009 11:41:28 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 13, 2009 11:42:31 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 13, 2009 11:42:34 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>> The Database Control URL is https://oel5-11g.localdomain:1158/em <<<<<<
Mar 13, 2009 11:42:48 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_1/oel5-11g.localdomain_DB11G/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 11:42:48 AM
$If this fails, then de-configure and configure Enterprise Manager.
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos createThe deconfig will look something like this.
$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Mar 13, 2009 12:09:48 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 12:10:07 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_12_09_47.log.
Mar 13, 2009 12:10:08 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 13, 2009 12:10:31 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 13, 2009 12:11:56 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 12:12:02 PM
$The config will look something like this.
$ emca -config dbcontrol db -repos create
STARTED EMCA at Mar 13, 2009 12:37:27 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.1.0/db_1
Local hostname ................ oel5-11g.localdomain
Listener port number ................ 1521
Database SID ................ DB11G
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 12:37:54 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_12_37_27.log.
Mar 13, 2009 12:37:55 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 13, 2009 12:42:03 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 13, 2009 12:42:07 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 13, 2009 12:42:57 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 13, 2009 12:42:59 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Mar 13, 2009 12:42:59 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Mar 13, 2009 12:43:07 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Mar 13, 2009 12:43:07 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Mar 13, 2009 12:43:20 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 13, 2009 12:43:20 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 13, 2009 12:44:17 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 13, 2009 12:44:17 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>> The Database Control URL is https://oel5-11g.localdomain:5500/em <<<<<<
Mar 13, 2009 12:44:21 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_1/oel5-11g.localdomain_DB11G/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 12:44:21 PM
EJEMPLO
$ emca -repos create
$ emca -config dbcontrol db
STARTED EMCA at Fri May 14 10:43:22 MEST 2004
Enter the following information about the database
to be configured.
Listener port number: 1521
Database SID: AKI1
Service name: AKI1.WORLD
Email address for notification: martin dot zahn at akadia dot ch
Email gateway for notification: mailhost
Password for dbsnmp: xxxxxxx
Password for sysman: xxxxxxx
Password for sys: xxxxxxx
---------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME: /opt/oracle/product/10.1.0
Enterprise Manager ORACLE_HOME: /opt/oracle/product/10.1.0
Database host name ..........: akira
Listener port number .........: 1521
Database SID .................: AKI1
Service name .................: AKI1
Email address for notification: martin dot zahn at akadia dot ch
Email gateway for notification: mailhost
---------------------------------------------------------
Do you wish to continue? [yes/no]: yes
AM oracle.sysman.emcp.EMConfig updateReposVars
INFO: Updating file ../config/repository.variables ...
Now wait about 10 Minutes to complete!
M oracle.sysman.emcp.EMConfig createRepository
INFO: Creating repository ...
M oracle.sysman.emcp.EMConfig perform
INFO: Repository was created successfully
M oracle.sysman.emcp.util.PortQuery findUsedPorts
INFO: Searching services file for used port
AM oracle.sysman.emcp.EMConfig getProperties
...........
...........
INFO: Starting the DBConsole ...
AM oracle.sysman.emcp.EMConfig perform
INFO: DBConsole is started successfully
INFO: >>>>>>>>>>> The Enterprise Manager URL is http://akira:5500/em <<<<<<<<<<<
Enterprise Manager configuration is completed successfully
FINISHED EMCA at Fri May 14 10:55:25 MEST 2004
Try to connect to the database Control
http://akira:5500/em
If you look at the installed schemas, you can now find the SYSMAN schema, which is the database Control Repository.
Troubleshooting
If you have troubles to connect, check your local configuration which can be found in $ORACLE_HOME/_. For Example our DbConsole Setup Directory looks as follows:
$ pwd
/opt/oracle/product/10.1.0/akira_AKI1/sysman/config
$ ls -l
-rw-r--r-- b64InternetCertificate.txt
-rw-r--r-- emagentlogging.properties
-rw-r--r-- emd.properties
-rw-r--r-- emomsintg.xml
-rw-r--r-- emomslogging.properties
-rw-r--r-- emoms.properties
-rw-r--r-- OUIinventories.add
The most important file is emoms.properties, where you can find all the configuration parameters.
#Fri May 14 10:54:49 CEST 2004
oracle.sysman.emSDK.svlt.ConsoleServerName=
akira_Management_Service
oracle.sysman.eml.mntr.emdRepPwd=0b878f6184e8319d
emdrep.ping.pingCommand=/bin/ping
oracle.sysman.eml.mntr.emdRepPort=1521
oracle.sysman.eml.mntr.emdRepDBName=AKI1.WORLD
oracle.sysman.emSDK.svlt.ConsoleMode=standalone
oracle.sysman.emRep.dbConn.statementCacheSize=30
oracle.sysman.db.isqlplusUrl=
http\://akira\:5560/isqlplus/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerPort=5500
oracle.sysman.eml.mntr.emdRepRAC=FALSE
oracle.sysman.emSDK.emd.rt.useMonitoringCred=true
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
oracle.sysman.db.isqlplusWebDBAUrl=
http\://akira\:5560/isqlplus/dba/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerHost=akira
oracle.sysman.emSDK.svlt.ConsoleServerHTTPSPort=5500
oracle.sysman.eml.mntr.emdRepServer=akira
oracle.sysman.eml.mntr.emdRepSID=AKI1
oracle.sysman.emSDK.sec.ReuseLogonPassword=true
oracle.sysman.eml.mntr.emdRepConnectDescriptor=
(DESCRIPTION\=(ADDRESS_LIST\=
(ADDRESS\=(PROTOCOL\=TCP)(HOST\=akira)(PORT\
=1521)))(CONNECT_DATA\=(SERVICE_NAME\=AKI1)))
oracle.sysman.eml.mntr.emdRepUser=SYSMAN
oracle.sysman.db.adm.conn.statementCacheSize=2
oracle.sysman.db.perf.conn.statementCacheSize=30
Automatically start and stop the DB-Console
$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole
CLUSTER
emca -reconfig dbcontrol -cluster -EM_NODE RACDBC1 -EM_SID_LIST RACDBC1 -EM_SID_LIST RACDBC2
emca -config dbcontrol db -repos create -cluster -EM_NODE RACDBC1 -EM_SID_LIST RACDBC1 -EM_SID_LIST RACDBC2
$ emca –config dbcontrol db –repos create
This example shows how to remove your database from Database Control:
$ emca –deconfig dbcontrol db –repos drop
This example instructs the EM configuration assistant to enable Database Control and configure backups for your database:
$ emca –config dbcontrol db –repos create –backup
$ emca –help
The web-based Enterprise Manager tool was introduced in Oracle 10g and has significant functionality improvements over previous versions. Even so, it seems there is not a day goes by without someone asking how to solve an Enterprise Manager configuration issue, so this article explains the first things you should look at. It's not meant to be an all encompassing guide. Just a first shot.
First, check the listener is running correctly.
lsnrctl statusIf you get something like the following, your listener is not running and you need to start it using the "lsnrctl start" command.
$ lsnrctl status
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 13-MAR-2009 13:32:49
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel5-11g.localdomain)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
$Next, check the Enterprise Manager service is running.
emctl start dbconsoleAssuming the console is already running, or fails to start, the next thing to do is to reconfigure Enterprise Manager. This is done using the Enterprise Manager Configuration Assistant (EMCA). For a first attempt, run the following command.
emca -config dbcontrol db -repos recreateIf it all goes well you would expect to see something like this.
$ emca -config dbcontrol db -repos recreate
STARTED EMCA at Mar 13, 2009 11:32:35 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Database Control is already configured for the database DB11G
You have chosen to configure Database Control for managing the database DB11G
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.1.0/db_1
Local hostname ................ oel5-11g.localdomain
Listener port number ................ 1521
Database SID ................ DB11G
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 11:33:19 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_11_32_35.log.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5540 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5520 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 1158 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 3938 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 13, 2009 11:33:22 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 13, 2009 11:35:12 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Mar 13, 2009 11:35:12 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 13, 2009 11:40:06 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 13, 2009 11:40:11 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 13, 2009 11:41:01 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 13, 2009 11:41:07 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Mar 13, 2009 11:41:07 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Mar 13, 2009 11:41:16 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Mar 13, 2009 11:41:16 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Mar 13, 2009 11:41:28 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 13, 2009 11:41:28 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 13, 2009 11:42:31 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 13, 2009 11:42:34 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>> The Database Control URL is https://oel5-11g.localdomain:1158/em <<<<<<
Mar 13, 2009 11:42:48 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_1/oel5-11g.localdomain_DB11G/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 11:42:48 AM
$If this fails, then de-configure and configure Enterprise Manager.
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos createThe deconfig will look something like this.
$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Mar 13, 2009 12:09:48 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 12:10:07 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_12_09_47.log.
Mar 13, 2009 12:10:08 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 13, 2009 12:10:31 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 13, 2009 12:11:56 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 12:12:02 PM
$The config will look something like this.
$ emca -config dbcontrol db -repos create
STARTED EMCA at Mar 13, 2009 12:37:27 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.1.0/db_1
Local hostname ................ oel5-11g.localdomain
Listener port number ................ 1521
Database SID ................ DB11G
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 12:37:54 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_12_37_27.log.
Mar 13, 2009 12:37:55 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 13, 2009 12:42:03 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 13, 2009 12:42:07 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 13, 2009 12:42:57 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 13, 2009 12:42:59 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Mar 13, 2009 12:42:59 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Mar 13, 2009 12:43:07 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Mar 13, 2009 12:43:07 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Mar 13, 2009 12:43:20 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 13, 2009 12:43:20 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 13, 2009 12:44:17 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 13, 2009 12:44:17 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>> The Database Control URL is https://oel5-11g.localdomain:5500/em <<<<<<
Mar 13, 2009 12:44:21 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_1/oel5-11g.localdomain_DB11G/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 12:44:21 PM
EJEMPLO
$ emca -repos create
$ emca -config dbcontrol db
STARTED EMCA at Fri May 14 10:43:22 MEST 2004
Enter the following information about the database
to be configured.
Listener port number: 1521
Database SID: AKI1
Service name: AKI1.WORLD
Email address for notification: martin dot zahn at akadia dot ch
Email gateway for notification: mailhost
Password for dbsnmp: xxxxxxx
Password for sysman: xxxxxxx
Password for sys: xxxxxxx
---------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME: /opt/oracle/product/10.1.0
Enterprise Manager ORACLE_HOME: /opt/oracle/product/10.1.0
Database host name ..........: akira
Listener port number .........: 1521
Database SID .................: AKI1
Service name .................: AKI1
Email address for notification: martin dot zahn at akadia dot ch
Email gateway for notification: mailhost
---------------------------------------------------------
Do you wish to continue? [yes/no]: yes
AM oracle.sysman.emcp.EMConfig updateReposVars
INFO: Updating file ../config/repository.variables ...
Now wait about 10 Minutes to complete!
M oracle.sysman.emcp.EMConfig createRepository
INFO: Creating repository ...
M oracle.sysman.emcp.EMConfig perform
INFO: Repository was created successfully
M oracle.sysman.emcp.util.PortQuery findUsedPorts
INFO: Searching services file for used port
AM oracle.sysman.emcp.EMConfig getProperties
...........
...........
INFO: Starting the DBConsole ...
AM oracle.sysman.emcp.EMConfig perform
INFO: DBConsole is started successfully
INFO: >>>>>>>>>>> The Enterprise Manager URL is http://akira:5500/em <<<<<<<<<<<
Enterprise Manager configuration is completed successfully
FINISHED EMCA at Fri May 14 10:55:25 MEST 2004
Try to connect to the database Control
http://akira:5500/em
If you look at the installed schemas, you can now find the SYSMAN schema, which is the database Control Repository.
Troubleshooting
If you have troubles to connect, check your local configuration which can be found in $ORACLE_HOME/
$ pwd
/opt/oracle/product/10.1.0/akira_AKI1/sysman/config
$ ls -l
-rw-r--r-- b64InternetCertificate.txt
-rw-r--r-- emagentlogging.properties
-rw-r--r-- emd.properties
-rw-r--r-- emomsintg.xml
-rw-r--r-- emomslogging.properties
-rw-r--r-- emoms.properties
-rw-r--r-- OUIinventories.add
The most important file is emoms.properties, where you can find all the configuration parameters.
#Fri May 14 10:54:49 CEST 2004
oracle.sysman.emSDK.svlt.ConsoleServerName=
akira_Management_Service
oracle.sysman.eml.mntr.emdRepPwd=0b878f6184e8319d
emdrep.ping.pingCommand=/bin/ping
oracle.sysman.eml.mntr.emdRepPort=1521
oracle.sysman.eml.mntr.emdRepDBName=AKI1.WORLD
oracle.sysman.emSDK.svlt.ConsoleMode=standalone
oracle.sysman.emRep.dbConn.statementCacheSize=30
oracle.sysman.db.isqlplusUrl=
http\://akira\:5560/isqlplus/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerPort=5500
oracle.sysman.eml.mntr.emdRepRAC=FALSE
oracle.sysman.emSDK.emd.rt.useMonitoringCred=true
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
oracle.sysman.db.isqlplusWebDBAUrl=
http\://akira\:5560/isqlplus/dba/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerHost=akira
oracle.sysman.emSDK.svlt.ConsoleServerHTTPSPort=5500
oracle.sysman.eml.mntr.emdRepServer=akira
oracle.sysman.eml.mntr.emdRepSID=AKI1
oracle.sysman.emSDK.sec.ReuseLogonPassword=true
oracle.sysman.eml.mntr.emdRepConnectDescriptor=
(DESCRIPTION\=(ADDRESS_LIST\=
(ADDRESS\=(PROTOCOL\=TCP)(HOST\=akira)(PORT\
=1521)))(CONNECT_DATA\=(SERVICE_NAME\=AKI1)))
oracle.sysman.eml.mntr.emdRepUser=SYSMAN
oracle.sysman.db.adm.conn.statementCacheSize=2
oracle.sysman.db.perf.conn.statementCacheSize=30
Automatically start and stop the DB-Console
$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole
lunes, 11 de mayo de 2009
Ejemplo Mover Tabla No particionada a una Tabla Particionada DBSMS_REDEFINITION
DBMS_REDEFINITION package.
Create and populate a small lookup table.
CREATE TABLE lookup ( id NUMBER(10), description VARCHAR2(50));
ALTER TABLE lookup ADD ( CONSTRAINT lookup_pk PRIMARY KEY (id));
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;
CREATE TABLE big_table ( id NUMBER(10), created_date DATE, lookup_id NUMBER(10), data VARCHAR2(50));
DECLARE
l_lookup_id lookup.id%TYPE;
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO big_table (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' i);
END LOOP;
COMMIT;
END;
/
-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
CONSTRAINT big_table_pk PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i ON big_table(created_date);
CREATE INDEX bita_look_fk_i ON big_table(lookup_id);
ALTER TABLE big_table ADD (
CONSTRAINT bita_look_fk
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
-- Create partitioned table.
CREATE TABLE big_table2 (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
PARTITION big_table_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION big_table_2005 VALUES LESS THAN (MAXVALUE));
With this interim table in place we can start the online redefinition.EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'BIG_TABLE');
If no errors are reported it is safe to start the redefintion using the following command.
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
resynchronization of the interim table is initiated using the following command.
-- Optionally synchronize new table with interim data before index creation
BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE2', cascade => TRUE);
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
-- Remove original table which now has the name of the interim table.
DROP TABLE big_table2;
-- Rename all the constraints and indexes to match the original names.
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
SELECT partitioned
FROM user_tables
WHERE table_name = 'BIG_TABLE';
PAR
---
YES
1 row selected.
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'BIG_TABLE';
PARTITION_NAME
------------------------------
BIG_TABLE_2003
BIG_TABLE_2004
BIG_TABLE_2005
3 rows selected.
2 EJEMPLO
CREATE TABLE "DBO"."TEM_SEG_OFERTA" ("HGO_ID" NUMBER NOT NULL,
"OFT_ID" NUMBER(9) NOT NULL, "FSO_ID" NUMBER(2) NOT NULL,
"HGO_FCH_REGISTRO" DATE NOT NULL, "FUN_ID" NUMBER(4),
"HGO_DESCRIPCION" VARCHAR2(300 byte)
)
PARTITION BY RANGE (HGO_FCH_REGISTRO)
(PARTITION HIS_SEG_OFERTA_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2005 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2006 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2008 VALUES LESS THAN (TO_DATE('01/01/2009', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2009 VALUES LESS THAN (TO_DATE('01/01/2010', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2010 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2011 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2019 VALUES LESS THAN (TO_DATE('01/01/2020', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2020 VALUES LESS THAN (TO_DATE('01/01/2021', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2021 VALUES LESS THAN (MAXVALUE)
TABLESPACE "USERS")
/
SQL> EXEC Dbms_Redefinition.can_Redef_Table(USER, 'HIS_SEG_OFERTA');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_REDEFINITION.start_redef_table(uname => USER,orig_table => 'HIS_SEG_OFERTA',int_table => 'TEM_SEG_OFERTA');
PL/SQL procedure successfully completed.
SQL> EXEC dbms_redefinition.sync_interim_table(uname => USER,orig_table => 'HIS_SEG_OFERTA',int_table => 'TEM_SEG_OFERTA');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TEM_SEG_OFERTA', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_redefinition.finish_redef_table(uname => USER,orig_table => 'HIS_SEG_OFERTA',int_table => 'TEM_SEG_OFERTA');
PL/SQL procedure successfully completed.
SQL>
ORA-12091: cannot online redefine table with materialized views
Problem Description
While I attempt to reorganize a table online whenever I do with DBMS_REDEFINITION.can_redef_table or DBMS_REDEFINITION.START_REDEF_TABLE procedure on the table it fails with error ORA-12091 as below.
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
BEGIN dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR'); END;
*
ERROR at line 1:
ORA-12091: cannot online redefine table "CR_2"."OUT_CDR" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1
Cause of the Problem
If you unsuccessfully launched the DBMS_REDEFINITION.START_REDEF_TABLE procedure
once, the materialized view would be created on the interim table and still be remained there. So subsequent run of the DBMS_REDEFINITION.CAN_REDEF_TABLE BMS_REDEFINITION.START_REDEF_TABLE will return error until you remove the materalized view against the table.
Solution of the Problem
Solution 01:
Remove the snapshot log and materialized view created during the unsuccessful
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure.
You can define the snapshot log and materialized view log of current table by,
SQL> select log_table from user_snapshot_logs;
LOG_TABLE
------------------------------
MLOG$_IN_CDR
MLOG$_OUT_CDR
SQL> select master,log_table from user_mview_logs;MASTER LOG_TABLE------------------------------ ------------------------------IN_CDR MLOG$_IN_CDROUT_CDR MLOG$_OUT_CDR
Remove it by,
SQL> DROP MATERIALIZED VIEW LOG ON IN_CDR;
Materialized view log dropped.
SQL> DROP MATERIALIZED VIEW LOG ON OUT_CDR;
Materialized view log dropped.
You can use the keyword SNAPSHOT in place of MATERIALIZED VIEW. The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
Now execute your statement like,
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.
And it works.
Solution 02:
Run the dbms_redefinition.abort_redef_table procedure which will automatically do the clean up task. This procedure will remove the temporary objects that are created by the redefinition process such as materialized view logs.
SQL> exec dbms_redefinition.abort_redef_table('CR_2', 'IN_CDR', 'IN_CDR_');
PL/SQL procedure successfully completed.
Suscribirse a:
Entradas (Atom)
