Creating 11g Databases - Manually

Maclean Liu(刘相兵

Creating11g Databases - Manually
PeterSmith, NBS Consulting Solutions



Introduction
Istarted playing with databases back in the old days, when all you hadwas a VT100 to connect to the server.  No graphical user interface,no colours, just you a text editor and SQLPlus.  Having learned howthings work from the command line it is very difficult to useOEM/Grid Control.  Yes, I'm a GUI bigot.  I do my work from theprompt and in SQLPlus.  It might be old-fashioned but you areguaranteed to understand what is happening if you have to type it.

Thisbrings us to the topic of the paper and presentation, "Creating11g Databases - Manually".  I feel it is very important for anyDBA to understand what is happening behind the scenes for any actionthey perform in the database.  What better place to start thencreating the database.

Dangerof Tools
I'vealready harped on using GUI's, but here's a story to help describethe dangers of using tools.  When I go shopping, I really don’twant to walk out with a tonne of coins in my pocket.  Say your billis $15.24, I hand over $20.25 so my change is $5.01 ($5 bill and apenny).  If I’d handed over a $20, then I would have gotten 2toonies, 3 quarters and a penny.  Yes, I am Canadian.  If the powerwas out and the cashier had to do it by hand, what type of look doyou think I would have gotten?  Take away the calculator/cashregister and how many people can remember their grade 5 arithmetic? Are you smarter than a 5thgrader?

I’mnot completely against GUI tools.  I personally use a tool calledGolden by Benthic.  It is not free, but it is cheap ($35US/seat). No, I don’t have any stock in the company.  SQLDeveloper closelyresembles Golden, so there’s a free tool which could provide SQLbased queries with tabular/XL result windows.  I like Golden as itprovides a window for entering SQL or PL/SQL and the results arereturned in a tabular format underneath.   If you use SQL Developer,you’ll say it’s got those features too, but Golden has beenaround for a lot longer.  Use what is best for you.

Howmany people take advantage of the 'Show SQL' link in OEM?  Probablynot too many, which is unfortunate as it is good to learn how Oracledevelopers write code in comparison to the users.

Figure1 - screenshot of OEM

Disks
Beforewe can create the database, we need somewhere to put the Oraclesoftware and the datafiles.  Start off with establishing somestandards.  This will make your life easier as the disk layout willbe the same on Dev/QA/Prod.

Onestep backwards first.  In the 'old days' DBAs would be worried aboutdata block location on the disk platters; whether a data block was atthe center or outside would be a performance concern.  We'd also wantto have the data, indexes, system datafile, temporary tablespace,redo logs and Oracle software all on different disks.

The'new reality' is we don't have control anymore.  The SAN/NAS presentsour database server with some disks, which are not necessarilyphysical disks.  Our /u1 disk could actually be 10 different diskswith only little pieces of each.  The SAN/NAS will worry about hotdata blocks, RAID and all those little things.  Not to mention theSAN/NAS is like another computer system with tonnes of RAM to performwrite caching.

Withthe 'new reality' in mind, here is how I would configure my filesystem
/u1        Oraclesoftware, with the trace files and alert logs
/u2        backups,mirrored copies of the redo logs and control files
/u3        datafiles

/u1and /u2 get backed up to tape, while /u3 is never backed up.  It'suseless to backup a datafile for an active database, just ask anybodywho knows what a 'fuzzy file' is.

CREATEDATABASE
Ratheressential to getting things started, CREATE DATABASE.  The commandhasn't changed too much over the years, but there are some niceadditions to highlight before we proceed.

Figure2 - CREATE DATABASE

• The        password for the SYS and SYSTEM accounts can be set at database        creation time.  Anything is better than leaving them at MANAGER and        CHANGE_ON_INSTALL.
• Default        tablespace can be established, so you can send everybody to TOOLS or        USERS right off the start
• BIGFILE        / SMALLFILE, unless you're dealing with video on demand, SMALLFILE        should be sufficient
• SYSAUX        tablespace is required, along with TEMPORARY, SYSTEM and UNDO as a        minimum

DIAG- Diagnostics
Thegood old ADMIN directory is now more.  Oracle has moved everythinginto a diagnostic area that takes into account the type of databaseyou have (e.g. rdbms or asm).  Rather than keep the ADMIN area foryour adump, pfile, logs, etc. move them into the DIAG area.

NOTE:alert log is now in the TRACE directory of the DIAG area.

Here'swhat I do for ensure that everything goes into 1 place.  I run itbefore creating the database to have all the directories in placethat I'll need.

set-x
echo"Enter the database name (lowercase) ?"
readDbname
exportDbname
echo$Dbname

mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/adump
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/alert
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/cdump
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/dpdump
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/hm
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/incident
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/incpkg
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/ir
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/lck
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/logs
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/metadata
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/misc
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/pfile
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/scripts
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/stage
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/sweep
mkdir-p /u1/app/oracle/diag/rdbms/$Dbname/$Dbname/trace

mkdir-p /u1/app/oracle/cfgtoollogs/dbca/$Dbname
mkdir-p /u2/oraback/$Dbname/rman
mkdir-p /u2/oraback/$Dbname/exports
mkdir-p /u2/oradata/$Dbname
mkdir-p /u3/oradata/$Dbname


Creatingthe Database - on File System
INIT.ORA
Here'sa sample INIT.ORA (will convert to SPFILE later) for database XYZ.

                                                                                                                                                                                                                                                                                

in                        DIAG area	*.audit_file_dest='/u1/app/oracle/diag/rdbms/xyz/xyz/adump'
database                        level auditing	*.audit_trail='db'
2                        control files	*.control_files='/u3/oradata/xyz/xyz_control01.ctl','/u2/oradata/xyz/xyz_control02.ctl'
	*.disk_asynch_io='TRUE'
I                        hate those extra funky named tables	*.recyclebin='OFF'
	*.db_block_size=8192
have                        images in BLOBs, so we have a 32k block size too	*.db_32k_cache_size=50M
	*.db_file_multiblock_read_count=128
	*.db_files=1500
force                        variables in similar code	*.cursor_sharing=FORCE
	*.open_cursors=300
	*.open_links=10
	*.db_domain=''
	*.db_name='xyz'
	*.db_recovery_file_dest='/u2/oraback/xyz/flashrecovery'
	*.db_recovery_file_dest_size=2147483648
	*.db_flashback_retention_target                        = 1440
DIAG	*.diagnostic_dest='/u1/app/oracle'
	*.java_pool_size=150M
only                        memory tuning available	*.memory_max_target=600M
only                        memory tuning available	*.memory_target=600M
	*.job_queue_processes=20
	*.sessions=2100
	*.processes=800
	*.log_archive_dest_1='location=/u2/oraback/xyz/flashrecovery/'
	*.log_archive_dest_state_1                        = enable
	*.log_archive_format=xyz_%t_%s_%r.arc
	*.archive_lag_target=600"
	*.remote_login_passwordfile='EXCLUSIVE'
should                        be '' to be more secure	*.os_authent_prefix                        = 'OPS$'
	*.undo_tablespace='UNDOTBS1'
	*.sec_case_sensitive_logon                        = FALSE
will                        be upgrading to 11.2, soon but not yet	*.compatible='11.1.0.0.0'
	*.workarea_size_policy='AUTO'
shouldn't                        be *, major security hole.	*.utl_file_dir=*

Creatingthe Database - on File System
We'vegot the init.ora located in $ORACLE_HOME/dbs.  DIAG and datafiledirectories have been created on the appropriate file systems.  We'reready to create the XYZ database.

CREATEDATABASE xyz
controlfilereuse
MAXINSTANCES8
MAXLOGHISTORY1
MAXLOGFILES16
MAXLOGMEMBERS3
MAXDATAFILES250
FORCELOGGING
DATAFILE'/u3/oradata/xyz/xyz_system_1.dbf' SIZE 300M REUSE
AUTOEXTENDON NEXT 10M MAXSIZE 10G
EXTENTMANAGEMENT LOCAL
SYSAUXDATAFILE '/u3/oradata/xyz/xyz_sysaux_1.dbf' SIZE 100M REUSE
AUTOEXTENDON NEXT 10M MAXSIZE 10G
SMALLFILE
DEFAULTTEMPORARY TABLESPACE TEMPORARY TEMPFILE'/u3/oradata/xyz/xyz_temp_1.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT10M MAXSIZE 10G
SMALLFILEUNDO TABLESPACE "UNDOTBS1" DATAFILE'/u3/oradata/xyz/xyz_undotbs1_1.dbf' SIZE 200M REUSE AUTOEXTEND ONNEXT 25M MAXSIZE 10G
CHARACTERSET WE8ISO8859P1
NATIONALCHARACTER SET AL16UTF16
LOGFILEGROUP 1 ('/u2/oradata/xyz/xyz_1_redo_1a.log',
                '/u3/oradata/xyz/xyz_1_redo_1b.log')SIZE 50M reuse,
        GROUP2 ('/u2/oradata/xyz/xyz_1_redo_2a.log',
                '/u3/oradata/xyz/xyz_1_redo_2b.log')SIZE 50M reuse,
        GROUP3 ('/u2/oradata/xyz/xyz_1_redo_3a.log',
                '/u3/oradata/xyz/xyz_1_redo_3b.log')SIZE 50M reuse
USERSYS
IDENTIFIEDBY "&&sysPassword“
USERSYSTEM
IDENTIFIEDBY "&&systemPassword";

Irun the above in a shell script, so the passwords for SYS and SYSTEMcome through as parameters and are not hardcoded.

• mirrored        the redo logs on /u2 and /u3
• SYSAUX        database
• SMALLFILE        being used
• Should        have the Unicode character set, but CSSCAN on an old system showed        issues.  Sticking with Western European 8bit until after conversion        to 11g and then can change to Unicode with minimal downtime in the        future.
• REUSE,        just in case the first run fails you don't have to delete all the        files before a rerun.
• FORCE        LOGGING in case LogMiner is needed for tracking user actions

Creatingthe database skeleton is just the first part.  There are tonnes ofOracle supplied scripts in $ORACLE_HOME/rdbms/admin which you can runto get your database configured.  Here's the minimum ones which Iwould run.

AsSYS
@$ORACLE_HOME/rdbms/admin/catalog.sql;
@$ORACLE_HOME/rdbms/admin/catblock.sql;
@$ORACLE_HOME/rdbms/admin/catproc.sql;
@$ORACLE_HOME/rdbms/admin/catoctk.sql;
@$ORACLE_HOME/rdbms/admin/owminst.plb;
@$ORACLE_HOME/rdbms/admin/dbmssml.sql;
@$ORACLE_HOME/javavm/install/initjvm.sql;
@$ORACLE_HOME/xdk/admin/initxml.sql;
@$ORACLE_HOME/xdk/admin/xmlja.sql;
@$ORACLE_HOME/rdbms/admin/catjava.sql;
@$ORACLE_HOME/rdbms/admin/catexf.sql;
@$ORACLE_HOME/rdbms/admin/catqm.sql&&sysPassword SYSAUX TEMPORARY;
@$ORACLE_HOME/rdbms/admin/catxdbj.sql;
@$ORACLE_HOME/rdbms/admin/catrul.sql;

AsSYSTEM
@$ORACLE_HOME/sqlplus/admin/pupbld.sql;
@$ORACLE_HOME/sqlplus/admin/help/hlpbld.sqlhelpus.sql;

AsSYS
createor replace directory data_pump_dir as '/u2/oraback/xyz/exports';
createspfile from pfile;

Catalog,Locks, PL/SQL, Java virtual machine, XML, Queues are all things whichyou should have in your database.  Maybe this is too many for yourenvironment or you need others as well.  These are things to figureout for yourself.

Pointthe default data pump database directory to the correct file systemdirectory which we created earlier on /u2.

Lastly,create the SPFILE based upon the INIT.ORA and we're done.  Now havean empty 11g database created manually with all the internalstructures we need to start with our application.

NOTE:  Did hit 1 bug in11.1.0.7 on HP-UX Itanium with CURSOR_SHARING=FORCE.  RunningDBMS_STATS fails!  So I alter my session to have CURSOR_SHARING=EXACTand then run.  The automated statistics gathering performed by thedatabase doesn't appear to have this problem.

Creatingthe Database - in ASM
ASM
AutomatedStorage Management (ASM) is where Oracle bypasses the OS and directlyaccesses the raw disk devices.  ASM has an in-memory database whichprovides the file system service to the databases.  It has the powerto manage hot files and RAID.

AnASM diskgroup is created and the device files for the physical diskare assigned to the diskgroup.  This information gets registered inthe ASM INIT.ORA.  ASMCMD provide a command line interface into theASM diskgroups and files (more in AppendixA). For those with SAN/NAS behind the scenes, trust the SAN/NAS to do thehot files and RAID and just use ASM as the go between for thedatafiles.

CREATEDISKGROUP DG_U3

EXTERNALREDUNDANCY
DISK
'/dev/rdisk/ora_asm1',
'/dev/rdisk/ora_asm2';


Thereare lots of enhancements to ASM in 11.2.  Including the addition ofnon-datafiles being stored within ASM and bringing in ClusteredServices.

I'msure there will be several sessions on ASM at the conference, soplease attend those sessions to get more details on using andmanaging ASM.  See AppendixB formy comments on backing up ASM instance.

Creatinga Database - in ASM
Asa standard, the /u3 was used to hold the datafiles of the database. Since we'll be using ASM to store the datafiles, we'll drop /u3 andreplace it with an ASM diskgroup called DG_U3.  
        DG=> diskgroup and U3 => /u3

Tomake creating and managing the database, we'll switch to OracleManaged Files (OMF).  We're relinquishing control of the tablespacenames, does it really matter anyway?

TheINIT.ORA now changes to refer to +DG_U3 rather than /u2 or /u3. Differences from the previous version are in bold.  The datafiles,redo logs, control files and archived redo logs will all be createdwithin the ASM diskgroup, +DG_U3.

*.audit_file_dest='/u1/app/oracle/diag/rdbms/xyz/xyz/adump'
*.audit_trail='db'
*.control_files='+DG_U3','+DG_U3'
*.db_create_file_dest='+DG_U3'
*.disk_asynch_io=TRUE
*.recyclebin='OFF'
*.db_block_size=8192
*.db_32k_cache_size=50M
*.db_file_multiblock_read_count=128
*.db_files=1500
*.cursor_sharing=FORCE
*.open_cursors=300
*.open_links=10
*.cluster_database=false
*.db_domain="'""'
*.db_name='xyz'
*.db_recovery_file_dest='+DG_U3'
*.db_recovery_file_dest_size=2147483648
*.db_flashback_retention_target= 1440
*.diagnostic_dest='/u1/app/oracle'
*.java_pool_size=150M
*.memory_max_target=600m
*.memory_target=600m
*.job_queue_processes=20
*.sessions=2100
*.processes=800
*.archive_lag_target=0
*.log_archive_dest_1='location=+DG_U3/'
*.log_archive_dest_state_1= enable
*.log_archive_format=xyz_%t_%s_%r.arc"
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
*.log_archive_trace=0
*.remote_login_passwordfile='EXCLUSIVE'
*.os_authent_prefix= 'OPS$'"
*.undo_tablespace='UNDOTBS1'
*.sec_case_sensitive_logon= FALSE
*.compatible='11.1.0.0.0'
*.workarea_size_policy='AUTO'
*.utl_file_dir=*

TheCREATE DATABASE command winds up being almost identical as well. Just switching the datafile creation information and letting OMFcreate the files.  Again, differences highlight in bold.

CREATEDATABASE xyz
controlfilereuse
MAXINSTANCES8
MAXLOGHISTORY1
MAXLOGFILES16
MAXLOGMEMBERS3
MAXDATAFILES250
FORCELOGGING
DATAFILE'+DG_U3'SIZE300M REUSE
AUTOEXTENDON NEXT 10M MAXSIZE 10G
EXTENTMANAGEMENT LOCAL
SYSAUXDATAFILE '+DG_U3'SIZE 100M REUSE
AUTOEXTENDON NEXT 10M MAXSIZE 10G
SMALLFILEDEFAULT TEMPORARY TABLESPACE TEMPORARY TEMPFILE '+DG_U3'SIZE 20M REUSE
AUTOEXTENDON NEXT 10M MAXSIZE 10G
SMALLFILEUNDO TABLESPACE "UNDOTBS1" DATAFILE '+DG_U3'SIZE 200M REUSE
AUTOEXTENDON NEXT 25M MAXSIZE 10G
CHARACTERSET WE8ISO8859P1
NATIONALCHARACTER SET AL16UTF16
LOGFILEGROUP 1 ('+DG_U3','+DG_U3')SIZE 50M reuse,
        GROUP2 ('+DG_U3','+DG_U3')SIZE 50M reuse,
        GROUP3 ('+DG_U3','+DG_U3')SIZE 50M reuse
USERSYS IDENTIFIED BY "&&sysPassword"
USERSYSTEM IDENTIFIED BY "&&sysPassword";

Aftercreating the database, run the same $ORACLE_HOME/rdbms/admin scripts.

createspfile from pfile;

• results        in the SPFILE being written into +DG_U3/xyz/parameterfile with a        link to +DG_U3/xyz/spfilexyz.ora

Nowgo into ASMCMD and take a look around to see what has been created. File names created by OMF contain the name of the tablespace followedby two sets of numbers which make a unique combination.
.oraenv
+ASM1
asmcmdls -l dg_u3/XYZ/datafile

Type     Redund  Striped  Time             Sys  Name
DATAFILE UNPROT  COARSE   FEB 11 13:00:00  Y    LOGMINER_DATA.332.696519829
DATAFILE UNPROT  COARSE   FEB 11 13:00:00  Y    SYSAUX.328.696519829
DATAFILE UNPROT  COARSE   FEB 11 13:00:00  Y    SYSTEM.326.696519829
DATAFILE UNPROT  COARSE   FEB 11 13:00:00  Y    TOOLS.325.696519829
DATAFILE UNPROT  COARSE   FEB 11 13:00:00  Y    UNDOTBS1.330.696519829
DATAFILE UNPROT  COARSE   FEB 11 13:00:00  Y    USERS.289.696519837

Creatinga RAC Database
We'vecreated databases on the file system and in ASM.  Now lets RAC enablea database.
Iwon't go into the CRS / Clustering configuration.  Others will coverthe topic far better than I can, so let's assume we've got a 2 nodecluster all set.  We'll take the XYZ database which is already in ASMand start from there.

ASM
RACenabling an ASM instance is quite easy since it is an in-memoryinstance.  Metalink Document #452758.1.

Createinit+ASM1.ora and init+ASM2.ora in the corresponding $ORACLE_HOME/dbsdirectories.
        

+ASM1

+ASM2

+ASM1.__oracle_base='/u1/app/oracle'#ORACLE_BASE                        set from environment                         *.asm_diskgroups='DG_U3'                         *.asm_diskstring='/dev/rdisk/ora_asm*'                         *.cluster_database=true                         *.diagnostic_dest='/u1/app/oracle'                         +ASM2.instance_number=2                         +ASM1.instance_number=1                         *.instance_type='asm'                         *.large_pool_size=12M

+ASM2.__oracle_base='/u1/app/oracle'#ORACLE_BASE                        set from environment                         *.asm_diskgroups='DG_U3'                         *.asm_diskstring='/dev/rdisk/ora_asm*'                         *.cluster_database=true                         *.diagnostic_dest='/u1/app/oracle'                         +ASM2.instance_number=2                         +ASM1.instance_number=1                         *.instance_type='asm'                         *.large_pool_size=12M

RAC-enablea Database
Icheated here.  It is easy to create a database within ASM, so startwith that and then RAC enable the database after you've got isworking correctly.  I followed the instructions in Metalink (nowOracle Support) Document #747457.1.  The document is copied inAppendixD.

Thereare some things which have now changed:

• DIAG        is now going to have the INSTANCE name and then the DATABASE name in        the directory structure.  Need to update INIT.ORA parameters        accordingly.
  • u1/app/oracle/diag/rdbms/xyz/xyz1
          
• Only        need to backup from 1 instance.         (don't laugh...)
• 2        init.ora files at $ORACLE_HOME/dbs and 1 SPFILE in ASM
• use        GV$ views rather than V$ to get info on all instances.

Yes,this section is very short; however, how to RAC enable a database isvery easy and the Oracle docs are very good at covering all thesteps.
Conclusion
Creatingdatabases is a basic task that all DBAs should be able to do. Knowing what is happening behind the DBCA screens is important so youunderstand how and where files have been placed.

Thispaper and the accompanying presentation cover the CREATE DATABASEcommand, how to create an 11g database on the file system and in ASM. RAC enabling a database is also covered with the details provided byOracle support.


PeterC. Smith
NBSConsulting Solutions
peter@nbsconsulting.ca



Acknowledgements
Screenshotstaken from Oracle websites
OracleSupport Documents c/o support.oracle.com
ASMMetadata backup script byJames Hardaker

http://jhdba.wordpress.com/2009/06/11/script-to-backup-asm-metadata/
Thanksto the Ottawa Oracle User Group
        wewere short a session for a meeting and I whipped up the presentationthe night before which in turn got selected by the IOUG forCOLLABORATE10.






AppendixA - ASMCMD - ASM Command Line Utility
NewASM Command Line Utility (ASMCMD) Commands and Options
ASMCMDhas the following four new commands: lsdsk,md_backup,md_restoreand remap.In addition, you can use new options for the lsand lsdgcommands. The following describes the four new ASM commands:

• cp—Enables        you to copy files between ASM disk groups on local instances and        remote instances.
• lsdsk—ASM        can list disk information with or without a running ASM instance.        This is a useful tool for system or storage administrators who want        to obtain lists of disks that an ASM instance uses.
• md_backup        and md_restore—These        commands enable you to re-create a pre-existing ASM disk group with        the same disk path, disk name, failure groups, attributes, templates        and alias directory structure. You can use md_backup        to back up the disk group environment and use md_restore to        re-create the disk group before loading from a database backup.
• remap—You        can remap and recover bad blocks on an ASM disk in normal or high        redundancy that have been reported by storage management tools such        as disk scrubbers. ASM reads from the good copy of an ASM mirror and        rewrites these blocks to an alternate location on disk.

AppendixB - Backing up ASM Instance
Howto backup the in-memory ASM instance.

• create        pfile='/u2/oraback/asm/initasm.ora' from spfile;
  • will                backup the link between diskgroup and device files
          
• asmcmd        md_backup
  • directory                and file information is stored as metadata, so md_backup will                perform a metadata backup.

Belowis code I got off the internet, c/o James Hardaker.  Reprinted herewithout permission but with credit where credit is due.
http://jhdba.wordpress.com/2009/06/11/script-to-backup-asm-metadata/

#!/bin/sh
#
#Date  Version Author          Comments
#09/06/09      1.1             Initial Version
#10/06/09      1.2             Added Error Handling
#
#
#Description
#Queries ASM for a list of disk groups and backs up the metadata ofeach to /app/oracle/backups
#Should be run as the oracle user

#Tunables
###

BACKUPDIR=/u2/oraback/asm

###

##########
#Start of functions
#
#asm_metadata - backup the asm metadata
asm_metadata()
{

exportORACLE_SID=`cat /etc/oratab | grep ^+ASM | awk '{FS=":"}{print $1} ' `
exportORACLE_HOME=`cat /etc/oratab | grep ^+ASM | awk '{FS=":"}{print $2} ' `

forDG in `asmcmd ls + | sed 's/\///'`
do
       echo "INFO:Backup of ${DG} diskgroup started at `date`"
       if [ -f${BACKUPDIR}/asm_metadata_${DG}.bkp ]
       then
               echo "INFO:Moving the file ${BACKUPDIR}/asm_metadata_${DG}.bkp to${BACKUPDIR}/asm_metadata_${DG}.old"
               cp${BACKUPDIR}/asm_metadata_${DG}.bkp${BACKUPDIR}/asm_metadata_${DG}.old 2>/dev/null
                       if [$? -ne 0 ]
                       then
                               echo"WARN: Unable to backup ${BACKUPDIR}/asm_metadata_${DG}.bkp,will overwrite it anyway"
                       fi
               rm -f${BACKUPDIR}/asm_metadata_${DG}.bkp 2>/dev/null
       fi
       echo "INFO:Backing up the ${DG} diskgroup to${BACKUPDIR}/asm_metadata_${DG}.bkp"
       rm${BACKUPDIR}/asm_metadata_${DG}.err 2>/dev/null
       ${ORACLE_HOME}/bin/asmcmdmd_backup -b ${BACKUPDIR}/asm_metadata_${DG}.bkp -g ${DG}2>${BACKUPDIR}/asm_metadata_${DG}.err
       if [ -s${BACKUPDIR}/asm_metadata_${DG}.err ]
       then
               echo ""
               echo "WARN:Unable to backup the ${DG} diskgroup to${BACKUPDIR}/asm_metadata_${DG}.bkp"
               cat${BACKUPDIR}/asm_metadata_${DG}.err
               echo ""
               ERR=98
       else
               echo "INFO:Backup of ${DG} diskgroup completed at `date`"
       fi
       echo ""
done
}

##########
#End of functions

###Main Program

echo"INFO: ASM Metadata backup started at `date`"
echo""
echo"INFO: This script is being run as user `/usr/bin/id -un`"
echo""

#create the backup directory if it does not exist
#

if[ ! -d ${BACKUPDIR} ]
then
       mkdir ${BACKUPDIR}2>/dev/null
       echo "INFO:Created the directory ${BACKUPDIR} as it did not exist"
       echo ""
               if [ $? -ne 0]
               then
                       echo"ERROR: Unable to create the directory ${BACKUPDIR}"
                       exit99
               fi
fi

#backup the asm metadata
#
ERR=0
asm_metadata

#Finish
#

if[ ${ERR} != 0 ]
then
       echo "ERROR: Anerror occurred backing up one or more disk groups, see above"
       exit 98
else
       echo "INFO: ASMMetadata backup completed at `date`"
fi

exit


AppendixC - RAC enable ASM database Doc ID#452758.1
               

How                        to Convert a Single-Instance ASM to Cluster ASM [ID 452758.1]
	Modified                        03-DEC-2009     Type                        HOWTO     Status                        PUBLISHED

Inthis Document
  Goal
  Solution
Appliesto: OracleServer - Enterprise Edition - Version: 10.2.0.1 to11.1.0.6
Information in this document applies to any platform.
GoalThisdocument describes the procedures for converting from Oracle ASM10g single-instance to Oracle Real Application Clusters (RAC).
SolutionToconvert a single-instance node using ASM to a RAC node using ASM:

1) Shut down the database instance, the ASM instance, and thelistener.

Remove the CSS auto-start line from the/etc/inittab file to enable you to shut down and uninstall CSS.

2)Follow the instructions in Chapter 2 and Chapter 3 (Oracle® DatabaseOracle Clusterware and Oracle Real Application Clusters InstallationGuide <platform>) to configure each node that you want to haveas a cluster member.
http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-ORA

