Not logged in : Login

About: VirtTipsAndTricksGuideTablesAndIndexesRecover     Goto   Sponge   NotDistinct   Permalink

An Entity of Type : atom:Entry, within Data Space : ods.openlinksw.com associated with source document(s)

AttributesValues
type
Date Created
Date Modified
label
  • VirtTipsAndTricksGuideTablesAndIndexesRecover
maker
Title
  • VirtTipsAndTricksGuideTablesAndIndexesRecover
isDescribedUsing
has creator
content
  • %META:TOPICPARENT{name="VirtTipsAndTricksGuide"}% ---+ Backing Up Individual Table(s) or Index(es) and Restoring Them to a Fresh DB ---++ What? Selectively dumping a database table or its schema, for reload into a separate instance, or for recovery in the original instance. <i><b>Note</b>: This is only possible with Virtuoso 6.0 and later.</i> ---++ Why? There are many reasons, ranging from system corruption to migration, why you might want to reconstitute data across database management system instances. ---++ How to Dump and Recover one or more SQL Table(s) 1 Create a function for dumping the key: <verbatim> SQL> CREATE PROCEDURE bkp_key ( IN f ANY , IN tb_name VARCHAR , IN key_name VARCHAR ) { backup_prepare ( f ) ; backup_index ( tb_name , key_name ); backup_flush (); backup_close (); }; </verbatim> <i><b>Note:</b> You can dump multiple tables by adding more calls to the <code>backup_index</code> function. 1 On the source DB, dump the <code>T1</code> table's primary key to a file named "<code>mylog.txn</code>", by executing: <verbatim> SQL> bkp_key ( 'mylog.txn' , 'DB.DBA.T1' , 'T1' ); </verbatim> 1 On the source DB host, stop the Virtuoso server, and dump only the schema tables (no data) into the <code>trx</code> file, by executing: <verbatim> % virtuoso +crash-dump +foreground +mode l ## ( lower case L ) </verbatim> 1 On the target DB host, make sure there is no existing <code>.db</code> file, and place the <code>.trx</code> file produced by previous step. Create a new DB with the schema from the source DB, by executing: <verbatim> % virtuoso +restore-crash-dump +foreground ; </verbatim> 1 Insert the PK data from the source DB dump into the new table in the new DB, by starting the new DB as normal, and executing: <verbatim> SQL> replay ('mylog.txn') ; </verbatim> <i><b>Note:</b> that this has only restored the Primary Key index. If there are multiple indexes on the table(s), the other indexes must be dropped and re-created, as they will be empty at this point.</i> ---++Related * [[VirtTipsAndTricksGuide][Virtuoso Tips and Tricks Collection]] * [[http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#backup][Virtuoso Data Backup & Recovery]]
id
  • 8289adfcfb9eb215db2e2b1935cde0e8
link
has container
http://rdfs.org/si...ices#has_services
atom:title
  • VirtTipsAndTricksGuideTablesAndIndexesRecover
links to
atom:source
atom:author
atom:published
  • 2017-06-13T05:36:10Z
atom:updated
  • 2017-06-13T05:36:10Z
topic
is made of
is container of of
is link of
is http://rdfs.org/si...vices#services_of of
is links to of
is creator of of
is atom:entry of
is atom:contains of
Faceted Search & Find service v1.17_git132 as of May 12 2023


Alternative Linked Data Documents: iSPARQL | ODE     Content Formats:   [cxml] [csv]     RDF   [text] [turtle] [ld+json] [rdf+json] [rdf+xml]     ODATA   [atom+xml] [odata+json]     Microdata   [microdata+json] [html]    About   
This material is Open Knowledge   W3C Semantic Web Technology [RDF Data] Valid XHTML + RDFa
OpenLink Virtuoso version 08.03.3332 as of Sep 11 2024, on Linux (x86_64-generic-linux-glibc25), Single-Server Edition (15 GB total memory, 2 GB memory in use)
Data on this page belongs to its respective rights holders.
Virtuoso Faceted Browser Copyright © 2009-2024 OpenLink Software