create the database manually

最新推荐文章于 2022-07-31 08:56:35 发布

congji1972

最新推荐文章于 2022-07-31 08:56:35 发布

阅读量795

点赞数

文章标签：数据库

1. ORACLE_SID=ocm
   export ORACLE_SID
2. orapwd file=$ORACLE_HOME/dbs/orapwocm password=sys entries=10 force=y

3. cd $ORACLE_BASE
   mkdir -p ocm/bdump
   mkdir -p ocm/cdump
   mkdir -p ocm/udump
   mkdir -p ocm/adump
   go to oradata directories
   mkdir -p ocm/cfile
   mkdir -p /usr/rmanbak/ocm

3. generate the initocm.ora against the init.ora at $ORACLE_HOME/dbs/init.ora
   cat init.ora | grep -v ^# | grep -v ^$ > initocm.ora
   a) add the parameter "sga_max_size and sga_targe" and set to 300M
   b) change the shared_pool_size to 32M
   c) change the db_cache_size to 100M (do not change the parameter)
   *****you also can delete the parameters "db_block_buffers、shared_pool_size"
   d) change the db_name to ocm
   e) change the control_file (/oracle/app/oracle/oradata/ocm/cfile/ora_control1,
   /oracle/app/oracle/oradata/ocm/cfile/ora_control2,/oracle/app/oracle/oradata/ocm/cfile/control3)
   these directories is the same as the ocm exam.
===two ways to start the dataserver====
use the spfile
4. sqlplus / nolog
   SQL> conn /as sysdba;
   SQL> startup nomount
   SQL> create spfile='$ORACLE_HOME/dbs/spfileocm.ora' from pfile='$ORACLE_HOME/dbs/initocm.ora';
   SQL> shutdown
   SQL> startup nomount

5. in the online document "administrator guide-Part I Basic Database Administration-2 creating
                             an oracle database – Step 7: Issue the CREATE DATABASE Statement"
    Notice that the online document do not have this line "DATAFILE ‘/usr/oradata/ocm/tbs01.dbf’ SIZE 200M REUSE"

CREATE DATABASE ocm
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY system
LOGFILE GROUP 1 (‘/usr/oradata/ocm/redo01.log’) SIZE 50M,
GROUP 2 (‘/usr/oradata/ocm/redo02.log’) SIZE 50M,
GROUP 3 (‘/usr/oradata/ocm/redo03.log’) SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/usr/oradata/ocm/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/usr/oradata/ocm/sysaux01.dbf’ SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DATAFILE ‘/usr/oradata/ocm/tbs01.dbf’ SIZE 200M REUSE
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE ‘/usr/oradata/ocm/temp01.dbf’
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE ‘/usr/oradata/ocm/undotbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

===got error==
ERROR at line 1:
ORA-30014: operation only supported in Automatic Undo Management mode

SQL> alter system set undo_management=auto scope=spfile;
SQL> shutdown
SQL> startup nomount

===Notice==
If we failed to create the database, and want to create it again, we have to first delete the control files

6. create another tablespace

create tablespace ring datafile ‘/usr/oradata/ocm/ring_data.dbf’ size 100M
extent management local
segment space management auto;

create tablespace ringidx datafile ‘/usr/oradata/ocm/ring_idx.dbf’ size 100M
extent management local
segment space management auto;

7. run the following scripts to create data dictionary view

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

end. now you create the database manually successfully

=====================
use the pfile
4. SQL> startup nomount pfile='?/dbs/initocm.ora';

5. create spfile from pfile;
   shutdown abort;
   startup nomount;
6. alter system set db_create_file_dest='/usr/oradata/ocm/dfile' scope=spfile;
   alter system set db_create_online_log_dest_1=’/usr/oradata/ocm/lfile' scope=spfile;
   alter system set job_queue_processes =5 scope=spfile;
   alter system set background_dump_dest='$ORACLE_BASE/ocm/bdump' scope=spfile;
   alter system set core_dump_dest= '$ORACLE_BASE/ocm/cdump' scope=spfile;
   alter system set user_dump_dest='$ORACLE_BASE/ocm/udump' scope=spfile;
   alter system set audit_file_dest='$ORACLE_BASE/ocm/adump' scope=spfile;
   shutdown abort;
   startup nomount;
7.
CREATE DATABASE ocm
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY system
LOGFILE GROUP 1 ('/usr/oradata/ocm/lfile/redo01.log') SIZE 50M,
GROUP 2 ('/usr/oradata/ocm/lfile/redo02.log') SIZE 50M,
GROUP 3 ('/usr/oradata/ocm/lfile/redo03.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/usr/oradata/ocm/dfile/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/usr/oradata/ocm/dfile/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1 datafile '/usr/oradata/ocm/dfile/tbs_1.dbf' size 50M
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/usr/oradata/ocm/dfile/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/usr/oradata/ocm/dfile/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

===

8.
alter database datafile 1 autoextend on;
alter database datafile 2 autoextend on;
alter database datafile 3 autoextend on;
alter database datafile 4 autoextend on;
alter database tempfile 1 autoextend on;

9.

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

10. backup the database;

10.1 check which rman you are using
       which rman
       if you are using the rmam of the linux, please modify the .bash_profile
       all one line as following:
       export PATH=$ORACLE_HOME/bin:$PATH
10.2
set the database to archive mode
then backup the database

RMAN> run{
2> backup full database
3> format '/usr/rmanbak/ocm/full_bk1_%u%p%s.rmn'
4> include current controlfile;
5> backup archivelog all
6> format '/usr/rmanbak/ocm/arch_bk1_%u%p%s.rmn'
7> delete input;
8> }


end.