3)If any cluster member node has had previous versions of Oracleclusterware (Cluster Ready Services or Oracle Clusterware) installed,then ensure that you remove the file ocr.loc on any node that hasbeen labeled previously as a cluster node. The file ocr.loc is in/etc/oracle.

4) Log in as the oracle user, and run DBCA insilent mode to deconfigure ASM. To run DBCA in silent mode, navigateto the directory $ORACLE_HOME/bin and use the following commandsyntax on the local node:

dbca -silent -deleteASM

Youcan de-install the single-instance ASM without losing data.

5)Install Oracle Clusterware on all nodes you intend to have as clustermembers, following the directions in Chapter 4 (Oracle® DatabaseOracle Clusterware and Oracle Real Application Clusters InstallationGuide <platform>).
http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-ORA
6)Install Cluster ASM using DBCA. At the Node Selection page, selectall the cluster member nodes for which you intend to use ASM tomanage storage. When the ASM Disk Groups page prompts you to selectdisk groups, select the existing disk groups you used with thesingle-instance ASM instance that you deleted in step 4.

7)Start up the single-instance Oracle Database.
AppendixD - RAC enable a database, Doc ID#747457.1

               

How                        to Convert 10g Single-Instance database to 10g RAC using Manual                        Conversion procedure [ID 747457.1]
	Modified                        16-JAN-2009     Type                        HOWTO     Status                        PUBLISHED

