Use Oracle 10 Gb to create a physical backup database on Windows/Unix servers (1)

Bkjia.com exclusive Article] Oracle 10 GB Data guard is a powerful tool to ensure high availability of enterprise data, data protection and disaster recovery, I have been using the grid console and SQL commands to work on the data guard/backup database for several years. My recent experience is LIMS, a laboratory information management system, half a year ago) using data guard to manually create a physical backup database, I need to maintain it every day. It works well and I want to share my experience with other DBAs here.

In this example, the database version is 10.2.0.3. The master and slave databases are located on different machines. The master database is called PRIM, And the backup database is STAN. I used the flash recovery zone and OMF.

1. Before you start:

1. Ensure that the operating system and platform architecture of the master and slave databases are the same.
2. Install only the Oracle database software on the backup server. Do not use the example database. Apply the patches to ensure that the versions of the primary and backup databases are consistent, and the Oracle home must be the same.
3. Test the creation of the backup database in the test environment before putting it into the production environment.

2. on the primary database:

1. Enable forced logging for the primary database:

SQL> ALTER DATABASE FORCE LOGGING;

2. If there is no password file, create it first:

2.1) check whether the password file exists:
SQL> select * from v $ pwfile_users;

2.2) if it does not exist, create one.

On the windwos platform:

$cd %ORACLE_HOME%\database $orapwd file=pwdPRIM.ora password=xxxxxxxx force=y

Note: replace xxxxxxxx with the password of the sys user)

On Unix platforms:

$Cd $ORACLE_HOME/dbs $Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y

Note: replace xxxxxxxx with the password of the sys user)

3. Configure backup redo log

3.1) The size of the backup redo log file should match the size of the current primary database online redo log file. Use the following command to find the size of the online redo log file:

SQL> select bytes from v$log; BYTES ---------- 52428800 52428800 52428800

3.2) use the following command to determine your current log file group:
SQL> select group #, member from v $ logfile;

3.3) create a backup redo log file group

My primary database originally had three redo log file groups. Use the following command to create three backup redo log file groups:

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M; SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M; SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

3.4) run the following command to check whether the log group is successfully created:
SQL> select * from v $ standby_log;

4. Enable archiving on the primary database

If your primary database does not work in the archive log mode, enable the archive log mode:

SQL>shutdown immediate; SQL>startup mount; SQL>alter database archivelog; SQL>alter database open; SQL>archive log list;

5. Set primary database initialization parameters

Create a text initialization parameter file pfile from the server parameter file spfile) and add the new master role parameters:

5.1) Create pfile from spfile

On Windows: SQL> create pfile = '\ database \ pfilePRIM. ora' from spfile; Note: Replace the content in ''with your Oracle home path) On Unix platforms: SQL> create pfile = '/dbs/pfilePRIM. ora' from spfile; Note: Replace the content in ''with your Oracle home path)

5.2) edit the pfilePRIM. ora file and add the Master/Slave role parameter. The path here is from the Windows system and the Unix System to modify the response)

db_name=PRIM db_unique_name=PRIM LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)' LOG_ARCHIVE_DEST_1= 'LOCATION=F:\Oracle\flash_recovery_area\PRIM\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIM' LOG_ARCHIVE_DEST_2= 'SERVICE=STAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=30 remote_login_passwordfile='EXCLUSIVE' FAL_SERVER=STAN FAL_CLIENT=PRIM STANDBY_FILE_MANAGEMENT=AUTO # Specify the location of the standby DB datafiles followed by the primary location; DB_FILE_NAME_CONVERT='E:\oracle\product\10.2.0\oradata\STAN\DATAFILE',' E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE' # Specify the location of the standby DB online redo log files followed by the primary location LOG_FLE_NAME_CONVERT=’ E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’ E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’ F:\Oracle\flash_recovery_area\STAN\ONLINELOG’,’ F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’

6. Create a spfile from pfile and then use the new spfile to restart the master database.

Data guard must use spfile to create spfile and restart the database:

On Windows:

SQL> shutdown immediate; SQL> startup nomount pfile = '\ database \ pfilePRIM. ora '; SQL> create spfile from pfile = '\ database \ pfilePRIM. ora '; -- Start the master database with the newly created spfile SQL> shutdown immediate; SQL> Startup;

Note: Replace the content in ''with your Oracle home path)
On Unix platforms:

SQL> shutdown immediate; SQL> startup nomount pfile = '/dbs/pfilePRIM. ora '; SQL> create spfile from pfile = '/dbs/pfilePRIM. ora '; -- Start the master database with the newly created spfile SQL> shutdown immediate; SQL> Startup;

Note: Replace the content in ''with your Oracle home path)