Thursday, January 16, 2014

Create database Oracle form command

 Step 1
export ORACLE_SID=orcl
export ORACLE_HOME=/path/to/oracle/home

 Step 2

 Step 3
 control_files = (/u02/app/oracle/oradata/orcl/control1.ctl,/u02/app/oracle/oradata/orcl/control2.ctl,/u02/app/oracle/oradata/orcl/control3.ctl)
undo_management = AUTO
undo_tablespace = UNDOTBS1
db_name =
db_block_size = 8192
sga_max_size = 11G
sga_target = 11G


$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwdorcl.ora password=abc123 entries=5

Chý ý: Cần khi  emctl start dbconsole



 sqlplus / as sysdba
startup nomount

create database solieu
logfile group 1 ('/u02/app/oracle/oradata/solieu/redo1.log') size 500M,
            group 2 ('/u02/app/oracle/oradata/solieu/redo2.log') size 500M,
            group 3 ('/u02/app/oracle/oradata/solieu/redo3.log') size 500M
character set AL32UTF8
national character set utf8
datafile '/u01/oradata/solieu/system.dbf' size 500M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile '/u01/oradata/solieu/sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited
undo tablespace undotbs1 datafile '/u01/oradata/solieu/undotbs1.dbf' size 100M
default temporary tablespace temp tempfile '/u01/oradata/solieu/temp01.dbf' size 100M;

sql> @?/rdbms/admin/catalog.sql
sql> @?/rdbms/admin/catproc.sql

sql> alter user sys identified by sys115;sql> alter user system identified by sys115;

Fix dbconsole: emca -config dbcontrol db fails with grid control error

$ orapwd FILE=orapw$ORACLE_SID ENTRIES=30

sql > drop user sysman cascade;
sql >  drop user MGMT_VIEW cascade;sql >  drop role MGMT_USER;sql >  drop public synonym MGMT_TARGET_BLACKOUTS;sql >  drop public synonym SETEMVIEWUSERCONTEXT;sql >  drop public synonym MGMT_AVAILABILITY;sql >  drop public synonym MGMT_CURRENT_AVAILABILITY;sql >  emca -deconfig dbcontrol db -repos dropsql >  emca -config dbcontrol db -repos create

First tried to start up dbconsole:
$ emctl start dbconsole
OC4J Configuration issue.
#ff0000;">not found.

\OK, missing file. EM wasn’t configured for this database, make sense. Let’s configure it
$ emca -config dbcontrol db -repos create
#ff0000;">SEVERE: Dbcontrol Repository already exists.  
Fix the error(s) and run EM Configuration Assistant again in standalone mode.
Looks like there is already a repository, so lets configure EM with out making the repository
# emca -config dbcontrol db
#ff0000;">WARNING: ORA-00942: table or view does not exist
SEVERE: The existing database you have specified has Grid Control repository. 
This operation is not supported.
Hmm, what table is missing? Put db in sql trace mode to see what SQL statement ran into the 942 error
sqlplus / as sysdba
alter database set sql_trace=true
rerun and grep for 942 in trace files in user_dump_dest
cd /home/oracle/oracle1123/diag/rdbms/soe60g/SOE60G/trace
grep 942 *
this show a dozen or so files.
Open each one up and searched for 942 and found two distinct SQL queries:

select count(*) from #ff0000;">sysman.mgmt_versions where status > 0 and component_mode
like 'SYSAUX' and component_name in ('DB','CORE')
The first table is from not having run $ORACLE_HOME/sqlplus/admin/pupbld.sql
Second is the problem emca was running into. Let’s look at sysman’s objects and see what’s there.

sqlplus sysman/sys
select object_name from user_objects;
-> no rows returned
OK, sysman looks empty , let’s drop it and recreated it

sqlplus / as sysdba
drop user sysman cascade;
drop user MGMT_VIEW cascade;
someone’s blog had suggested dropping the following but I didn’t
# drop role MGMT_USER;
# drop public synonym MGMT_TARGET_BLACKOUTS;
# drop public synonym SETEMVIEWUSERCONTEXT;
#                                                        host      port  SID
# $ORACLE_HOME/sysman/admin/emdrep/bin/RepManager antarctic 2483 sprod -action drop
# ./emca -repos create
rerun emca
$ emca -config dbcontrol db -repos create
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 20, 2013 7:27:51 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at 
Apr 20, 2013 7:27:51 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
#ff0000;">WARNING: ORA-01031: insufficient privileges
Apr 20, 2013 7:27:51 AM oracle.sysman.emcp.EMConfig perform
Database connection through listener failed. Fix the error and run EM Configuration Assistant again.
Some of the possible reasons may be:
1) Listener port 1521 provided is incorrect. Provide the correct port.
2) Listener is not up. Start the Listener.
3) Database service SOE60G is not registered with listener. Register the database service.
4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to
 virtual host. Unset ORACLE_HOSTNAME environment variable.
5) Listener is up on virtual host. Set environment variable ORACLE_HOSTNAME=.
6) /etc/hosts does not have correct entry for hostname.
Notice the ORA-01031. Hmm, I can connect as sqlplus / as sysdba but connecting as sysdba through the listener requires a password file.  Let’s see if password files are set up

sqlplus / as sysdba
show parameters remote_login_passwordfile
remote_login_passwordfile            string      EXCLUSIVE
that’s set correct, but is there a password file?

ls orapw$ORACLE_SID
ls: cannot access orapwdSOE60G: No such file or directory
not there, let’s create it

now emca works !
Now what URL to I use to access OEM for the second database?
It’s the same URL as the first database but with different port. We can find the ports for each database in the following file

cat $ORACLE_HOME/install/portlist.ini
Enterprise Manager Console HTTP Port (o1123) = 1158
Enterprise Manager Agent Port #ff0000;">(o1123) = 3938
Enterprise Manager Console HTTP Port (SOE60G) = 5500
Enterprise Manager Agent Port #ff0000;">(SOE60G) = 1830
Post script
After the above steps, one of the bizarre things I ran into was being able to  access OEM database page in Chrome but nothing else. Coming into OEM, I’m first asked for a login. I login as system, I see the database home page, then if I click on the performance tab, then I get prompted for the login again. I then  login again, and I’m back at the database home page. No matter what tab I click, I end up back at the database home page after giving login credentials.
In Firefox, though once I login, I can go to any of the other tabs and it works!

PS when running emca above I was prompted for a number of variables.  didn’t try this, but looks like all the arguments can be given in the command line

emca -repos create -silent -ORACLE_HOSTNAME perf234-target1 -SID SOE60G 
-SERVICE_NAME SOE60G -ORACLE_HOME /home/oracle/oracle1123/product
 Post Script II:
Never seen this one before and it was super annoying. I set up a VM in Virtual Box on my Mac with LINUX and Oracle and OEM. On the VM I could access OEM but not from my Mac. On my Mac I kept getting