Inthis Document
  Goal
  Solution
  References
Appliesto: OracleServer - Enterprise Edition - Version: 10.1.0.2
Information inthis document applies to any platform.
This note applies to allUnix platforms.
GoalThisarticle provides a method to convert a Single Instance 10gdatabase to a RAC 10g database. It can be used for 10gR2 or11gR1, too.
SolutionFollowingare the steps:
1.on the first node

Makea full database backup of the single-instance database beforeyou change anything.

1) Install Oracle Clusterware on allnodes you intend to have as cluster members, following the directionsin Chapter 4 and Chapter 5 (Oracle® Database Oracle Clusterware andOracle Real Application Clusters Installation Guide <platform>).
http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-ORA
Eventuallypatch the clusterware software (the clusterware versionneed to be equal or higher than the rdbms version) as well as therdbms software

During the "Install Oracle Database 10gSoftware with Real Application Clusters", make sure that youselect a new Oracle home other than the one from which thesingle-instance database was running in case the single-instancedatabase is running on one of the systems. Upgrade the RAC RDBMSsoftware to the same version as the original single-instancedatabase.
2)Configure the cluster listener, i.e. configure the listener throughnetca from the new cluster RDBMS Home. Eventually stop the singleinstance listeners when they are running on one of the clusterednodes in case they are using the same listener ports. Ideally, usethe 'LISTENER' as name for that listener.

