New database param files init.ora pfile etc

[stevenriz]

Hi all. I created a new database and am a little confused.

In the $ORACLE_HOME/dbs directory is an init.ora file. In the $ORACLE_HOME/admin/dbname/pfile directory is another init file called init.ora.418200616949 of all things. Changes to this file don't seem to affect anything. Can I put all the parameters I see here in the regular dbs/init.ora file? The funny thing is, in past databases I have installed, the init.ora file was named initdbname.ora. What gives here? I am confused which is easily done I know

 

[dbtoo2001]

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#CIACJGDI

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#i1013946

 

[stevenriz]

Thanks for all these links dbtoo .... I was able to figure out the spfile theories and ended up removing that and adding an init-dbname.ora file. I sort of like the spfile way but trying to set up this archiving and standby db stuff, seemed I could use the extra control of log locations etc...

btw have you set up standby db servers in the past?

 

[dbtoo2001]

Yes. But not at 9i and above.

Here is a clip from my server/database document:

1.1.1 Creating a Standby Database
Assumes you will be able to create the standby database with the exact same directory structure on a separate set of hardware! (You cannot run a standby database on two servers that use netapps storage for their database files. Both servers would attempt to use the exact same files. This is dependant on the NetApps volume and qtree mappings. If each server maps the same directory names to different volumes and qtrees, then this would be acceptable.)

Ensure that the two servers are setup to perform ssh.

1. Create hot backup of primary database or shutdown the database for a cold backup.
2. On primary database enter (sqlplus /nolog):
2.1. alter database create standby controlfile as 'standby_<SID>.ctl';
2.2. alter system archive log current;
3. Transfer all of the files (datafiles, control files, logfiles) to standby server
3.1. Place files into the same directory structure!!! Rename the standby control file to the correct control file names and to the appropriate directories.
3.1.1. Make sure you have all the files in the correct locations, including control files. These files MUST be in the exact same locations as the Primary database's files. There can be only two modifications made, one for ALL datafiles and One for ALL logfiles.
Example:
DB_FILE_STANDBY_NAME_CONVERT=" /share/ora_db", "/share/ora_db1" will rename every datafile that starts with /share/ora_db to /share/ora_db1 INCLUDING any datafile that may be named /share/ora_dbbk to /share/ora_db1bk !!! The database will open in standby mode, when you shut it down after activation, it will fail on startup because the naming convention is not correct.

Note: there was some strange behavior with control file names for the 2nd and 3rd control file directories being exchanged.

3.1.2. Update init<SID>.ora file
3.1.2.1. DB_FILE_STANDBY_NAME_CONVERT=" ", " " # only if you can change ALL of the datafile locations.
3.1.2.2. LOG_FILE_STANDBY_NAME_CONVERT=" ", " " # only if you can change ALL of the log file locations.
4. On the standby server:
4.1. Export the sid of the standby database
4.2. Ensure that ORACLE_HOME is pointing to the correct oracle binaries.
4.3. Copy all of the log files into the log file directory.
4.4. Start the standby database in NOMOUNT mode:
4.4.1. startup nomount
4.4.2. alter database mount standby database exclusive;
4.4.3. set autorecovery on
4.4.4. recover standby database

5. To keep the servers in sync
5.1. On the primary, force a logfile archive
5.1.1. alter system archive log current;
5.2. Transfer the archived log file to the standby (7.3.4 or manual methods, 8i and above this is automatic if network, listener, etc., are available.)

5.3. To Recover Logfile on the standby:
5.3.1. set autorecovery on
5.3.2. recover standby database

