Oracle: verschil tussen versies

Uit Documents
Ga naar: navigatie, zoeken
(→‎Standby database)
 
(15 tussenliggende versies door dezelfde gebruiker niet weergegeven)
Regel 1: Regel 1:
−
== Oracle ==
+
== Startup / Shutdown ==
  
−
=== RMAN ===
+
Login to SQL prompt:
 +
<syntaxhighlight lang="bash">sqlplus / as sysdba</syntaxhighlight>
  
−
==== Segmentation Fault while restoring ====
+
Startup normally
 +
<syntaxhighlight lang="sql">SQL> startup;</syntaxhighlight>
  
−
<ref>http://www.symantec.com/business/support/index?page=content&id=TECH57313</ref>
+
Startup without actually mounting
 +
<syntaxhighlight lang="sql">SQL> startup nomount;</syntaxhighlight>
  
−
== Referenties ==
+
Use additional initialization file:
−
{{Reflist|3}}
+
<syntaxhighlight lang="sql">SQL> startup nomount pfile='/path/to/init.ora';</syntaxhighlight>
 +
 
 +
Shutdown:
 +
<syntaxhighlight lang="sql">SQL> shutdown;</syntaxhighlight>
 +
 
 +
Shutdown and terminate existing connections
 +
<syntaxhighlight lang="sql">SQL> shutdown immediate;</syntaxhighlight>
 +
 
 +
Shutdown and abort everything (last resort):
 +
<syntaxhighlight lang="sql">SQL> shutdown abort;</syntaxhighlight>
 +
 
 +
== RMAN ==
 +
 
 +
=== Segmentation Fault while running sbttest ===
 +
 
 +
Netbackup server was unable to resolve clients ip address. Added an entry to the /etc/hosts file and the issue was solved.<ref>http://www.symantec.com/business/support/index?page=content&id=TECH57313</ref>
 +
 
 +
=== Make disk backup ===
 +
<syntaxhighlight lang="sql">
 +
rman target /(@sid) NOCATALOG
 +
RUN {
 +
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '<location>/bck_%s_%p_%t';
 +
ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '<location>/bck_%s_%p_%t';
 +
BACKUP DATABASE PLUS ARCHIVELOG;
 +
RELEASE CHANNEL d2;
 +
RELEASE CHANNEL d1;
 +
}
 +
 
 +
RUN {
 +
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '<location>/cntrl_%s_%p_%t';
 +
BACKUP CURRENT CONTROLFILE (FOR STANDBY);
 +
RELEASE CHANNEL d1;
 +
}
 +
</syntaxhighlight>
 +
 
 +
Make backup of control file from SQL prompt
 +
 
 +
<syntaxhighlight lang="sql">
 +
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '<location>';
 +
</syntaxhighlight>
 +
 
 +
=== Restore disk backup ===
 +
 
 +
<syntaxhighlight lang="sql">
 +
SQL> startup nomount;
 +
RMAN> RESTORE CONTROLFILE FROM '<location>';
 +
SQL> alter database mount;
 +
RMAN> RESTORE DATABASE;
 +
</syntaxhighlight>
 +
 
 +
=== Make backup using Netbackup ===
 +
 
 +
<syntaxhighlight lang="sql">
 +
rman target /(@sid) NOCATALOG
 +
RUN {
 +
ALLOCATE CHANNEL t1 DEVICE TYPE 'SBT_TAPE' parms="ENV=(NB_ORA_POLICY=<POLICY>,NB_ORA_SERV=<BACKUP_SERVER>, NB_ORA_CLIENT=<CLIENT_NAME>)";
 +
BACKUP FORMAT 'bk_%s_%p_%t' DATABASE;
 +
RELEASE CHANNEL t1;
 +
}
 +
 
 +
RUN {
 +
ALLOCATE CHANNEL t1 DEVICE TYPE 'SBT_TAPE' parms="ENV=(NB_ORA_POLICY=<POLICY>,NB_ORA_SERV=<BACKUP_SERVER>, NB_ORA_CLIENT=<CLIENT_NAME>)";
 +
BACKUP FORMAT 'al_%s_%p_%t' ARCHIVELOG ALL;
 +
RELEASE CHANNEL t1;
 +
}
 +
 
 +
RUN {
 +
ALLOCATE CHANNEL t1 DEVICE TYPE 'SBT_TAPE' parms="ENV=(NB_ORA_POLICY=<POLICY>,NB_ORA_SERV=<BACKUP_SERVER>, NB_ORA_CLIENT=<CLIENT_NAME>)";
 +
BACKUP FORMAT 'cntrl_%s_%p_%t' CURRENT CONTROLFILE (FOR STANDBY);
 +
RELEASE CHANNEL t1;
 +
}
 +
</syntaxhighlight>
 +
 
 +
=== Manually add backup files to catalog ===
 +
<syntaxhighlight lang="sql">
 +
CATALOG BACKUPPIECE '/location/backupfile1', '/location/backupfile2';
 +
</syntaxhighlight>
 +
 
 +
== Standby database ==
 +
=== Start standby database ===
 +
<syntaxhighlight lang="sql">
 +