3) Restore thebackup of datafiles,redo logs,control file to a shared location onthe cluster
(If you are using ASM ,then please follow Note452758.1 How to Convert a Single-Instance ASM to Cluster ASM)

4)Take a backup of original single-instance pfile to e.g./tmp/initorcl.ora and Add the following entry in pfile, e.g. for atwo node RAC cluster
*.cluster_database= TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
<SID1>.undo_tablespace=undotbs (undo tablespace whichalready exists)
<SID1>.instance_name=<SID1>
<SID1>.instance_number=1
<SID1>.thread=1
<SID1>.local_listener=<LISTENERNAME>_<HOSTNAME1>
<SID2>.instance_name=<SID2>
<SID2>.instance_number=2
<SID2>.local_listener=<LISTENERNAME>_<HOSTNAME2>
<SID2>.thread=2
<SID2>.undo_tablespace=UNDOTBS2
<SID2>.cluster_database= TRUE
<SID2>.cluster_database_instances = 2

<SID1>is equal to "<db_name>1". <SID2> is equalto "<db_name>2", e.g. ORCL1, ORCL2.

5)change the location of control file in parameter file

localdrive to shared cluster file system location

iecontrol_files='<local path>/control01.ctl'

to iecontrol_files='<shared cluster file system path>/control01.ctl'