1.1.2 Automated Process
The automated process performs the following:
1. A cron job is activated from the standby server (run_archive server sid)
2. It runs a script on the primary
3. The script will:
3.1. Force an archive of the current logfile (force_archive sid)
3.2. Copy the logfile to a shared NetApp directory based on a comparison timestamp to the primary's file /share/home/oracle/bin/last_copied_{SID} or directly to the archive log directory, via tnsnames alias.
Note: last_copied_{SID} and last_update_{SID} are text files containing arbitrary or no data. The timestamp that the file was last modified (touch) affects the comparisons. The contents of the file have no relevance. For Oracle 8.1.7, the archive log file will automatically be copied IF the standby database is up and the listener is available, and the init<SID).ora file is setup appropriately.
3.3. It then runs update_logs {SID} which applies the logs to the standby database.
3.4. It will then delete old logs, keeping the log file directory clear in case you have to activate the standby database.
1.1.3 Activating The Standby Database
Perform these steps for all standby databases on the servers.
1. TURN OFF CRON JOBS!!! Do this first or the logs for the newly activated database may be deleted by the cron jobs.
1.1. Turn off log copy from previous primary
1.2. Turn on exports (as appropriate.)
1.3. Turn on hot/cold backups (as appropriate)
2. Edit the initXXXXXprd.ora file and uncomment the standby database entry for archive log destination 2. Be aware that this may produce an error message until the hardware is restored and the ‘new’ standby database is created. You may want to leave this turned off, until a log gap sequence can be performed and the automated methods restored.
#log_archive_dest_state_2 = 'ENABLE'
#log_archive_dest_2 = 'SERVICE=standby_XXXXXprd.gddsi.com OPTIONAL REOPEN=60'
3. Copy final logs from primary, If you can.
4. Recover logs and apply them, If you could retrieve them.
5. Ensure database is in exclusive mode.
5.1. alter database mount standby database exclusive;
6. Enter the following:
6.1. alter database activate standby database;
6.2. shutdown
6.3. startup
7. Have ITI plumb the XXXXXSSG associated IP to the server with the new Primary Database or perform a DNS push with the new alias (i.e. in the case of XXXXXSSG - XXXXXgd03 would be changed to XXXXXgd04 or vice versa.) Ensure tns listener is up for the new primary database and the port is the same as the previous primary database. (DNS push may take up to an hour to propagate the changes out to the client machines. Rebooting the clients, may shorten the timeframe required.) (If the VIP is not un-plumbed from the previous primary, the network may attempt to route requests to the old server, or cached copies of a DNS alias may route requests temporarily to the old primary server.)
8. Test access to the database from a LAN attached client.
9. Update the oratab and crontab files.
10. This database and server are now the Primary, AND SHALL REMAIN the primary until the next 'failover'.
11. Perform a Hot or Cold Backup. A Hot Backup will probably be the most beneficial at this point, so that production can be up, but run degraded (slow) until the hot backup completes.
12. When the old hardware is back online do the following (on the old hardware) -
12.1. Ensure that the server does not attempt to become XXXXXSSG by un-plumbing the psuedo-IP, if applicable.
12.2. Update Crontab turning on/off appropriate jobs.
12.3. Update the init.ora file and comment out the references to archive log destination 2.
12.4. Create a new Standby Database
12.5. Ensure the listener is started
12.6. Perform a log gap sequence and recover to current log. Depending on the length of time, some of the archive files may have already been tar’d and removed. The tar’d archives are in the /archive/log directories.

 

[dbtoo2001]

For 9i and above - learn and use dataguard broker.

http://otn.oracle.com/pls/db92/db92.show_toc?partno=a96629&remark=drilldown&word=dataguard+

 

[stevenriz]

this is one document I haven't found yet. This looks promising along with yours, I appreciate it. It looks like I should go back to the spfile config deal and let oracle control everything or it could get a little tricky.

What I was going to try and do is setup a db in archive log mode and work on manually transferring the logs and the control file to the secondary server and manually processing those logs but don't know if this will work for me. Couldn't I copy the control file from the primary to the secondary, process the control file, then do a restore as long as the rman backups and archive files are in the exact same place?

 

[stevenriz]

As it turns out we are licensed for standard edition thus cannot legally use Data Guard although it is a very interesting package. So I am going to work on moving archive logs the old fashion way. Correct me if I'm wrong....

- Backup Primary DB
- create Control file for the secondary or Standby DB
- create the PFILE for the Standby DB
- copy the backup DBF files from the Primary to the Secondary DB servers
- modify the new Standby DB SPFILE
- automatically send (SFTP) the archive logs from the Primary to the Standby DB servers
- process the archive logs periodically on the Secondary DB

I don't know some of the specifics on how to do this but is my theory ok?