SQL> startup nomount;
 +
SQL> alter database mount standby database;
 +
SQL> alter database recover managed standby database disconnect from session;
 +
</syntaxhighlight>
 +
 
 +
=== Stop standby database ===
 +
<syntaxhighlight lang="sql">
 +
SQL> alter database recover managed standby database cancel;
 +
SQL> shutdown immediate;
 +
</syntaxhighlight>
 +
 
 +
=== Determine if database is standby ===
 +
<syntaxhighlight lang="sql">
 +
select CONTROLFILE_TYPE from v$database;
 +
</syntaxhighlight>
 +
 
 +
=== Manually register a logfile ===
 +
It is sometimes necessary to manually add a logfile if one misses the transport or so.
 +
<syntaxhighlight lang="sql">
 +
alter database register logfile '/full_path_to/logfile';
 +
</syntaxhighlight>
 +
 
 +
== References ==
 +
<references />
 +
 
 +
== Links ==
 +
=== Backup ===
 +
* [http://download.oracle.com/docs/cd/B12037_01/server.101/b10735/maint.htm#BRBSC174 Oracle - Recovery Manager Maintenance Tasks]
 +
* [http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/recov004.htm Oracle - RMAN Restore]
 +
* [http://download.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmstand.htm Oracle - Creating a Standby Database with Recovery Manager]
 +
* [http://www.shutdownabort.com/dbaqueries/Backup_DataGuard.php Shutdown-Abort - Backup - DataGuard]
 +
* [http://psoug.org/reference/control_file.html psoug.org - Oracle Control Files]
 +
=== Standby database ===
 +
* [http://download.oracle.com/docs/cd/B10501_01/server.920/a96653/manage_ps.htm Oracle - Managing a Physical Standby Database]

Huidige versie van 12 aug 2011 om 08:18

Startup / Shutdown

Login to SQL prompt: 

sqlplus / as sysdba

Startup normally 

SQL> startup;

Startup without actually mounting 

SQL> startup nomount;

Use additional initialization file: 

SQL> startup nomount pfile='/path/to/init.ora';

Shutdown: 

SQL> shutdown;

Shutdown and terminate existing connections 

SQL> shutdown immediate;

Shutdown and abort everything (last resort): 

SQL> shutdown abort;

RMAN

Segmentation Fault while running sbttest

Netbackup server was unable to resolve clients ip address. Added an entry to the /etc/hosts file and the issue was solved.[1]

Make disk backup

rman target /(@sid) NOCATALOG
RUN {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '<location>/bck_%s_%p_%t';
ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '<location>/bck_%s_%p_%t';
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL d2;
RELEASE CHANNEL d1;
}

RUN {
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '<location>/cntrl_%s_%p_%t';
BACKUP CURRENT CONTROLFILE (FOR STANDBY);
RELEASE CHANNEL d1;
}

Make backup of control file from SQL prompt 

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '<location>';

Restore disk backup

SQL> startup nomount;
RMAN> RESTORE CONTROLFILE FROM '<location>';
SQL> alter database mount;
RMAN> RESTORE DATABASE;

Make backup using Netbackup

rman target /(@sid) NOCATALOG
RUN {
ALLOCATE CHANNEL t1 DEVICE TYPE 'SBT_TAPE' parms="ENV=(NB_ORA_POLICY=<POLICY>,NB_ORA_SERV=<BACKUP_SERVER>, NB_ORA_CLIENT=<CLIENT_NAME>)";
BACKUP FORMAT 'bk_%s_%p_%t' DATABASE;
RELEASE CHANNEL t1;
}

RUN {
ALLOCATE CHANNEL t1 DEVICE TYPE 'SBT_TAPE' parms="ENV=(NB_ORA_POLICY=<POLICY>,NB_ORA_SERV=<BACKUP_SERVER>, NB_ORA_CLIENT=<CLIENT_NAME>)";
BACKUP FORMAT 'al_%s_%p_%t' ARCHIVELOG ALL;
RELEASE CHANNEL t1;
}

RUN {
ALLOCATE CHANNEL t1 DEVICE TYPE 'SBT_TAPE' parms="ENV=(NB_ORA_POLICY=<POLICY>,NB_ORA_SERV=<BACKUP_SERVER>, NB_ORA_CLIENT=<CLIENT_NAME>)";
BACKUP FORMAT 'cntrl_%s_%p_%t' CURRENT CONTROLFILE (FOR STANDBY);
RELEASE CHANNEL t1;
}

Manually add backup files to catalog

CATALOG BACKUPPIECE '/location/backupfile1', '/location/backupfile2';

Standby database

Start standby database

SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;

Stop standby database

SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate;

Determine if database is standby

select CONTROLFILE_TYPE from v$database;

Manually register a logfile

It is sometimes necessary to manually add a logfile if one misses the transport or so.

alter database register logfile '/full_path_to/logfile';

References

  1. ↑ http://www.symantec.com/business/support/index?page=content&id=TECH57313

Links

Backup

Standby database

Overgenomen van "http://www.lambiek.eu/wiki/index.php?title=Oracle&oldid=127"