6) create spfile from pfile( spfile should be stored inshared device)

export ORACLE_SID=ORCL1
sqlplus "/ assysdba"
create spfile='<shared cluster file systempath>/spfileORCL.ora' from pfile='/tmp/initORCL.ora';
exit

7) Create the $ORACLE_HOME/dbs/init<sid>.ora e.g.initORCL1.ora file that contains the following entry

spfile='spfile_path_name'
spfile_path_nameis the complete path name of the SPFILE.

example :-

spfile='/cfs/spfile/spfileORCL1.ora'

8) create newpassword file for ORCL1 instance.

orapwd file=orapwORCL1password=oracle

9) start the database in mount stage

10)Rename the datafile,redo logs to new shared device

alterdatabase rename file '<full path of source file name>' to '<newshared location path';

11) Add second instance redo logs (ormore when multiple instances will be started)

alter database
add logfile thread 2
group 3 ('<sharedlocation/redo2_01_100.dbf') size 100M,
group 4 ('<sharedlocation/redo2_02_100.dbf') size 100M;

alter database enablepublic thread 2;


12) create the second (or more) instanceundo tablespace from existing instance

Path and file namewill different for your environment


CREATE UNDOTABLESPACE UNDOTBS2 DATAFILE
'/dev/RAC/undotbs_02_210.dbf' SIZE200M ;


13) Open your database (i.e. alter database open;)and run $ORACLE_HOME/rdbms/admin/catclust.sql to create clusterdatabase specific views within the existing instance


2.On the second node and other nodes

14)Set ORACLE_SID and ORACLE_HOME environment variables on the secondnode

15) Create the $ORACLE_HOME/dbs/init<sid>.ora e.g.initORCL2.ora file for the second node the same way as with point 7.
16)create new password file for second instance ORCL2 instance as inpoint 8

orapwd file=orapwORCL2 password=oracle

17)Start the second Instance

3.on one of the nodes
18) Afterconfiguring the listener,you have to add the database in cluster asbelow
srvctladd database -d <database name> -o <ORACLE_HOME path> -p<spfile location and name>

srvctl add instance -d<database name> -i <instance 1 name> -n <node 1 name >

srvctl add instance -d <database name> -i <instance2 name> -n <node 2 name >
19)in case ASM is used, add the rdbms instance / asm dependency, e.g.
srvctlmodify instance -d <db_unique_name> -i <SID1> -s <+ASM1>
ReferencesNOTE:208375.1- How To Convert A Single Instance Database To RAC In A Cluster FileSystem Configuration
NOTE:452758.1- How to Convert a Single-Instance ASM to Cluster ASM
NOTE:746767.1- How to convert RAC ASM to Single-Instance ASM





Creating 11g Databases - Manually.doc (213.5 KB, 下载次数: 2, 售价: 10 金钱)

2012-4-19 23:53 上传

点击文件名下载附件
售价: 10 金钱  [记录]

zhuqibs

现在刘老大都把邮件直接黏贴在网页上了